With the help of my colleague’s Blogpost pertaining to: StepByStep SAP PaPM Cloud <> SAP PaPM On-Premise Integration, you already learned about how to consume data from SAP Profitability and Performance Management On-Premise (SAP PaPM OP) utilizing Data Provisioning Agent (DPAgent) through HANA Adapter. In this blogpost, we will journey through extending our knowledge in some more integration procedures on reading and writing data to and from SAP Profitability and Performance Management Cloud (SAP PaPM Cloud) to a remote SAP Business Warehouse (SAP BW).
Scenario:
You have an advanced data store object (ADSO) in SAP BW which you will want to use as input data for further calculations through SAP PaPM Cloud. Resulting data from the process is needed to be written back to the ADSO in SAP BW.
Reading Data from SAP BW
Data reading from SAP BW will be achieved by creating an ADSO wherein the External SAP HANA View option is enabled through BW Modeler perspective in Eclipse/HANA Studio. Using local machine, DPAgent Installation will take place with the registration of HANA Adapter. Afterwards, Virtual Table of the ADSO will be created in the chosen schema preferably SAP_PAPM_ADMIN. After providing necesarry privileges for Virtual table, connections can be made and ADSO data can be utilized for consumption in SAP PaPM Cloud through Model View HANA View Function.
Procedure:
- Using your local machine, follow the procedure in setting up DP Agent> HANA Adapter from blogpost section: SAP PaPM Cloud >> source: SAP PaPM On-Premise.
- Launch the Integrated Development Environment (IDE) either Eclipse or HANA Studio then login into the SAP BW System > BW Modeler Perspective.ADSO is created where it is configured with External SAP HANA View enabled. Reporting views will be available for usage.
- In SAP PaPM Cloud Tenant Database using SAP_PAPM_ADMIN user
3a. Go to Remote Sources
3b. Choose the External HANA View
3c. Choose Create Virtual Table and place it in chosen schema e.g. SAP_PAPM_ADMIN
3d. Virtual Table is now available for further modeling steps.
In SQL Console, Perform granting of privileges in order to read the Virtual Tables created.
GRANT ALL PRIVILEGES ON SCHEMA "<SCHEMA>" TO SAP_PAPM_ADMIN
GRANT ALL PRIVILEGES ON SCHEMA "<SCHEMA>" TO SAP_PAPM
- In SAP PaPM Cloud Tenant
In Connection Management, Create a connection pointing to the Virtual Table.
Then Model View HANA View for read only purposes can be used to use the connections which in turn allows the usage of ADSO data in Modeling Environment.
Writing Data back to SAP BW
Data which has been consumed from SAP BW will be the starting point of the model where simple processing configuration will be performed using View Function. Afterwards, HANA table within the Database of SAP BW system should be created via Eclipse/HANA Studio through HANA Modeler Perspective. Writer will be used to write the processed data into a Virtual Table of this HANA Table.
Note: You may wonder why virtual tables are created in between. This is done mainly as best practice approach where SAP PaPM Cloud only acts as calculation or processing tool thereby omitting the dependency of holding the data in the tenant's database.
Once desired data is already in HANA Table, DataSource -> Transformation -> Data Transfer Process will be created and executed in order to push the data back into the original ADSO.
Procedure:
- Launch the Integrated Development Environment (IDE) either Eclipse or HANA Studio then login into the SAP BW System > HANA Modeler PerspectiveCreate a HANA Table with the same fields as ADSO.
CREATE COLUMN TABLE "<SCHEMA>"."HT_ZINTADSO1"(
"ZQA_CUST" NVARCHAR(50) DEFAULT '' COMMENT '/BIC/ZQA_CUST',
"ZQA_PROD" NVARCHAR(50) DEFAULT '' COMMENT '/BIC/ZQA_PROD',
"ZQA_QTY" DECIMAL(17, 3) DEFAULT 0 COMMENT '/BIC/ZQA_QTY',
"ZQA_AMT" DECIMAL(17, 3) DEFAULT 0 COMMENT '/BIC/ZQA_AMT'
)
UNLOAD PRIORITY 5 AUTO MERGE
COMMENT 'Enrich BW Data ';
2
. In SAP PaPM Cloud Tenant Database using SAP_PAPM_ADMIN > Remote Sources
Create a Virtual Table based from the HANA Table created on previous step (as reference see step 3 of Reading BW Data Scenario).
3
. In SAP PaPM Cloud Modeling Environment
3a. The virtual table from Read Data from ADSO scenario will serve as input table.
3b. The virtual table from previous procedure step scenario will serve as output table.
3c. Enrichment via a simple ZQA_QTY * ZQA_AMT formula for input table will take place via View Function in SAP PaPM Cloud.
3d. Afterwards the resulting data of View will be fed into a writer and will be written into output table.
4. Launch the Integrated Development Environment (IDE) either
Eclipse or HANA Studio then login into the SAP BW System > HANA Modeler Perspective.
Check the contents of HANA Table if data is filled. Basically, writing into Virtual table means that data is being written into the original HANA Table.
5. Choose the BW Modeler Perspective in Eclipse/HANA Studio and Create DataSource on top of user’s
Source system.
5a. Right click on Source System and choose new DataSource
5b. BW Project and Source System will be automatically populated since DataSource is created on Source System level, else populate with the correct Source System then choose Next.
5c. Choose Proposal from SAP HANA Table or View
5d. Search and Choose the Virtual Table to be consumed. Chooe Next.
5e. Confirm the necessary information of DataSource then choose Finish. Chooe Next.
5f. Check extraction properties which should look like below then Activate the DataSource. Datasource should be in Active Version.
6. In
SAP GUI > Login to SAP BW System > Go to RSA1. Under Modeling > Infoprovider > Find the ADSO
Right Click and choose New > Transformation.
Fill in the Object Type as DataSource and the previously created DataSource.
6a. Map the DataSource fields to the ADSO fields. Check and Activate Transformation.
7. Still in
BW System > RSA1
Right Click on the ADSO and choose New > Data Transfer Process (DTP)
DTP information autofill with take place. just verify if entries are desired then choose Check button to proceed.
8. Adjust some configuration in Data Transfer Process
8a. Change Extraction Mode to ‘Full’.
8b. Check and activate. Make sure Version is in Active Version with green status.
9. Perform DTP Execution of data from HANA Table to ADSO
9a. Go to Execute Tab
9b. choose Execute Button to feed the data from data source to ADSO.
10. Recheck the ADSO after execution. Data has now been written into ADSO.
Note: Not to confuse readers, Initial or Original Data section are aggregated in the list output screenshot above as compared with the original data which was consumed from the Reading Scenario
The procedure above is one way of writing data from SAP PaPM Cloud into SAP BW. It can happen that there is a more optimized way to do this. The presentation of steps above are mainly based on the investigation i have done. At any case you have something which can benefit our community, do not hesitate to comment it out so other consultants will be able to use it for their projects.
Nevertheless, I hope this will be a starting point in your SAP PaPM Cloud <<>> SAP BW Integration knowledge! Have fun and see you again on future integration blogposts 😊