Using date as part of PK .. caveats?
3 posters
Page 1 of 1
Using date as part of PK .. caveats?
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...
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...
samalex- Posts : 2
Join date : 2012-01-25
Location : Waco, Tx
Re: Using date as part of PK .. caveats?
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.
Re: Using date as part of PK .. caveats?
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
Similar topics
» Date Dimension: Representing partial dates/Imputing date values
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Initial date of effective date column for SCD 2 implementation
» scd2 effective date, end date data type
» Always link date fields to Date Dimension?
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Initial date of effective date column for SCD 2 implementation
» scd2 effective date, end date data type
» Always link date fields to Date Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|