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

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