Hybrid SCD dimension modelling?

[vvij]

We have an absurd requirement for a particular dimension attribute. Personally I have given up, so thought if someone on this forum could help.

1. Consider a simple fact containing revenues of stores with time. Time and store are only two dimensions
2. Now store dimension has an attribute store_type. Store_type follows a business rule - for first 13 months a store is NEW and after that it is called COMP store.
Let's say a store opened in Jan 2014, so accordingly to the rule above:
From Jan 2014 - Feb 2015, store is NEW
From Mar 2015 onward, store is COMP
I followed SCD type 2 implementation for the store dimension.

Absurd requirement - consider creating a report showing revenues across store_types across different time periods
A report from Jan 2015 - Jul 2015 will show revenues across both NEW and COMP --> FINE
A report from Jan 2014 - Jul 2014 will show revenues across NEW --> FINE
A report from Jan - Jul for both the years (on columns) will show revenues across both NEW and COMP --> this is where business is asking for something else. They say as the store was COMP from Mar 2015 onward, therefore while comparing with last year it should also show COMP from Mar - Jul in 2014! Similarly report should show it as NEW from Jan - Feb for both 2014 and 2015

Can this be achieved? Maybe using hybrids?

[nick_white]

I would implement this as a calculated field in your BI tool (or as a DB view if you are not using a BI Tool).
This type of business logic sits much better in the UI then in your data model - where it is much easier to change when your business users change their minds (which they always do!). Plus, once you have shown you can implement this type of logic once they are sure to come up with other similar scenarios and you don't want to have to keep updating your data model with business logic

[vvij]

I understand that such requirements should not be modeled at data design level. I am not entirely sure how to handle the logic in the UI or in the BI tool. We will be using Mondrian OLAP and as of now I am unsure how deal with the requirement. Did you mean that we will have to process the MDX result-set and tweak it for presentation?

[gvarga]

You can solve it on data design level in a tricky way:
In the fact table Month_Key has to be present. ( I think this is already an aggregate table with
Month_Key instead of Day_Key)
Besides the normal SCD2 type Store dimension you will have to create a MONTH_STORE table in the following way:
Month_Key, Store_Key, Store_Name, Store_type (NEW/COMP), Comparative_Store_type.

Let’s see the rows in your example: (Month_Key starts by 1)
1,100,Store A,NEW,NEW
2,100,Store A,NEW,NEW
3,100,Store A,NEW,COMP
4,100,Store A,NEW,COMP…..

13,100,Store A,NEW,NEW
14,100,Store A,NEW,NEW
15,101,Store A,COMP,COMP
16,101,Store A,COMP,COMP…

For the special reports you will have to use this MONTH_STORE dimension(?). You will connect to the fact table by Month_Key & Store Key and in the reports  Comparative_Store_type will be used.

[vvij]

Thanks gvarga
What you suggested will definitely help. I'll try to implement it. Thanks again

[Sponsored content]