Slowly changing fact with SCD2 Dimensions

View previous topic View next topic Go down

Slowly changing fact with SCD2 Dimensions

Post  Rilib on Thu Apr 18, 2013 4:09 pm

Hi guys,
I have an interesting business requirement and would like to ask if anybody had such experience. It looks like a audit case but reporting requirements is a bit different.
We have a slowly changing fact table which has start and end dates like a scd2.
This fact table has SCD2 dimensions around.

Business requirement is to have queries which can achieve
1- fact and dimension information on the exact fact transaction time which is typical situation
2- fact information with corresponding dimension information for any given time
i.e. for situation 2

Dimension
ID KEY value Start End
1 A Q 01.01.2013 01.02.2013
2 A Y 02.02.2013 31.12.9999

Fact
id_dim FACT_PK measure Start End
1 X 50$ 15.01.2013 16.02.2013
2 X 70$ 17.02.2013 31.12.9999

so when they query the fact for the date 14.02.2013 they want to get the dimension value Y which is active at that point for scenario 2 which I mentioned above.
they also want to get value Q for that date for scenario 1 above.

periodic snapshot is not that feasible because it will require daily snapshots and will generate a huge fact table.
I am thinking to put a super key in dimension table and self join dimension to itself to make SQLs for scenario 2
i.e.
ID sup_key KEY value Start End
1 1 A Q 01.01.2013 01.02.2013
2 1 A Y 02.02.2013 31.12.9999

so for scenario 2 date predicate should be given for both dimension and fact and fact fk should be joined to a view (or mview) which has self join between id and sup_key
anybody implemented such solution? How is the performance ?
Thanks in advance

Rilib

Posts : 3
Join date : 2013-04-18

View user profile

Back to top Go down

Re: Slowly changing fact with SCD2 Dimensions

Post  LAndrews on Thu Apr 18, 2013 4:49 pm


You may want to revisit the requirements

That said, assuming the requirements are sound, you probably don't want to change your data model to address the request.

Adding a secondary key to the fact won't solve the problem - you wouldn't be able to manage the situation of multiple dimension changes for the same fact record.

Probably the simplest way to achieve this is to self join the dimension on the natural key (e.g. A=A0 , and then as you mentioned, use a date filter on both the dimension and the fact.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Slowly changing fact with SCD2 Dimensions

Post  Rilib on Thu Apr 18, 2013 5:24 pm

Thanks for your opinion.
yeah that one will achieve the same if the natural key is not a composite one
My main concern is how the query performance for such model? Case 1 is simple and usual life thing but auditing an exact moment with self joined dimension, I never experimented it yet..

Rilib

Posts : 3
Join date : 2013-04-18

View user profile

Back to top Go down

Re: Slowly changing fact with SCD2 Dimensions

Post  ngalemmo on Thu Apr 18, 2013 8:58 pm

The thing with a Type 2 dimension is, you can retrieve any version of the Type 2 row you want, regardless of the Type 2 key maintained on the fact.

For example, the basic pattern to retrieve the current row from a Type 2 is to perform a self-join on the dimension table using the natural key and locate the current row (usually identified by a current flag). This pattern also works for any other row version you may want by selecting based on the effective period of the row.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Slowly changing fact with SCD2 Dimensions

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