SAP PaPM Cloud: Downloading Output Data Efficiently (including master data description)
Let’s say that as a Modeler, you have successfully uploaded your data into SAP Profitability and Performance Management Cloud (SAP PaPM Cloud) and utilized SAP PaPM Cloud’s extensive modeling functions for enrichment and calculation. And as a result of your Modeling efforts, you now have the desired output that you would like to download from the solution. The question is: Depending on the number of records, what would be the most efficient way to do this?
To make it simple, I’ll be using ranges to differentiate small and large output data. Under these two sections are step-by-step procedure on how to download results generated from SAP PaPM Cloud – in which based on my experience, would be the most efficient way.
In Modeling, I have used a Model Table with four fields and having ten rows of data records. This was then used as the input function of an Iterative View function. The High property of the Iterative View function will be adjusted accordingly to produce larger data results as we go along.
As segue, if you’re curious about view functionality, you might want to check this blog post:
View Function: Advanced tab
I. Small [Output < 5, 000 data records]
With the Modeling function chosen (in my case Iterative View function), click on the Show icon under the header tools section. While on the right side of the Show icon is the Analyze icon.
In the Show screen, if you are looking at just less than a thousand rows of data records, a simple Select All [CTRL + A] + Copy [CTRL+C] and Paste [CTRL+V] to an excel sheet can be performed as an alternative to the succeeding steps.
If copy + pasting isn’t your cup of tea, then, let’s focus on the header tools of Show screen. In here you will find Import & Export section, choose the Export icon. Choose either: (a) CSV or (b) XLSX from the list of options. Additional options can be introduced in this list by configuring File Formats in Modeling.
Upon choosing either default format, the exported file will be downloaded locally.
Export raw data is also true to all functions with enabled Show and/or Analyze screen, talk about a very convenient feature!
However, this is not the be-all-and-end-all of data download in SAP PaPM Cloud. When working on larger data sets, it would be best to do it on the underlying database in which will be discussed in the next section.
II. Large [Output > 5, 000 data records]
For this section, I have adjusted the High property setting of the Iterative View function from 100 -> 10000 to produce an expected 100,000 rows of data records as its execution result.
To extract results from a modeling function that falls under large data range, follow these steps:
- From any of the SAP PaPM Cloud screens, in the upper left corner, expand the Menu > Administration > Settings
- Under Database Settings section, click on the HANA Dashboard URL link.
- In the SAP Business Technology Platform login prompt, you may opt to use the Default Identity Provider or sign-in using an Alternative Identity Provider credentials.
- In the Database Overview screen Credentials dialog box, login using user SAP_PAPM_ADMIN and the password as provided under Database Settings from Step #2.
Then choose SQL Console.
- In SAP HANA Database Explorer, choose the Preferences icon
- 1.Choose SQL Console
6.2.Focus on the Max Number of rows to display setting which is set to 1000
6.3 Change its value to 100000 (or depending on the number of data records you would want to extract).
6.4. Choose Save
6.5. Confirm User Preferences have been saved
USEFUL TIP: In your web browser tool bar, you might want to bookmark the current URL as this will point automatically to Database of your SAP PaPM Cloud Tenant. Having a direct link, will save few steps when accessing the DB the next time around.
This will expose all Tables under SAP_PAPM schema which is the default schema of SAP PaPM Cloud. Meaning, all generated tables from the application are stored here.
- From the side-pane, use the search bar to locate the Y-Table of the modeling function with the guidance of the following naming convention:
- Right-click on the matching table result and choose Generate SELECT statement.
Remove TOP 1000 from the generated statement
- 1. As you notice, FS_PER* technical fields are present in the select statement, remove this segment if you do not wish to have these fields in the extracted data. Afterwards, Run the statement.
10.2. Choose Download icon on the Results tab.
10.3. In the Download Table to CSV File dialog box, maintain desired settings for the download file and finally choose Download.
Notice that in just a matter of seconds, the output result of the Iterative View function which consists of 100,000 rows worth of data records has been downloaded successfully.
Question: “What if I have masterdata and I want to download the data records with not just ID, but description included, how can this work in HANA side?”
Using the same example from above, I have used field CH_CHAR that has master data records. This characteristic is then used in Input table function of the Iterative View.
To extract the result of a modeling function together with the master data description of a field, steps are provided below:
- From the Modeling Environment, expand the Palette and choose Query function.
- 1. Drag to create query function into the Modeling diagram and maintain its Function ID
2.2. Then, fill out the function Description
2.3 Lastly, indicate the Query function’s input function, in my case, it will be the Iterative View function.
- Expand the Query node and add its elements
NOTE: Ensure to add the Characteristic field with master data to be extracted, in my case, it’s CH_CHAR
- Save changes and Activate the configured Query function
- 1. Switching to the SAP HANA Database Explorer, in the left pane, choose Views
5.2. Search for the Query view
5.3. Right-click the matching result and choose Generate Select Statement
- In the generated statement, notice the fields wherein information is stored:
pointer a: master data ID
pointer b: master data description
- Execute the statement and proceed with the download procedure accordingly.
Notice that under CH_CHAR_T column, the description of CH_CHAR field’s Master data is provided in the downloaded results as marked by pointer a.
While pointer b shows the total data record rows downloaded including the header.
If for some instance, you’d like to utilize the extracted results to another SAP PaPM Cloud Tenant, perhaps my previous blog post: SAP PaPM Cloud: Uploading Input Data Efficiently will be useful to you.
I hope that I was able to help you on how to download output results from SAP PaPM Cloud efficiently. A like and quick share to your colleagues would be awesome! Feel free to comment on your questions or if you have suggestions for a next topic that would benefit the community.
Until my next blog post, cheers!