Authors: Kanchana Subbiah (Kanchana.subbiah@sap.com)
Pankaj Singh (pankaj.singh04@sap.com )
Reviewed : jaideep.chatterjee@sap.com; sean.kelly01@sap.com
Summary: Project requirement is to migrate BPC Comments BPC 7.5 MS to BPC 11 NW by replicating MS SQL tables data to BPC 11 (BW) comments table.
Since BPC 11 is on BW4HANA we had the following approach to achieve the use case
Please find the following outline of the steps illustrated below.
- BPC 7.5 MS comments had to be replicated from MS SQL source to BPC 11 generated BW tables in each model
- Smart Data Integration tool introduced to achieve this
- Replication task used to replicate table structure (Virtual / Physical) from MS SQL to BW4HANA system
- HANA procedures defining the source and target table structure for the comments to be migrated In BW4HANA by virtual tables created through replication task.
- Then Comments were populated to BW4HANA comments table via execution of HANA procedure
Overall solutioning looks as below
Please find the following explanation of each steps-
- MSSQL, SDI, HANA Integration customer scenario’s: This is how the customer scenario looks, where we had to get that comments tables to SAP HANA.
- Smart Data Integration tool was used to achieve this: Following are the steps which was followed to configure data provisioning agent and setup the remote source.
SDI MSSQL Log reader adapter was used for data replication. And as we know we had to install data provisioning agent and register this adapter and give agent name, please find the screenshots as below-
Now, using this registered agent create remote source to create link between remote database. Please find the following screenshots of the same.
Replication task was created to replicate data from MS SQL to BW4HANA system- Once the remote source is created, use the remote source to create replication task for virtual table creation. Screenshots as below-
- Create the HANA Procedure
Code snippet of the HANA DB Procedure for each model the dimensionality has to be taken care;
The key for the BPC 11 Comments table is MANDT (System ID) & RECORDID. RECORDID must be same as the one in MS 7.5 as the Insert into the BPC 11 comments table happens at the DB layer using HANA procedures. RECORDID that is created by BPC input templates from Application Layer generate unique combination with combination of Alphabets and Numbers
MANDT : Client Number; USER_ID : Valid BPC admin user id
DELETE from "SAPBPC"."/1CPMB/KXHLCMMT" where USER_ID ='SAP_BPCADMIN';
INSERT INTO "SAPBPC"."/1CPMB/ KXHLCMMT "
( "MANDT",
"RECORDID",
"USER_ID",
"DATEWRITTEN",
"KEYWORD",
"PRIORITY",
"KPI",
"COMPANY",
"COST_CENTER",
"DATA_TYPE",
"DATASRC",
"DETAIL",
"GM_ACCOUNT",
"GM_LEVEL",
"PARTNER_CTR",
"PARTNER_UNIT",
"TIME",
"VERSION",
"RPTCURRENCY",
"MEASURES",
"SCOMMENT"
)
(SELECT '020' AS MANDT,
"RECORDID",
'SAP_BPCADMIN' AS "USERID",
TO_DECIMAL(TO_CHAR("DATEWRITTEN",'YYYYMMDDHHMMSS')),
"KEYWORD",
"PRIORITY",
"KPI",
"COMPANY",
"COST_CENTER",
"DATA_TYPE"
"DATASRC",
"DETAIL",
"GM_ACCOUNT",
"GM_LEVEL",
"PARTNER_CTR",
"PARTNER_UNIT",
"TIME",
"VERSION",
"RPTCURRENCY",
"MEASURES",
"COMMENT"
FROM "dbo_CommentTest" );
6. Execute the HANA Procedure which pull all the comments information from MSSQL to BPC BW table, this takes milli seconds for Millions of records as this is via replication task and virtual table
7. BW Comments table: Comments moved from MSSQL is shown as 280905 highlighted and data saved from BPC 11 template is shown as combination of Alphabets and Numbers
8. BPC Input Template: Post the comments are migrated the BPC input template / Report could be executed to validate that the comments relevant are moved to BPC11 appropriately
9. This is a one-time activity to pull all the historic comments to be migrated from MSSQL to BPC11 NW BW table. To abide by BPC comments table, when the comments are migrated run a copy package to copy all the comments information from migrated RECORDID to BPC generated RECORDID (combination of Alphabets and Numbers) to ensure that there is no overlap / inconsistency in the comments table post migration in BPC11 NW BW table.
APPENDIX:
SDI Installation steps: https://blogs.sap.com/2016/03/10/hana-smart-data-integration-installing-the-dp-agent/
Please find my comments on SDI Un-installation:
Once MSSQL goes then SDI remote source connection to MS SQL can be removed and DP Agent installed on source server(MS SQL) can also be uninstalled.
https://help.sap.com/viewer/7952ef28a6914997abc01745fef1b607/2.0_SPS03/en-US/f57aaed4101640d2a1f6effcbeb63e9a.html
===========================================
• Admin guide for installations : https://help.sap.com/doc/7952ef28a6914997abc01745fef1b607/2.0_SPS00/en-US/index.html?
• File Adapter Configurations : https://blogs.sap.com/2016/01/06/hana-smart-data-integration-the-file-adapter/
• For our reference I would say go through this blog series: all steps are pointed here: https://blogs.sap.com/2016/07/14/step-by-step-setup-sdi-for-your-hcp-trial-account/