This blog post provides an approach on how we can reconcile missing data using CPI DS and SAP HANA. For example, Missing Customers, Orders, Invoices etc during large volume migration.

Please refer  blog post to know basics of CPI DS Task setup. Please refer  blog post for connection setup with Sap Hana Data Base system.

Step 1:

After extracting all customers ID’s from Cloud system via OData API Get method into file using CPI PI flow, i used CPI DS TASK to read data from Sftp Server and insert it into HANA data Base Table "DUMMY_CUSTOMERS_RECONCILIATION".

Create and configure CPI DS TASK to read data from Sftp Server and insert them into Hana data base table"DUMMY_CUSTOMERS_RECONCILIATION".

It is simple one to one mapping TASK.

Step 2:

Compare customer ID’s from Table "DUMMY_CUSTOMERS_RECONCILIATION" against customer ID’s from main table "DUMMY_CUSTOMERS" (which has all customers data) using below SQL EXCEPT Query in Hana Data Base  and insert them into Table "DUMMY_CUSTOMERS_REPROCESS_ERRORS".

insert into SCHEMANAME.DUMMY_CUSTOMERS_REPROCESS_ERRORS  as ( SELECT customerid FROM (SELECT customerid FROM SCHEMANAME.DUMMY_CUSTOMERS except SELECT customerid FROM SCHEMANAME.DUMMY_CUSTOMERS_RECONCILIATION));

Please Refer blog post for more information on HANA queries https://blogs.sap.com/2014/05/25/all-about-joins-using-sql-in-hana/

Description of Query: selecting customer data from table "DUMMY_CUSTOMERS" which is not present in table "DUMMY_CUSTOMERS_RECONCILIATION" and inserting it into new table "DUMMY_CUSTOMERS_REPROCESS_ERRORS".

Now missing customer ID’s is available in table "DUMMY_CUSTOMERS_REPROCESS_ERRORS".

Step 3:

Create and configure CPI DS Task with either Inner Join or filter condition as shown below to get full data for missing customerid’s..

Using option "Source Table" Tables can be added to data flows.

Table "DUMMY_CUSTOMERS": Which has all customer data

Table "DUMMY_CUSTOMERS_REPROCESS_ERRORS" : Which has list of missing contact ID’s.

Below screen shot is for Table "DUMMY_CUSTOMERS" Columns. Mapping Transformation will be done as per requirement between Table "DUMMY_CUSTOMERS" and OutPut Data Store (DUMMY_TEST_CUSTOMERS_C4M_FILE).

Below is screen shot for Table "DUMMY_CUSTOMERS_REPROCESS_ERRORS" Columns and filter condition on 2 tables.

Explanation of Filter Condition:

If CustomerID in table "DUMMY_CUSTOMERS_REPROCESS_ERRORS" is equals to  with CustomerID in Table "DUMMY_CUSTOMERS" then all columns of a customerid from table "DUMMY_CUSTOMERS" will be returned as output.

Output file for missing customers is generated as per data store and mapping transformation rules. This file can be read using CPI PI iflow and upload it into Cloud for marketing using ODATA call.

Using CPI DS standard configuration (either Filter or Join), it is easy to compare tables and get missing data.

，bi大数据分析，淘客社区，云服务器的，大数据网，大数据解决方案