Skip to Content

Faster Universe-Based Access To BW via MDX

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 SAP Business Warehouse – OLAP BAPIs (BW-OBI). 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 Better Performance For Universe-Based Access To BW.

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 SQL Access to BW via Data Federator. 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. Native Excel 2007 On Top Of Netweaver BI 7.0) 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.1 Fix Pack 1.2 or later
  • SAP Integration Kit XI 3.1
You must be Logged on to comment or reply to a post.
  • SAP Netweaver Version:
    We are on SAP Netweaver 7.01 SPS2.  You mentioned this enhancement was available with SPS3.  Is there a set of SAP Notes that we could apply as advanced corrections to our SPS2 system to get the enhancements mentioned in this blog?

    Business Objects Enterprise Version:
    In the blog you mention “Fix Pack 2 w/ LAFix2.1” is required.  I understand what fix packs are, but what is LAFix2.1?  We are on Business Objects Enterprise XI 3.1 with Fix Pack 1.3.  Is this a high enough version or is something additional needed?

    • Hi Mark,

      a LAFix is basically a pre-delivered fix on top of a FixPack. Think about it like a customer specific correction note.
      With FixPack 1.3 you should be on the correct level on the BusinessObjects software.


    • Hi Mark,

      Check out SAP Note 1293428 including the file, which is attached to it. The file provides the list of prerequisites on NW side.


  • Thomas –

    Good stuff on performance. Now that the sorting of MDX results can be suppressed, it would be great if QaaWS could be changed now to be able to sort results from an OLAP source?  This would be a big help for dashboard design.

    I have to scroll back and forth to read this blog.


  • I’ve implemented the integration Kit and an OLAP universe query based and it runs.
    I’ve a problem on a report that runs via Rich Client and fails via web client.
    We are on BI 7.0 sp 19. Is it necessary to upgrade to BI 7.01 SP03 or SP04 ? We’ve seen your interesting document about the improvement of perfomance of BI 7.01 SP04 but we’d like to upgrade our BI system 7.0 in 2 months and not now.

    Thanks in advance.

    Best Regards.