Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Slowly Changing Facts?

3 posters

Go down

Slowly Changing Facts? Empty Slowly Changing Facts?

Post  Mike123 Sat Dec 10, 2011 4:55 am


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

Mike123

Posts : 1
Join date : 2011-12-10

Back to top Go down

Slowly Changing Facts? Empty Re: Slowly Changing Facts?

Post  hkandpal Sat Dec 10, 2011 4:38 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

Back to top Go down

Slowly Changing Facts? Empty Snapshot

Post  elmorejr Mon Dec 12, 2011 10:07 am

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...

elmorejr

Posts : 25
Join date : 2011-10-20
Location : Columbus, Ohio, United States of America, Earth, Sol

Back to top Go down

Slowly Changing Facts? Empty Re: Slowly Changing Facts?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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