Technical Articles
Collaborative Investment Planning (with BI licenses)
Introduction
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)
Creating the 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.
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.
Submitting Investment Proposals
(with a BI license) by Business Rep.
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();
}
How to Update Investment Proposals
(with a BI license) by Business Rep.
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
EditIP_D.open(); //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(),
properties:{D_AMT:inpAMT.getValue(),D_AMT_TIMING:ddAMT.getSelectedKey()},
};
TestModel.updateMembers("Investments",IP_D)
Application.refreshData();
this.close();
}
Done! Now your Business Rep’s can continuously update their investment proposals.
Approving Investment Proposals
(with a Planning license) by Sector Rep.
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])
Conclusion
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 light 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 & complexity. 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
This is a workaround. There’s no guarantee this may work in future.
SAP may perhaps even disable master data API through BI license.
Let’s hope for a structured solution instead of these kind of workarounds.
Let me know your thoughts!
On the one hand, Jef, absolutely impressive to come up with this creative idea to bend the system in such a way in order to achieve your requirement.
On the other hand, really sad that it is necessary to find a way to get around the (really good, feature-loaded and intuitive) standard data input functionality. No offence, Jef!
Oh I fully agree... Sad but true. We'd rather not use these kind of ugly workarounds either.
If other tools or offline spreadsheets would still be needed, it would also be a shame.