Hybrid SCD dimension modelling?

View previous topic View next topic Go down

Hybrid SCD dimension modelling?

Post  vvij on Mon Jul 20, 2015 8:53 pm

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?

vvij

Posts : 7
Join date : 2015-07-13

View user profile

Back to top Go down

Re: Hybrid SCD dimension modelling?

Post  nick_white on Tue Jul 21, 2015 3:40 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Hybrid SCD dimension modelling?

Post  vvij on Wed Jul 22, 2015 1:49 am

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?

vvij

Posts : 7
Join date : 2015-07-13

View user profile

Back to top Go down

Re: Hybrid SCD dimension modelling?

Post  gvarga on Wed Jul 22, 2015 12:46 pm

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.

gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: Hybrid SCD dimension modelling?

Post  vvij on Wed Jul 22, 2015 9:30 pm

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

vvij

Posts : 7
Join date : 2015-07-13

View user profile

Back to top Go down

Re: Hybrid SCD dimension modelling?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum