Food for thought: Creating a (UNX) universe with data from a BEx Query
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:
- 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.
- 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.
1828466 – How to Create a Universe Based on MS Office Files (Excel, Access) in BI 4.0