Saturday, June 1, 2013

[SQL Server Data Warehousing] DW Newbie Question - Updating fact table when have type 2 dimension


I'd suggest instead of updating the fact table and throwing away the natural partitioning you get with the historical surrogate key, you can add the dimension's durable key (non-changing natural key) to the fact table in addition to the existing dimension surrogate key.  With this design, you can slice the facts both ways: according to dimension's historical values (join to dim on surrogate key) and according to current values (join to current view of dim on durable key).


There's a good Kimball Design Tip on this topic here:


http://www.kimballgroup.com/2012/07/10/design-tip-147-durable-super-natural-keys/


Let me know if that helps.



Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com



.

social.technet.microsoft.com/Forums

No comments:

Post a Comment

Search This Blog