Skip to Content

Delta load for fact table.

Good day everyone.

I'm new to SAPDS, starting to build a DW from a retail sales system.  What is the "best/must used/common" method to make the delta load of the fact table.

I've done delta load for dimension tables using table comparison, but I believe table comparision for fact tables is not a good idea.

Thanks in advance.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 06, 2015 at 03:11 PM

    Hi Emerson,

    why do you think, table comparison is not a good idea?

    If you want to have a delta load and you do not have any CDC support from the database, you have to use the table comparison (or create your own comparison in a custom transform or validation).

    However, do you really need a delta load on the fact table? It is much easier to create a full load.

    Regards

    Severin

    Add comment
    10|10000 characters needed characters exceeded

    • Hello Dirk,

      Thanks for your reply and sharing the links, it will help to better understand DS.  Will give it a try to the table comparison transform. 

      For this project target database is IQ 16 and initial load of fact sales data is about 15 million rows. So I'll see if performance is acceptable or will try one of the other methods mentioned here.

      Regards.

  • Jan 07, 2015 at 11:42 AM


    table comparison for fact table? never ever, table comparison is effective, but not that much. What if you FT consist of 1 billion or more rows? Your DBA will not be happy, if you compare data based on index column, then yes, you can achieve good results.  Best option how to implement delta load for fact table is to fix the timestamp of fact table and load anly those where Timestamp is greater than one fixed in fact table.

    So.

    1. Create a Local variable in script and find the Max timestamp value (there should be one)... select max(timestamp_col) from MyFDacttable

    2. Then create DF which extracts data from Source system to target and add this vavariable to WHERE tab of Query.

    By implementing this you will extract only those values where timestamp is greater than existing one... You can implement the same for multiple columns

    Add comment
    10|10000 characters needed characters exceeded

    • Another possibility is to use the CDC transformation. But you need a supported database (e.g. ORACLE or SQL Server) and have to activate CDC in the database.

      The advantage is, that the DS just loading the new or different rows. The disadvantage is, that the database need a bit more time to insert / update or delete database rows.