Qlik replicate with CDC for views with SQL Server

[guilherme-matte]

Hello guys!

I've seen topics in the forum about and in the help section about the replication of views (i.e https://community.qlik.com/t5/Official-Support-Articles/Selecting-Views-inside-a-Replicate-task/ta-p...)

As far i understand, there is no way to replicate it with CDC, since we are using SQL Server as source. But is there any way to overcome the situation with work arounds? 

Like, would be possible the schedule a full load every X days to update the target, or any similar actions?

Thanks and as always, appreciate your help!

Matte

[john_wang · Accepted solution]

Hello @guilherme-matte ,

Thanks for reaching out.

Qlik Replicate is log-based CDC product (a few exceptions), so far if the object's changes cannot be recorded in transaction logs (TLOG in SQL Server, Redo LOG in Oracle, Journal in DB2i, BinLog in MySQL, OPLOG in MongoDB ...) then it's impossible to get the relevant changes from transactions logs.

In this scenario, VIEW in SQL Server, as SQL Server does not record VIEW's data into TLOG, Replicate has no way to capture the view's change data - in most of RDBMS, the view does not contains real data (only metadata, or view definition) but will get data during the query (the only exception as far as I know is Oracle Materialized View which stores data).

The workaround to replicate VIEW's data from SQL Server source:

1- As you mentioned, repeat Full Load  only task to replicate the view data in an interval eg every X days or every X hours

2- Every view is based on one or several base table(s), we may still use regular CDC tasks to replicate all the base tables to target side (adding filters if possible to discard the useless records), and then build up the similar VIEW in target database to get the wish data (certainly the target side DB must support VIEW as well)

Hope this helps.

Regards,

John.

[guilherme-matte]

Hello John!

Thank you very much for your answer and explanation, helped me getting a better grasp of the tool and how it works.

We will indeed be going with the second option, replicating the base tables and we are going to recreate the views in the target after seems the easiest solution.

Cheers!

[john_wang]

Hello @guilherme-matte ,

Thank you for your feedback, feel free to let us know if you need any additional assistance.

Good luck,

John.