Skip to main content
To homepage To homepage
Solved

Proper way to incrementally load from Business Unit layer to Data Warehouse layer

  • February 26, 2026
  • 6 replies
  • 31 views
G
Contributor
Forum|alt.badge.img

I have 3 big fact tables in the source which i load incrementally. Primary keys are CompanyID and EntryNo (applicable for each table separately, incrementing), the records in these fact tables are never deleted.

I need to merge these tables to a single table in DSA layer also incrementally. I think of following setup:

  • Make the DSA table also incremental, check “Don’t handle deletes” (as the records are never deleted in source)
  • Include both CompanyID and EntryNo as primary keys in the table;
  • Incremental Selection Rule → EntryNo > (Last max value);
  • Map all tables from source in this single DSA table

 

The result which i expect is every time DSA table is executed - it incrementally loads data from 3 related fact tables - based on EntryNo for each separate CompanyID (one CompanyID per one source table). This way i have one huge table containing all the records for all 3 “Companies”. Am i correct to assume this is the setup i need? Are there any risks with possibly missing or overwriting the data?

 

Thank you

Best answer by devin.tiemens

Hi,

I think this setup should meet your requirements. In particular, adding CompanyID to the primary key will enforce uniqueness across the three companies.

One question to confirm: is EntryNo always increasing within each individual table?

Kind regards,
Devin

6 replies

devin.tiemens
TimeXtender Xpert
Forum|alt.badge.img+6
  • devin.tiemens
  • TimeXtender Xpert
  • Answer
  • February 26, 2026

Hi,

I think this setup should meet your requirements. In particular, adding CompanyID to the primary key will enforce uniqueness across the three companies.

One question to confirm: is EntryNo always increasing within each individual table?

Kind regards,
Devin

G
Contributor
Forum|alt.badge.img

Hi Devin, thanks for swift reply. Yes that is the case. Originally all 3 tables have EntryNo increasing independently by 1 each time a record appears. I added CompanyID in each separate table as a fixed value just for the purpose of creating uniqueness (CompanyID+EntryNo). CompanyID 1 is in 1st table, CompanyID 2 is in 2nd table etc.

 

devin.tiemens
TimeXtender Xpert
Forum|alt.badge.img+6
  • devin.tiemens
  • TimeXtender Xpert
  • February 26, 2026

Perfect, this should work!

R
Problem Solver
  • RLB
  • Problem Solver
  • February 27, 2026

I'm curious. If the last entryNo in table 1 is higher than the one in table 2, aren't you going to miss any new records from table 2 on the next incremental load?

Maybe I'm not understading some basic functionality here :)

G
Contributor
Forum|alt.badge.img

​@RLB 

If i am not mistaken, that is what i include CompanyID to primary key for. If the CompanyID was not present as a part of primary key, then it would be as you say. At least thats what my testing showed. If someone from TX could confirm it would be great

    rory.smith
    TimeXtender Xpert
    Forum|alt.badge.img+8
    • rory.smith
    • TimeXtender Xpert
    • February 27, 2026

    Hi,

    if you are using TDI with Ingest or 20.10.x with ODX Server and have incremental load set up there, you should be loading incrementally to DSA by default. You don't need to set up an explicit incremental selection rule.

     

    In 20.10.x with Business Units you would need to explicitly set up incremental load to the DSA, but would do that using DW_Timestamp as there is no data modification in the BU so ‘perfect’ incrementals can be used,

    You can check the setup in the _I table by running a preview on your DSA table and using the pulldown in the bottom left. You should see separate Entry No_ entries in that table for this to work, otherwise a quickly rising Entry No_ in one table will mask records in the other two.

    Terms and ConditionsCookie settingsAccessibility statement