Qlik Community

Ask a Question

Documents

Qlik Sense documentation and resources.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY

Fully-Automated Incremental (CDC) universal SalesForce table data extractor with 4+1 Qlik variables

cheenu_janakira
Creator II
Creator II

Fully-Automated Incremental (CDC) universal SalesForce table data extractor with 4+1 Qlik variables

Attached is a sub-routine that will automatically create a fully-automated incremental (change-data-capture/CDC) SalesForce extractor for Qlik.

logo-company-large.png

Youtube video explaining script.

Sub-routine QVS file downloadable here from Github.

NEW VERSION (that's why "4+1" vars): Script cross-compares the SFDC table structure (field names) and if there are changes to the table, it rebuilds the QVD extracts with the new fields and then returns to the full incremental loading.

The SOQL query will fetch all the fields in the underlying table and requires only these simple parameters:

  1. the LIB connection to your target root folder for storing your QVDs (it will automatically create an "SFDC" sub-folder under this root;
  2. the Salesforce table name and
  3. the primary-key field in that table (typically, the "Id" field).
  4. the datetime stamp for the incremental load (typically, either LastModifiedDate or CreatedDate).
  5. Whether you want to cross-compare the QVDs with the SF table in order to trigger a full rebuild when the table changes.

Sample of script:

//Below is the attached sub-routine file you need to INCLUDE
$(Must_Include=lib://QVDATA-USER_SBI_QlikSense/SFDC/SFDC_FullyAutomatedIncrementalLoad.qvs);

LIB CONNECT TO 'Salesforce';

LET vRootFolder = 'lib://QlikSense/QVData';

//Below are the CALLs for the sub-routine in the above QVS file
CALL SFDC_FullyAutomatedIncrementalLoad('$(vRootFolder)','CaseHistory','Id','CreatedDate','YES');
CALL SFDC_FullyAutomatedIncrementalLoad('$(vRootFolder)','Contact','Id','LastModifiedDate','not_needed_currently');

The script does 3 things.
Firstly, it fetches the data from daystart of last reload using the SOQL "LAST_N_DAYS" function between the last known "datetime stamp for the incremental load" and TODAY() for that SFDC table (the script generates a QVD file in a "_Delta" folder with the "datetime stamp for the incremental load" for that table) and puts this in a "SFDC_[SalesForceTableName]" sub-folder by "datetime stamp for the incremental load" YYYYMM.
Secondly, it runs through the whole set of QVDs in the "SFDC_[SalesForceTableName]" sub-folder, loads them reverse-chronologically with a "WHERE NOT EXISTS on the Primary-Key" so that you only end up with Id-record for the last known modified date (not duplicates or the QVD 'history' of that record). It then writes this QVD (ready for consumption in an Qlik analytical app) in the root "../SFDC" folder.
Lastly, it compares the current QVD structure to the SF DB table. If the structure has changed, you can toggle a full YYYYMM rebuild. This will prevent synthetic keys if using "load *" when loading from these QVDs.

Ideally, you should reverse chronologically load the YYYYMM QVDs with a "WHERE NOT(EXISTS(Id))", so that you will always get the latest modified ("date") for that (unique) ID record.

The first run of this script might take longer to run as it creates the underlying QVD structure, loading ALL the data from the SFDC table (using a "startdate" variable that you can set).
Thereafter it will run 'incrementally'.

Many thanks to @JonasValleskog whose YYYYMM incremental load technique has inspired this solution.

Have fun. If you have thoughts or comments, please post.

C  ;o)

Attachments
Version history
Revision #:
23 of 23
Last update:
‎2020-12-28 06:05 PM
Updated by: