QLIK Replicate - Supplemental tools to verify replicated data.

[Jolley]

Hi All!

Around 2 years ago we moved to QLIK replicate for our DW replication needs. Its been awesome for the most part. 6 second latency on most of our data warehouses. Its highly customized. We opted to keep the original structure of the users data (what we call the Data Warehouse) in the same format (even though our backend SQL server databases changed to a completely new version and structure). We did this to go easy on our customers because they have thousands of reports, and they did not want to rewrite all the reports to match the new structure. This has become a thorn in our side.

We've come across an issue where there are unhandled failures in QLIK, and we find that QLIK is unable to repair itself. So, our DBA wrote a bunch of procedures\scripts to check all data for the past 3 months to see if there are any differences between the source and destinations. We find discrepancies daily and she auto runs stored procs to send a "null" update so that the missing data\record gets refreshed in destination.

QLIK thinks it's some issue on our end or an issue with our version (We are on May 2022, upgrading to May 2023 this weekend). We are hoping this will resolve the issue.

In the event that our issue is not resolved... does anyone know of an 3rd party application that can export massive tables to text files and do data comparisons at a high rate of speed? We've tried Red Gate SQL but it doesn't quite have what we need to get the job done.

Thank you for your time!

Bryan Jolley - Conifer Health

[PGN · Accepted solution]

It sounds like you are using MS-SQL.  

We've set up weekly and ad-hoc jobs using the native Microsoft replication tool Tablediff.exe.  I believe that's what Redgate uses under the covers. However, if you use the tool itself without that wrapper, it runs a lot faster.  It's available for download from Microsoft and its use is described there.  It will compare tables as long as the table schema is the same, and it will create the sql to make the corrections.  We have Powershell scripts set up to do the comparisons.  We do not, however, automatically execute the result scripts since we like to review any discrepancies.  In cases where there are huge differences, it's sometimes more efficient to do table reloads.

Hope that helps.

[Jolley]

Thank you very much for the quick response... this is very helpful and i'm looking in to Tablediff.exe now! Passing the info on to our DBA as well and i'll let you all know what we come up!