Better Performance For Universe-Based Access To BW
A universe gives a meaning to a set of tables or other data repositories. It defines how those tables link together or what subset of tables hold customer information. It is the principal way for many of the Business Objects client tools to access data – the most prominent one being WebIntelligence (or WebI for short). There is two types of universes, namely relational universes (based on relational tables and access via SQL) and OLAP universes (based on cubes and access via MDX). Thereby, the SQL is generated by a module called query technique (QT) while MDX is generated via an OLAP data access (ODA) component. In the context of NW Business Warehouse (NW BW aka NW BI), OLAP universes (based on a BW query or a BW InfoProvider) have been the way of choice for releases BW 7.0 and below. By using the MDX-based access, all the rich semantics can be leveraged that have been set up and defined in BW meta data repository, like currency and unit conversions, fiscal year or other time semantics etc. Figure 1 shows this situation. For completeness let me add that the existing BICS-based (for the BEx tools or Pioneer in the future) or other MDX protocols (e.g. see Native Excel 2007 On Top Of Netweaver BI 7.0) will continue to exist and are simply outside the scope of this discussion in this blog.
With BW 7.01 (enhancement pack 1 for BW 7.0), things have changed in two ways – see figure 2:
- The ODA – BW interoperability has been significantly improved; it has been streamlined and that translates nicely into a better performance and less memory consumption.See Faster Universe-Based Access To BW via MDX for details.
- Business Objects Data Federator – in general a tool to create unified view of data sources with virtual data integration – can now read data from a BW 7.01 system via a new BW connector. Basically, Data Federator exposes an SQL interface to access BW InfoProviders, i.e. only the data as it is materialized on BW’s underlying RDBMS; there will be no advanced semantics like usage of external hierarchies, restricted, calculated or non-cumulative key figures etc. See SQL Access to BW via Data Federator for more details on the DF-based approach.
Figure 1: Universe-Based Access to BW 7.0
Figure 2: Universe-Based Access to BW 7.01
The technical requirements for leveraging the options are as follows:
|Minimal Technical Prerequisites|
|MDX-Based Access||SQL-Based Access|
Now, one can argue now that there is suddenly a choice of interfaces and that the customer is left with yet another decision to take. While this is true there is also a number of benefits to that choice. In the end, it is not a “black & white decision” with one interface being always superior over the other but a balanced trade-off that considers a number of design aspects:
- Centrally defined semantics in BW: this allows for a consistent definition of semantics with all users on a central BW server. Simply consider KPI definitions, such as a profitability which, in some cases, can be broken down into a complex series of calculations that an average BI user or consumer does not and does not want to comprehend. Allowing or requiring such a KPI to be defined in an ad-hoc fashion can be too challenging and/or too dangerous regarding the false conclusions driven from an erroneous definition.
- Ad-hoc enrichment of materialized data: what has been described as a challenge and risk in the previous paragraph, namely ad-hoc definition of semantics, can be something very valuable for users who want to create new analytical queries on the existing data. Here, an access to materialized data via SQL and exploration facilities of WebI can be benefitial.
- End-user mindset: some users are very familiar with tabular data; they might even know exactly what data to find in which table on an RDBMS. Such users might prefer table-based data models to explore. In this case, a relational universe is the likely choice. On the other hand, there is users who prefer multi-dimensional views (i.e. cubes) to analyze. They look at BW data in terms of dimensions (characteristics), hierarchies, measures (key figures), calculated measures etc. They want to leverage centrally defined services such as approved and compliant currency and unit conversion calculations or KPI definitions. In this case, an OLAP universe is probably the preferred option.
- Technical features: BW’s analytic engine provides a set of features that are only available via an OLAP universe (see figures 1 and 2). Find below a list of the most prominent BW features and how well they are supported. It is important to note that for most blank squares there is a workaround, e.g. by using prompts in a universe rather than BW variables. See the blog on the SQL-based access to find out more suggestions.
A notable new feature – that almost comes in as a side effect of the Data Federator based approach – is the option to federate a BW system with either another BW system or an arbitrary RDBMS using Data Federator’s capabilities.
BW Server Feature MDX SQL BW Hierarchies + Restricted and Calculated Key Figures + Security + + BW Variables + Currency and Unit Conversion + Exceptions, Conditions + SUM, MIN, MAX Aggregations + + AVG, COUNT, COUNT DISTINCT, … Aggregations + o OLAP Cache + Navigational Attributes + + Mass Data Enabled o + Federation (e.g. BW – RDBMS, BW – BW) + + = fully supported, o = partially supported
For blank fields there might be acceptable workarounds.
PS (Nov 2009): I’d like to point the readers to a Performance Study: Web Intelligence on top of SAP BW.