SAP PaPM Cloud integration with Microsoft Excel

[UrosMatijasevic]

Note: There will be lots of images in this blogpost to guide the readers.

In case the images are too small, double-click on the image to zoom-in

In the world of databases sometimes you can get lost in tons of data rows. If you have already read the blogpost ‘Access SAP HANA Cloud via Excel’ you were able to see that you can consume data from desired tenant in Excel. If you are wondering whether is it possible to consume Excel file into SAP Profitability and Performance Management Cloud (SAP PaPM Cloud)? The answer is a simple – YES! I have investigated this topic and would like to share the knowledge I got as a result via this blogpost. I will split this blogpost through two chapters, first we will generate data from SAP PaPM Cloud to Excel and after that we will upload data from local Excel to SAP PaPM Cloud. Also, there are two prerequisites: 

1. Data Provisioning Agent (DPAgent) installation on local  


2. Registered Excel Adapter in the created DPAgent 

So, let’s start step by step! 

CHAPTER 1: Retrieving from SAP PaPM Cloud 

Consumption of SAP HANA Table in Excel 

Regarding the above mentioned blogpost, you can see that in Excel it is possible to consume data from SAP HANA Cloud Table with Get Data From SAP HANA Database option: 

The following step would be to specify Server and Port of the desired SAP HANA (Cloud) database and type the SQL query under Advanced options which is used to specify the Table from which the data will be generated:  

Additionally, in SAP HANA Database menu, Database parameters should be added and Validate server certificate checkbox should be unchecked before pressing the Connect button: 

Now, the data from SAP Cloud HANA Table is generated in Excel: 

 

Create Sheet2 and name it ‘MODIFY’. There you can make changes and that Sheet will be imported into SAP PaPM Cloud. 

CHAPTER 2: Uploading data from local Excel to SAP PaPM Cloud 

For uploading local Excel file to SAP PaPM Cloud, DPAgent is needed as it will be used as bridge between SAP PaPM Cloud and local machine. 

DPAgent installation 

First, you will need the installation of DPAgent which can be found here. For additional reference, I have performed the steps below regarding DPAgent installation:  

1. Extract files from the installation zip file 


2. Run hdbinst.exe as an administrator  


3. Enter the path where you want to install the DPAgent: C:\usr\sap\dataprovagent\<YourPath> (you do not have to create a custom path and can leave this empty, in that case the agent will be installed in C:\usr\sap\dataprovagent\)


4. Name the agent (create a unique agent name, for example by using the machine name or user name)  


5. Provide a valid domain/username under whose credentials the agent service shall run


6. Enter domain/user password and confirm (for the listener and agent administration port just press enter, don’t modify unless your firewall configuration requires other ports to be used):


7. Windows Security Alert will pop up, allow access according to your needs 

Data Provisioning Agent (DPAgent) is now created and visible in path which you have specified. (C:\usr\sap\dataprovagent\<YOUR_PATH>). Now, you can continue with configuration. 

Creation of messaging user in HANA 

Note: Messaging user is a prerequisite for DPAgent configuartion. As your DPAgent will communicate with a tenant from SAP PaPM Cloud, you will need messaging user, which will be created in the underlying SAP HANA Database of the SAP PaPM Cloud tenant. 

1. In SAP PaPM Cloud go to Menu:


2. In SAP PaPM Cloud, go to Settings:


3. Redirect to external link to the HANA dashboard URL (or copy the link and paste it into your browser): 


4. In HANA Cockpit, go to SQL Console:


5. After opening SAP HANA Cockpit, in the SQL Console create a messaging user for your DPAgent:
   CREATE USER <messagingusername> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE
   GRANT AGENT MESSAGING ON AGENT <AGENTNAME> TO <messagingusername> 


6. 

DPAgent configuration   

If you prefer video instead of reading, in this video you can follow the steps which I will perform below:

1. In your local machine, open a windows command prompt, run as an administrator (Type “cmd” in start menu and open it as administrator)  


2. Type command: cd C:\usr\sap\dataprovagent\<YourAgent>\bin and press ENTER 


3. Type agentcli.bat -configAgent and press ENTER, DPAgent configuration Tool menu will pop up:


4. Choose number 1 (Agent Status), you will get feedback that agent is not running, go back to the menu (choose letter “b”) 


5. In the menu select number 2 (Start or Stop Agent), then choose number 1 (Start Agent)


6. Error will occur:Open a CMD prompt as an administrator, in the CMD prompt type services.mscWindows Services will open (running in elevated admin privileges). Alternatively, you may also open Services via Explorer or other means, yet you need to run it as an administrator).          In Services, search for “SAP HANA SDI agent service” and configure the service to run as a local system account:
   Choose Local, check apply and OK


7. Go back (choose letter “b") and try again


8. In the DPAgent configuration Tool menu choose number 7 (SAP Hana connection) then choose option 1 (Connect to SAP HANA Cloud via JDBC)
   Enter needed information (you can find it in HANA Cockpit under hamburger menu, administration, settings):


9. Go back to DPAgent configuration tool menu and choose number 8 (Agent Registration) and then choose number 1 (Register Agent)
   Note: you can change the name of the agent (but leave host name as default):


10. Go back to the menu and choose number 9 (Adapter Registration)
    Enter number 1 (Display Adapters)From the list you are able to see available adapters
    Choose number 2 (Register Adapter)
    Copy the names from the list that you got from 1.
    As we are dealing with Excel files here, you should choose ExcelAdapter. After choosing any adapter that you need, you can again choose number 1 (Display Adapters) to see if desired adapters are registered.Note: after registration of DPAgent and the adapters you can open HANA cockpit and expand the Catalog and then you will see your Agent and the Adapters you registered:

Creation of an access token 

As DPAgent hosts all SAP HANA smart data integration Adapters and acts as the communication interface between SAP HANA for ‘transportation’ of data from the agent machine to the Remote Source on Cloud, we will have to create and register an access token. 

Any unique and safe access token can be used, but also one which can be used is Windows SID (System ID) as the access token, which is my recommendation: 

1. Open CMD as an administrator and determine the SID number of user by typing command whoami /user and copy the entire SID number which will be used as the access token


2. In CMD type cd C:\usr\sap\dataprovagent\Your_Path\bin and press enter. To access Secure Storage Utility enter agentcli.bat –setSecureProperty


3. Choose number 13 (Set ExcelAdapter Access Token) and enter the token (in our case the SID number)
   What we specify here, later we will have to tell the SAP PaPM Cloud environment to use the same access token when requesting information from our DP Agent. 

Disclaimer: The numbers in the version of dpagent you download might differ from the ones in this blogpost, but the names of the options are the same and you should pay attention to them while installing    

Creation of Remote Source 

 Now that you have configured the Data Provisioning Agent, real deal starts! You are prepared for upload, you can add a remote source in HANA. 

1. In HANA Database Cockpit, go to Remote Sources, right click on it, and then choose ‘Add Remote Source’:


2. Name your Remote Source as you want, select the corresponding adapter (ExcelAdapter in this case) and for the source location, select the name of DPAgent that you have already created: 


3. Specify in which folder remote source should look for data


4. For Credential Mode choose ‘Technical User’ and paste SID number that was previously generated


5. When you open the newly created remote source, you will be able to expand your excel file and choose which sheet you would like to use in SAP PaPM (If you remember at the begging of this blogpost, Sheet2 was created and named ‘MODIFY’, that sheet will be imported now)


6. When you chose Create Virtual Object(s), you can choose the Schema and name of the Table


7. In Tables under Catalog folder, you can search for your created table: Note: Right click on it and choose Open Data, you will be able to see fields and data inside of that fileNow you can create a connection in SAP PaPM and consume data using Model Table HANA function.  

In case that you did not understand how to use ExcelAdapter, detailed video step by step can be seen on the following link. If someone is wondering about consumption of CSV files like this, this is possible, but in that case FileAdapter should be used instead of ExcelAdapter. 

Thank you for reading this blogpost, hope that I made your modeling easier. If you find this blogpost helpful, a like and quick share to your colleagues would be awesome! Until the next blogpost stay tuned via SAP PaPM Cloud community tag and always be informed on what’s cooking in our kitchen!