Creating an Input-Ready Query for BW Integrated Planning
How to plan business data with BW Integrated Planning
BW Integrated Planning is an infrastructure used to plan business data on SAP BW, branded as the tool BPC 10.1 Embedded.
BPC Embedded no longer sits “on top” of BW like the ‘Standard’ versions of BPC, but objects are created in BW and planned in a front-end tool like Analysis for Office, using a Business Explorer Query (BEx) as a source for the input schedule. Being “integrated” allows for improved save and refresh times, synchronized Master Data, and is better at enforcing a unique business case compared to previous BPC releases.
There are several other advantages of BPC 10.1 Embedded over 10.1 Standard, mainly due to differences in the key figure based model used for embedded as opposed to the account based model in standard. Read more HERE (Link to Graham’s blog post).
Today’s blog post will outline at a high level the steps necessary to create an Input-Ready query.
The Planning Model in BW includes a variety of objects, consisting of data created in InfoCubes, as well as DataStore objects for direct updates. To set the level of data that can be entered, an input-ready BEx query should be built on an InfoProvider of type aggregation level.
An Aggregation Level denotes the level in which the cube will be planned on. While an InfoCube may contain all key figures (measures) and characteristics (dimensions), an Aggregation Level will consist only of the key figures and characteristics necessary to reach a granularity to plan on a specific intersection of data.
Planning on an InfoCube alone is possible, however in most all cases using a MultiProvider is best practice for a few reasons:
- MultiProvider is a logical view of a combination of DSO, InfoCube and other InfoObjects (real-time, planning, etc.)
- Allows planning/reporting on multiple InfoObjects if no aggregation levels are included in the MultiProvider
- MultiProvider uses parallel processing when queries are executed
- Any future cube updates can be added to the MultiProvider, unlike a singular InfoCube
In this example, we will assume that we are planning and reporting on multiple cubes of data in a MultiProvider, and creating an Aggregation Level on that logical view. SAP BusinessObjects Analysis for Office will be used as a front-end application for planning AND reporting, utilizing a BEx Query to pull data from a BW system.
NOTE: You should set a standard naming convention for all BW Objects using in the Planning Model before following any of the below steps.
HIGH LEVEL OVERVIEW
AGGREGATION LEVEL CREATION
Navigate to t-code RSPLAN in BW to create an Aggregation Level on the MultiProvider and drag/drop Characteristics and Key Figures to create your level of aggregation. The level of aggregation’s dimensionality must be “filled” to plan at that level.
Example: You are planning on an Aggregation Level, which sits on top of a MultiProvider. The MultiProvider has MANY InfoProviders. The Aggregation Level consists of the Characteristic InfoProvider, but does not denote WHICH InfoProvider you are planning on. You MUST either restrict to ONE InfoProvider in a Planning Filter or in the BEx Query itself (See Restrictions sections). This is true for every Characteristic that has many values.
1.) Open the BEx Query Designer and (1) Select New, (2) Select Find, (3) Search for your aggregation level by technical name, and (4) select Open.
NOTE: By utilizing * after any combination of characters, the system will find ALL InfoProviders with those characters before the star, and return that full list.
This is particularly helpful if a standardized naming convention has been established, allowing developers to find common queries within the same functional area. This method of search can be used in most SAP tools.
2.) Drag and drop Key Figures and Characteristics in the (1) row or (2) column axis. Depending on the report view desired, key figures may be on the rows OR columns, but not a combination of Key Figures and Characteristics on the same axis.
(3) Attributes of a characteristic may be used in the report and drag/drop in the same fashion as above.
(4) Free characteristics are used as navigational attributes within the Input Schedule or Report, and can be utilized like a pivot similar to Excel.
Planning filters can be used to narrow the data set by restricting values at the Aggregate Level, prior to being pulled into the report by the BEx query. Utilize these in the query itself by dragging the available (5) Filter to (6) Characteristic restrictions (Created in RSPLAN similar to Aggregate Level).
For a simpler requirement, drag a Characteristic and drop on (6) Characteristic Restrictions and follow the instructions below to restrict within the Query.
3.) INPUT READY SETTINGS
Select the Key Figure in Rows or Columns pane, Select Planning pane in Properties tab, Check the Input-Ready (Relevant for Locking) box.
STARTING QUERY IN CHANGE MODE:
Change mode will allow users to automatically launch the Analysis Workbook with the Query in change mode, which allows input of values.
(1) Select the drop down in Properties tab, (2) Select the name of the Query.
(3) Select Start Query in Change Mode
NOTE: If the intersection between Key Figure and Characteristic is open in ANY way depending on the dimensionality in the Aggregation Level, the Key Figure will not be Input-Ready after checking the above, and will not be started in change mode.
CREATING A PLANNING FILTER, PLANNING FUNCTION, PLANNING SEQUENCE
For more advanced requirements, the use of planning filter in conjunction with a planning function and planning sequence is necessary.
These can be used in an Analysis Workbook to call a specific planning sequence in conjunction with VBA to meet business requirements (ie. Copy a forecast to another dimensionality, calculate a certain tax rate on data save, etc).
Planning functions provide the below procedures:
- Unit Conversion
- Generating Combinations
- Delete Invalid Combinations
- Reposting by Characteristic Relationships
- Distribute by reference data
- Distribution by keys
- Currency translation
BW-IP provides several canned allocation procedures cited above, but for many advanced business requirements the developer will need to write a FOX formula (code written in a Planning Function of type Formula). Read more here: FOX Formula Example
INSERTING QUERY INTO ANALYSIS WORKBOOK
- (1) Launch Excel, (2) Select Analysis tab, (3) click Select Data Source.
2. Login to your development system
3. Search for your Query with * indicator, select the query and select OK