SAP PaPM Cloud: Query Master Data and Hierarchy with external source
If you have already read the blogpost about SAP PaPM Cloud: Introduction of Query based Master data and Hierarchy by Arlie San Isidro wherein it is described how to use a Query as a master data source for a characteristic field, you might have a question if this is also applicable in case you want to use master data and hierarchies coming from external source. Well, the answer is yes. As a team member of Quality Assurance and Maintenance team, I was investigating this topic further, so I wanted to share my experience from this investigation with you. In this blogpost, we will go through the procedure of setting up a Query Master Data and Hierarchy with external source. Two cases will be described in the following examples. First one includes usage of master data and hierarchies coming from the SAP HANA Cloud View, and the second one will describe the scenario of using master data and hierarchies coming from SAP Business Warehouse’s (SAP BW) infoobject.
Master data and hierarchies coming from SAP HANA Cloud View
Allow me to show you in this example that SAP HANA Cloud View (External Source) can be used as master data and hierarchy source which can be adjusted externally anytime. With the help of Query Value List, integration of this external source with SAP Profitability and Performance Management Cloud (SAP PaPM Cloud) is now possible. Meaning SAP PaPM Cloud will directly take into consideration the information from this external source without the need to adjust any modeling setup everytime the external master data and hierarchy information changes. Let me show you below how to set it up.
The prerequisite for using this functionality with external source is to have a well-structured external source as shown in the following example.
- Create your SAP HANA Cloud Table and make it have the same structure as Master Data and Hierarchy sheet from SAP PaPM Cloud. Figure 1: Master Data and Hierarchy sheet structure
- In case you don’t want to have an option to write back to the source, you can create SAP HANA Cloud View on top of the SAP HANA Cloud Table and use it as a connection source in SAP PaPM Cloud tenant. To do so, in the Database Explorer create SAP HANA Cloud View under the same schema where your SAP HANA Cloud Table is.
CREATE VIEW "SAP_PAPM_ADMIN"."MDH-VIEW" ("VALUE", "DESCR", "IS_NODE", "PARENT_VALUE", "HIERARCHY_NAME") AS SELECT "VALUE", "DESCR", "IS_NODE", "PARENT_VALUE", "HIERARCHY_NAME" FROM "SAP_PAPM_ADMIN"."MDH"
- For you to be able to use your SAP HANA Cloud View in SAP PaPM Cloud tenant, first you have to create a connection. In Connection Management Screen of SAP PaPM Cloud Tenant create a new connection pointing to your SAP HANA Cloud View. Add HANA View as a Connection Source. Find Authoring Schema that you used for SAP HANA Cloud Table and SAP HANA Cloud View creation. Search for your SAP HANA Cloud View in the dropdown list. For more information on how to set up a connection, go to Connections.Figure 2: HANA View Connection creation
- In SAP PaPM Cloud Modeling Environment configure Model View HANA View function
4a. Add created connection as a source to Model View HANA View function.
4b. Map all source fields, save and activate the function.Figure 3: Model View HANA View configuration
- In SAP PaPM Cloud Modeling Environment configure Query function the same as shown in SAP PaPM Cloud: Introduction of Query based Master data and Hierarchy and finally you can make use of the field with external master data and hierarchies in Model Table or processing functions.
Master data and hierarchies coming from SAP BW’s Infoobject
Since SAP BW is often used as a master data and hierarchy source, the second scenario will show that it is also possible to use SAP BW as an external source for a Query Value List.
Note: SAP BW is a private network system, hence connection to the information is through DPAgent. As an option and a suggestion, prepare a database view from the source that will be read by SAP HANA Cloud (SAP PaPM Cloud Runtime database) prior using it as a Query Value List data records.
A) Create your BW InfoObject and maintain its master data and hierarchies. By entering transaction code RSD1 in the system, you will be able to see Master Data and Hierarchy information, which also include tables and views and where are they being stored. Those information are needed so you could be able to proceed with B).
Note: As per my investigation, the following tables helped me get the proper view structure, but it might be the case that there are also some other useful tables and a more optimal way of view structure creation.
- P<INFOOBJECT NAME> e.g. PMD_INFOOB
- T<INFOOBJECT NAME> e.g. TMD_INFOOB
- H<INFOOBJECT NAME> e.g. HMD_INFOOB
- In the SAP HANA Cloud Database Explorer find previously created Remote Source.
1a. If it is successfully connected to your dpagent, you will be able to see all the schemas that are coming from your external source.
1b. Find the View with master data and hierarchies that you have already created before in your external source.
1c. Once you find the View, select it and choose Create Virtual Object(s) button. By creating virtual object, you will be able to consume your external view in SAP PaPM Cloud tenant.
1d. Choose the schema under which you want your virtual object to be created.
1e. Look for the virtual object under Tables section of the chosen schema. It should have the same name as your source view. Now, you are able to see data that coming from external source.Figure 6: Remote source configuration
- In Connection Management Screen of SAP PaPM Cloud Tenant create a new connection pointing to your virtual object. Add HANA Table as a Connection Source. Find Authoring Schema under which you created your virtual object. Search for your virtual object in the dropdown list. For more information on how to set up a connection, go to Connections.Figure 7: HANA Table Connection creation
- Once you set up your connection, you can consume it in the Modeling Screen as a Model Table HANA or Model View HANA Table source. You can then, follow the same steps as shown in SAP PaPM Cloud: Introduction of Query based Master data and Hierarchy for the creation of a Master Data Query.
If you are still reading this, thank you and I hope you are now better equipped with how to utilize external master data and hierarchies through:
a. use of SAP HANA Cloud View
b. use of SAP BW
Finally, you can enjoy playing around with this new functionality. 😊 Hope you learned something and see you on my next blogpost! Until the next blogpost stay tuned via SAP PaPM Cloud community tag!