Skip to Content
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.
DF Access to BW 7.01
Figure 1: DF Access to BW 7.01
Rowsets and Cellsets during Query Processing
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.
Options for Universe-Based Access to BW 7.0
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

Figure 4: Some Limitations & Workarounds

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.
Data Federator strategy in the Universe Designer
Figure 5: The DF strategy in the Universe Designer.
Options for Universe-Based Access to BW 7.0
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.

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. T Bala
    Thomas,

    Great blog. I have successfully configured DF 3.1 to a very large HR HeadCount Cube in BW 7.01 and the performance is very good. I would like to mention some important OSS notes (as I had to find the hard way) to make their lives easy in case they want to try…Note 1316106 – data federator : configuration for RFC. This note is important for setting up the connection. Note 1332831 – create a SAP BW relational universe using DF strategy.

    it is important that the support pack level is correct on both sides (BW and DF)… as mentioned in your blog.

    One suggestion I have for the product team is to reduce the work in DF. At the present time, I have to build relationships in my mapping in DF and in my case, the cube has so many underlying tables, it is very tedious to map table relationships… Since DF is anyway reading the meta data tables and “knows” the table linkages.. you can provide that as the starting point so we don’t spend days in configuring in DF (both relationships and mapping). Of course, if we want to federate with another data source, we can either map manually or have DF auto detect like Bobj does for universes based on strategies like column name matching etc.,

    Since DF is a new concept, people may not be catching on to this tool quickly, but, not having to extract data and virtually accessing BW (where you need a relational interface) is going to be key BI capability and SAP should market this product heavily. We have cubes that are over 25 to 30 million rows and not having to replicate to another database is a boon. Of course, if users want true OLAP, then, they have to go the olap universe route.

    Another productivity improvement is automatic generation or way to generate universe from within DF just like the option with Data Services.

    For those of you who are curious, there is also a nice webinar by Tahir hussain Babar on DF that was broadcast recently. “Connecting People and Data for Better Decision Making – April 23, 2009” and can be found in past webinars or on service.sap.com/BIValueacks.

    Thanks again for the great blog with fantastic technical details.

    Bala

    (0) 
      1. T Bala
        there is another blog on the same topic.

        Do you want to query SAP BW using SQL?

        also those who are on SP3 may need all of these notes mentioned in the note below and also referenced in the above blog.

        SAP Note 1278491 – Composite note Data Federator facade
        7.01 Support Package 04

        Thanks
        Bala

        (0) 
    1. Manabu Matsudate
      Hi Bala,

      I’ve also challenged this scenario with successfully.
      Now I’m trying to build restricted key figures liked as BW query.

      You wrote:

      > I have to build relationships in my mapping in DF and in my case, the cube has so many underlying tables, it is very tedious to map table relationships…

      As my understanding, I didn’t map table relationship, just click “Make Final” button then,
      all the table relationships are mapped in the phase of creating the universe using the strategifile.

      Have you mapped each table manually?

      (0) 
      1. T Bala
        Thanks Manabu. That really helps. Not having to map saves time.
        There is an excellent blog (released May 18)  by Frederic Vanborre explaining in detail the universe build etc.,

        I tried to map them manually because I would like to use the virtual database using non-bobj query tools (example: MS SSRS) that cannot access via universe.

        Bala

        (0) 
        1. Manabu Matsudate
          SSRS/MS Analysis Service can be accessed using OLAP Universe.
          But, as you mentioned, the good point of DF is accessibility to any data sources and virtually combined them.
          (0) 
  2. Troy Horton
    Can you elaborate on how security is supported with data federator against BW?    Are authorization variables with default values somehow integrated into the solution?

    Thanks,
    Troy

    (0) 
    1. Thomas Zurek Post author
      Hi Troy,

      BW security is available via DF. However, features that are only avaliable via a BW query definition – such as variables – are not available via DF. Such features require to go through the Analytic Engine (access path on the right of figure 1).

      Regards

      Thomas

      (0) 
      1. Arvind Bhaskar
        Thomas,

        We upgraded to BI7.0 EHP1 SP5 and connected to business objects. Most reports in webI are based off ODS data. After the upgrade – due to the MDX generator change, most of the queries are not running. BW tries forever to build rowsets.
        Is there any possibility to convert the Existing Universes to use the Data federator instead of OLAP/MDX connection between BOBJ and SAP BW?

        (0) 
  3. Thomas Zurek Post author
    Hi Bahram,

    yes, your point is valid, i.e. some of the multi-dimensional modelling BW will not be leveraged when accessing the data via SQL (i.e. DF). This is where the MDX access (as discussed Faster Universe-Based Access To BW via MDX) complements.

    With Explorer it will be possible to materialize some of the analytic calculations in an index that is then browse-able via Polestar. This requires BW 7.01 SP5 and the next version of Explorer to be released soon.

    Regards

    Thomas

    (0) 
  4. suzane paul
    can you provide the workarounds for restricted keyfigure. how to create the restrcited keyfigure based on same measure which has been restricted based on different dimensions.any guide like you have for olap universes.
    it is really urgent
    (0) 
  5. David Aldridge
    Thanks for the article, Thomas.

    There are many comments on the forums enquiring about being able to use system date to retrieve the last month of data from a BW cube, which is not supported by MDX. Is this architecture the solution to that problem? If so I think it’s regrettable that it’s not more widely promoted — it’s taken me quite a while to get find this as a potential solution.

    By the way what dialect of SQL is used in this method? Pure ANSI?

    Also is it possible to use derived tables, and to create object based on SQL functions etc?

    (0) 

Leave a Reply