GRC
HR
SCM
CRM
BI
Expand +


Article

 

Create Flexible Planning Workbooks Using Analysis for Office and SAP BPC 11.0

by Pravin Datar, Director, SAP BusinessObjects Planning and Consolidations Solution Management

May 23, 2018

Learn how you can create flexible planning workbooks with measures, local calculations, and local hierarchies using the SAP Business Planning and Consolidation 11.0 embedded model and Analysis for Office.

SAP Business Planning and Consolidation (SAP BPC) 11.0, version for SAP BW/4HANA introduced many new features. SAP BPC 11.0, version for SAP BW4/HANA supports both the embedded and standard models of SAP BPC. The recommended front end for the standard model is Analysis for Office with the Enterprise Performance Management (EPM) plug-in, whereas for the embedded model, the recommended front end is Analysis for Office with the Analysis for Office plug-in (AO plug-in). I include only information about the embedded model for SAP BPC 11.0, version for SAP BW4/HANA and how you can create sophisticated planning workbooks with it using the Analysis for Office AO plug-in.

New Planning Features

SAP BPC 11.0, version for SAP BW/4HANA embedded model along with the Analysis for Office AO plug-in introduced some new planning features that were not available before. In the earlier versions of SAP BPC, users could plan on the leaf nodes of a hierarchy, but the input templates did not allow planning on the parent nodes. Now users can plan on the parent nodes if they want to, and disaggregation to leaf nodes is automatically handled in SAP BPC 11.0.

Users can also add local calculations within the input template by creating a row after or a row before a dimension member. The Analysis for Office AO plug-in then can use heuristics to automatically adapt that calculation for the entire planning sheet. If the user does not want this automated adaption of the heuristic, the user can also create new design rules that are applied to specific lines in the planning workbook.

The user can now create a local hierarchy instead of using an SAP BW hierarchy if the business scenario requires it. The user is no longer restricted by the hierarchies available in SAP BW as far as the planning workbooks are concerned. Although the data is stored in the same SAP BW fact table, the flexibility available to users in creating the planning sheets is greatly improved by these features. I now discuss how you can configure these features in SAP BPC 11.0, version for SAP BW4/HANA embedded model with the Analysis for Office AO plug-in.

Configure the Features

Follow these steps to create flexible planning sheets.

1. Enter the data at a parent node. Let’s consider a hierarchy in which the product group Cola is a parent node of Cola 0.3L Can, Cola 1L Bottle, Diet Cola 0.3 L Can, Diet Cola 1L Bottle, and Diet Cola 1.5L Bottle. Also let Ice Tea be the parent node of IceTea 0.5L Bottle and Ice Tea 1L Bottle. Also let’s assume that Cola and Ice Tea are sold in Argentina, Brazil, Canada, and United States. If you want to run a query of sales revenue generated by these product groups in these countries, the query result in Analysis for Office will look similar to the one shown in Figure 1.


Figure 1
Sales revenue by country and product group

You can see that the revenue numbers are at the parent node level, but they are still available for input. You can change the revenue numbers and save the data. You can verify that they are at the parent node by displaying the product and product group in the rows. To do that, click the Display button in the design panel as shown in Figure 2.


Figure 2
Access the Design panel

Drag and drop the Product into the Rows section as shown in Figure 3.


Figure 3
Drag and drop Product to Rows

You can see that the data that was entered at the parent node is automatically disaggregated to the leaf nodes as shown in Figure 4.


Figure 4
Revenue by product and country

In SAP BPC 11.0, version for SAP BW4/HANA, you can create measures at the parent nodes. For example, if you want to create measures for revenue for specific months and quarters, then you can select the Add Restriction option from the Calculations menu of Analysis for Office as shown in Figure 5.


Figure 5
Add restrictions

When you click Add restrictions, you get the screen shown in Figure 6. There you can click specific months or multiple months. You can also create a selection for a range of months.  

 


Figure 6
Individual selections

Figure 7 shows the resulting view (which you get after you click the OK button in Figure 6) with the measures.


Figure 7
Query with measures

2. Create local calculations. SAP BPC 11.0, version for SAP BW4/HANA with the Analysis for Office AO plug-in allows you to create local calculations in the planning workbook by adding rows before or after the dimensions or specific dimension members. For example, in the above query, if you want to have a calculation about the share of the Cola product group before the Ice Tea product group results are displayed, then you can create a calculation by adding a row after Cola by selecting the New Lines option and then Row After Cola from the context menu as shown in Figure 8.


Figure 8
Add a row after Cola

As you can see in Figure 9, new lines are created after Cola.


Figure 9
Create new rows after Cola

In that new line that is created, you can add a calculation by dividing the value of Cola by the result as shown in Figure 10. The formula is automatically copied to the other occurrences of Cola in the column.


Figure 10
Add a local calculation for Share Cola

If you want to calculate the share of each month or quarter to the total value, you can create a new empty line after each Result for the product group as shown in Figure 11. As you did before, you use the context menu to insert an empty line after the result of the product group.


Figure 11
Create a local calculation for a share of each month or quarter

These calculations can be viewed in the design panel in the Design Rules tab. You can find the design rules tab by going to the design panel of Analysis for Office shown in Figure 12.


Figure 12
Access the design panel

Click the Display and then Display Design Panel options to go to Figure 13, which shows the Design Rules tab. These rules are created by default with the rule type of New Lines in Figure 13. You can change the rule type to Formulas if you want to save the calculations as formulas.


Figure 13
Design rules

Note that these design rules have an activate icon (third from left in Figure 13). This allows users to activate and deactivate the formulas as necessary. For example, if a user wants to enter the values for specific months instead of calculating with a formula, he or she can deactivate the formula and let the values be updated manually or by an Excel formula. In other cases, the user can choose to activate the formula and values are calculated automatically by Analysis for Office. Thus, SAP BPC 11.0, version for BW4/HANA with the Analysis for Office AO plug-in provides you with a lot of flexibility in creating planning sheets.

You can also create design rules for formatting. For example, if you want to create a format for the calculation, you can choose Format Cells from the context menu of the new line as shown in Figure 14.


Figure 14
Create a new format for specific rows

That opens the format screen shown in Figure 15. Here you can then choose the style for the row.


Figure 15
Design rules for the format

When you click OK button in Figure 15, you can see that the new lines for Share Year are formatted in bold as shown in Figure 16. Note also that the result lines are input enabled as the input of the result lines is automatically disaggregated to the children.


Figure 16
New format for calculations

3. Create local hierarchies. SAP BPC 11.0, version for SAP BW/4HANA embedded model with the Analysis for Office AO Plug-in allows you to create local hierarchies within the planning workbook without creating an SAP BW hierarchy. This feature is not intended to replace an SAP BW hierarchy, but rather, to allow more flexibility to the users to create additional local hierarchies to facilitate planning. For example, if you want to create a hierarchy for North America and South America in the query result shown in Figure 16 without creating an SAP BW hierarchy, you can do so within the planning sheet. The screen is displayed on the right side in Analysis for Office.

To do this, first you need to remove the existing hierarchy by dragging and dropping Flat Presentation to the rows as shown in Figure 17.


Figure 17
Create a flat presentation

Next, mark all countries and select Group Members from the context menu as shown in Figure 18.


Figure 18
Select Group Members

Enter the name America for the group header in the planning sheet. You can then mark the countries Argentina and Brazil and again choose Group Members from the context menu. Enter the header South America. Similarly, mark the countries United States and Canada, select Group Members from the context menu and enter the group header North America. This step creates a local hierarchy in the planning sheet as shown in Figure 19 without creating an SAP BW hierarchy.


Figure 19
Local hierarchy

Thus, you can create very flexible planning workbooks leveraging the features offered by the embedded model of BPC 11.0, version for SAP BW4/HANA with the Analysis for Office AO plug-in.

An email has been sent to:





 

Pravin Datar

Pravin Datar works as director in the Enterprise Performance Management (EPM) Solution Management team at the SAP BusinessObjects Division. He has more than 20 years of business experience that includes management consultancy, ERP project management, and SAP SEM, BPC RIG, and EPM solution management. He holds a master’s degree in industrial engineering and an MBA from University of California.



COMMENTS

Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!


SAPinsider
FAQ