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.
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.
Access the Design panel
Drag and drop the Product into the Rows section as shown in 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.
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.
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 7 shows the resulting view (which you get after you click the OK button in Figure 6) with the measures.
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.
Add a row after Cola
As you can see in Figure 9, new lines are created after Cola.
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.
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.
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.
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.
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.
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.
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.
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.
Create a flat presentation
Next, mark all countries and select Group Members from the context menu as shown in 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.
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.