Improve the performance of the SAS ci360-cdm-loader-sas job from Github

[Michel_Shahan]

Introduction

SAS® Customer Intelligence 360 makes APIs available for you to download CDM data into your on-premise environment. Once downloaded, you can load the data into the on-premise database of choice. SAS has made sample programs available to help with these two steps. You can find more information in the community post New CDM - Preview of CI360 October 2020 release (20.10).

Heavier use of SAS Customer Intelligence 360 leads to more data being collected in the cloud system. When the data grows, so does the time it takes to load the data into the database. Because of this, you might notice slower load performance with two tables, CDM_IDENTITY_MAP and CDM_IDENTITY_ATTR. These tables are both non-partitioned and are therefore downloaded in full each time, leading to increased load time. This consequently delays the time that the data is available for consumption. The time to insert the data is explained by the way that the SAS Loader job works.

The SAS Loader job has a SAS DATA step for each table, whose work is to do the following:

1. Compare the downloaded data with your on-premise database to determine which rows already exist in your on-premise database table.
2. Replace the existing rows with the new rows to update the time stamps.
3. Insert the new rows.

These steps ensure that new data is inserted and that data that has been deleted from the cloud using GDPR delete are retained in your on-premise database.

Improving Load Performance

You can use pass-through SQL queries in your native database language to improve the performance of these load steps. 

1. Upload the data to a temporary table in your database.
2. Use pass-through queries to compare the temporary table with the existing database table and update or insert depending on the result of that comparison. See the next paragraphs for details about how to accomplish this step.

Fortunately, there is example SAS code for Oracle that you can use as a model. The example code is found in the cdm_load.sas  program. The program contains a SAS macro code section for each CDM table comprised of PROC SQL and DATA step statements. You can locate the example code for the cdm_identity_map  and cdm_IDENTITY_ATTR tables using the below example search strings:

%if %sysfunc(exist(cdmmart.cdm_identity_map))

%if %sysfunc(exist(cdmmart.cdm_identity_attr))

The cdm_load_datastep.sas program contains the current  load DATA steps. If you use an Oracle database, you can replace the DATA steps in cdm_load_datastep.sas with the pass-through versions found in the cdm_load.sas program. If you use a different database, you can use the cdm_load.sas program as a model to create similar code for your database. Like the cdm_load.sas program, you can locate the appropriate code to replace by searching for the macro condition for that specific table.

Once changed, you should see improved load time into the database for your non-partitioned tables.

Statement of Support

The SAS product functionality described above is provided for informational purposes only based on SAS’ current product roadmap. It does not represent nor alter official SAS product documentation nor is it a guarantee as to the timing of the availability of any product functionality or that such functionality will be made available.  

[shahh]

Thanks for posting.

For information related to microsoft azure visit.