topic Re: Replicate with 1:M in Qlik Replicate

Replicate with 1:M

Dumma — Mon, 08 Aug 2022 20:11:50 GMT

Hi Gurus,

I am trying to go from 1 table in source to Many tables on the target database. source is SQL and target is Oracle. What is the are some of the patterns to achieve this? The table schemas are different b/w source and target.

Thanks!

Re: Replicate with 1:M

Michael_Litz — Mon, 08 Aug 2022 20:16:41 GMT

Hi Dumma,

It sounds like a log stream task will allow you to have a single task reading the source table and then many replication tasks that would move the data to the target.

Please have a look at this article as it will get you going in the right direction.

Log Stream tasks
https://community.qlik.com/t5/Knowledge/LogStream-Setup/ta-p/1743657

Thanks,

Michael

Re: Replicate with 1:M

Dana_Baldwin — Mon, 08 Aug 2022 20:20:09 GMT

Hi @Dumma

Your best bet would probably be to use Log Stream. One task would replicate the table to binary files on disk, then you would set up a task for each table you need on the target that reads the binary data and writes to the target.

Here's a link to our documentation on that feature:

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/Log%20Stream%20Staging/intro.htm

Thanks,

Dana

Re: Replicate with 1:M

Dumma — Mon, 08 Aug 2022 21:11:23 GMT

Thanks @Dana_Baldwin @Michael_Litz

I will look at logstream to do this. Can the target tables have a different primary key than the source table primary keys?

Re: Replicate with 1:M

SwathiPulagam — Mon, 08 Aug 2022 21:19:34 GMT

Hi @Dumma ,

yes, you can select the required primary key columns in the table transformation.

Thanks,

Swathi

Re: Replicate with 1:M

Dana_Baldwin — Mon, 08 Aug 2022 21:19:41 GMT

Hi @Dumma

Yes you can change which columns are used for PK/UI in Table Settings:

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

Thanks,

Dana

Re: Replicate with 1:M

Michael_Litz — Mon, 08 Aug 2022 22:15:16 GMT

Hi Dumma,

Please do mark one of the posts as Accept as Solution if you find it helpful.

Thanks,
Michael

Re: Replicate with 1:M

shashi_holla — Mon, 08 Aug 2022 22:58:34 GMT

Hi All,

I don't have anything new to add here but just summarizing the details so that the steps are clear and we follow the best practice.

1) Create Logstream Staging task for which the source is SQL Server and target is Logstream endpoint. This will ensure we are reading source table only with one connection to avoid bottleneck.

2) Create Oracle endpoint and choose a user/schema which has access to all other schemas. This will avoid redundancy and maintenance of multiple endpoints.

3) Start building replication tasks each connecting to the same Logstream as the source and target Oracle endpoint. For each task either create a global transformation to choose the right schema or if it's only one table per task then choose the schema in the table settings, example below:

4) Start the Logstream parent and the replication tasks in sequence and ensure all tasks are running in parallel and as expected.

Thank you,

Re: Replicate with 1:M

Dumma — Mon, 08 Aug 2022 23:07:35 GMT

we tried different PKs on the target, however we noticed that we got two inserts and then update went to second insert, the update should have gone to the first insert.