{"id":1690,"date":"2025-12-03T21:20:26","date_gmt":"2025-12-03T21:20:26","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1690"},"modified":"2025-12-03T21:20:28","modified_gmt":"2025-12-03T21:20:28","slug":"qlik-replicate-is-the-record-there-or-not-im-confused","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1690","title":{"rendered":"Qlik Replicate: Is the record there? Or not? I’m confused!"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

I think as Qlik Replicate developers; we have all been here before.<\/p>\n\n\n\n

The testers berating you through MS Teams saying, \u201cWe have made a change, and we cannot see it downstream! It is all Qlik Replicate\u2019s fault!\u201d<\/p>\n\n\n\n

Opening the task and checking the monitoring screen \u2013 I can see the change against the table.  What are they going on about?  Qlik has picked up something; why can\u2019t they see it? (Sixty percent of the time; something downstream has failed, twenty percent of the time something upstream has failed, nineteen percent of the time they are looking in the wrong spot and the remaining one percent of the time \u2013 well we won\u2019t mention that one percent.)<\/p>\n\n\n\n

But looking at the monitoring screen \u2013 what does those numbers mean?  Also, if you look in the analytics database; what does those figures mean?<\/p>\n\n\n\n

Hopefully, this article will help you understand the monitoring and analytics numbers with some simple examples.<\/p>\n\n\n\n

Filters – why does it have to be filters?<\/h2>\n\n\n\n

We have two main types of Qlik Replicate tasks.  One type grabs all changes from a particular table and sends it to our Data Lake in micro batches of fifteen minutes.<\/p>\n\n\n\n

The other type are our speed tasks; only grabbing changes on specific columns on a table.  To limit QR to only picking up specific changes; we have filters like this:<\/p>\n\n\n

\n($AR_H_OPERATION != 'UPDATE' AND $AR_H_OPERATION != 'DELETE') OR\n(\n  ($AR_H_OPERATION == 'UPDATE') AND \n  (\n    ( $BI__FIELD_1 != $FIELD_1 ) OR ( $BI__FIELD_1 IS NULL AND $FIELD_1 IS NOT NULL ) OR ( $BI__FIELD_1 IS NOT NULL AND $FIELD_1 IS NULL ) OR\n    ( $BI__FIELD_2 != $FIELD_2 ) OR ( $BI__FIELD_2 IS NULL AND $FIELD_2 IS NOT NULL ) OR ( $BI__FIELD_2 IS NOT NULL AND $FIELD_2 IS NULL ) OR\n    ( $BI__FIELD_3 != $FIELD_3 ) OR ( $BI__FIELD_3 IS NULL AND $FIELD_3 IS NOT NULL ) OR ( $BI__FIELD_3 IS NOT NULL AND $FIELD_3 IS NULL )\n )\n)\n<\/pre><\/div>\n\n\n
So, the question is \u2013 if we made an update to $FIELD_4 meaning that QR will send the change downstream; what would the monitoring tab on the task display?<\/p>\n\n\n\n
Let is create an example to find out.<\/p>\n\n\n\n
An example<\/h2>\n\n\n\n
Here is a simple table -pre-populated with six record:<\/p>\n\n\n
\nCREATE TABLE dbo.CDC_ANALYTICS_EXAMPLE\n(\n\tRECORD_ID INT IDENTITY(1,1) PRIMARY KEY,\n\tDATA_INT INT,\n\tDATA_VARCHAR VARCHAR(100),\n\tINCLUDE_RECORD CHAR(1)\n);\n\nGO\n\nINSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(1, 'Record 1', 'Y');\nINSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(2, 'Record 2', 'Y');\nINSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(3, 'Record 3', 'Y');\nINSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(4, 'Record 4', 'N');\nINSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(5, 'Record 5', 'N');\nINSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(6, 'Record 6', 'N');\n\nSELECT *\nFROM dbo.CDC_ANALYTICS_EXAMPLE WITH (NOLOCK);\n<\/pre><\/div>\n\n\n
And we create a simple QR task with the following filters:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The first filter is the Fullload Passthru Filter<\/em>. When the full load initially runs; only three of the six records will be brought across.<\/p>\n\n\n\n
The second filter Record Selection Condition<\/em>; should filter any data changes on when $INCLUDE_RECORD has a value of ‘Y’.<\/p>\n\n\n\n
Results after running the QR task<\/h2>\n\n\n\n
Full load<\/h3>\n\n\n\n
After running the full load; qlik shows the following on the monitoring screen:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Three records – that’s what we expected out of the six.  <\/p>\n\n\n\n
Interesting under the “Total Completion” section; the distinction of the filter is quite clear.  Three records brought across and three remaining.<\/p>\n\n\n\n
Change Processing<\/h3>\n\n\n\n
Let’s make some changes to the data in our test table:<\/p>\n\n\n
\n-- This will be captured\nBEGIN TRANSACTION\n\tDECLARE @IDENTITY INT;\n\n\tINSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(null, 'Record x', 'Y');\n\tSET @IDENTITY = @@IDENTITY;\n\n\tUPDATE dbo.CDC_ANALYTICS_EXAMPLE \n\tSET DATA_INT = @IDENTITY, \n\t\tDATA_VARCHAR = 'Record ' + CAST(@IDENTITY AS varchar)\n\tWHERE RECORD_ID = @IDENTITY;\n\n\tDELETE dbo.CDC_ANALYTICS_EXAMPLE WHERE RECORD_ID = @IDENTITY;\n\nCOMMIT;\n\nGO\n\n-- This will be excluded\nBEGIN TRANSACTION\n\tDECLARE @IDENTITY INT;\n\n\tINSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(null, 'Record x', 'N');\n\tSET @IDENTITY = @@IDENTITY;\n\n\tUPDATE dbo.CDC_ANALYTICS_EXAMPLE \n\tSET DATA_INT = @IDENTITY, \n\t\tDATA_VARCHAR = 'Record ' + CAST(@IDENTITY AS varchar)\n\tWHERE RECORD_ID = @IDENTITY;\n\n\tDELETE dbo.CDC_ANALYTICS_EXAMPLE WHERE RECORD_ID = @IDENTITY;\n\nCOMMIT;\n<\/pre><\/div>\n\n\n
This is what the monitoring screen shows:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Looking at the Recent activity<\/em> screen; hmm – this could lead to some confusion.  <\/p>\n\n\n\n
The metrics shows the two blocks of changes; even though one block was filtered out.  So this is indicating that this gives the results when there is a change in the table; not matter if it was subsequently filtered out.<\/p>\n\n\n\n
If those testers in the opening paragraph created a record that was filtered out in QR; we could give them false direction if we said “it should be downstream”<\/p>\n\n\n\n
If we look at the Aggregates <\/em>screen; it paints a true picture of what was detected and transferred to the target:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We can see the three records in the “Total Changes Applied” column.<\/p>\n\n\n\n
Analytics database<\/h3>\n\n\n\n
From the analytic database:<\/p>\n\n\n
\nSELECT \n    *\nFROM public.aem_taskv t\nWHERE\n    t.task_name IN ('TEST_ANALYTICS') \nORDER BY\n     t.server_name, t.task_name, t.retrieval_time\n<\/pre><\/div>\n\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t
Field<\/strong><\/em><\/th>Value<\/strong><\/em><\/th>\n<\/tr>\n<\/thead>\n
id<\/td>110876201<\/td>\n<\/tr>\n
retrieval_time<\/td>2025-12-03 12:02<\/td>\n<\/tr>\n
server_id<\/td>5<\/td>\n<\/tr>\n
task_name_id<\/td>914<\/td>\n<\/tr>\n
task_state_id<\/td>3<\/td>\n<\/tr>\n
task_stop_reason_id<\/td>1<\/td>\n<\/tr>\n
task_profile_id<\/td>1<\/td>\n<\/tr>\n
cdc_evt_applied_insert_count<\/td>1<\/td>\n<\/tr>\n
cdc_evt_applied_update_count<\/td>1<\/td>\n<\/tr>\n
cdc_evt_applied_delete_count<\/td>1<\/td>\n<\/tr>\n
cdc_evt_applied_ddl_count<\/td>0<\/td>\n<\/tr>\n
full_load_tables_completed_count<\/td>1<\/td>\n<\/tr>\n
full_load_tables_loading_count<\/td>0<\/td>\n<\/tr>\n
full_load_tables_queued_count<\/td>0<\/td>\n<\/tr>\n
full_load_tables_with_error_count<\/td>0<\/td>\n<\/tr>\n
full_load_total_records_transferred<\/td>3<\/td>\n<\/tr>\n
full_load_est_records_count_for_all_tables<\/td>6<\/td>\n<\/tr>\n
full_load_completed<\/td>1<\/td>\n<\/tr>\n
full_load_start<\/td>2025-12-03 10:57<\/td>\n<\/tr>\n
full_load_finish<\/td>2025-12-03 10:57<\/td>\n<\/tr>\n
full_load_thrput_src_thrput_records_count<\/td>0<\/td>\n<\/tr>\n
full_load_thrput_src_thrput_volume<\/td>0<\/td>\n<\/tr>\n
full_load_thrput_trg_thrput_records_count<\/td>0<\/td>\n<\/tr>\n
full_load_thrput_trg_thrput_volume<\/td>0<\/td>\n<\/tr>\n
cdc_thrput_src_thrput_records_count<\/td>0<\/td>\n<\/tr>\n
cdc_thrput_src_thrput_volume<\/td>0<\/td>\n<\/tr>\n
cdc_thrput_trg_thrput_records_count<\/td>0<\/td>\n<\/tr>\n
cdc_thrput_trg_thrput_volume<\/td>0<\/td>\n<\/tr>\n
cdc_trans_read_rollback_count<\/td>0<\/td>\n<\/tr>\n
cdc_trans_read_records_rollback_count<\/td>0<\/td>\n<\/tr>\n
cdc_trans_rollback_change_volume<\/td>0<\/td>\n<\/tr>\n
cdc_trans_applied_transactions_in_progress_count<\/td>0<\/td>\n<\/tr>\n
cdc_trans_applied_records_in_progress_count<\/td>0<\/td>\n<\/tr>\n
cdc_trans_applied_comitted_transaction_count<\/td>2<\/td>\n<\/tr>\n
cdc_trans_applied_records_comitted_count<\/td>6<\/td>\n<\/tr>\n
cdc_trans_applied_volume_committed<\/td>0<\/td>\n<\/tr>\n
cdc_trans_read_memory_events_count<\/td>0<\/td>\n<\/tr>\n
cdc_trans_read_swapped_events_count<\/td>0<\/td>\n<\/tr>\n
cdc_trans_applied_memory_events_count<\/td>0<\/td>\n<\/tr>\n
cdc_trans_applied_swap_events_count<\/td>0<\/td>\n<\/tr>\n
cdc_source_latency<\/td>0<\/td>\n<\/tr>\n
cdc_apply_latency<\/td>0<\/td>\n<\/tr>\n
memory_usage_kb<\/td>0<\/td>\n<\/tr>\n
disk_usage_kb<\/td>0<\/td>\n<\/tr>\n
cpu_percentage<\/td>0<\/td>\n<\/tr>\n
data_error_count<\/td>0<\/td>\n<\/tr>\n
task_option_full_load_enabled<\/td>1<\/td>\n<\/tr>\n
task_option_apply_changes_enabled<\/td>0<\/td>\n<\/tr>\n
task_option_store_changes_enabled<\/td>1<\/td>\n<\/tr>\n
task_option_audit_changes_enabled<\/td>0<\/td>\n<\/tr>\n
task_option_recovery_enabled<\/td>0<\/td>\n<\/tr>\n
cdc_trans_read_in_progress<\/td>2<\/td>\n<\/tr>\n
server_cpu_percentage<\/td>0<\/td>\n<\/tr>\n
machine_cpu_percentage<\/td>16<\/td>\n<\/tr>\n
tasks_cpu_percentage<\/td>0<\/td>\n<\/tr>\n
server_name<\/td>xxxx<\/td>\n<\/tr>\n
task_name<\/td>TEST_ANALYTICS<\/td>\n<\/tr>\n
server_with_task_name<\/td>xxxx::::TEST_ANALYTICS<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
A couple of take aways from the results:<\/p>\n\n\n\n
    \n
  • Unfortunately the records in the the analytic database is grouped up at a task level; not at an individual table level.  This makes it harder to determine if a change from a particular table was applied<\/li>\n\n\n\n
  • cdc_trans_applied_records_comitted_count is a count of all the records read; but not necessarily applied <\/li>\n\n\n\n
  • If you want the number of records flowing down to downstream; add together the fields cdc_evt_applied_insert_count, cdc_evt_applied_update_count and cdc_evt_applied_delete_count together<\/li>\n<\/ul>\n\n\n\n
    Final thoughts<\/h2>\n\n\n\n
    The monitoring and analytics screen is useful tools for diagnosing QR tasks – and general ASMR; watching the numbers bounce up as transactions flow through the system.<\/p>\n\n\n\n
    Understanding the figures of what they translate to – whether changes getting detected or applied downstream is fundamental in diagnosing problems.<\/p>\n\n\n\n
    Nothing is better than getting access to the source and target systems and checking yourself to confirm if things are working correctly; but the monitoring screen is a good place to start investigating.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Introduction I think as Qlik Replicate developers; we have all been here before. The testers berating you through MS Teams saying, \u201cWe have made a change,...<\/p>\n","protected":false},"author":1,"featured_media":1691,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16],"tags":[36],"class_list":["post-1690","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-qlik-replicate","tag-qlikreplicate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1690"}],"version-history":[{"count":5,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1690\/revisions"}],"predecessor-version":[{"id":1703,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1690\/revisions\/1703"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1691"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1690"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}