topic Re: Replicate with Many source to One target in Qlik Replicate

Replicate with Many source to One target

Dumma — Wed, 10 Aug 2022 15:40:41 GMT

Hello,

I am looking for patterns that will allow us to solve for following use cases-

many source tables->one target table

many source tables ( across multiple databases)->one target table

*the source and target data models are different

*source is oracle, target is sql

thanks,

Re: Replicate with Many source to One target

lyka — Wed, 10 Aug 2022 15:59:02 GMT

Hi @Dumma

You can choose to store changes to all tables in a single Audit table (located in the target endpoint)

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/Audit%20Table/use_audit_table.htm#Using%20an%20Audit%20table

Thanks

Lyka

Re: Replicate with Many source to One target

Dumma — Wed, 10 Aug 2022 16:19:33 GMT

Thanks @lyka . How would i process the changes from Audit table to propagate into target? what is the best way to do this?

Re: Replicate with Many source to One target

lyka — Wed, 10 Aug 2022 16:34:38 GMT

Hi @Dumma

The audit table is created on the target endpoint. Yu can only use this for changes processing. So all changes will be recorded to the audit table. For full load, they will still have their own base table. What is your use case?

You may refer to this link that describes store changes and how you can use audit table:

https://help.qlik.com/en-US/replicate/May2022/Content/Global_Common/Content/SharedEMReplicate/Customize%20Tasks/store_changes_settings.htm

Hope this helps!

Thanks

Lyka

Re: Replicate with Many source to One target

Dumma — Wed, 10 Aug 2022 16:45:10 GMT

yes, i understand that Audit table will hold the changes on the source, however my question is how do i process the changes to the target from the Audit table? Is there anything in replicate or i need to implement process to do it? What are best ways to do this?

Other using Audit tables, is using logstream also one of the options?

My use case is I have multiple tables in my Oracle source db that i am trying to move to 1 table on SQL server target.

Thanks,

Re: Replicate with Many source to One target

lyka — Wed, 10 Aug 2022 16:48:35 GMT

Hello,

So what you need to do in Replicate is go to task settings > turn on store changes and then change the option Store Changes in to use Audit table

Thanks

Lyka

Re: Replicate with Many source to One target

Dana_Baldwin — Wed, 10 Aug 2022 18:20:53 GMT

Hi @Dumma

For the first use case, where all the tables are in one source database, you could use log stream. You would have one task writing all the tables to the log stream, and as many tasks as you have source tables to read from the log stream files and write to the target table. You would need to plan for how to manage potential collisions on your PK value.

Thanks,

Dana

Re: Replicate with Many source to One target

Michael_Litz — Wed, 10 Aug 2022 17:40:51 GMT

Hi Dumma,

Please check out this article and video that discusses many to one scenarios and let me know if it helps out.

Many to One task 2/25 Article and Video
https://community.qlik.com/t5/Knowledge/Qlik-Replicate-Many-to-One-Replication-Configuration/ta-p/1787230

Thanks,
Michael

Re: Replicate with Many source to One target

Dumma — Tue, 16 Aug 2022 13:32:48 GMT

Hello @Michael_Litz ,

The article is relevant in the case of the two source tables having the same schema. However in my use case, the tables don't have the same schema and are on two different databases. And I dont think using logstream also makes sense as the source tables are on two different databases.

Thanks,

Re: Replicate with Many source to One target

Barb_Fill21 — Fri, 02 Sep 2022 19:30:15 GMT

Hi @Dumma

For your Use case: "My use case is I have multiple tables in my Oracle source DB that I am trying to move to 1 table on SQL server target."

I agree with @Dana_Baldwin so,,, you have 1 DB (multiple tables ) source to One table "X" on Target.

The idea of a Log stream task putting the changes in the Staging folder and then having independent Child tasks per table all pointing to the exact same single table target is technically doable.

I am not sure what contention there may be but I believe it's worth a try.

For the Other Use Case: " many source tables ( across multiple databases)->one target table"

Use the same logic and make several Log stream tasks and many more Child tasks. This would really need to be tested because I would think there would be some sort of locking issue or contention, at some point.

Definitely needs to be tested!

Hope this helps...

Barb

Re: Replicate with Many source to One target

Michael_Litz — Fri, 02 Sep 2022 19:36:39 GMT

Hi Duma,

Okay, if there are different tables on different sources and you are going to replicate them into a single target that should work out fine.

Just create a task for each source and use the same target endpoint.

Thanks,
Michael

Re: Replicate with Many source to One target

Heinvandenheuvel — Tue, 06 Sep 2022 04:56:32 GMT

I don't see the problem. You'll need one task per distinct source db. You can fold multiple tables into one target but of course you have to use transformations to get to a common target data model. If that gets too tricky then you can use a staging table and use target procedures to sort out complex data model issues such as 1 row into 2. Sometimes, as pointed out, a change-table becomes the only way to handle the 'merge' on the target side, notably to handle PK transformations/collisions.

One thing is for sure, you must take the utmost care to have all task touching the shared target table be set to 'do nothing' on full load other wise any task full-load accidental or not, will blow away all data - not just the data which originated from that task. An alternative to the 'do nothing' is a clever private SQL syntax redefining TRUNCATE to become a selective delete for the data originated by the task.... not the easiest thing to do, and even harder to maintain.

Good luck,

Hein