As planning data in SAC is often changed by business users, these changes need to be replicated to the target systems like BW/BPC, S/4 HANA or Data Warehouse Cloud. This is a feature highly demanded by many customers.
The Data Export Service API is already GA in Q2.2022 with full data. With SAC QRC4 2022 the Data Export Service Delta feature will also be generally available without any delta toggle. Currently, if you are on a fast-track tenant, you can request the feature to be toggled on in your system. For further information please have a look at the help document for the delta feature in this API.
This blog will focus on how to use BW/4 HANA (as target) integrated features (Smart Data Integration) to pull the delta data from SAC (source) using the Data Export Service. Please be aware that this in contrast to a live connection or an import connection where SAC is the target and BW is the source.
Architecture and Prerequisites
|1||Install DP Agent and connect BW/4 to SAC||Prepare for the connections|
Using SAC Planning model as BW source
– Verification at HANA side
– Create BW source system
– Create Data Source
– Understand the logic of changing data
– Understand the logic of deleting data
|Understand the delta logic from SAC to the replicated table at BW source|
Setup BW data flow
– Create an ADSO Z1DETADSO with change log.
– Create a DTP and simple mapping from data source to the ADSO
– How changes at SAC side are reflected the ADSO Inbound Table, change log and Active Data
|Understand the delta logic from replicated table to BW ADSO.|
Call for actions
Below is the architecture we are going to use. Compared to the way to use ABAP and the API to write to a specific table, the advantages are here 1) best to utilize the HANA artifacts and memory for the real-time replication with SDI technology; 2) No programing is required.
Please remember that the toggle for the delta handling must be set explicitly in SAC (when this blog is written in Nov. 2022). Delta API is planned to release in QRC4 2022 according to the current roadmap.
A DP Agent of version 2.5.3 or higher is necessary.
The target systems can be a BW/4HANA, DWC, or HANA on premise system (at time of publication of this article). Please check the latest PAM HANA SDI to check the latest status if more target systems are supported.
There is already blog introducing this. Please refer to this help about the details how to install DP Agent and connect to the HANA system under BW/4: Leverage the SAP Analytics Cloud Data Export Service to extract your planning data to SAP HANA, SAP Business Warehouse and SAP S/4HANA
Verification at HANA side
Before start doing anything in BW, you can verify in HANA side –>Provision –> Remote sources, it looks like below. The planning modes are under SAC folder. In case it is empty and also you do not get error, it is many cases due to the result was blocked by firewall in your local network.
Create BW source system in BW Modeling Tool (BWMT)
Now we change to BW modeling Tool to create a Smart Data Access type source system. We call it SACDES here. Attention: All the SDA and SDI connection type is under this Smart Data Access. We are in fact using SDI here, however it is still called SDA in the UI.
Create Data Source in BWMT
Now we create a Data Source under SACDES. All the planning transaction data are delta enabled. Master data does not support delta. Thus, we search “FactData” here, which is transaction data. Attention: This is going to take a while to retrieve all the metadata from SAC. And you need to check in your SAC modeling URL to find the models.
My model is https://<mySACURL>sap/fpa/ui/app.html#/modeler&/m/model/C9ZOQZN2GI2L4HV6S4MK8ULFK
Here we created the data source called “Z1DELTAS”
Go to extraction Tab. Make changes as below for the delta enabled transaction data. The real-time replication type Upsert is like after image and insert is similar to before/after image in BW context. We will use UPSERT here and will see how delta are reflected later.
Here are more information about the difference of UPSERT and INSERT.
Activate your data source first. Then click “Manage“ button, which is behind “Remote Subscription Type UPSERT”. You should see status Initial here. By this step, BW will generate a virtual table, replication table and subscription, which we are going to check in details later.
Now you execute the delta initialization in the background.
After the jobs are done successfully, you can check how many records are replicated into the replication table.
Here is the way to find out what the virtual table, replication table and subscription. Click Overview and you will see all the subscription generated by BW in the system. BW delta enabled data source generate VT (/BIC/CMV<Datasourcename>0000X000001) , Replicated Table (/BIC/CMT <Datasourcename>0000X000001) and subscription (/BIC/ CMU<Datasourcename>0000X000001) )
In our case, the generated replication table is /BIC/CMTZ1DELTA00001000001 and our BW/4 is in SAPHANADB, thus you can run SQL in the HANA directly. We have replicated 508 records.
Understand the logic of changing data
We will change a record (GLAccount =“Cost of Goods Sold”, products “city” and Date “Jan (2022)”) from 9.000k to 6.500k
First, we check it in the replicated table it looks like below. SignedData is 9.000k.
Now we are going to modify a record in SAC story: change this SignedData from 9.000k to 6.500K . and click publish. IMPT: only public version can be pulled via the API.
The virtual table points to SAC and it will be changed immediately. Verify by below
select * from "SAPHANADB"."/BIC/CMVZ1DELTA00001000001" where "GLAccount"='Cost of Goods Sold' and "Products"='City'
Depending on the volume, after some seconds or maybe several minutes, you can see the replicated table is also changed. SDI_CHANGE_TYPE” A” (as in Autocorrect) is for inserted or updated records.
select * from "SAPHANADB"."/BIC/CMTZ1DELTA00001000001" where "GLAccount"='Cost of Goods Sold' and "Products"='City'
Understand the logic of deleting data
Now I delete this record (GLAccount =“Cost of Goods Sold”, products “city” and Date “Jan (2022)”) from SAC and publish.
Here is the result from the virtual table, nothing could be found now.
Here you will see the record is SDI_CHANGE_TYPE has value “D” for deleted in the replicated table.
Similarly, you can test the remote subscriptions of type INSERT, it returns the complete delta history in the target table of the replication.
Maybe you are already thinking about after some time, how to clean up the replicated table. The good news is BW has also implemented the house-keeping for the delta tables as below. Here is the LINK.
House keeping of replicated table
Create an ADSO Z1DETADSO with change log.
Create a DTP and simple mapping from data source to the ADSO
How changes at SAC side are reflected the ADSO Inbound Table, change log and Active Data
Here are 3 tables generated behind this ADSO.
We are looking at the Inbound table /BIC/A<ADSO>01 as below
Here is the active data table after the request is activated. The delete 1 record is not active and only 507 are there.
select * from "SAPHANADB"."/BIC/AZ1DETADSO2"
After this, I changed one record in SAC, you will see from ADSO request only 1 record is loaded and 1 is activate.
Here is the change log for this ADSO.
Does the delta API support Classis Account Model in Planning?
A: Yes, it supports both SAC Classis Account Model and new model. All transaction data has delta. Master data does not support delta.
Do I need to filter out private version while using the API?
A: No, only published data is exposed by API.
How to trouble shooting subscription at HANA side?
A: Download the SQL statements from Note 1969700 – SQL statement collection for SAP HANA.Search the keywords “SmartDataIntegration”. There will be around 9 SQL statements. For example , “HANA_SmartDataIntegration_Agents” is to check the DP Agent status connected to your HANA. “HANA_SmartDataIntegration_RemoteSubscriptions” is to check the remote subscriptions.
How to check the trace at the DP Agent side?
How to check if my SAC Odata API works correctly?
A: You can test your SAC Odata API in Postman or below link Try Out | Data Export Service | SAP API Business Hub
I have Dev/QA/Production landscape for my on premise systems, can they share the same DP Agent?
A: No, DP Agent can only have one target system. Thus, you have to install DP Agent for each of your BW,/4 HANA, S/4HANA or HANA system.
Can I create more than one subscriptions to one SAC planning model.
A: yes, each subscription will have its own Subscription ID.
Further Blogs/links on the usage of SAC Data Export Service API:
This method is a seamless combination from SAC Data Export Service Odata API to BW/4 and utilized SDI technology. There are still a few things we can tune the performance in case of huge data volume. For example, parameter “Pagesize” of the data source in HANA is 10.000 by default and you could increase much 10 times large or even more. Parameter “Poll Period” (in mins) is by default 5 minutes, this could be reduced if you want to try to check the poll more frequently.
Be free to share your test result or any other comments here.
In case there is a need for guidance on how to use this architecture for HANA on primes or Data Warehouse Cloud, please also let me know.