Using date as part of PK .. caveats?

View previous topic View next topic Go down

Using date as part of PK .. caveats?

Post  samalex on Mon Feb 06, 2012 11:07 am

We mainly archive our data on the 1st of each month after our 'end of month' processes are complete, and that's when I plan on loading data into our DW, which I'm just now planning. With this every dimension table will basically be a monthly snapshot of our OLTP database. Though SCD Type 2 kind of matches this we really don't need the End Date and ideally I'd like to use the month end date along with the account number together as the primary key to link everything together since in most cases we'll never aggregate data between monthly data.

I've yet to see any models that follow this methodology, but in our situation it seems like the best fit and it's basically what I'm doing now in my data mart. But i'd like insight from anyone with experience working in a similar environment if there are caveats or problems that may arise or if there are better options.

Thanks in advance...
avatar
samalex

Posts : 2
Join date : 2012-01-25
Location : Waco, Tx

View user profile

Back to top Go down

Re: Using date as part of PK .. caveats?

Post  ngalemmo on Thu Feb 09, 2012 12:27 am

Do you mean the PK of the fact table? It's usually referred to as the grain or 'base grain' of the fact, but PK is fine too. Declaring a PK is more for documentation than anything else. Usually, PK constraints are not enforced in the physical model because it slows loads down and is redundant to rules used in the ETL process. However, some database systems benefit from such declarations but you may not need to enforce it, so your experience may vary.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Using date as part of PK .. caveats?

Post  hang on Thu Feb 09, 2012 1:30 am

What you are planning to do is not data warehousing in dimensional sense. If you want to follow proven best practice to build a data warehouse, firstly you need to introduce surrogate key into your dimensions as primary key, secondly you need to use SCD process to track the dimension changes to replace the snapshot change tracking. I suggest to have a read of Kimball's dimensional modeling book before your modeling attempt for your data mart.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Using date as part of PK .. caveats?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum