Maintain Master Data in Planning Model – Sap Analytics Cloud (SAC)

Goal:

In this blog we will learn how to insert new members into dimensions (master data) in a Planning Model using SAP Analytics Cloud Application Designer.

Context:

There were 2 main methods to update Master data as described below (apart from filling them up from an on-premise data source or flat files through data management).

1. Story Table: New un-booked member combinations can be added from a story through a table using the insert new member feature. One of the main disadvantage of this method is that the user is free to insert any attribute value (through text box) which may not be desired. In many real-life scenarios, it is desired to restrict the attribute value to a set of fixed values and/or auto generate memberID’s.
2. Dimension Editor: New members can be added by using the dimension management tool in SAC and manually entered. The main disadvantage of this method is the relative technical nature for the end user and un-necessary exposure to full dimension control.

The release of SAC version 2020.09, brings a few PlanningModel API’s, namely:

PlanningModel.getMember(); PlanningModel.getMembers(); PlanningModel.createMembers(); PlanningModel.updateMembers(); PlanningModel.deleteMembers();

Using the above API calls, Master data can be updated by full customization possibility through an analytic application. This method also overcomes the disadvantages of both the methods described above. In what follows, we will describe the steps and coding examples to produce a simple analytical application to update Master data.

Planning Model:

We start by building a simple analytic application with 2 main dimensions, Account and Material. The account dimension has one member called “Amount” and the Material dimension has 2 attributes, namely, “MaterialType” and “MaterialColor” and a few lines of existing Master data. There is no need to upload any transaction data as it will not be required/used. The step by step instructions to create a Planning model from scratch are explained in this blog post.

Analytic Application:

Create a new analytic application and add a table widget on the canvas page. Pull in the Material dimension to rows and Account dimension to columns. With a little smart styling of the table widget, the amount column can be hidden so that only Material members are visible. Additionally make the properties “material type” and “material color” visible. The table with existing Master data is now visible in the table as shown below.

Add a few objects to the application designer:

– Add a Planning Model (1) object and select the model that was created previously – Add a button to the main canvas (2) that will be our main entry point to the master data addition – Add a popup widget (3). Design the popup as shown below. Name the dropdowns as “dropDownMatType” and “dropDownMatColor” for their selection. Enable footer for the popup and name the buttons “bttnOK” and “bttnCancel”. – Add 2 script variables (4) called “materialMember” (type: PlanningModelMember) and “NewMaterialID” (type: string) – Finally, add a script object called “getAutoMaterialID” (5). This custom script will look into the existing master data and generate a new member ID (auto-increment)

Add the following code to the “getAutoMaterialID” method. Using the planning model and getMembers method this method returns the number of members in the Material dimension as a string.

Select the “onClick” event of the create material button (on the main canvas) and add the following code. This part of code, populates the data in various widgets on the popup control and then opens the popup.

function onClick() : void
{
	//get a new memberID from a custom method (see script objects) 
	newMaterialID = CustomUtils.getAutoMaterialID();
	lblMaterialID.applyText(newMaterialID);
	
	//populate the material type and material colors dropdown
	//this restricts users to select only predefined values
	var materialTypes = ArrayUtils.create(Type.string);
	var materialColors = ArrayUtils.create(Type.string);
	var allMatMembers = PlanningModel_1.getMembers("Material");
	for (var counter = 0; counter < allMatMembers.length; counter++)
	  {
		  var currentMatType = allMatMembers[counter].properties.MaterialType;
		  var currentMatColor = allMatMembers[counter].properties.MaterialColor;
		  
		  if(materialTypes.indexOf(currentMatType) === -1 && currentMatType)
			{
			   materialTypes.push(currentMatType);
			   dropDownMatType.addItem(currentMatType);
			}
		  if(materialColors.indexOf(currentMatColor) === -1 && currentMatColor)
			{
			   materialColors.push(currentMatColor);
			   dropDownMatColor.addItem(currentMatColor);
			}
	  }	
	dropDownMatType.setSelectedKey("Others");
	dropDownMatColor.setSelectedKey("Red");

	popUpNewMember.open();
}

First it gets the auto incremented materialID and assigns it to the global variable “newMaterialID” and also to the label to show the ID in the popup.

The below part of the code gets all the members in the Material dimension, loops through them including their properties (MaterialType and MaterialColor) and adds to the respective drop down boxes.

Finally, the default values of the dropdownboxes are set and the popup is shown.

On the onButtonClick event of the popup widget, add the following code.

function onButtonClick(buttonId: string) : void
{
	if(buttonId === "bttnOK")
	 {
		//Insert the new material
		 materialMember = ({id: newMaterialID, description: txtMaterialDesc.getValue(), properties: {MaterialType: dropDownMatType.getSelectedKey(), MaterialColor: dropDownMatColor.getSelectedKey()}});	
            var result = PlanningModel_1.createMembers("Material",materialMember);
			if(result)
				{
					Application.refreshData([Table_1.getDataSource(),]);
					Application.showMessage(ApplicationMessageType.Success,"Material Successfully Created");
				}
			else
				{
					Application.showMessage(ApplicationMessageType.Error,"Failed to Insert Material");
				}
		 
		 //finally Close popup
         //Empty the text box and labels
		 lblMaterialID.applyText("<auto ID>");
		 txtMaterialDesc.setValue("");
		 popUpNewMember.close();
	 }
	if(buttonId === "bttnCancel")
	 {
		popUpNewMember.close();	
	 }
}

If the OK button is clicked, assign the autogenerated ID, description and the selected attribute values (dropdowns) to the Planning model member (global variable).

Call the createMembers method of the planning Model and insert the newly defined materialMember. Subsequently, refresh the table data and show the application message that the insertion was successful. Finally, clear the values from the description text box and the label so that they are ready for next insertion. Lastly, close the popup. If the insertion fails, show the failed message.

If the cancel button is clicked, close the popup.

Finally, Demo of the application can be seen in the video below.

Conclusion:

With the new SAC release 2020.09, we can now use the PlanningModel methods to insert, update or delete members from dimension master data. The scenario explained above is quite simple. In real-life scenarios, it may be required to modify the logic to suit custom needs. The current code also populates the MaterialType and MaterialColor selections from the existing Master data values. If needed fixed values can be added to the dropdown boxes or values picked up from other dimension values. In the current blog, we explored the createMembers function. Dimension members can also be updated and deleted using the other API functions.