Replicate with Many source to One target

[Dumma]

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,

[lyka · Accepted solution]

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.h...

Thanks

Lyka

[Dumma]

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

[lyka]

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/Custom...

Hope this helps!

Thanks

Lyka

[Dumma]

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,

[lyka]

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

[Dana_Baldwin]

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

[Michael_Litz]

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/17...

Thanks,
Michael

[Dumma]

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,

[Barb_Fill21]

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