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

Fact-incremental approach

Go down

Fact-incremental approach Empty Fact-incremental approach

Post  SathyJaanu Tue Dec 29, 2015 2:16 pm

My source table would always be a Trucnate and load. My Dim is SCD2. MY fact will have an incremental load.

Can you please let me know which would be the ideal approach for getting the desired result



Day1:

StgActivity Table:{truncate and Load}
ActivityID ProjectID ActivityName ActivityVal  

1          1         TestData1      100    

DimActivity
ActivityKey ActivityID ActivityName Active

100         1          TestData1    1

FactActivity
ActivityKEy    ActivityValue

100              100

Day2:

StgActivity Table:{truncate and Load}
ActivityID ProjectID ActivityName ActivityVal  

1          1         TestData2      100    

DimActivity
ActivityKey ActivityID ActivityName Active

100         1          TestData1     0
101         1          TestData2     1

FactActivity
ActivityKEy    ActivityValue

100             100
101             100

Day3:

StgActivity Table:{truncate and Load}
ActivityID ProjectID ActivityName ActivityVal  

1          1         TestData1      105    

DimActivity
ActivityKey ActivityID ActivityName Active

100         1          TestData1     0
101         1          TestData2     1

FactActivity
ActivityKEy    ActivityValue

100              100
101              105

SathyJaanu

Posts : 20
Join date : 2015-10-07

Back to top Go down

Fact-incremental approach Empty Re: Fact-incremental approach

Post  zoom Wed Dec 30, 2015 11:37 am

Is your example for desired approach or it is for current situation?

Anyway, if your fact is incremental load then the last data state of your fact should be as:

FactActivity
ActivityKEy    ActivityValue

100              100
101              100
101              105

You need a date dim to add in the fact table so you know when you get ActivityValue.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

Back to top Go down

Fact-incremental approach Empty Re: Fact-incremental approach

Post  nick_white Wed Dec 30, 2015 1:44 pm

Just wondering if there's a typo in your example? On Day 3 the ActivityName = TestData1 so your DimActivity should look like this (I'm assuming this is an SCD Dim and has eff start and end dates, etc):

DimActivity
ActivityKey ActivityID ActivityName Active

100 1 TestData1 0
101 1 TestData2 0
102 1 TestData1 1

nick_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Fact-incremental approach Empty Re: Fact-incremental approach

Post  ngalemmo Wed Dec 30, 2015 2:38 pm

To summarize the problem:

You have a snapshot fact table that has Type 2 dimension keys as part of the primary key.  Changes to the type 2 value prevent you from properly updating the snapshot row as you can no longer locate the row you need to change.

Basically, you need a stable primary key.  You can do this by adding the natural key columns to the fact (in this case activity_id).  However, since natural keys can be large and inefficient to index, a more common approach is to maintain a persistent stable surrogate key in the dimension (i.e. a Type 1 key) and store both in the fact.  You use the persistent key as the primary key and update the type 2 value in the row.

Maintaining a persistent key is simple.  Add a column for it in the dimension.  When you add a new natural key, set the persistent value to the assigned type 2 key.  As the row is updated, carry forward the persistent value on each new version of the row.

There are a couple of other styles of fact tables you can use to represent the data (accumulating snapshot, transactional) but they all need stable primary keys to function properly.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Fact-incremental approach Empty Re: Fact-incremental approach

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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