Starting with SAP BW Release 3.5, you can use its Business Planning and Simulation (BPS) functionality to analyze your data in new ways and create plans, budgets, and forecasts.

Although both the reporting and planning sides of BW use the same platform for querying data, the requirements and focus vary. Different rules apply to data modeling in a BW-BPS environment than for reporting. The data modeling rules that allow an InfoCube to efficiently read data and generate queries are neither necessary nor consistent with what you need to read and write the data and generate plan information.

Prior to implementing BPS, you must review the planners’ requirements and planning layout formats to understand what functions they may need later. Also, you must make sure that your InfoCubes include the characteristics and key figures related to your planning requirements. A simple way to do this is to develop a BPS design document to give the configuration group the necessary characteristics and key figures that planners need.

I’ll review data modeling methods and investigate the different approaches to accommodate BW-BPS processes and data modeling around configuration of InfoProviders, variables, and other structures in BW. I’ll focus on the data modeling issues from the application side of the process and identify required and optional components to efficiently construct an InfoCube for BW-BPS. I’ll show you how to use BW hierarchies, characteristics, key figures, dimensions, and attributes of an InfoCube in planning, as well as data modeling and planning BW-BPS functionality.

InfoCubes for Planning

SAP first introduced BPS as part of Strategic Enterprise Management (SEM). SEM-BPS used BW InfoCubes to store planned data. Usually, BW consultants would copy existing InfoCubes and activate the transactional option in the create screen, and then use the InfoCubes for planning processes. They need to rethink this strategy for several reasons.

When the InfoCube contains too many characteristics, the planners’ data can become locked by intersecting characteristic values when two planners try to access the same combinations of characteristic values at the same time. Also, the planning InfoCube’s performance may not be optimal when the system reads too much data because the InfoCube has too many unnecessary characteristics. Finally, the copied InfoCube may not contain all the necessary InfoObjects (characteristics and key figures) the planner needs.

Developers must include all of the characteristics a planner needs to formulate a consistent and efficient planning process without including unnecessary ones. I follow this rule of thumb: If the characteristic that you want to plan against is not in the InfoCube, you will be unable to access a desired characteristic easily and effectively during the planning process.

Attributes

Now I will review in detail different aspects of data modeling in BW-BPS. Planners often need to use the attributes of a characteristic in the planning process. If planners need to use the master data combinations that are available based on the upload of master data from the source system, then it is necessary to have both the characteristic and the attribute identified in the InfoCube as characteristics. Essentially, you leave the characteristic and attribute alone and add the attribute to the InfoCube as a characteristic. This means that the object is both an attribute and a characteristic. This allows you to use the inherent link between the master data of your characteristic and the attribute to plan against and to drive you to use the correct combinations of values.

When creating a planning InfoCube in BW-BPS, you should remember that a characteristic can serve dual functions — as a characteristic in the InfoCube and as a navigational/display attribute. Using the master data links between the characteristic and attributes for planning can help the planners use the correct value combinations between two or more characteristics. The system correctly generates those combinations automatically. BW creates a master data link as a unique string of values when it uploads master data from the source system. This link stores the correct value combinations that planners can post to and plan against. For example, if you plan on company code, business area, and functional area using the characteristic/attribute link, you can get the correct combinations of these three values for the planner.

You can easily accomplish this if your planning attributes also exist as characteristics in the InfoCube (Figure 1). In this situation, I want to plan based on the profit center and cost center using master data relationships functionality. Uploaded master data from the source systems allows me to create the proper combinations of profit center and cost center. This allows me to make sure that what I’m planning against really exists and is in the correct combinations.

Figure 1

You cannot access either profit center or cost center unless your InfoCube includes both characteristics

In Figure 1, the InfoCube has the characteristic cost center included and profit center is an attribute but not a characteristic. I can’t find the cost center in the list of basic characteristics to use as a root to access the profit center because the InfoCube does not contain both objects. You cannot access either the master data relationships between profit center and cost center unless your InfoCube includes both characteristics. In this situation, the planner is unable to plan by cost center and profit center. I can’t get to the characteristic cost center to even try to use the attribute profit center since neither cost center nor profit center is in the InfoCube.

In contrast, the InfoCube in Figure 2 shows that both cost center and profit center are characteristics. Profit center is also an attribute. BW allows you to access the combinations because the InfoCube includes both characteristics. In Figure 2, I can get to the cost center and in turn get to the connected profit center to use the attribute integration between these two InfoObjects.

Figure 2

Planning area with an InfoCube that contains cost center and profit center as characteristics. You must configure the InfoCube to include profit center as both an attribute and a characteristic

This scenario (characteristic as both a characteristic in the InfoCube and navigational attribute) might exist due to a specific need in a report. For example, you might report on a characteristic with the requirement to see both historic views and current views of the data simultaneously for time dependency or other unique situations.

If you want to plan on region and customer, you must supply the planner with the appropriate combinations of region and customer within that region. You should use the master data relationship between these two characteristics: Add both region and customer in the InfoCube as characteristics and also make region an attribute of customer. This generates a true set of combinations of values for the planner. Using this data modeling approach, planners can use the master data combinations and post them to the InfoCube.

Although you could use different options such as user exits to identify the correct master data combinations, this article focuses on the standard approach. The profit center-cost center combination is one of the standard sets of characteristics that connect and therefore offers this option. The master data combinations are a many-to-one relationship, and the relationship you need for BPS can be many to one or one to many. In this situation, you can use the functionality’s characteristic relationships in BW-BPS to make sure that the InfoCube includes the required attribute (e.g., profit center or customer group) as both an attribute and a characteristic.

If you want to plan on all correct combinations, it’s best to use master data relationships in your planning process rather than relying on the relationships that BW creates when you load transactional data into the InfoCube. Transactional data offers only the combinations that have been posted to, and not all of the correct combinations. You can create the characteristic relationship that I’ve been talking about based on combinations that are in the master data tables rather than relationships created based on uploaded transactional data. This scenario occurs in SAP BW 3.5 and SAP NetWeaver 2004s.

Characteristic Relationships

The characteristic relationships functionality of BW-BPS offers several options. It allows you to get to the attributes of a characteristic and accomplish the scenarios above. You can use characteristic relationships with combination check and propose and characteristic relationships with derivation.

Combination check and propose is a type of characteristic relationship in which the system checks the master data tables to confirm that the combinations of characteristic values are correct, then proposes combinations in the planning layout. In derivation, the system checks the combinations. If incorrect combinations or missing values exist, the system derives the correct combination based on the master data tables. The two methods require the same data modeling approach, but the outcome differs. Again, the rule of thumb is, if it’s not in the InfoCube, you can’t plan on it.

If you use these characteristics for this process alone, identify a separate dimension for the characteristics that you are going to use and group them together in that dimension. They do not affect other activities within your InfoCube. For instance, you should use this method when a planner wants to plan on only the combinations within the master data table for profit center and cost center values, as explained in Figure 2.

InfoObjects

You should have an InfoObject available to help with your InfoCubes’ locking or maintenance problems. When planners work in the system at the same time, multiple planners might try to plan on the same set of characteristic values. One planner can lock out other planners from working in the system. You can use InfoObject 0VERSION. It is part of standard business content and has an unlimited numbers of versions. You could also use a customized InfoObject that performs the same function as 0VERSION.

The combinations of characteristics and key figures trigger the default locking process in BW-BPS. The default locking process in BW-BPS checks who is working in the system and what they are working on. If another planner tries to post to the same values the system locks that transaction. To ensure that multiple planners don’t plan on the same combinations of characteristics and key figures at the same time, assign a series of unique versions to each planner.

The InfoObject 0VERSION can help with maintaining the data that planners post. When planners post to the InfoCube, BW opens the InfoCube request ID and leaves it open until it contains more than 50,000 records. You must manage the partitioning of the data in your request ID because you can have multiple planners working and the data in a particular request ID depends on who saved it and who was planning. This is unlike a normal BW process where you upload what is in the InfoPackage. Therefore, if one of your planners makes an error, you have to find the erroneous records versus having to point to a specific InfoPackage upload. This scenario occurs in SAP BW 3.5 and SAP NetWeaver 2004s.

If one planner identifies bad data and all planners post to the same request ID, this can pose a problem because you don’t know who found the error. To prevent this, use different versions to identify the data so that you can delete any incorrect data based on the version and possibly the date. To accomplish this, you could use the ABAP program RSAPO_CLOSE_TRANS_REQUEST_ALL3 or function module RSAPO_CLOSE_TRANS_REQUEST to close the request ID after each planner saves.

One of the other required InfoObjects required for data modeling for planning that you should be aware of is 0VTYPE. This InfoObject directly links to cost center planning and the process of retracting the data from the BW system back to the mySAP ERP Central Component (ECC) or R/3 systems. You must include 0VTYPE in the InfoCube for the retraction to work.

Key Figures

In a reporting scenario you can use functionality in Query Builder to help manipulate the data for additional information and key performance indicators (KPIs). However, in BW-BPS, you cannot use any tools for creating calculated or restricted key figures in planning layouts. In the 3.5 version of BW-BPS, a basic option called Total allows you to create a total for data in the planning layout (Figure 3).

Figure 3

If you turn on the Total functionality, you are able to create a total for both rows and columns for your planning layout

Your InfoCube must represent all the possible key figures before you can use it in a planning layout. An example of this would be the need to create a key figure such as contribution margin I, II, and III or having a key figure named net profit. A business might have this requirement for sales planning. If you review the key figures in a basic InfoCube for sales or cost center accounting, you would find that the system generates restricted and calculated key figures from the query point of view without storing them in the InfoCube.

If you use a transactional InfoCube for planning, the InfoCube creates and includes restricted and calculated key figures needed for planning. From there, you can use the planning functions available in BW-BPS to generate the calculations necessary to fill these key figures with the desired results. Therefore, in the data modeling process you need to identify all the possible key figures that you use in the planning activities and create placeholders for them to be available in the planning layouts.

Figures 4 and 5 compare the key figures in a basic reporting InfoCube to those in a transactional InfoCube for planning. As you can see, the transactional InfoCube’s list of key figures includes calculated key figures (e.g., contribution margin I, II, and III; net sales, and net revenue). In Figure 4, key figures for a basic InfoCube are based on raw data that the system uploads into the InfoCube. Few of these are based on calculations. This scenario occurs in BW 3.5 and the SAP NetWeaver 2004s release of BW-BPS.

Figure 4

The key figures in a basic sales InfoCube

Figure 5

The key figures in a transactional planning InfoCube

Another concern in the data modeling process for planning is to make sure that the InfoCube includes all objects for the planning functions. For example, your planners want to use the planning function distribution with reference data. This planning function allows planners to use other key figures in the process of distributing values top down to other levels. Say your planner wants to distribute overall business units’ variable costs. You can distribute these variable costs using a key figure such as headcount or sales revenue. You must include these key figures for planning purposes. If the required tracing factors are not in the InfoCube, then you need to look to ABAP coding to achieve the same results. This applies to SAP BW 3.5 and SAP NetWeaver 2004s.

Hierarchies

You have the option of using either a BPS-created hierarchy or a BW-created hierarchy. BPS hierarchies contain a combination of characteristics in the planning layouts. Characteristic relationships, the structure of the planning level, and the options within the planning layout can affect BPS hierarchies. BPS hierarchies are very flexible and useable. The significant difference between them and BW hierarchies is that master or transactional data combinations drive the BPS hierarchy.

Master data combinations alone drive the BW hierarchies. BPS hierarchies are easier to use in a planning layout because they require less configuration. You can use all three types of BW-based hierarchies (external, internal, and text), but only external hierarchies work directly in BW-BPS for planning. The others require additional configuration. For example, you can’t use text hierarchies from BW directly in BW-BPS as a hierarchy, although you can use them in conjunction with functionality linked to the planning layout.

Figure 6 shows BPS hierarchies with the characteristic relationships option restricting the number of profit center/cost center parent-child relationships. However, since you’re using a BPS hierarchy, you can also activate the check entry and the top down/bottom up options to control calculations and data entry. This is an example of the use of the BPS hierarchies in the planning layout. You can use this to filter the profit center/cost center combination in a hierarchy within the characteristic relationship functionality. This offers the same flexibility as a BW hierarchy within the planning layout.

Figure 6

Use BW-BPS hierarchies in the planning layout to offer the same flexibility as a BW hierarchy in the planning layout

Figure 7 shows that you can use BPS hierarchies to restrict characteristic values in the planning layout. This offers the ability to create, on the fly, a hierarchical relationship between two characteristics without creating an actual hierarchy. This technique uses the master data relationships between the profit center and cost center. This is as flexible as using a BW hierarchy in the planning layout. If a change in the relationships occurs, the BPS hierarchy picks up the change based on uploaded master data rather than waiting for an additional upload of a BW hierarchy. This scenario occurs in SAP BW 3.5 and SAP NetWeaver 2004s.

Figure 7

Use a BW-BPS hierarchy to create a hierarchy on the fly using just the master data relationships between the profit center and cost center. This is as flexible as a BW hierarchy in the BPS planning layout.

When using this approach, you need to review the performance of the planning layout. Consider that using BPS hierarchies without BW hierarchies generates a hierarchy with all possible combinations, which could slow performance. BW expends extra effort when using BPS hierarchies since it creates them on the fly, similar to the display as hierarchy option within Query Builder. Also, using characteristic relationships can negatively affect the performance of the planning process. Weigh this against the maintenance and limitations of using only certain types of BW-based hierarchies.

Dimensions

When reporting, you usually organize the dimensions in an InfoCube by creating the dimensions so that you can execute queries efficiently based on the InfoCube’s extended star schema. For example, a dimension of an InfoCube groups logical characteristics. One dimension might contain sales organization, division, and distribution channel because you would likely report on them together. Another data modeling rule for reporting is to examine the query and the drill-down process and construct the dimensions based on that information.

When planning on a transactional InfoCube this process differs, unless you plan to report on the same transactional InfoCube that you are planning against. In this case the discussion that follows would not apply, since you would need to be able to execute reports quickly as well as execute planning layouts.

In planning, the emphasis differs because you can’t slice and dice or drill down on planning layouts. You can navigate or toggle from one value to another, depending on how you have set up your planning layout, but there is no drill-down functionality. Therefore, you should create separate InfoCubes with separate dimensions for planning and reporting. This applies to SAP BW 3.5 and SAP NetWeaver 2004s.

The structure of the planning layout contains two sections — the header and the body. You may want to experiment creating the dimensions by looking at the planning layout and using that as a format for the dimensions. For example, you might have controlling area in the header of your planning layout and cost center in the body. This means that controlling area is in one dimension and cost center is in another. This goes against the traditional reporting rule of combining the compounded and base InfoObject in the same dimension.

In this example, the controlling area is compounded to cost center. Compounding means linking two or more required characteristics (e.g., you always need a controlling area for a cost center and need a chart of accounts for a G/L account). Generally speaking, compounded InfoObjects should exist in the same dimension. However, you can’t drill down on the planning layout, so you can add compounded InfoObjects to different dimensions to enhance performance.

Figure 8 shows different planning area options. In Figure 8, SDPA18 and MDPA18 are identical in all aspects. The planning area, levels, packages, and layouts are the same. The difference is the data modeling of the two InfoCubes.

Figure 8

Two planning areas, SDPA18 and MDPA18, are identical in all aspects except the data modeling of the InfoCubes

Figure 9 shows the difference in run times based on BW-BPS statistics data when executing the planning layout. In this figure, BW ran planning areas SDPA18 and MDPA18 against the planning layout. The planning layouts and all of the components were the same. The only difference was the data modeling of the InfoCube. I configured SDPA18 with the header/body of the planning area in mind and MDPA18 with the query/reporting process in mind. Notice that the run times of the two planning areas are very different. The SDPA18 ran approximately 1.2 milliseconds faster than the MDPA18 with small amounts of data.

Figure 9

Based on the statistics collected from running the planning layout for both planning areas, you can see that the SDPA18 planning area ran faster and opened the Excel planning layout more quickly

If you look at this format and use it, review the performance tuning tools for BPS to confirm that the planning environment’s performance is better if you have controlling area and cost center in different dimensions. The InfoCube is not doing any additional work. In fact, depending on the number of characteristics the planning layout uses, the smaller InfoCube runs faster than a normal InfoCube because it contains fewer dimensions.

Since the planning layout doesn’t offer the slice and dice option, the planning layout is generated once and doesn’t go back and re-execute on the InfoCube. Figure 8 shows the performance numbers for both InfoCubes. This test executed the same planning layout with the same sample data against both of the InfoCubes with different dimension structures. If you move to increasing amounts of values, you notice that the performance for the two-dimensional InfoCube is better than the regular dimensional InfoCube. Therefore, the planning layout would work faster with this data modeling option.

Peter Jones

Peter Jones is a business intelligence solution architect in the areas of CO, EC, SAP BW, BI, BusinessObjects, Planning and Consolidation (BPC), and SEM for MI6 Solutions. He is certified in all these areas and is a subject-material expert for SEM, CO, BI, and BPC, with over 14 years of experience in these areas working for SAP and MI6 Solutions. Currently, he is involved in projects incorporating SAP BW 7.4, HANA, BPC 10.0, and EPM 10.0. He has consulted in all those areas for the last nine years. Peter was an SAP instructor for seven years and has written several books about SAP NetWeaver BW, FI/CO, and has recently revised his book about BPC Implementation to version 10.1. He is a contributor to Best Practices for Financial Reporting in SAP, an exclusive anthology of articles that delivers unparalleled guidance on how to optimize financial reporting processes with SAP applications.

You may contact the author at peter.jones@mi6solutions.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.