SAP Analytics Cloud Application to add Master Data to a planning model

In this article, I would like to describe a typical business challenge in the context of extended Planning and Analytics (xP&A) and how they are handled within SAP Analytics Cloud.

Problem definition:

In the area of planning and forecasting, for example in sales planning or as well as in cost planning, typically business users often will have the ability to add new Master Data to planning models and the related dimensional structures. Especially in planning, topics often must be planned and simulated that are not available as Actual in the leading systems like ERP or Data Warehouse. For example, a business user like a sales manager or a controller needs to plan and simulate the introduction of new products, new cost center or new plants. How do such activities affect the business success, the P&L, and the balance sheet? For these reasons it must be easily possible to add new master data to planning dimensions during a planning process for the involved business users.

Technical Solution:

To support the business the SAP Analytics Cloud, provides a functionality to add master data without IT support in a self-service way. This can be done directly in a planning grid table within the context menu.

“Add Member” dialog within the SAP Analytics Cloud context menu

Furthermore, it is possible to add new elements in the traditional default way directly in the dimension within the SAP Analytics Cloud Modeler in the dimension editing dialogue and of course also through import processes.

Dimension Editor in SAP Analytics Cloud Modeler

Sometimes this should be done directly by the business user in a more controlled and steered process. In this case a simple Analytics Designer application can be used. In the following such an Analytics Designer application and the way to create this will be explained and described. The first step is to create a new Analytical Application. This can be done very easily with the menu inside SAP Analytical Cloud by pressing the plus icon.

Dialog to create a new Analytical Application

The next step is to save it and to give it a name. In this case I would like to name it “Create New Element Application”. The 3 components we do need for this example application are a Canvas page with a table grid to display data from our underlying planning model. Additionally, a Popup dialog to add a new element for the product dimension and the underlying planning model.

Layout menu to add the requirement components

We would like to start to add the related planning model, in this case it will by the planning model “I_Price”.

Adding planning model

The next step is to design the canvas page. This page should include a table with the dimension “I_PRODUCT” in the rows and Version “Budget” and the year 2023 with quarter and month in columns. The account or measure we have selected in this example is “Price”. Additionally, we have added a logo image and a background image behind the logo to improve the look and feel. Both are not absolutely required for the functionality of the application.

Table on Canvas

What we need additional is a button on the canvas to open the required popup to start the “Create New Product” dialog. To this button we can add JavaScript logic afterwards. Prior to this we must create the Popup. The Popup need to have two Input Fields and one Dropdown Box. The Input Fields are used to add the ID and the Description of the new creating product into the “I_PRODUCT” Dimension.

PopUp definition dialog

Till this point it was more or less same approach as to create a simple Story Canvas page. What we have to do now is to add the JavaScript logic to the Button of the Canvas page and the Popup dialog. We will start with the script logic on the Canvas Button.

Button to open Popup Java Script logic

With a click on fx dialog and the selection of the “onClick” event a new tab is opened to add Java Script which will be executed if a user drops the “Create New Product” button on the canvas page. The script we will use is listed below:

Canvas Button JavaScript

//##########################################################################
// Set initial values for the input fields
InputField_1.setValue("New Product ID");
InputField_2.setValue("New Product Name"); // Get the elements of the dimension I_PRODUCT from the planning model object
var productGroups = PlanningModel_1.getMembers("I_PRODUCT"); //Loop over all Elements for (var i=0;i<productGroups.length;i++) { // If the element has TOTAL as parent it represents a product group if (productGroups[i].hierarchies.H1.parentId==="T1000") { // Add the product group element to the drop down menus Dropdown_1.addItem(productGroups[i].id); // DropDown on Popup_1 }
} // Open Popup Dialog
Popup_1.open();
//########################################################################## 

It starts with the preassignment of the Input fields on the Popup dialog. Followed by reading all elements of the Dimension “I_PRODUCT”. Then a filtering of all elements that have the parent element “T1000” and thus must be product groups. The result list of product group elements will then add to the drop-down list in the pop-up dialogue for the “Parent group” field.

Popup OK Button JavaScript

In the next step we can define the script which will be executed by clicking of the OK Button on the Popup (“button1”). If we click the OK button, the content of the InputField_1 and InputField_2 will be used for the Element Id and Element Description for the new creating element in the “I_PRODUCT” dimension. Additionally, the result of the Dropdown box with the list of the product groups will be used as the parent element for the new creating element.

Popup OK Button JavaScript

//##########################################################################
// button1 represents th OK button on the Popup Dialog
if (buttonId === "button1") { // get the values the user has entered (id, description and product group) var productId = InputField_1.getValue(); var productDesc = InputField_2.getValue(); var parentId = Dropdown_1.getSelectedKey(); // create a new element for the dimension product with the user inputs PlanningModel_1.createMembers("I_PRODUCT", { id: productId, description: productDesc, hierarchies: { H1: {parentId: parentId} } });
} // refresh the table
Table_1.getDataSource().refreshData(); // close the popup window
Popup_1.close();
//##########################################################################

The script starts with an If-Statement to check if the Ok Button is pressed. If yes, the content of the InputField_1, InputField_2 and the dropdown box will be stored in variables, which be used to create a new member element. Afterwards the Table on the Canvas will be updated, and the Popup will be closed. The last step is to save the analytical application and to run it.

Application run

After the Canvas page is opened, it is possible to analyze and to enter Prices to the various products on every product level in combination with the planning timeline. If the business user now needs to add a new product to the product dimension, it is possible to start the creating process with a push on the “Create New Product” button.

Popup Dialog

Afterwards the popup dialogs opened with the dropdown field to select the product group which will be used as the parent element for the new product and two input fields to define the product ID and the product description. To execute the creating process, the OK button must be pushed.

New Product is created

Immediately after the execution, the new product element is created and visible in the product dimension hierarchy inside the planning table.

New Product can be planned

It can be used directly for data entry, planning and simulation. This example uses the product group level as possible parent elements. As well it is possible to create elements through such a process on every position in a dimension hierarchy.

Outlook:

Such an SAP Analytics Cloud Analytical Application can be used directly by business user additional it is possible to integrate the master data creating process application with links in existing stories or canvas pages. Especially with the further development of Story 2.0 and the merging of Story Builder, Digital Boardroom and Analytical Application, the handling and use will again be greatly improved.