SQL Access to BW via Data Federator
- 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.
|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|
Figure 4: Some Limitations & Workarounds
Figure 5: The DF strategy in the Universe Designer.
Figure 6: The universe generated via the DF strategy.
- 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.