Slowly Changing Facts?

Hi,

I know the classic Kimball approach, where you have fact tables and dimension tables with different types of SCD.

My current customer has a "fact table" which should be of Slowly Changing Type 2. I never had that requirement, but the fact tables contains for example expected sales values for the next year, etc, which can change. There are several dimensions related to that fact table. Based on the DWH there are reports, with the requirement, that the user can enter a date, and the reports shows exactly the result how it was at that time.

If i want to use surrogate keys, I actually have to "cut my fact table into pieces" any time when a value of a related SCD2 dimension table changes and add the new surrogate key of my dimension to my new fact record.

I just wonder if there is any theory or best practice approach for such an architecture, i didn't find anything on the internet.

Hope you can give me some hints!

Thanks

Hi,

How frequently there are changes in the fact table?

Did you think of the accumulating fact approach.
http://www.kimballgroup.com/html/designtipsPDF/DesignTips2002/KimballDT37ModelingPipeline.pdf

thanks

From your requirements, sound like you need a snapshot. Each day (week, month, quarter, etc), load data into the fact table with a snapshot date. The fact will record the value of the expected sales at the time of the snapshot.

Read more here...

http://books.google.com/books?id=XoS2oy1IcB4C&lpg=PT162&dq=kimball%20periodic%20snapshot%20fact&pg=PT161#v=onepage&q&f=false

However, if you are mixing your expected sales metrics alongside actual transactional data, then you may have to revisit your design. For example, you do not want to have the following in a single transactional fact:

Salesperson Key
Sales Day Key
Sale Amt
Unit Qty
Expected Sales For Year Amt

A transactional Sale Amt and the Expected Sales For Year Amt are different grains...

A little more explanation from your end may help clear up the question...