Fact-incremental approach

View previous topic View next topic Go down

Fact-incremental approach

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

View user profile

Back to top Go down

Re: Fact-incremental approach

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

View user profile

Back to top Go down

Re: Fact-incremental approach

Post  nick_white on 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 : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Fact-incremental approach

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact-incremental approach

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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