Manage dates-driven planning processes with SAP Analytics Cloud

[Thomas_Cayzerg]

Introduction

In many operational planning scenarios, companies need to leverage key-dates information to calculate their forecasted figures precisely.

The common reflex is to use a well-known spreadsheet application, embedding some easy-to-use and well documented date calculation functions.

But relying on spreadsheets for such critical and massive calculation process might be limitative for business execution.

In this blog, we will present you how you could leverage SAP Analytics Cloud Planning for modelling and writing business logic to serve such operational planning use cases.

Description of the business scenario

Let’s consider a simple business case where a company must forecast the terms of projects according to an estimated calendar.

Each projects controller would be responsible of a dedicated portfolio and would like to perform his own simulations based on dates assumptions.

A global page would be dedicated to collect planning assumptions of project step term, then a business logic would be executed to transposed them in a financial vision available for reporting.

Of-course, not-only dates assumptions can be collected this way and project referential might be enriched with more analytical details for reporting purpose (status, delay root cause, geography, …).

Designing the planning model

Login to your SAP Analytics Cloud tenant as a user with a planning professional role.

Go to Modeler menu with the left bar panel and click on Create new model tile:

Select the option Start with an empty model and click on the Create button:

Once the Model Structure workspace of the Modeler has opened, add a new dimension to manage the projects with the + button.

Call the new dimension “Project”, select Generic as type (to enrich the master data through the JavaScript API), and keep it private (just for our example). If you select public, choose a unique dimension Id over the tenant. Click on the Add button to create the new project dimension:

In the same manner, add a new Country dimension to your planning model.

Add a new measure with the + button, name it Amount and keep the default settings for type, aggregation and format:

Save your planning model with the Save button:

Name your model M_ProjectPlanning and save it into a dedicated folder you create with the new folder button:

Edit the Project dimension table with the dedicated arrow button:

When the Project dimension worksheet has opened, create a new parent-child hierarchy from the right bar panel:

Give an ID and a description, then click on the Create button:

Create a new property from the right bar panel for handling the billing date; click the Create button to validate:

From the right bar panel, add a new property for handling the project’s country; click on Create to validate:

A new Geo Area property has been created. Possible property values are managed by SAP Analytics Cloud and can be downloaded with the info link:

Add two new properties "IsProject" and "IsMilestone" as single text values:

Definition of properties is now complete for our use case, but you can of course define more project properties to help characterization (manager, sub-region, intermediate dates, …) or facilitate the story navigation.

Copy the content of the below table without the header row, and paste it from the second row of the project members’ grid:

ID	Description	H1	Billing date	Country	Is Project	Is Milestone
ALL_PJT	All Projects	<root>		DE
PJT_AAAA	Project AAAA	ALL_PJT		AU	X
PJT_AAAA_001	PTC Storage Q3-2024	PJT_AAAA	2024-09-15	AU		X
PJT_AAAA_002	PTC Storage & Maintenance Q4-2024	PJT_AAAA	2024-12-15	AU		X
PJT_DDDD	Project DDDD	ALL_PJT		ES	X
PJT_DDDD_001	PTC Storage Q3-2024	PJT_DDDD	2024-09-20	ES		X
PJT_DDDD_002	PTC Storage & Maintenance Q4-2024	PJT_DDDD	2024-12-18	ES		X
PJT_HHHH	Project HHHH	ALL_PJT		BR	X
PJT_HHHH_001	PTC Storage & Maintenance Q4-2024	PJT_HHHH	2024-12-10	BR		X
PJT_HHHH_002	PTC Storage & Maintenance Q4-2025	PJT_HHHH	2025-12-15	BR		X
PJT_XXXX	Project XXXX	ALL_PJT		CA	X
PJT_XXXX_001	Holdback - Hydro Quebec Testing	PJT_XXXX	2024-06-30	CA		X
PJT_XXXX_002	Holdback - Hydro Quebec Testing 2	PJT_XXXX	2024-09-30	CA		X
PJT_YYYY	Project YYYY	ALL_PJT		PL	X
PJT_YYYY_001	Punch-List Holdback	PJT_YYYY	2024-10-15	PL		X
PJT_YYYY_002	Punchlist Holdback	PJT_YYYY	2025-03-20	PL		X

Click on the Save button to save the model:

From the right bar panel, click on the info button and the Download locations link:

Open the downloaded Supported_locations.csv file with Excel.

Select the columns B and C and copy them:

Paste the columns to a new worksheet and remove the data/duplicates with the Excel menu:

Copy the content of the columns A and B:

Edit the Country dimension table from the SAC Modeler:

Paste the content of the clipboard starting at the row 2 to populate the members’ grid:

Add a new parent-child hierarchy from the right bar panel:

Add a new dimension member for handling the global world position:

Attach the Global and Unassigned members to the root node of the hierarchy.

Attach all other countries to the Global node:

Click on the Save button to save the model.

Open the Date dimension table:

Modify the Dimension Table properties to set the default booking behavior to unassigned and the default date hierarchy to Year, Month:

Save the planning model.

Add a new measure named “Probability” to the model measures list.

Set scale as Percent with a precision of 2 digits, set Exception aggregation to Average for all dimensions as Exception:

Add a new measure named “ExpectedYear” to the model measures list.

Set it as integer, with Max as exception aggregation and none as unit:

By copying ExpectedYear measure from the 3 dots menu, create two new measures “ExpectedMonth” and “ExpectedDay” in the same way:

Save the model when finish.

Our planning model is now ready to be used through SAC stories.

Creating a planning story

From the modeler view, create a new canvas story:

Insert a new table object into the canvas:

The new table should be automatically linked to your planning model M_ProjectPlanning.

From the builder panel, arrange the table structure to get Projects in rows and Version and Measures in columns:

Select all the available measures and sort them to get Probability at the end:

Right click the Actual version and copy it to a new Plan version:

Publish the new private version Plan version as public version Plan:

Select the columns 2 to 6 in the table and hide them:

Drill to the leaf level of the project dimension by right clicking the Project dimension header:

Add projects properties Billing date and Country to rows:

Save the story as “S_ProjectPlanning” in the same folder as the planning model:

From the right builder panel, filter the Country dimension to the unassigned member:

Do the same for the Date dimension (it should be the case by default):

Save the story from the story menu.

Now, every data entered with this story table is described by project, version and measure only.

Populate some Actuals values in the table and publish them:

Feed expected dates information for the Plan and the associated probability:

Publish the Plan assumptions when you are done.

Simplify user experience for planning with dates

Instead of changing each expected project date individually in the story, we would like to manage it through a simple user interface.

The Datepicker widget is a custom widget, that helps to work this way (if the widget is not available on your tenant, ask your admin to download and install it from https://github.com/SAC-Custom-Widgets/datepicker).

From the story designer, insert a new custom widget Datepicker in your canvas:

Then insert a new text label before the date widget and set its value to “Expected Date:”:

Add two more labels upon the former as “Selected Project:” and “Please select a project…”:

Rename the Datepicker_1 widget instance to “Datepicker_SelectProject” and the Text_3 instance to “Text_SelectProject” in the Outline section of the left bar panel:

Add a global variable for handling the planning model from the Scripting area of the Outline view:

Select the model M_ProjectPlanning and rename the model variable accordingly before clicking the done button:

Add a new script global variable from the outline view and rename it “gProjectId”:

Add a new function on Table_1 for handling the selection event:

Copy and paste the following code to the function:

// Retrieve current table selection
var select = Table_1.getSelections();

// Extract selected project id
var project_id = select[0].Project.substring("[Project].[H1].&[".length, select[0].Project.length-1);

// Retrieve member from the Planning model
var vproject = M_ProjectPlanning.getMember("Project", project_id);

// Set project desc to the UI
Text_SelectProject.applyText(vproject.description);

// Retrieve selected project data
var year_data = Table_1.getDataSource().getData({Version: 'public.Plan', CustomDimension1: 'ExpectedYear', Project: select[0].Project });
var month_data = Table_1.getDataSource().getData({Version: 'public.Plan', CustomDimension1: 'ExpectedMonth', Project: select[0].Project });
var day_data = Table_1.getDataSource().getData({Version: 'public.Plan', CustomDimension1: 'ExpectedDay', Project: select[0].Project });

// Build a JavaScript Date variable from YYYY MM DD
var vdate = new Date(Number.parseInt(year_data.rawValue), Number.parseInt(month_data.rawValue)-1, Number.parseInt(day_data.rawValue));

// Set the data to the UI
Datepicker_SelectProject.setDateVal(vdate);

// Store the select project id for further operation
gProjectId = select[0].Project;

Save you story and test it in View mode:

When you click on a row in the projects table, the date widget must now present the full expected date:

Go back to the story in edit mode (close the browser View tab).

Add a new function to the DatePicker_SelectProject instance by clicking the fx symbol:

Copy and paste the following JavaScript code to your new function:

// Local variable for handling boolean result
var bresult = false;

// Retrieve new date value
var vdate = this.getDateVal();

// Write to the dates measures in the table
bresult = Table_1.getPlanning().setUserInput({ 'Version': 'public.Plan', '@MeasureDimension': 'ExpectedYear', 'Project': gProjectId }, vdate.getFullYear().toString());
bresult = Table_1.getPlanning().setUserInput({ 'Version': 'public.Plan', '@MeasureDimension': 'ExpectedMonth', 'Project': gProjectId }, (vdate.getUTCMonth()+1).toString());
bresult = Table_1.getPlanning().setUserInput({ 'Version': 'public.Plan', '@MeasureDimension': 'ExpectedDay', 'Project': gProjectId }, (vdate.getUTCDate()+1).toString());

// Submit the data to the backend
if(bresult) {
       bresult = Table_1.getPlanning().submitData();
}

Save the story and execute it in View mode.

Select a project and modify the date value directly in the datepicker widget using the displayed calendar.

The new date must now be written to the selected project:

You are now able to change every project expected date from a nice visual widget using this method.

As usually in SAC Planning, you can revert your changes with the Version management button or the History list:

More planning KPIs based on dates

Calculating some additional KPIs like the expected delay in days per project could be something useful to forecast additional costs.

Reopen the M_ProjectPlanning model from the main left bar menu:

Navigate to the Calculations Workspace:

Add a new calculated measure with the + button and name it “ExpectedDelay”:

Copy and paste the following formula to the editor:

([ExpectedYear]*31556926 + [ExpectedMonth]*2629743 + [ExpectedDay]* 86400 - (INT(TONUMBER(SUBSTRING([d/Project].[p/BillingDate] ,0 ,4 )))*31556926 + INT(TONUMBER(SUBSTRING([d/Project].[p/BillingDate] ,5 ,2 )))*2629743 + INT(TONUMBER(SUBSTRING([d/Project].[p/BillingDate] ,8 ,2 ))) * 86400)) / 86400

We calculate the difference between the expected date and the Billing date property in seconds, then convert it in days (1 day = 86.400 sec). The behavior is equivalent to the standard DateDiff function but applied to measures and properties values (vs date members).

Define the unit type by typing Day as a new label:

Set the scale to none and the decimal places to 2:

Save the model when finish:

Reopen the S_ProjectPlanning story in edit mode and go the first page, select the Table_1 widget.

Open the Measures filter in Column and add the Expected Delay measure to the selection:

Save the story and open it in view mode.

You must now be able to display the Expected Delay KPI in the table:

When you change the expected date from the Datepicker widget, the expected delay is recalculated dynamically:

Financial logic based on dates

SAP Analytics Cloud can help you to go further with date-based operations by transforming your delay assumptions into a financial vision.

We wanted to see the impact of costs over time periods and geography to analyze delay shifting effects and its financial impact.

For such a purpose, we are going to define a business logic based on a Data Action.

Open the story S_ProjectPlanning in edit mode and add a new canvas page:

Add a new table widget from the menu and set Project and Country in rows, Version and Date in columns; filter on Amount measure:

Add a new table filter to exclude the Unassigned Country and do the same for the Date dimension:

Save the story with the save button.

Keeping the story tab, open a new tab in your browser to SAP Analytics Cloud.

Go to the Data Actions menu and click on the Create New Data Action tile:

Name the Data Action and attach it to your planning model M_ProjectRisk; save the Data Action:

Add a new copy step from the main menu:

Position the filters appropriately to retrieve Actuals amounts from the input page, i.e. Country and Date as unassigned and All Projects:

Add a new Advanced Formulas Step:

Name it “Calculate Plan”.

Add new parameters from the top menu:

Add a parameter for the list of dates:

And add a parameter for the list of projects:

Open the Script view of the Calculate Plan step and paste the following script code:

//-----------------------------------------------------------------------------------
//Set Configurations Definitions for the Advanced Formulas.
//-----------------------------------------------------------------------------------
CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.GENERATE_UNBOOKED_DATA = OFF

//-----------------------------------------------------------------------------------
//The data region against which the formulas will be executed.
//-----------------------------------------------------------------------------------
MEMBERSET [d/Measures] = ("Amount", "ExpectedYear", "ExpectedMonth", "ExpectedDay", "Probability")
MEMBERSET [d/Country] = "#"
MEMBERSET [d/Date] = BASEMEMBER([d/Date].[h/YM] , %Dates%)
MEMBERSET [d/Project] = BASEMEMBER([d/Project].[h/H1], %Projects%)

INTEGER @EXPECT_YEAR
INTEGER @EXPECT_MONTH

//-----------------------------------------------------------------------------------
//Calculation and Data Writing
//-----------------------------------------------------------------------------------

FOREACH [d/Project]

       @EXPECT_YEAR = RESULTLOOKUP([d/Measures]="ExpectedYear", [d/Date]="000000")
       @EXPECT_MONTH= RESULTLOOKUP([d/Measures]="ExpectedMonth", [d/Date]="000000")

       IF YEAR([d/Date])=@EXPECT_YEAR AND MONTH([d/Date])=@EXPECT_MONTH THEN
              DATA([d/Measures]="Amount", [d/Country]=[d/Project].[p/Country]) = RESULTLOOKUP([d/Measures]="Amount", [d/Date]="000000")
              DATA([d/Measures]="Probability", [d/Country]=[d/Project].[p/Country]) = RESULTLOOKUP([d/Measures]="Probability", [d/Date]="000000")
       ENDIF

ENDFOR // Project

Use the Validate button to ensure, that no error is found:

You can switch to Visual mode to analyze the code of the Calculate Plan step:

For each single project, the script checks to expected date value and copy the amount and the probability to the appropriate date period and country member.

Add a new advanced formulas step to clear the actual figures we copied on the first step.

Name it “Clear Actuals”:

Paste the following script code in the script view:

//-----------------------------------------------------------------------------------
//Set Configurations Definitions for the Advanced Formulas.
//-----------------------------------------------------------------------------------
CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.GENERATE_UNBOOKED_DATA = OFF

//-----------------------------------------------------------------------------------
//The data region against which the formulas will be executed.
//-----------------------------------------------------------------------------------
MEMBERSET [d/Measures] = "Amount"
MEMBERSET [d/Country] = "#"
MEMBERSET [d/Date] = "000000"
MEMBERSET [d/Project] = BASEMEMBER([d/Project].[h/H1] , %Projects%)

//-----------------------------------------------------------------------------------
//Calculation and Data Writing
//-----------------------------------------------------------------------------------
DELETE()

Validate and save the data action with the Save button:

Back to the story S_ProjectPlanning in edit mode, add a new data action trigger:

Name the trigger and select the Data Action created before; you can add page or story filter to preselect the execution context for target Version, Date and Project values:

Save the story.

You can test immediately the Data Action by clicking the arrow button.

Select the dates 2024 / 2025, and all the projects, click the Run button:

Once the execution finished, the table is refreshed with the calculated values:

Use the hierarchy selection, story filters and formatting options to drill into the calculated data by month, project and country:

New assumption can be made on the projects delays in the first page and the plan simulation can be recalculated on demand before publishing it.

More advanced presentation can also be set, like displaying over a geo map:

Conclusion

This simple project planning example lets you understand how SAP Analytics Cloud can be configured for managing dates-based planning scenario with few concepts.

Interoperability between the modeler, the story and the data action is key to handle on-the-fly calculations or user triggered business logic.

We hope that would now extend this example to your own use cases and more easily fill the gap between operational planning based on dates and financial reporting.