SAP on IBM i – Update week 46, 2015: Faster Analytics with Flat InfoCubes for SAP NetWeaver Business Warehouse on DB2 for IBM i
One of the most important data objects for SAP Business Warehouse are “InfoCubes”. They can consume data from various sources and provide capabilities for analysis and reporting. InfoCubes are essential for many business processes and therefore it is important to have good performance for all technical processes involving InfoCubes.
SAP recently introduced the so called “Flat InfoCubes” that show significant performance benefit compared to the standard InfoCubes. The main difference between standard and Flat InfoCubes is the number of involved dimension tables. For Flat InfoCubes only the package dimension is present as physical table in the database while all other dimensions exist just as metadata and certain data is moved to a single fact table. This reduces the technical overhead for the handling of dimension IDs (DIMIDs). Although the underlying data structures are changed as a result of converting a standard InfoCube to a Flat InfoCube resulting in better performance, the way in which the end user interacts with the Business Warehouse system is unaffected, meaning this is a technical infrastructure change only.
With shipment of SAP NetWeaver BW 7.4 Support Package 13 on November 9th 2015, it is possible to use Flat InfoCubes on DB2 for IBM i. You can also start with Support Package 9 of SAP NetWeaver BW 7.4 or higher provided that all ABAP corrections attached to SAP Note 2073519 (and their dependent SAP notes) are implemented using the Note Assistant (transaction SNOTE). After implementation of either Support Package level the feature needs to be turned on using a software switch in the BW specific table RSADMIN as described in SAP Note 2073519 (IBM i: Support for flat InfoCubes for BW for DB2 for IBM i).
During lab measurements using the SAP Enhanced Mixed Load Benchmark (BW-EML) a significant performance gain could be proven in all important data processes using Flat InfoCubes with SAP BW on DB2 for IBM i compared to standard InfoCubes:
- Load time of transactional data reduced to less than 50% of that of standard InfoCubes
- Load time of delta data reduced to less than 85% of that of standard InfoCubes
- Query runtimes reduced to 30% – 70% compared to standard InfoCube query runtimes
Most InfoCubes may be converted into Flat InfoCubes without concern. However, we currently recommend that rarely found InfoCubes with more than 77 characteristics be left in standard InfoCube format until a BW index naming limitation can be resolved. Such InfoCubes can be converted successfully, but may not experience optimal performance until development has removed the index naming limitation.
Update: After additional code analysis we found a lower limit of the possible maximum number of characteristics where we can build the needed indexes automatically. Instead of 99 indexes for characteristic fields just 77 characteristics are supported. At the moment we develop a different solution to increase the index limit significantly. We will post the results here after this work has finished.