A lot of my customers keep asking me if it is somehow possible to create a (UNX) universe on top of a BEx query. Well, the short answer here is no, but anyway in BI4, you do not need to manually create a universe because you can directly access BEx queries from the front ends. Today it is possible to create relational universes (UNX) on SAP BW InfoProviders (e.g. Cubes), but what about your restricted and calculated key figures from BEx queries? What if you would like to create a multisource universe using the data from a BEx Query?

 

Why not schedule a report based on a BEx Query to an Excel file on the server, and use that Excel file as source for a relational universe?

I have successfully applied the following workflow and helped a customer create a complex multisource universe by combining data from a third party data base and a BEx query.

High level workflow:

  • Create a report (Crystal Report or Web Intelligence) in tabular format based on a BEx query
  • Schedule the report (best with the corresponding frequency of the process chains running on SAP BW), Format: Excel, Destination: File System.
  • Create a universe based on the output of the schedule (KBA 1828466 could be helpful here)

But keep in mind that this is a very situational workaround, it might work for your requirement, or it might not. Let’s discuss some pros and cons:

Pros:

  • Creation of relational (UNX) Universes on top of data comming from a BEx queries
  • Creation a multisource universes and be able to leverage the restricted and calculated key figures defined in a BEx query
  • Add complex logic to multisource universe (e.g. derived tables, SQL Functions)
  • Less effort needed compared to a solution where you load the third party DB’s data into SAP BW.

Cons:

  • Only additive measures will be aggregated correctly when navigating freely
  • Be aware of non-additive measures, these will only work in a static report (no extra aggregation) without free navigation
  • No analysis authorizations from SAP BW
  • Usual cons of relational access apply: No hierarchies, no variables from BEx (but you could use prompts in the Universe)
  • Need MS Office ODBC drivers (Server should run on windows)
  • Performance will not be as good as a solution purely based on SAP BW

Do you have more pros or cons about this approach? Feel free add a comment below.

Victor

Related articles:

1828466 – How to Create a Universe Based on MS Office Files (Excel, Access) in BI 4.0

To report this post you need to login first.

5 Comments

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

  1. Josef Kuenzli

    Thanks Victor, thats a good one.

    One year ago, I did a BO Explorer Information Space on top of a Bex the following way:

    1. create a direct-update DSO in BW with one keyfigure Infoobject for each Keyfigure in the Bex query.

    2. With Analyses Process Designer (RSANWB) you can load the Bex query into the direct update DSO

    3. In BO IDT create a Relational Connection via JCO to the DSO

    4. In BO IDT create a Multisource-Enabled Data Foundation, a Business Layer and publish it as an .unx

    5. In BO Explorer create an Information Space on the unx

    The issue is that a DSO can have maximum 12 Infoobjects in the key fields and I see no way to go beyond that. So the datamodel has to be simple. Also, large datasets are no good for APD.

    regards

    Josef

    (0) 
    1. Victor Gabriel Saiz Castillo Post author

      Hi Josef,

      thanks a lot for sharing this approach. At the end of the day it is similar to the approach via Excel file proposed in this blog, only that in Excel you can involve a lot more columns than in the key part of a DSO.

      Best regards,

      Victor

      (0) 

Leave a Reply