topic Re: Qlik Replicate for Versioning in Qlik Replicate

Qlik Replicate for Versioning

hitztyl — Fri, 10 Mar 2023 20:15:52 GMT

Hi, i'm wondering if Qlik Replicate would work for versioning tables within a SQL Server database. For example I have Table A in an OLTP database and I want to replicate any changes made to that table to Table B in an ODS database. Table B contains a VersionStartUTC and VerstionEndUTC that needs to be updated every time there is an update, insert etc... So if I modify a column in Table A, I will set the VersionEndUTC for the last record in Table B to the current time then create a new record in Table B. The VersionStartUTC will be the current time and the VersionEndUTC with be 9999 etc..

Re: Qlik Replicate for Versioning

Dana_Baldwin — Fri, 10 Mar 2023 22:03:54 GMT

Hi @hitztyl ,

In your use case, when you say "modify a column in Table A", do you mean an insert/update/delete, or an alter statement that changes the DDL of the table/column? I think you mean the "version" of the DDL, just want to confirm. If so, I can't think of a way to accomplish that at the moment - maybe a lookup query against the source metadata to get the last timestamp of a DDL change... would need to investigate further.

Thanks,

Dana

Re: Qlik Replicate for Versioning

john_wang — Sat, 11 Mar 2023 01:00:05 GMT

Hello @Dana_Baldwin ,

Besides Dana's comment, it's rather complex even it's DML in source TableA only... looks to me Replicate + Compose may help on ODS/HDS data versioning.

Regards,

John.

Re: Qlik Replicate for Versioning

hitztyl — Sun, 12 Mar 2023 02:09:36 GMT

Hi Dana, thanks for the reply. Yeah when I say modify i mean insert, update or delete. For example you could have a CustomerAddress as table A, and CustomerAddressVersion as table B. So when you update, delete or insert a new record in the CustomerAddress table, that will be reflected in the CustomerAddressVersion table.

Re: Qlik Replicate for Versioning

hitztyl — Mon, 13 Mar 2023 17:00:17 GMT

@Dana_Baldwin Just to add from my last response, I'm thinking Compose is probably the better solution but I was asked to investigate Replicate since we are currently using that product for our replication tasks. We were hoping to keep it all within the same solution but as I investigate Replicate i'm beginning to doubt that it is possible. I just wanted to confirm my doubts before I decided to move on.