Ideal setting for DM load in compose

[garyclark]

Hi Team,

What is the ideal setting for DM load in compose? Can we select incremental option to perform full and incremental load?
or should we use full load option during initial load and then switch back to incremental?
Also I see an option "Optimize initial load" when you we select this option?
We are using queries are source in our DWH load and current compose version does not support CDC option if we use queries as source so will this a problem during DM load? 
Example: Lets say we have a query which loads the data into table_A and lets assume that query returns 5 records. First when we run the full load all these 5 records will be loaded into DM, next time when we run DM with incremental option selected will it tries to these 5 records again? as runno_inser and runno_update for these fields is being updated?

[TimGarrod · Accepted solution]

Hi Gary - when you configure Compose DM loads to perform "incremental loads" - the FIRST load is a FULL load. 

Subsequent loads will look to determine which records have changed (using the runno_update column in the DWH as you alluded to).     
Even when you perform query based mappings - Compose is determining if there is a change in the business attributes when applying to the DWH structures.   

For example - DWH Load1 inserts 10,000 records into the DWH. 

   DMLoad1 runs - loads the 10,000 records into the dim / fact table
 In DWHload2 the query returns the same 10,000 records, but only 5 of them have actually changed - Compose will only "update" or type 2 the 5 records (the ETL processes detect the changed records).  

DMLoad2 runs - Compose will know that 5 records were changed and will process those into the DM / DW. 

If you are completely re-writing ALL data EVERY DAY with only TYPE1's- i.e. all data is loaded and updated as some business value changes every single day, then doing a FULL LOAD only in the Data mart may make sense.   However typically incrementally loading the data mart is a better method as Compose will determine the changes for dimensions and transactional fact tables.  (Note that State-Oriented and Aggregate facts are always recreated). 

Hope that helps!

[Nomaan]

Hi Tim, 

The FIRST load with DataMart configured for "incremental loads" is generating very in-efficient queries. We have instances were it is trying to hold millions of records in memory as part of sub queries ... as a result the first load runs for hours and still doesn't complete. 

Is datamart load configured to change the query for first (full) and consecutive (inc) loads when you confirgure DM loads to be incremental?

[TimGarrod · Accepted solution]

The queries for the "first load" when designated as incremental are different to the actual incremental load processes.  You can see this by looking at the generated statements and the "Runtime Clause".  The runtime clause designates if the specific statement should run or not. 

For example - you can see below the "intial load of dimension..." which does a straight insert into the dim. versus determining changed data and updating the dim  when the RowCnt >1.  (RowCnt = 1 is there because Compose deploys the "unknown" dimension member when creating the tables. 

[Dana_Baldwin]

Hi @Nomaan 

Did Tim's reply help? If so, please mark it as an accepted solution.

Thank you!