SAP on IBM i – Update week 51, 2015 Optimize Performance Settings of InfoCubes for SAP NetWeaver Business Warehouse on DB2 for IBM i
In order to run your SAP Business Warehouse with optimal performance, you need to ensure that all your indexes are created correctly. Running SAP on IBM i, you can also use Encoded Vector Indexes (EVI). An EVI is used to provide fast data access in decision support and query reporting environments. EVIs are a complementary alternative to existing index objects (binary radix tree structure) and are a variation of bitmap indexing. Because of their compact size and relative simplicity, EVIs provide faster scans of a table and can also be processed in parallel. Read the complete description of EVIs at www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/whatareevi.htm.
EVI support for star and snowflake schemas in BW queries was introduced for OS/400 V4R5M0 in 2002 and significantly enhanced for i5/OS V5R3M0 in 2004. SAP BW has taken advantage of them since SAP BW release 3.0B. SAP recommends to enable the use of EVIs right after the installation of a SAP NetWeaver Business Warehouse system by applying SAP Note 501572 – IBM i: EVI Stage 2 Support. After implementing this SAP Note, the SAP system creates all needed indexes for InfoCubes automatically as EVIs instead of standard radix indexes to ensure optimal performance when running queries on InfoCubes. This recommendation is valid for all currently supported releases of SAP Business Warehouse running on IBM i.
There are several checks available in the Business Warehouse system to verify performance relevant settings. Some general checks are integrated in the DBA Cockpit as described in SAP Note 541508 – IBM i: Checking the system parameters for BW. This cockpit collects data for different aspects of the SAP system from the Business Warehouse point of view. One of the integrated checks is verifying the EVI stage 2 support settings. The results are shown in the SAP GUI like in this example:
Transaction RSA1 provides index checks for a single InfoCube. Select option “Manage” from the context menu of the desired InfoCube and switch to tab “Performance”. Here are pushbuttons to check the indexes for this InfoCube or its aggregates, to delete all indexes, and to recreate them again. The result of the index check is displayed by a traffic light, with green meaning that all indexes are set correctly. Red or yellow means that indexes are missing or not matching the configured index strategy.
In order to repair missing or incorrect indexes, there are different repair strategies available. You can simply press the pushbutton “Repair DB Indexes (Now)” which will correct the indexes for this InfoCube. Or you can run report SAP_INFOCUBE_INDEXES_REPAIR as described in SAP Note 401242 – Problems with InfoCube or aggregate indexes. This will check and if necessary correct the indexes of all InfoCubes. Please keep in mind that creating indexes on large InfoCubes can be a time consuming task, so the Business Warehouse system always runs it in the background.
If you want to get more detailed information about the indexes of an InfoCube, you can start transaction RSRV and follow the path “Database” -> “Indices of an InfoCube and its Aggregates”. The check log shows which indexes exist, which are missing, where they are missing (either in the database or in the Data Dictionary or both), and the expected and actual index type (EVI vs. radix). If the check results in a red or yellow traffic light, you also have the option to repair the indexes here.
In the near future SAP will be taking advantage of enhanced DB2 EVI functionality and making sure all the SAP-created EVIs are in place will become even more important. More information regarding that will be discussed in an upcoming blog entry. We would hope that you have been familiar with the check and repair functionality for many years. However, if this is information new to you, we highly suggest that you run the check 1-2 times a year and then implement any missing parts at your earliest convenience for optimal BW performance.