Faster Universe-Based Access To BW via MDX Thomas Zure

Faster Universe-Based Access To BW via MDX
Thomas Zure

Business Objects's semantic layer can expose cubes as OLAP universes to client tools like WebIntelligence (or WebI for short). This applies to OLAP servers like Microsoft's Analysis Services or Hyperion's Essbase but also to SAP BW. There is a component called OLAP Data Access (ODA) that retrieves data via MDX - a query language for multi-dimensional data sources. In the case of BW, the ODA component connects to BW's OLAP BAPI. This connection has now been optimized and streamlined in order to improve the interoperability. This blog gives some insight in what has been done and what the effects are. It complements the blog on the two options to access BW data via universes.

Figure 1 shows the improvements that have been implemented with BW 7.01 (aka NW BI 7.0 EhP1). On the left hand side, there is a new option based on the Data Federator and leading to relational universes. Further information can be found in this blog. On the right hand side, the existing option based on the ODA connecting to the OLAP BAPI is shown. It has existed for many years and also for earlier releases of BW. However, with BW 7.01 the interoperability between ODA and the OLAP BAPI has been streamlined leading to better performance and less resource consumption - see the discussion on figure 3 below. Please note: for simplicity, non-universe based query access via BICS (for BEX or Pioneer), ODBO (e.g. for native Excel) or XML/A is not shown in figure 1. This does not indicate or imply that they will go away.

Figure 1: Universe-Based Access Options with BW 7.01

The interoperability has been improved in the following three areas, also visualized in figure 2:

1. Avoid unnecessary sorting:
   MDX standard imposes results to be sorted. However, those sorts are typically ignored or not required by WebI as it sorts the results on its own anyway. Consequently, sorting inside the OLAP BAPI is redundant and can now be skipped by using a new UNORDER() function in SAP's MDX.
2. Leaner memory consumption during flattening:
   A new API has been created that uses a new, non-MDX-standard compliant result structure. This new API is private to ODA as it is tailored towards ODA. The MDX standard imposes a cell-based result in which each cell carries a number of properties. A lot of that information has been neglected by the ODA, similar to the sort order discussed in item 1. This means that the new API has a much slimmer result structure - thus (a) consuming less memory and (b) requiring less processing. This has led to an optimized flattening algorithm, now on the API side.
3. Leaner communication ODA – OLAP BAPI:
   The communication between the ODA and the new API has been switched to a compressed data exchange via binary basXML - an optimized SAP-proprietary format. This reduces (a) memory consumption and (b) communication costs.

In order to implement these changes w/o affecting the certified partner tools that are based on the OLAP BAPI - go here and search for BW-OBI certifications - a new, so called, rowset API has been created. This API is not part of the OLAP BAPI but is private and proprietary to ODA as it is tailored towards the ODA use case.

Figure 2: New Interoperability via ODA

Figure 3 shows the effects of those improvements with respect to query runtime performance (red line) and memory consumption (blue line). Runtime and memory consumption values have been compared for a number of real-world customer queries defined in WebI. The vertical axis shows the runtime in 7.01 (i.e. leveraging the improvements described above) relative to the runtime in a BW 7.00 system: a value of 60% means that the runtime in 7.01 is only 60% the runtime in 7.00, i.e. it went down by 40%. The memory consumption is calculated in the same way. On the horizontal axis, the test queries are lined up. They have been sorted in a way that queries to the left have shown the most runtime improvements (i.e. red line is low) while the queries to the right have improved only slightly (i.e. red line closer to 100%). On average, the runtime has gone down by 30% while memory consumption has been reduced by over 50% with the used set of WebI queries.

Figure 3: Performance and Resource Consumption Improvements with BW 7.01

The technical prerequisites for leveraging the improvements are the following:

• SAP NetWeaver BW 7.01 SPS3
• Business Objects Enterprise XI 3.0 with
  • Fix Pack 2 w/ LAFix2.1
  • SAP Integration Kit Fix Pack 2 (+ LaFix 2.1)

Thomas Zurek is a development manager for BI in SAP NetWeaver