Collaborative Investment Planning (with BI licenses)

This is a use case to facilitate an enterprise-wide (cross-sector) investment planning solution.

Typically, from corporate planning perspective you rely on investment plans from each sector representative (IT, Finance, HR, Operations, etc.). These representatives would be given a SAC Standard Planning license to enter their respective Capex, Opex & FTE plans, after which cross-sector alignment takes place.

However, the investment proposals within each sector also need to be recorded, collected, reviewed and approved. Often, this still happens in offline excel files, sharepoint lists, and many, many other ways… all managed somehow within each sector and without visibility from corporate point of view.

How can we facilitate this in SAC in the mindset of collaborative enterprise planning?

How can a sector representative, connect all of his business stakeholders on the same integrated plan, and establish a single version of the truth?

Business Challenge

For investment planning, there can be many different stakeholders within each sector.

Business representatives should be able to:

  • Submit investment proposals
    (to be approved by sector representative)
  • Update latest estimates
    (to be reviewed by sector representative)

Sector representatives should be able to:

  • Approve or Reject submitted investment proposals
  • Review latest estimates
  • Create a consolidated overview of all the approved investment proposals (bottom-up)
  • Set high-level sector targets that supplement the bottom-up investment plan
  • Automatically incorporate the agreed sector investment plan into the corporate planning

License Challenge

Providing all stakeholders with a SAC standard planning license will be a bit heavy, especially the business representatives.

And how about the SAC BI license?
“Simulation” and “Basic Data Entry” are among features stated to be possible with BI license, but they only work on a Private version which cannot easily be shared with anyone else!

Technically a ‘shared private version’ is possible, but the user would have to click & assign other users one by one! So it’s very hard to get this streamlined in a central planning process.
This was all deemed unacceptable in our project.

Proposed Solution/Workaround: use SAC master data API’s

SAP SAC Analytics designer offers an API for master data creation and updates.
And the best parts are:

  • dimension master data is immediately shared with others
    So no private version, publishing, sharing or data locking steps needed.
  • this works with a BI license!
    So no planning license would be needed for the vast majority of end-users (Business Rep’s)

1. Create a Planning model:

2. Create an Investments dimension with 2 hierarchies, and a few investments with following properties:

Technical ID’s of properties used: Status (H_STATUS), Total Amount (H_AMT), Amount (D_AMT), Timing (D_AMT_TIMING)

3. Create an Account dimension with only 1 member which will be used to display the requested amounts in a table widget. It’s important to use Exception Aggregation here to let the formula calculate for each investment line item.

Notice the account formula is taking up the properties (amounts) of the Investments dimension:
IF([d/Investments].[p/D_AMT_TIMING] = [d/Date].[p/CALQUARTER] ,TONUMBER([d/Investments].[p/D_AMT] )/3,NULL )

4. Create an Analytics Designer Application containing:

  • Button widget with ‘Add’ text
  • Table widget with investment proposals (headers) using 1st hierarchy and header (H_*) properties
  • Table widget with investment proposal details (line items) using 2nd hierarchy and detail (D_*) properties

Each header represents the collective business proposal for the investment.
Each line item represents an acquisition amount in a specific period (we chose for quarter level).

Notice the account formula takes the property values Amount and Timing, and plots it nicely across the Date dimension.

Create a new popup screen that allows a user to submit a new Investment Proposal:

When user presses OK button, here comes the script that will add this new request into the Investments dimension master data:

if (buttonId === "OK") { //Add Investment Proposal Header
var IP = "IP00003"; //implement your own logic to auto-increment this (using getMembers() API)
var NewIP = ArrayUtils.create(Type.PlanningModelMember); NewIP[0] = {
id: IP,
description: IP_Descr.getValue(),
properties:{H_STATUS: "Submitted",H_AMT:inpAMT1.getValue()+inpAMT2.getValue()+inpAMT3.getValue(),D_AMT:"",D_AMT_TIMING:""},
hierarchies: {H1: {parentId: "IP_TOT"},H2: {parentId: "IP_TOT"}}
}; TestModel.createMembers("Investments",NewIP) //Add Investment Proposal line item details
var NewIP_D = ArrayUtils.create(Type.PlanningModelMember);
NewIP_D[0] = {
id: IP+"_001",
description: IP_Descr.getValue()+" 001",
properties:{H_STATUS: "",H_AMT:"",D_AMT:inpAMT1.getValue(),D_AMT_TIMING:ddAMT1.getSelectedKey()},
hierarchies: {H2: {parentId: IP}}
NewIP_D[1] = {
id: IP+"_002",
description: IP_Descr.getValue()+" 002",
properties:{H_STATUS: "",H_AMT:"",D_AMT:inpAMT2.getValue(),D_AMT_TIMING:ddAMT2.getSelectedKey()},
hierarchies: {H2: {parentId: IP}}
NewIP_D[2] = {
id: IP+"_003",
description: IP_Descr.getValue()+" 003",
properties:{H_STATUS: "",H_AMT:"",D_AMT:inpAMT3.getValue(),D_AMT_TIMING:ddAMT3.getSelectedKey()},
hierarchies: {H2: {parentId: IP}}
}; TestModel.createMembers("Investments",NewIP_D) Application.refreshData(); this.close(); }

When a user needs to update the values, to provide a latest estimate, instead of entering values in the table, you could use the onSelect() event on the line-items table to provide a nice pop-up screen for the user to adjust either the amount or the timing:

With following example logic in the table onselect() event:

var sel = this.getSelections(); //get current selected cell from the table
var IPid = "";
var IP = ArrayUtils.create(Type.PlanningModelMember); for (var i=0;i<sel.length;i++){ //loop through each cell if (sel[i]["Investments"] !== undefined) { //if there is an Investments dim member IPid = sel[i]["Investments"].replace("[Investments].[H2].&[","").replace("]",""); //get the investment id IP[0] = TestModel.getMember("Investments",IPid); //get the investment member details if (IP[0] !== undefined && IPid.length >7) { txtIP.applyText(IPid); //set id in text label inpIPDescr.setValue(IP[0].description); //set description in textbox inpAMT.setValue(IP[0].properties.D_AMT); //set investment amount into the textbox ddAMT.setSelectedKey(IP[0].properties.D_AMT_TIMING); //set investment timing into the dropdown; //open 'Edit Proposal' pop-up screen } }

and after the user clicks OK button in the onSubmit() event of the popup:

if (buttonId === "OK") { var IP = txtIP.getPlainText();
var IP_D = ArrayUtils.create(Type.PlanningModelMember); //Add Detail Line Items
IP_D[0] = {
id: IP,
description: inpIPDescr.getValue(),
}; TestModel.updateMembers("Investments",IP_D) Application.refreshData(); this.close();

Done! Now your Business Rep’s can continuously update their investment proposals.

Now that the investment proposals are all recorded in dimension master data with the ‘Submitted’ status, it is now up to the sector representative to either set proposals to ‘Approved‘ or ‘Rejected‘ status.

Similar screens and scripts as above can be used, but with the addition of a data action that could then read all investments properties and transfer only the approved proposals into the corporate plan -> and thus publish it into a public version as fact data.

For running data action & publishing steps, a planning license will be needed.

The data action script could look like like this:

MEMBERSET [d/Investments].[p/D_STATUS] = "Approved"
DATA([d/Date]=[d/Investments].[p/D_AMT_TIMING]) = ATTRIBUTE([d/Investments].[p/D_AMT])

With this workaround, you can involve more business stakeholders as part of your corporate, collaborative planning process with minimal license impact (BI licenses).

Similar approach can be applied for other planning use cases such as: Cost reduction initiatives, Expense/Budget requests, and many more…

Hopefully some day SAP enables ‘simple data entry’ with a BI license, where the data entry can more naturally flow into the corporate plan and into a public version, without these kind of workarounds.

p.s. This was just a small demo and still a (highly) simplified example of real implementation requirements. Some more limitations you should be aware of:

  • You cannot update the Currency property with master data API()
  • You cannot update read/write properties for Data Access Control with master data API()
  • No revert/redo options with master data
  • Unsure if change log / audit features on master data are sufficient.
  • Unsure if entering all investment financials in dimension master data is secure enough

Let me know your thoughts!