pietlinden (9/24/2016)
Good news! Easy question...I have a simple data warehouse. Looks pretty much like your standard sales data warehouse with dimensions (why do I want to write "Dementias"??) like:
Customer
Product
Calendar
Territory...
The only weird part is that the Customers, which are hospitals and clinics can be "redistricted" every year. So some will be assigned to a new territory. Screws up the rollup if you want to compare a static definition of a territory over time.
Just to be clear, in Year 1, Customer A could be in Territory T1, and then the next year, it could be assigned to T2.
So how would i have to structure my Fact table and Customer and Territory tables to do this? Expire the Customer and do a typical Type 2 SCD? (Shows how well I understand slowly changing dimensions, I guess!)
Thanks!
Pieter
Which method to use is dependent on the business requirements and definitions, i.e. does the activity move with the customer? etc.
My approach would normally be to use a Type 6 SCD with both the surrogate and the natural key in the fact table which covers (almost) all schenarios.
😎
Question, are you using cubes or tabular for this?