Unlocking Advanced Planning Capabilities in SAP Analytics Cloud: a Deep Dive into Scripting for Customized Sales Promotion Processes

In SAP Analytics Cloud (SAC) planning, businesses often rely on standard building blocks like Data Actions, Allocations, and Multi Actions to meet their operational needs. While these standard artifacts prove effective in most scenarios, there are situations where the complexities of a particular planning process demand more than these conventional artifacts. The question then arises: In cases where direct IT department involvement, such as uploading files into the model, is impractical and not desired, can SAC Planning still offer a viable solution?

The affirmative answer lies in the power of scripting using APIs. However, before delving into a fully scripted planning process, it is essential to thoroughly evaluate whether the standard artifacts can genuinely address the unique demands of the process. It’s worth noting that scripted solutions are specifically crafted for analytic applications and unified stories.

An exceptional planning process could involve planning sales promotions for products. This blog will guide you through the overarching concept of the devised solution. It is not a manual but aims to provide you with insights into potential options if your company is grappling with similar requirements.

 

Planning Sales Promotions

A promotion is characterized by the following:

• It needs to be created on the fly by a user
• It has several properties (like product, type, color, etcetera)
• It has a certain validity time span
• It can be changed/ deleted

 

Therefore, the difficulties associated with these include:

• a member of dimension “Promotion” needs to be created on the fly. Creating numerous members in advance doesn’t make sense, as it could hinder the user when generating a promotion, especially if there are no “free” members available.
• next to a quantity of the promotion (i.e. number of products it concerns) also property values of the promotion member need to saved
• as the promotion has a certain validity time span, the quantity needs to be equally distributed over the days within that timespan

 

In order to meet all the requirements and taking into account the difficulties, scripting is the solution here.

As values of the properties of a dimension can not be entered using a table, widgets like dropdowns, inputfields, texts etcetera can be used to fill out the values of the properties. These widgets can be part of a pop-up which appears when the user clicks a button “Create Promotion”.

But of course a member of the Promotion dimension should also be created. In order to accomplish this, the “.createMembers” api is used.

Script 1: Create the Promotion member

 

After the user fills out all the properties of the new promotion member, including its quantity, in the respective input fields, drop-downs, text fields, etc., within the pop-up, the input can be processed and saved. This processing occurs through scripting when the user clicks the OK button on the pop-up.

The validity timespan of the promotion is determined by the Start and End dates captured in its properties, allowing for the calculation of the timespan. Subsequently, the promotion quantity per day is computed. To distribute this daily value across the timespan, a loop is executed over a created date array, handling the submission date by date. Finally, within the same script, the properties are updated, and the plan version is published.

Script 2: Create the date array, where “do-times” is the number of days within the timespan minus 1 (as the start date itself is not taken into account using the global script Distributeperday)

 

Script 3: global script Distributeperday

 

Script 4: Distribute the quantity per day over the days of the timespan

 

Script 5: Update the properties and publish.

In essence, we have successfully generated a new Promotion member with assigned property values, distributing its quantity evenly across the specified validity timespan through the utilization of scripting. And all of this is done without any interference of the IT- department or key-user and all-in one go!

The next question that arises is how to handle the scenario when the user wants to edit a promotion. Initially, the user selects a promotion from an overview table displaying all promotions and clicks an “Edit Promotion” button. Subsequently, an edit pop-up screen is opened, resembling the creation pop-up. Since the properties of the selected promotion are known, the dropdowns, input fields, texts, etc., in the edit pop-up can be populated with the current values. Users have the flexibility to make changes, and upon pressing the “OK” button of the edit pop-up, scripting is utilized to update the members with the new values.

It’s important to note the following: if the user decides to modify the product, timespan and/or quantity of the promotion, the current quantity must first be set to 0. This precaution is necessary to ensure that currently booked dates do not retain their previous values, preventing any inaccuracies in the total quantity.

Script 6: Set the Promotion quantity to 0

 

This is the good “out of the box” part of SAC. Only the dates which had a value for this specific selection are set to zero.

Once this step is completed, essentially the same logic and approach as described during the creation of a promotion member can be followed.

 

Mass creation / Mass edit

“If we can create and edit dimension members along with their properties, and submit quantities evenly distributed across the validity timespan for individual dimension members, could we extend this capability to multiple dimension members with just one click?”

This requirement emerged after the business observed that the solution was functioning seamlessly. Automating the creation or editing of promotions would significantly benefit users, eliminating the need for manual, one-by-one processes.

The response to this requirement was affirmative! You may have encountered informative blogs on uploading Excel files into SAC stories or analytic applications. While this blog isn’t intended to replicate those, they served as inspiration for expanding the functionality. In essence, it boils down to the following.

A fixed format in Excel is created where for example column A is the Product property, column B the Color, column C the Type, column D the Start_Date, column E the End_Date and column F the quantity.

Figure 1: Fixed format Excel for creation of Promotion member. * means mandatory to fill.

 

What’s crucial in this context is ensuring that the values and formats utilized in the Excel sheet precisely match the possible values of the dropdown fields in SAC. For instance, if the Color dropdown has values “RED” and “BLUE,” but the Excel sheet uses lowercase (“red”), the Color dropdown in the Edit pop-up won’t display “RED” and will remain empty, which is not the desired outcome.

Regardless, when the user has finished filling out the Excel, they should select all cells excluding the header—in this example, from cell A2 to cell F3—and copy it. Subsequently, in SAC, this selection is pasted into an input field, then pressing a button triggering a script that essentially performs the same actions as described earlier when creating a promotion member. The difference now is that it is executed automatically for each line in the Excel, one by one.

So, essentially, the user now has the capability to create promotions, inputting property values, and equally distributing the quantity within the validity timespan by simply clicking a single button after pasting the Excel input into the SAC input field.

Figure 2: Inputfield for pasting the Excel selection. “Import Data” starts the process.

 

A similar streamlined process has been devised for editing promotions with just one click. The user initiates the process by selecting the promotions for editing from a table in SAC, downloading the fixed-format table to Excel, and pasting it into an Excel format for editing promotions. Subsequently, the procedure closely mirrors the manual edit process described earlier.

 

Deletion of a Promotion

Last but not least, it is also possible to delete a promotion member. To achieve this, the user sets a promotion to the status “delete” (i.e. a value of the property “Promotion Status”). This alteration can be executed through the “Edit Promotion” button or via the mass edit option in the event that multiple promotions need to be deleted.

Through scripting, these promotions can be gathered from a “help table” containing promotions with the status “delete.” However, they cannot be deleted directly without first eliminating the transactional data. This is where the standard building block “Data Actions” comes into play. A Data Action is created to delete the transactional data first, and in the subsequent line of the script, the promotion members are deleted from the promotion dimension.

Script 7: Deletion of the Promotion members

 

Conclusion

SAP Analytics Cloud (SAC) Planning provides standard building blocks like Data Actions, Allocations, and Multi Actions for operational needs. Yet, for intricate planning processes, scripting with APIs is a powerful solution or enhancement. In essence, scripting empowers businesses to customize planning without direct IT involvement.