Slowly Changing Facts?

View previous topic View next topic Go down

Slowly Changing Facts?

Post  Mike123 on 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

View user profile

Back to top Go down

Re: Slowly Changing Facts?

Post  hkandpal on 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

View user profile

Back to top Go down

Snapshot

Post  elmorejr on 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

View user profile

Back to top Go down

Re: Slowly Changing Facts?

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