none
Snapshot of Fact Table

    Question

  • I'm working on a snapshot of a Fact. The snapshot's purpose is to record changes of columns x,y, and z. If either x,y, or z change in the Fact, a new row should be added to the Snapshot and flag columns for the three columns should read Y or N (depending on which of the three columns changed).

    My approach was to simply compare columns x,y, and z between extract date and dates from the day before(could be mins or secs before, nonetheless, I just want a variance in time).

    My thought was to use a CASE statement in the source query:

    SELECT CASE WHEN [x] <> (SELECT [x] FROM dw.dbo.FCT_TABLE WHERE LAST_MOD_DATE = DATEADD(DAY,-1,[LAST_MOD_DATE])) then 'Y' else 'N' end as FLAG_X 

    But I don't seem to be getting the correct results. Any suggestions? Ultimately, I will apply the logic to SSIS


    • Edited by Rookie3000 Friday, February 07, 2014 12:00 AM Forgot some essential details
    Thursday, February 06, 2014 11:58 PM

All replies

  • This WHERE clause

       WHERE LAST_MOD_DATE = DATEADD(DAY,-1,[LAST_MOD_DATE])

    will of course never match any rows.

    Supposedly, you mean LAST_MOD_DATE from two different tables, but you need to tell the computer that. Computers are not very smart.

    Also, what data type is LAST_MOD_DATE? If it's datetime or datetime2 and includes a time portion, your changes for a hit are bad.

    If x can be NULL, the logic needs to be more sophisticated.

    You could of course also consider Change Data Capture (if you are on Enterprise Edition) of Change Tracking (any edition), but it may be too heavy artillery, if this is your only snapshot.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, February 07, 2014 9:32 AM
  • X won't be NULL is any of our cases...

    I want to default the flags to 'N'...then conduct lookups on x,y, or z based on the keys in the FCT and FCT Snp. If the Keys match but x,y,z don't then I'll insert a new record with 'Y' for the values that have change. What do you think of this approach?

    Friday, February 07, 2014 4:57 PM
  • Since fact tables are usually large, it does not sound overly efficient, but maybe it doesn't matter. In any case, I know too little of what you are doing to be prepared to have any opinion at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, February 07, 2014 10:48 PM