Skip to Content
Technical Articles
Author's profile photo Prasad Gurla

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.

Assigned tags

      28 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Henry Banks
      Henry Banks

      Very insightful, thank you for posting this! Regards, H

      Author's profile photo Prasad Gurla
      Prasad Gurla
      Blog Post Author

      Thank you for the encouragement Henry. Looking forward to writing some more around SAC planning and Application Designer.

      Author's profile photo Christian Straub
      Christian Straub

      Thanks a lot for sharing! Just tried & learned it is not supported to update a member of an organizational dimension. Do you think that might be possible in the future for custom properties of the organizational dimension?

      Thanks & best regards,

      Christian

      Author's profile photo Prasad Gurla
      Prasad Gurla
      Blog Post Author

      Does not work for Organization Dimension. Additionally also does not work with Account and Version dimension.

      Author's profile photo Rio Higashi
      Rio Higashi

      Thank you for the useful blog!!!!

      this function is only available for private dimension, right?

      Is it impossible to create new members of public dimension??

      Author's profile photo Prasad Gurla
      Prasad Gurla
      Blog Post Author

      The above also works for Public Dimension.

      Author's profile photo Rio Higashi
      Rio Higashi

      Really?

      When I tried to do for public dimension, and then there was errors..

      I am going to check my code again anyway, maybe there is some mistake.

      Thank you for your reply.

      Author's profile photo Rio Higashi
      Rio Higashi

      I could confirm that it also works for public dimension. Thanks again for sharing variable information!!

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Great stuff!
      Is that including Account & Version dimension?

      Author's profile photo Prasad Gurla
      Prasad Gurla
      Blog Post Author

      Unfortunately, it does not work (yet!) with Version dimension, Account Dimension and Organization dimension. 🙁

      Hoping SAP will remove this limitation soon.

      Author's profile photo Azy Pather
      Azy Pather

      Awesome blog. Could not have been better timing for me!

      Author's profile photo Varun Garg
      Varun Garg

      Is there a way to update the value of a measure using the script?

      Author's profile photo Erik Fosser
      Erik Fosser

      Hi Prasad

      Excellent blog! I helped me a lot 🙂

      Did you find out how you can specify what hierarchy node your Material should belong to (update one of the hierarchy fields of the master data member)? It looks like it is possible in the documentation but I am unable to find the correct syntax.

      I tried the syntax below but it doesnt work...

      materialMember = ({id: newMaterialID, description: txtMaterialDesc.getValue(), hierarchies: {"<name of hierarchy>": "<ID of hierarchy node>"}, properties: {MaterialType: dropDownMatType.getSelectedKey(), MaterialColor: dropDownMatColor.getSelectedKey()}});

      Any suggestions?

      Kind regards

      Erik

       

       

      Author's profile photo Noel Munday
      Noel Munday

      I resolved this with the following format:

      materialMember = ({id: newMaterialID, description: txtMaterialDesc.getValue(), hierarchies: {<name of hierarchy>: {parentId: “<ID of hierarchy node>”}}, properties: {MaterialType: dropDownMatType.getSelectedKey(), MaterialColor: dropDownMatColor.getSelectedKey()}});

      So take note the hierarchy name is NOT A STRING. The hierarchy name is captured without quotation marks.

      Author's profile photo Radharaman Rajkumar
      Radharaman Rajkumar

      Hi Noel,

      Hope you are fine and safe.

      I tried using script below

      IDMember = ({id: newID, description: Desc.getValue(), hierarchies: {H1: {parentId: “All”}}, properties: {Properties1: dropDown.getSelectedKey()}});

      Where hierarchy name/ID is "H1" and ParentID under which I want the member to be is "All".

      But this doesn't seem to be working. Could you please help.

      If I try to create on the dimension without hierarchy everything works fine.

       

      Regards

      Raman

      Author's profile photo Noel Munday
      Noel Munday

      Hi Radharaman,

      Your syntax looks fine to me. Try normalising all your variables to direct strings and see if it works. If it does then there is an issue with one of your get methods.

      Regards,

      Noel.

      Author's profile photo Radharaman Rajkumar
      Radharaman Rajkumar

      Thank you Noel. I mapped for all the properties as well and it worked.

       

      Regards

      Raman

      Author's profile photo Noel Munday
      Noel Munday

      Excellent! It's really great that end users don't have to jump between the application and the back-end model to manage master data in hierarchies and their property values. So I'm really pleased you got it to work.

      Author's profile photo Ferry Djaja
      Ferry Djaja

      Hi Prasad,

      Thank you for your nice blog. I am quite new to planning model. I have added the member in planning model, but how to update the table in SAC Analytic App to reflect the updated member ?

       

      I posted a question here:

      https://answers.sap.com/questions/13134172/added-member-in-planning-model-not-showing-in-the.html

      Regards

      Ferry

      Author's profile photo Prasad Gurla
      Prasad Gurla
      Blog Post Author

      Have a look at Application.Refreshdata();

      Author's profile photo Ferry Djaja
      Ferry Djaja

      I have done that too. But still the data is not showing up...I guess something wrong with my model, but not sure which one. I added these records highlighted in Red box, but is not showing in Table.

      Author's profile photo Ferry Djaja
      Ferry Djaja

      It is working now. Thank you Prasad

      Author's profile photo Noel Munday
      Noel Munday

      Very nice blog. Thanks for taking the time to put it up and for going into all the detail.

      Author's profile photo Daniel Weiskircher
      Daniel Weiskircher

      Thank you for this great summary!

      One Question:

      How to best implement masterdata authorizations? As far as I am aware, there is no native method to do this. We have teams which are responsible for maintenance of there assigned dimension members (maintain attributes via the update method). A the moment, the app pulls all members of the dimension and loops over them to identify the elements the user has access to and puts them into a drop down box for selection by the user. The problem is there are about 60.000 members. It is really slow.

      I hoped there would be a filter option in the getMember method which let's me filter on an attribute, but it looks this is not possible. Also, I would expect the getMembers method to only return masterdata, the user has access to. But it delviers the complete dimension. The user has only access to a part of the dimension. This works perfectly for Stories with data. But not on the getMembers method.

      Any suggestions?

      Author's profile photo Anurodh Jindal
      Anurodh Jindal

      Hi,

      Thanks for blog.

      One Question : How to hide Amount Column?

      Thanks

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak

      Hello Prasad!

      Thanks for the blog post!

      I'm trying to create a script variable as you mentioned, but "PlanningModelMember" type is not available to the variable. Do you know what can it be?

       

      kind regards

      Mayumi

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak

      Solved myself! I had forgotten to define the Planning Model!

      regards

      Mayumi

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak

      Hello Prasad,

      I'm getting error in the lblMaterialId.applyText code. Where does the "lblMaterialId" variable fits?

       

      regards,

      Mayumi