Hello everyone!
In one of our previous blog posts of What’s New in SAP Profitability and Performance Management Cloud (SAP PaPM Cloud), an overview of the Query based master data and hierarchy feature was introduced. In case you haven’t seen it yet, I encourage you to check it out along with other What’s New blog posts for SAP Profitability and Performance Management Cloud (SAP PaPM Cloud):
In this blog post, we will further dive in how to utilize this feature to be more efficient when it comes to modeling that involves master data and hierarchies. This blog post will discuss two parts:
A. Creating the Query based Master Data and Hierarchy using Internal source
B. Assigning the Query based Master Data and Hierarchy using Internal source to multiple Fields/Parameters
Query based master data and hierarchy will also allow user to acquire maintained master data and hierarchy from an external source which will be discussed further on another blog post. For now, let’s start with an introduction on how to configure the Query based master data and hierarchy in this blog post which will use an internal source.
Let's get started!
Prerequisites:
- Create Characteristic fields to be assigned to the Model Table which will serve as the container of the master data and hierarchy values.
- Create a Model Table in the Modeling with the created characteristic fields and set it as the Input of the Query function to be used for Master Data and Hierarchy.
Note: This blog post contains images. In case the images are too small, kindly click on the image to view with a better resolution.
A: Creating the Query based Master Data and Hierarchy using Internal source
In below example, characteristic fields have been created and assigned to a Model Table. In the Data Editor of the Model Table, master data and hierarchies have been maintained just like how it is done when user defines master data and hierarchy for each field in the Fields node, which will serve as the Input for the Query function and source of the master data and hierarchy.
Now that we have the input data, we can now create the Query function to be used as our “Master Data and Hierarchy Query”. To do this, drag a Query function into the Modeling diagram then:
1. Set the created Model Table as the Input of the Query which can be done in two ways:
2. Assign fields to the Query by:
a. Expand Query Elements
b. Perform copy-paste action of fields from Model Table Input to Query Elements.
3. Select each field in Query Elements. In the Properties Panel under Format section, there is a “Tag” dropdown for each field.
When field “Tag” dropdown is chosen, user would be able to see the following Query field tags:
As we know, we can fill in our desired master data and hierarchy for each field in the Master data and Hierarchy table via the General Entities section of the Modeling. Notice that it includes sections that are the same with most of the Query field tags:
Now, the Query field “Tag” under Format section when a field in Query Elements is selected will allow user to map each field in the Query accordingly considering how we defined the master data and hierarchy from its source. The Query field tags are equivalent to each field in the Master Data and Hierarchy table via the General Entities section of the Modeling which user can select for each field taking note of below information for appropriate mapping:
| Query field Tag | Master Data & Hierarchy Table | Definition |
| KEY | Value | Defines the master data |
| DESCR | Description | Describes the entered value (or master data) |
| IS_NODE | Is Node | Displays data as a node if this is marked
|
| PARENT_KEY | Parent Value | Defines which data is treated as the parent node |
| HIER_NAME | Hierarchy Name | Specifies the name of the hierarchy |
| LANGUAGE | - | Specifies the language of the master data value (should always be defined in two letters in example: “EN”) |
| SEQUENCE | - | Allows user to define the sequence which master data should be displayed (applicable only using characteristic values in example: A,B, C….) |
In our sample scenario, the field ‘Key’ has been maintained with Query field tag “KEY”, field ‘Description’ with field tag “DESCR”, and so on…
4. Once desired Query field tag is set for each field, select the Query function and under “Tags” dropdown, choose VALUE_LIST. This Query tag allows user to use the data from the Query to be used as Master Data and Hierarchy.
5. Save and Activate the Query function.
Now that the Query based Master Data and Hierarchy is created, how do we use or assign it to a field? Let’s discuss this further below.
B: Assigning the Query based Master Data and Hierarchy using an Internal source to multiple Fields/Parameters
To assign the Query based Master data and Hierarchy to a desired field in the Modeling, the checkbox for Master Data and/or Hierarchies of the desired target field should be marked as enabled. Then in the Query dropdown, Queries which have been tagged “VALUE_LIST” would appear.
This allows user the possibility to reuse the same master data and hierarchy from a tagged Query for multiple fields/parameters.
In case the Query has hierarchy maintained and the Hierarchies checkbox of the field is enabled, the user can select a Hierarchy Name. When a specific Hierarchy Name is chosen, only the master data from selected hierarchy will be displayed. Otherwise, if the Hierarchies checkbox of the field is disabled, the Hierarchy Name would remain disabled and all master data from the Query will be visible.
Once a tagged Query (and Hierarchy Name if applicable) is selected, Save the changes in the Modeling.
Note: If the user has made changes to the Query configuration, reactivation of the Query is necessary.
By means of this, any field/parameter that is referenced with a tagged Query will be able to see its maintained master data and hierarchy upon selecting its Value Selection dropdown which can be used for filtering or selection conditions during modeling configuration.
At this stage, we are done with two of our goals:
A. Creating the Query based Master Data and Hierarchy using Internal source
B. Assigning the Query based Master Data and Hierarchy using Internal source to multiple Fields/Parameters
Now the question is, how would my data look like? The next section is to provide some scenarios considering two things that were mentioned earlier regarding Hierarchies. Consider the following source data for our sample scenarios:
Scenario 1 – When a specific Hierarchy Name is chosen, only the master data from selected hierarchy will be displayed. In this scenario with the Hierarchies checkbox enabled, the option to select a Hierarchy Name has also been enabled.
Since user has selected the Hierarchy Name ‘DESSERT’ from the Query, it is expected that only the master data values under ‘DESSERT’ will be displayed.
At the same time, only the values from selected hierarchy will be accepted for the target field.
Scenario 2 – If the Hierarchies checkbox of field is disabled, the Hierarchy Name would remain disabled and all master data from the Query will be visible. In this scenario with the disabled Hierarchies checkbox, the option to select a Hierarchy Name has also been disabled.
With this, it is expected that all the master data values from the referenced Query will be displayed.
Note: Partitioning using field with master data and hierarchy coming from Query (VALUE_LIST) is not supported.
That's it! I hope that you can find this introduction to using Query based Master Data and Hierarchy helpful. You can also check the next blog post on how to further utilize this functionality using an external source in the blogpost SAP PaPM Cloud: Query Master Data and Hierarchy with external source.
Thank you for taking the time reading this and see you again on the next one!
- SAP Managed Tags:
2 Comments
