In Better Performance For Universe-Based Access To BW, a number of improvements have been discussed regarding universe-based access to BW infoproviders. One major new option is that it is now possible to access BW infoproviders via SQL. Technically, this has been achieved via Business Objects's Data Federator (DF) that includes SQL engine running on top of federated data sources - one of those can be a BW 7.01 system. This blog provides some more insights into that approach.
Figure 1 shows in red the changes that come with BW 7.01 and Data Federator XI 3.1: there is a new BW connector for DF that connects to a DF facade on the BW side. The DF fasade bypasses BW's analytic engine and connects directly to the underlying data repository in BIA or the relational DB. This means that the data can be accessed only "as-is". i.e. as it is materialized in BIA or the relational DB. Analytic features and calculations such as external hierarchies, currency or unit conversions, calculated key figures or formulas, exceptions, conditions etc. are not available via this access. In that sense, it is merely a way to browse through BW's data repositories. Still, this might be exactly what is required if a user has defined calculations and other analyses inside a relational universe. Figure 2 indicates that sometimes expensive conversions between rowset and cellset paradigms can be avoided. Please note that security defined in BW still applies when the data is accessed via the DF facade.
 Figure 1: DF Access to BW 7.01 |  Figure 2: Rowsets and Cellsets during Query Processing |
In the Better Performance For Universe-Based Access To BW I've already compared this new DF-based approach to the existing, MDX-based one. It is important to emphasize that they complement each other in many ways. The DF-based approach is very well suited for scenarios for which the MDX-based approach is actually not designed. Overall, it is a decision of balancing between the advantages and the drawbacks:
- Figure 3 shows a relative performance comparison between the SQL- and the MDX-based access for a set of queries that requires only materialized values from BW but no analytic functionality. This set originates from a real-world customer scenario and is an ideal case for the DF-based approach. On the horizontal axis the set of discrete test queries is listed. For each test query we compared runtime before (i.e. via ODA) and after (i.e. via DF). So a value of 10% means that the runtime afterwards (with DF) was only 10% of the runtime before, i.e. it runs 10 times faster via DF. For convenience the test queries were sorted in a way that the queries with the best improvement are on the left and those with less improvement on the right.
- Figure 4 lists a number of possible workarounds and limitations for the SQL-based access. Please refer to the Better Performance For Universe-Based Access To BW for a more detailed discussion on the trade-offs.
- A kind of side effect of the SQL access is a completely new option, namely the possibility to federate between a BW system and an arbitrary RDBMS - see green box in figure 1.
Figure 3: DF Access to BW 7.01
| BW Server Feature | Workaround |
|---|---|
| Calculated Key Figures, Formulas | Define calculated measures in Universes / WebI |
| BW Variables | Define @prompt in Universes or WebI Query Panel; does not work for exit variables or variables filled through authorizations |
| Currency and Unit Conversion | Simulate via joins in Data Federator |
| Exception Aggregations | SQL standard aggregations (AVG, COUNT DISTINCT) available via Data Federator processing |
| Display Attributes | Define joins (in DF) from InfoProvider to master data InfoProvider tables |
| Conditions | Use filters on measures |
| BW Hierarchies | No workaround |
| Non-Cumulative Key Figures | No workaround |
DF exposes an infoprovider as a set of (virtual) tables that are organized in a star schema. In addition, the Universe Designer now comprises a new strategy that allows to automatically generate a relational universe from that set of tables. See Figures 5 and 6 to that end.
 Figure 5: The DF strategy in the Universe Designer. |  Figure 6: The universe generated via the DF strategy. |
The technical prerequisites for using Data Federator on top of BW are the following:
- SAP NetWeaver BW 7.01 SPS3
- Business Objects Enterprise XI 3.1 Fix Pack 1.1 or later
- Data Federator XI 3.1
- SAP Integration Kit XI 3.1
PS (March 2013): In the context of BW-on-HANA there is an interesting alternative which is described here.
PPS (June 2013): Please consider OSS note 1704897 which is based on real-world experience and describes the suitable scenarios for this approach.
12 Comments
