Fact-incremental approach

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

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.

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

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.