Skip to main content
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER
Showing results for 
Search instead for 
Did you mean: 

Using Incremental load logic with SQL 08 Change Data Capture (CDC) Feature

QV Community,

We are upgrading our SQL server soon to the 08 edition. One of the databases I pull info from is on this server and its an operational DB. I've been told that read access will be cut off after the upgrade and that I must incorporate Change Data Capture (CDC) into my logic. Problem is that I know little about it. I have a QVW that is solely used to create my QVDs and I am using some incremental load logic.

I am hoping that someone could offer their expertise on SQL functionality so that I may be able to incorperate it into my existing logic. Below are some specifics:

  • On my initial load I direct QV to a back up of the operational DB which will serve as my base QVD data and on every subsequent load new data will be available only through the CDC tables.
  • Due to security issues I will have to have QV execute a stored procedure which will load the CDC data in staging tables. (all fields are a mirror image of the original tables except for fields generated by the CDC)
  • I have to incorporate the LSN (Logical Sequence Number) into my logic
  • I have to use the values in the __$operation field to deal with inserts, updates and deletes (All of my tables are updates and inserts only). _$operationcolumn values are:  1 = delete, 2 = insert, 3 = update (values before update) which I am  ignore and 4=update (values after update).

From what I have read the LSN is a binary number but I am not sure how to get the MAX to use in logic. SQL has a function for it but dont know how to use it in QV. I've attached example of how I am dealing inserts and updatable tables currently. The stored procedure I was given and need to be executing is:

SQL execute [DBA].[CDC_Read] '2012-06-12', '2012-06-13', 'ActivityLog';

But I think I should replace the dates with QV variables but not sure how to do this yet.

Maybe something like:  SQL EXECUTE [DBA].[CDC_Read] '$(vCDCStart)', '$(vCDCEnd)', 'ActivityLog';

I was given this link to see example of the SQL execution script to get ideas:

0 Replies