Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
Hope that helps!
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.
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
Hope that helps!
Hi @TimGarrod ,
I will try Method 1 for my case here - thanks!
A few follow up questions:
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).
Make sense, thank you very much for the answer.