GocalF
Programmer
- Dec 6, 2012
- 1
Hello,
I am having an issue with slowly changing dimensions Type II in MSTR. My dimension table is updated about once a week (so I guess it would be a quickly changing dimension, lol) and when user's run reports in MSTR, they see multiple records for the same store.
The dimension table consists of a:
[surrogate key],
[Store ID],
[store description],
[zone id],
[end effective date]
(there are more fields, but for simplicity I will limit the fields).
The only way my fact tables relate back to which store the items were sold from is by the surrogate key (store_key).
Everything was running smooth until I started updated the DIM table, now my reports show the same store_id and description multiple times.
My MSTR environment has a SQL Server 2008 R2 backend, and is pointed to views. I created a new view with everything in the current DIM table EXCEPT the surrogate key. Then I modified the hierarchy in MSTR so that all the parents of the store attribute would flow through the newly generated attribute (which is pointed at the new view). I also assigned the appropriate Parent/Child relationships to this newly created attribute.
This has worked for me on some reports, but on reports that pull revenue, per store, my report fails. The fact table with revenue only has a store_key relating back to the dimStore table, and my newly created attribute is pointed at a new table that does not have the store_key.
Maybe I have tried to explain this too much and made this thread too long... but the question is - How do you deal with SCD type II in MSTR, when you want to SUM the sales of a duplicated attribute?
The only info I could find online is how to report on 'AS IS' or 'AS WAS'. Meaning creating two attributes, one pointed to the old record, and another pointing to the new. But this seems like a maintenance nightmare.
I am having an issue with slowly changing dimensions Type II in MSTR. My dimension table is updated about once a week (so I guess it would be a quickly changing dimension, lol) and when user's run reports in MSTR, they see multiple records for the same store.
The dimension table consists of a:
[surrogate key],
[Store ID],
[store description],
[zone id],
[end effective date]
(there are more fields, but for simplicity I will limit the fields).
The only way my fact tables relate back to which store the items were sold from is by the surrogate key (store_key).
Everything was running smooth until I started updated the DIM table, now my reports show the same store_id and description multiple times.
My MSTR environment has a SQL Server 2008 R2 backend, and is pointed to views. I created a new view with everything in the current DIM table EXCEPT the surrogate key. Then I modified the hierarchy in MSTR so that all the parents of the store attribute would flow through the newly generated attribute (which is pointed at the new view). I also assigned the appropriate Parent/Child relationships to this newly created attribute.
This has worked for me on some reports, but on reports that pull revenue, per store, my report fails. The fact table with revenue only has a store_key relating back to the dimStore table, and my newly created attribute is pointed at a new table that does not have the store_key.
Maybe I have tried to explain this too much and made this thread too long... but the question is - How do you deal with SCD type II in MSTR, when you want to SUM the sales of a duplicated attribute?
The only info I could find online is how to report on 'AS IS' or 'AS WAS'. Meaning creating two attributes, one pointed to the old record, and another pointing to the new. But this seems like a maintenance nightmare.