SAP Netweaver Business Intelligence BI / BW

Symptom

This note gives you an overview of the most important factors which influence performance that you must take into account when you create planning applications in BI planning. The note discusses the following topics:
1.Background knowledge for BI planning architecture2.Important influencing factors for performance3.Recommendations for modeling4.Availability of optimizations

Other terms

BI planning, planning modeler, input-ready query, planning function, planning sequence, planning buffer, delta buffer, characteristic relationships, data slices, performance, memory consumption
Reason and Prerequisites

Customizing and design of BI planning
Solution

1. Background knowledge for BI planning architecture

The most important BI planning components for this note are:
Planning buffer: You can use this buffer to process queries from BI planning to the data manager. These queries occur using a technical query that is automatically generated by the system. As a result, the OLAP cache for this technical query can be reused to buffer queries.Delta buffer: The delta buffer contains all the delta records that occur during a planning session up until a “Save” operation. The delta records are generated from input-ready queries after each round trip (manual entry) and from planning functions.Input-ready queries: These are normal queries that are defined for InfoProviders that are supported in BI planning. In addition, at least one structural component is ready for input for these queries and the query is started in change mode.Planning functions: A planning function is a planning function type at the time of execution, that is, with all replaced parameters and variables. A planning function selects the data to be changed and, if necessary, reference data, using the planning buffer. It executes the algorithm (for example, copy), determines all delta records and writes them to the delta buffer.Characteristic relationships: These are rules that you can define for real-time InfoCubes so that you can maintain allowed characteristic combinations for BI planning. These rules also enable you to derive characteristic values or generate a quantity of valid characteristic combinations from a selection.Data slices: These are selection tables or rules (exit) that enable you to prevent data records from being changed by manual planning or planning functions.Planning application:This is an application created by the BEx Analyzer in MS Excel or a Web application created with the WAD (Web Application Designer).

Each data changing component (input-ready queries and planning functions) of BI planning must take into account the constraints of BI planning (characteristic relationships and data slices) and additional technical constraints (validity of master data and consistency of master data ID (SID) values) so that a consistent database status is transferred to a new consistent database status after you change and save data. There is also a locking concept for the data to be changed. Here, the quantity of data specified in a selection table is locked (regardless of whether it exists in the real-time InfoCube or not).
If you compare the runtime of reporting and input-ready queries, there may be considerable differences in the runtime due to the effort required to ensure the planning model is consistent. The more time consuming the planning model is, the higher the ‘price’ for the runtime. Note also that although redundant data generally has a positive effect on performance, in the planning area it can be counterproductive because even redundant information must be checked for consistency (for example, SIDs, navigation attributes and redundant times). You can find more detailed information about this later on in this note.
We now want to describe which activities the system carries out at runtime for planning functions and input-ready queries. We will only describe the work steps roughly here: For extreme data models (very large MultiProviders, a large number of variables, a very complex authorization concept), other steps may also be relevant for the performance of the whole application.

1.1 Planning functions at runtime.

Here, we assume that the planning functions are not executed in the context of a planning application (that is, in connection with an input-ready query). During the execution of a planning function, the system behaves as follows:
1. First it replaces variables in the parameters of the function. It also replaces the variables in the filter that describes the selection of the planning function. If you use a planning sequence, the system carries out this replacement for parameters and filters.2. Then, the system determines which data must be read in change mode (planning data) and, if necessary, which data must not be read in change mode (reference data). This data is read by the planning buffer. The planning buffer takes into account any delta records that may have been created in the session.3. The algorithm that forms the planning function is now executed. In this case, the system changes, creates or deletes data records. At this time, the system also checks the conditions.4. The system now checks whether the data records are consistent: Data records that have just been created must be contained in the planning function filter. In addition, all characteristic values must be valid (check against master data table, if it exists) and they must conform to the characteristic relationships. Changed or new data records are not allowed to be protected by data slices.5. if the data is consistent, the system determines the delta records and writes them to the delta buffer. In the delta buffer, the system determines which fields can be derived from the filled fields and the relationships of type derivation. Then the system fills these fields as necessary.

If you execute the planning functions several times or if the planning data or reference data required by a function already exists in the planning buffer, the system does not access the database (or SAP NetWeaver BI Accelerator (BIA), if it exists).
The system always carries out the three steps ‘read’, ‘process data’ and ‘write changes to the delta buffer’ for each planning function in a planning sequence. See section 3.4.3.

1.2 Input-ready queries at runtime.

Here, we assume that only queries and at least one input-ready query exist in the planning application. At the start of the application, the system executes the following actions:

1. The system replaces the variables of the query.2. Then it opens an online analytical processing (OLAP) instance (if necessary, additional instances for other queries) that now determines the required planning data and reference data. The system determines the reference data from the settings of the structural components of the query and, if necessary, from the restrictions on InfoProviders that are not relevant for planning. The data manager reads the data and then forwards the queries to the planning buffer.3. Now the system fills the data in the internal structures of the OLAP processor and filters it if necessary (for example, if there are queries with two structures). In addition, the system carries out the first checks against the planning model (data slices and characteristic relationships), however, only when all required characteristics have been defined uniquely from aggregation levels.4. Then there are queries from the front end to collect the data from the OLAP processor and transfer it into an abstract grid format. The concept of rows and columns is not defined until this point. As a result of the rows/columns structure, there are empty cells. The system must check if it is possible for these cells to be ready for input.5. Depending on the BI front end, the following tasks are to be carried out:a) BEx Analyzer: The system converts the data from the abstract grid format and uses Remote Function Call (RFC) to transfer it to the .NET runtime of the BEx Analyzer. This triggers rendering in Excel.b) BEx Web Analyzer: The system normalizes the data from the abstract grid format using an RFC (JCo) and transfers it to BICS (BI Consumer Services). Then the system creates HTML and sends the results to the browser and renders them.

After you make an entry, the system carries out the following steps:

1. The system carries out some basic checks in components related to the front end, for example, the validity of the characteristic values entered. If these checks are successful, the system transfers the data to the OLAP processor.2. In the OLAP processor, the system checks changed data or entries from the new rows against the planning model. Characteristic values from new rows must be contained in the current query filter, there must be valid combinations according to the characteristic relationships and the relevant data records are not allowed to be protected by data slices. If necessary, the system carries out further checks. If the changed data is consistent, the system determines the delta records and writes them to the delta buffer. Derivations are carried out there, if necessary.3. Now the front end requests the current data from the OLAP processor. The OLAP instances for input-ready queriesandreporting queries in the planning application are automatically sensitive to changes in the delta buffer: These instances therefore obtain the required data from the planning buffer and the delta buffers (one for each real-time InfoCube) and update the internal status of the OLAP instances. Then the system again transfers the data to the abstract grid format and sends it to the front end.

1.3 Planning applications with input-ready queries and planning functions
For the executions in sections1.1and1.2, it is necessary to explain how planning functions and input-ready queries communicate data changes between each other.
Here, data changes generally lead to new data records in the delta buffer. Therefore, according to sections1.1and1.2, the planning functions andallqueries in the planning application see the data changes that have taken place in the planning session using functions or manual entries.

2. Important influencing factors for performance

If you want to optimize the runtime of the created planning application or if the runtime is not what you expect, it generally makes sense to carry out the following procedure.
Proceed iteratively, as you would in any good project, that is, plan sufficient small steps and investigate the performance of components of a planning application individually at an early stage. Then there is generally time for you to adjust the data model appropriately and make other Customizing adjustments. Then combine the components in the application and test the performance.
Use transactionRSRT, for example, to analyze the performance of individual queries: You can use transaction SE30 to measure the runtime of this transaction.
You can use reportRSPLS_PLSEQ_EXECUTEto execute the planning sequence and then measure it using transaction SE30.
You can use transactionRSTTto record typical actions that are executed in a planning application (see Note 899572). You can use transaction SE30 to measure the RSTT trace and search for hotspots in the application.
If you open a customer message about the performance of a planning application, the RSTT traces of an application should always be available.
In BI planning, there are many parameters that can influence the performance of the application. Take at least the following points into account during the application design. Collect information about the following points and also take into account the recommendations from section 3.

2.1 Data model

The data model includes master data, the InfoProvider and the infrastructure of BI planning for each real-time InfoCube, that is, that characteristic relationships and data slices. Also relevant for BI planning are technical characteristic relationships for time characteristics (for a complete list, see the documentation), navigation attributes and technical relationships that ensure the SID consistency for compound characteristics.

2.2 Number of data records read and to be processed

In planning, you require data that is only read (reference data) and data that is changed in planning (planning data). Determine the number of these data records for queries and planning functions. The number of empty cells in an input-ready query can be relevant if the check for the ready for input status is time-consuming. Furthermore, it is important to know how many data cells are sent to the front end.

2.3. Construction type of the input-ready query

For input-ready queries, collect information about restrictions in the filter and the type of the selection there (single value, hierarchy, variable and so on) and about the setting “Access Type for Result Values”. The value that replaces the variable at runtime is important.
Does the query have one structure or two structures? How many components does each structure have? What restrictions are used in the structures? If there are two structures: Are restrictions carried out for each cell? How many cells are there? Are calculations made in the query (formulas)?
Are characteristics drilled down using structures? Are the drilldown characteristics different from the characteristics used in the structures? How many hierarchies are used in the query? How large are the hierarchies?

2.4 Number of components in the planning application

Determine the number of queries, query views (using the data provider) and planning functions that are used in the planning application. How many variables are used and how are they replaced? Are all queries executed at the start of the application or are they started later on using commands when they are required?

3. Recommendations for modeling

This section contains recommendations for the influencing factors mentioned in the second section.

3.1 Data model

Compound characteristics may require consistency checks if there are input-ready queries and special list geometries (when you use the characteristics for different axes of the query). Use characteristic compounding sparingly.
We recommend that you store planning data and actual data in different InfoCubes and use aggregation levels on a MultiProvider for planning. MultiProviders only require slightly more effort than InfoCubes. However, note that in BI planning, the additional effort of MultiProviders in comparison with reporting increases further since each record in the MultiProvider must be able to be assigned to a relevant InfoProvider of the MultiProvider without information being lost. The system carries out this check for each data record that can be changed. The more inhomogeneous the relevant InfoProviders of the MultiProviders, the larger the effort. Therefore, resist trying to create a universal MultiProvider, that is, create as small a MultiProvider as possible.
Use characteristic relationships sparingly since the system has to check the validity of each data record that can be changed and each delta record carries out all applicable derivation steps. The characteristics of the aggregation level determine which steps are applicable. The more relationships you use, the more checks are required. Refer to the documentation to find out exactly when a combination check or a derivation is carried out (see also Note 955993). For optimal modeling of characteristic relationships, proceed as follows:
Choose the characteristics in the relationships as ‘orthogonal’ as possible, that is, the characteristic quantity of combination checks must be as disjunctive as possible. For derivations, target characteristics should occur in only one relationship if possible. This makes it easier to keep the rules in the characteristic relationships as consistent as possible.Derivation: Combine as many steps into one relationship as possible if you only require the derivation for delta records, that is, the relationship does not work as a combination check as well. If this is the case, you should not create one relationship for each target characteristic during the derivation of characteristics from attributes of a master record. Here, enter only all required target characteristics. Keep the data model as simple as possible. If you want a derivation (for example, in a query) to also work as a combination check, you must ensure that the relationship does not contain too many target characteristics because this may result in the combination check not being called.Combination check: As for derivations that work as combination checks, you must choose the number of characteristics for each relationship so that the combination check is also called in queries and the planning function. Under this constraint, choose the maximum number of characteristics in the relationships.

In addition to the characteristic relationships explicitly defined for the InfoCube, the system recognizes characteristic relationships for redundant time characteristics, for navigation attributes and for technical characteristic relationships, for example the consistency check of SIDs for compound characteristics. Therefore, use as few redundant time characteristics as possible in the real-time InfoCube, navigation attributes and compound characteristics. See also section 3.3.

3.2 Number of data records to be processed

Recommendation: Select only the data that is actually required. Start by considering the number of characteristics in aggregation levels. Use only the required characteristics there. If you want data to be distributed top down, you can execute this with planning functions in the background, for example.
For input-ready queries, you should restrict the number of data cells to those that are relevant for manual planning. In Web applications, you should use the settings for paging in the Web template so that you can define the number of cells to be rendered.
During the parameterization of planning functions, ensure that the relationship of read data to changed data is balanced. If approximately 1500 records are read and only 100 are changed, the relationship is no longer balanced. Therefore, restrict the filter accordingly. Whenever possible, include the selections from conditions in the filter. It does not make sense to select a large number of data records using one filter and then to only edit a few of them using conditions.

3.3 Construction type of the input-ready query

Make the restrictions in the filter and the restrictions of structures as severe as possible. For the variables, ensure that the filter is also as restrictive as possible after the values have been replaced. Use the variables in the query filter because only the selection in the filter and in structural components of the query are used to set the locks. The restrictions from the default values are not used.
Severe restrictions are required if you use the setting ‘Access Type for Result Values’ according to characteristic relationships or master data.
If the query is defined for a MultiProvider (this may occur indirectly using an aggregation level), you should restrict characteristic 0INFOPROV into structural components if possible. A typical example here is the planned/actual comparison if the planning data is from a real-time InfoCube and the actual data is from a standard InfoCube.
Use as few redundant characteristics in the query as possible. If characteristics are redundant and are used in the drill downs, they should not be on different axes. For compound characteristics, it generally makes sense to restrict the higher-level characteristics to an individual value in the filter.
If possible, use only one structure. Characteristics that are restricted in the structures should not then also be used in drilldowns. In an extreme case, this makes it necessary for the intersection to be calculated for each cell to determine if it is ready for input.
A ‘formula-based’ construction of a cell/column structure can often be achieved with one structure and one drilldown. For this, you must use the ‘Access Type for Result Values’ option according to characteristic relationships and, if necessary, create relevant characteristic relationships that model the required characteristic combinations.
Queries with two structures and, if necessary, cell definitions are almost always ‘large’ in the following sense: Each cell is described by selections, in addition, there are selections from the filter. As a result, the query has a high fixed cost portion that is required just for the cell description. In addition, there is the effort required to assign movement data to these cells and, if necessary, also to determine whether the cells are ready for input. For queries with two structures, you should use one characteristic in exactly one structure or as a free characteristic. If you use variables in a structure, replace these by a single value butneverby a long list of single values.

3.4 Components of a planning application

The components of a planning application are all objects that work together to form the actual planning application, for example, variables, queries, planning functions and planning sequences. Here we sometimes notice a tendency to create universal planning applications, that is, large Web templates or large BEx Analyzer applications that contain everything that you need. However, with regard to good performance, these applications are difficult to optimize, many components are rarely required and they are then simply ‘overhead’. Therefore, planning applications should contain only the components that are actually required. If in doubt, create another application.

3.4.1 Variables

If you use a large number of variables, try to determine the runtime that is required to replace the variables. You can exclude the variables during a performance analysis and define the selections. Compare the result with the version of the application that uses variables. In particular, measure the runtime when you use variable exits that require a complex logic to determine the values.

3.4.2 Queries

You should first individually optimize queries that occur in a planning application. If you use several queries in a planning application, you should use the option of only starting queries using commands when you actually require them. Otherwise, the system will start all queries at the start of the application and the runtime increases accordingly.

3.4.3 Planning functions/planning sequences

If you use planning sequences that contain a large number of planning functions, check whether you can combine some functions or whether some functions are already contained in others (for example, ‘Delete’ may already be contained in a ‘Copy’ function). In some cases, it may be useful to use a good implementation of a FOX function that combines some planning functions into one function.

4. Availability of optimizations

Customizing for this application and the quantity of data to be processed have the largest influence on the performance of a planning application. Therefore, the emphasis of performance optimizations of planning applications should be in this area.
Below, we have listed notes that may further improve the performance of planning applications. However, note that they are not universal, that is, they do not improve every planning application. Therefore, we have grouped the notes according to their main topics. If your planning application falls under a particular area, implement the notes specified in that section.
In general, we recommend that you import the most current Support Package. We recommend Support Package Stack 14 (that is BI Support Package 16) or higher. You must import at least BI Support Package 12. Notes with a validity before this Support Package are not handled here.

Optimizations for queries (general)

1055044 Performance in SAPLRRK0, form s_data_fuellen
1063768 Performance of plan queries with high no. of key figures
1067433 Performance optimization when you use derivations
1084602 Performance of combination check in the input-ready query
1094422: Input-ready query: Corrections for various errors
1101187 The OLAP tunnel
Note: Bear in mind that Note 1101187 contains very complex correction instructions. Read this note carefully and do not forget the required manual steps. This note is also relevant for planning functions, in particular when processing mass data. This note also helps to reduce the memory consumption in complex planning scenarios.
1104114 Follow-on error: Optimizing the performance of derivations
1114620 No new lines after Note 1063768 & Note 1084602 implemented
1117348 Subsequent correction to Note 1101187

Optimizations for queries with two structures

1019326 Integrated planning: Ready for input status of cells
1020323: Input-ready query: Characteristic relationships, memory consumption
1039781: Input-ready query: Performance
1055965 Performance: Queries with two structures, collect_chafix
1056050 BI planning: Internal errors
1063768 Performance of plan queries with high no. of key figures
1084602 Performance of combination check in the input-ready query
1114620 No new lines after Note 1063768 & Note 1084602 implemented
1132992 Performance problem when initializing variables
Note:Notes 1063768 and 1084602 also help with queries with just one structure that processes a large amount of data. In this situation, you should also use Note 1067433.

Optimizations if you use a large number of data slices with variables

1059304 CL_RSPLFU_IOBJ_HELPER->VAR_EXISTS performance improvement
1060908 Performance when you instantiate data slices
1068150 Performance: Buffering of data slices and char. relationships

Optimizations when using planning functions

1096045 Planning functions: Parsing before the execution
1101313 Reading the planning buffer with the correct date
1115910 Planning functions: Performance improvements
1121202 Planning functions: Distribution with keys
1149337 Main memory consumption and planning functions

Optimizations if you have very large MultiProviders

1040293 IP: Optimizations for writable InfoProviders
1075101 Subsequent correction to Note 1040293
Note: Implement this note only after consultation with SAP development.This warning concerns all notes that have Note 1040293 as prerequisite. This note is very large and has many complex dependencies. Therefore, we recommend that you import Support Package 14 for MultiProvider optimizations.
1072982 Metadata buffer for MultiProvider is not deleted
1090490 Buffering the MultiProvider runtime object
1110997 Subsequent correction to Note 1090490
1128031 Correction instructions in Note 1090490
Note: Bear in mind that Note 1090490 contains very complex correction instructions. Read this note carefully and do not forget the required manual steps.

Optimizations for the runtime of characteristic relationships and data slices

1016632: Unnecessary instantiation of characteristic relationships
1020059 Inconsistent filter in input-ready query
1044708 IP: Performance of generic time derivation
1067433 Performance optimization when you use derivations
1104114 Follow-on error: Optimizing the performance of derivations

Optimizations if you have large hierarchies

1058679 Several key selections in the select statement
1065041 Performance Problems in Queries with huge key selections
1068357 Planning functions: Selections on hierarchy nodes
See also the section ‘Optimizations of the BEx Web Analyzer’

Optimizations of the memory requirements

1020323: Input-ready query: Characteristic relationships, memory consumption
1042896 Release of memory after you close query
1090119 Table SELDR requires a large amount of memory space
1098057 Query: Dump NO_ROLL_MEMORY or other memory overflow
1107072 Improving the input help for 0INFOPROV
1112519 Inaccuracies in OLAP cache
1118671 Unnecessary memory consumption
1144702 Memory release, additional corrections to Note 1101187
1146957 Releasing memory OLAP_CACHE
1149337 Main memory consumption and planning functions

Optimizations of the BEx Analyzer

1017965 Variable screen takes a long time to appear and to close
1044219 Analyzer variable screen: Performance and documentation
1054168 Performance problems during conversion to formulas
Tip:If you use BEx Analyzer queries with two structures,
the time until the variable screen is called may be significant if you use the reference view option in the data provider settings.
1094799 Corrections in the BEx Analyzer server runtime
Caution:This note requires manual activities before implementation.
1118671 Unnecessary memory consumption
1150242 Improving performance/memory in the BEx Analyzer

Optimizations of the BEx Web Analyzer

1055003 Performance probs for hierarchies in Java Runtime (1062537)
1053054 Poor performance in hierarchy nodes in the filter
1062608 Long runtimes if you use an info field item
1085446 Poor performance due to result suppression
1086332 Long runtimes: Queries with many characteristics/attributes
1092068 ‘Dropdown Box’ Web item: Performance problems
1111470 Poor performance during filtering with many single values
1113195 Improving performance when there are several data providers
1128508 Performance improvements for web template with multiple tabs
1162580 Analysis item: Slow line selection performance

Various optimizations

1024554 Improving performance in queries in SAPLRSEC_CHECKS
1055044 Performance in SAPLRRK0, form s_data_fuellen
1059381 InfoProvider restriction in input help using MultiProviders
1060170 Performance improvement during analysis authorizations
1069675 Further performance improvement
1121993 Analysis auth’s: Performance optimization for special situ’s
1132992 Performance problem when initializing variables

Other collective notes for BI topics

1025307 Composite note for NW2004s performance: Reporting
1055581 Recommendations for Support Package Stacks for BI 7.0
1077830 BI-IP Support Package Stack recommendations
1101143 Collective note: BEx Analyzer performance