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..
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.
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.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
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.
@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.
.png "Former Employee"){kind=icon}
