Concatenate/Union multiple source table (landing) into 1 "Fact" table

[bilzekek]

Hi,

I am new to Qlik Compose and is playing around in the software.

I have 1 Replicate task (CDC, apply changes) that is transferring data from the 5 source tables to landing zone on DWH (MS SQL). I want to combine/Concatenate these 5 tables into 1 Fact table, preferably using CDC ETL (i.e. utilizing that the data is flowing from Source to Landing by Replicate - so keep the data flowing up till the data mart).

In the Model step, I have defined the 1 Fact table.

In the DWH step, it seems I have to use the "Query" option in Source type.

This doesnt seem the correct approach as it does not seem to exploit the CDC features of Compose (?). Am I designing this the right way? 

Or, would you keep the data normalized in the DWH and instead de-normalize into 1 Fact table in the Data mart step? And if so, how would you do this

Thanks.

[TimGarrod · Accepted solution]

Hello, in general, your approach is correct.  Curate and map the data to a conformed model  - so that the data mart is automated and can be built by developers or even analysts as required. 

Sound like your requirement is a UNION ALL and there are 2 approaches to this in Compose that support the CDC features. 

• Option 1 - Multiple Mappings
  
  
  • Create a mapping for source source table to the target DWH model  /structure
  • These mappings can be run in their own Task or you can combine them into a single task
  • When in a single task - Compose will load all the data from the 5 source tables into the TSTG_XXX table (this is a staging table to get a static set of data to process). 
  • Then Compose will process the entire data set together. 
  • This simulates a UNION ALL approach and CDC processing from _CT tables is fully supported. 
  • Best used when you have only a few tables in the model that require multiple sources mapped in this UNION all manner. 
  • Best used when source tables are not the same structure

  • Assigning them to the same task just requires selecting the task and checking the checkbox. (for CDC tasks make sure you check the "Handle Duplicates" box also.)

    

Option 2  - Uniform Source Consolidation

• Compose has a feature in the February SR release known as Uniform Source Consolidation. (https://help.qlik.com/en-US/compose/May2022/Content/ComposeDWDL/Main/DW/Data%20Warehouse/modify_etl_...)
• This is useful for customers who have multiple sources with the exact same source structure, where you wish to re-use a mapping or set of mappings across multiple sources without duplicating the mapping. 
• In this case - you have 5 landing connections in the Compose project. 
• 1 mapping to the target table.   
• You will have to put just the mappings that require the "UNION ALL" approach, and configure the task to consolidation all 5 schema's by selecting them in the Task setting. 
• Compose will generate the code to look at the 5 landing schema's within the task automatically. 
• This removes the need to duplicate the mappings. 
• This requires that the source tables all have the same metadata (table name, column name) but can be in different source databases / schema's 

Hope that helps!

[bilzekek]

Hi @TimGarrod ,

I will try Method 1 for my case here - thanks!

A few follow up questions:

• You mention that "Best used when you have only a few tables in the model that require multiple sources mapped in this UNION all manner". Do you mean that I don't have too many Entities defined in the Model step, where I want to use this approach? Because I plan too try this for 3 Entities (for now, hehe)
•  Furthermore. I have only set the Replicate task to "apply changes" (So not the Store changes). Is Compose able to do CDC ETL? (as I understand it the __CT tables are deleted / not present in target). 

[TimGarrod]

1-  You can do this for as many entities in the model as you wish.   What I mean here is if you only have a few to do this with - then go ahead and duplicate mappings.   If you had 20 tables that were the same in each source - mapping to 20 entities in your model, then Uniform Source Consolidation (Option 2) may be better. 

For Compose to process changes - you must set Replicate to "Store Changes" also.   Compose "CDC" tasks read the CT data to grab only changed records and process them.    If you do not do this - you CAN run "full load" tasks in Compose and Compose will detect the differences at runtime between the tables Replicate loaded and your DW.   However, be aware that in order to do this, Compose will load data the entire data set to TSTG (so its a static copy to work off) - and then it will process the entire data set - comparing it to the DWH to see what is new / changed.   

If you use the CT tables - its typically more performant as only the changed records are in the CT table.  (Compose then has settings to Archive, Delete or Keep the changes in the CT tables). 

[bilzekek]

Make sense, thank you very much for the answer.