To homepage To homepage
Solved

Incremental Load ODX and DSA with multiple Data sources set up - DSA load takes forever

  • 12 February 2024
  • 2 replies
  • 42 views
K
Rank
Userlevel 2

TimeXtender Version 20.10.35.64

I'm integrating 6 SQL databases/sources from the same ERP at the moment and want to set up incremental loading on my data source & DSA. I followed the basic steps but it seems like refreshing the incremental DSA tables takes way longer than a full load.

The ODX datasources incremental LOAD tasks from all 6 data sources finish under 60 seconds, but the DSA load for e.g. AP_Products table now takes almost 15 minutes, for ± 40.000 rows, against 3 minutes for a full load.

 

 My Last modified DateTime column is ‘Date_Captured’ and is applied on the tables below. For all the 4 tables the PK's are set up.

 

 

In the DSA I included column ‘Source’ in every table, which represents DW_SourceCode, to the PK of the tables to have an unique identifier per row.

 

However, it looks like I'm doing something wrong or i’m missing a step, since it takes forever to refresh these DSA tables now

icon

Best answer by daniel 12 February 2024, 13:59

View original

2 replies

daniel
Rank
Userlevel 4
Badge +5

Dear @KCMT ,
your set up looks good to me. The only things that I Can think of are:

  1. You've set the ODX to check for updates and deletes, maybe checking updates and deletes not for just the seperate tables but it also needs to check the other sources in the union for the same / deleted PK's takes TX a long time?
  2. The Source column in a transforation: you've set the DW_SourceCode in there. Are you sure that this is the DW_SourceCode from the ODX Or a copy from the DSA DW_SourceCode? i'm not sure if the lineage of the data in the DW_SourceCode changes or stay the same between the layers. Anyway, maybe try to set the DW_SourceCode as PK and not the new column Source (Containing the ‘Transformation’ of the DW_SourceCode)

Other things that poped in my head:

  1. Did you check the Error's and Warnings? Maybe it take TX a long time because it is checking all the PK's and finding (a lot of) duplicates?
  2. If Incremental takes such a long time, set it to full? I mean I get you that you want to know why this is happening, but I've seen this ‘issue’ happen on big tables (100 million + rows) and incremental, but then the Data Cleansing part takes a very long time. I've put in an idea here

Hope this helps

= Daniel

K
Rank
Userlevel 2

Dear @KCMT ,
your set up looks good to me. The only things that I Can think of are:

  1. You've set the ODX to check for updates and deletes, maybe checking updates and deletes not for just the seperate tables but it also needs to check the other sources in the union for the same / deleted PK's takes TX a long time?
  2. The Source column in a transforation: you've set the DW_SourceCode in there. Are you sure that this is the DW_SourceCode from the ODX Or a copy from the DSA DW_SourceCode? i'm not sure if the lineage of the data in the DW_SourceCode changes or stay the same between the layers. Anyway, maybe try to set the DW_SourceCode as PK and not the new column Source (Containing the ‘Transformation’ of the DW_SourceCode)

Other things that poped in my head:

  1. Did you check the Error's and Warnings? Maybe it take TX a long time because it is checking all the PK's and finding (a lot of) duplicates?
  2. If Incremental takes such a long time, set it to full? I mean I get you that you want to know why this is happening, but I've seen this ‘issue’ happen on big tables (100 million + rows) and incremental, but then the Data Cleansing part takes a very long time. I've put in an idea here

Hope this helps

= Daniel

Hi @daniel ,

I followed the steps in your comment and that seemed to do the trick, thanks!

  1. Set up Incremental Load Setup Rule to only handle PK updates, no PK deletes. Synchronize data sources & ODX.
  2. Included original DW_SourceCode System Field in Primary Key instead of custom field Source.Checked it for my table AP_Products, which did one longer load and only delta's afterwards.

Also made sure all the tables had the same Indexes configured, not sure if it mattered.

 

Reply


Terms and ConditionsCookie settings