September 24, 2016 at 2:37 pm
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
September 25, 2016 at 5:08 am
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?
September 25, 2016 at 2:37 pm
Okay, let me approach this from a different angle, and maybe I can think this out.
The sale would always be associated with a given customer and the salesperson at the time of the sale. So one option would be to store the SalespersonID in the Sale fact. I could create a new record for each customer when that customer is assigned to a new territory, and keep the original key in the Customer dimension.
I guess it's time to read up more on SCDs...
September 26, 2016 at 7:30 am
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.
It seems to me that the originally proposed Type 2 SCD would accomplish the task. What is different between 2 and 6 that makes the difference for you? You are a little ahead of me inasmuch as I'm very familiar with SCDs 1-3. I've scanned Kimball's 3rd addition book and he added additional SCDs that I have not yet dug into. But in this case the more traditional would seem to be adequate. What are you seeing to say otherwise?
//Edited for punctuation
September 26, 2016 at 3:33 pm
If you want to preserve a static definition of territory over time, I am guessing this is by your definition and not the clients. I think of having a extra dimension titled [Geography] that will list this and the other one named as [SalesTerritory] so as to make the meaning of territory more apparent. I would not not also assume that the two are one to one as a sales territory can cross many of your geographies. That is why I would break this out to keep them independent definitions.
----------------------------------------------------
September 26, 2016 at 4:10 pm
I think that will work... Gotta re-read that part of the book. A few times. and then try it, and then read a few more times. And then maybe read the relevant part of Star Schema Complete Reference...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply