Use case for .UNV universe instead of BICS on BEx Query
When developing Web Intelligence reports on top of BEx query, the recommended connection method is BICS. In some cases, you cannot accomplish the reports requirements using BICS but can still get the job done by generating a .unv universe using the Universe Design Tool from BI 3.x. Some of the use cases are:
- If you have a need to use different levels of a characteristic hierarchy, then the Universe is a good option. For example you are building a report on Milestones, the Milestones are related to level 4 WBS element, and the user would like to display level 3 WBS element or one of the attributes from level 3 WBS element. A universe generated on BEx query makes each level of the characteristic hierarchy as a dimension, including all the master data attributes of that level.
- When using key figures where the aggregation type is not “SUM”, and you have a need to report by dimensions that are built using formulas on the original BEx query objects, you should consider using a .unv universe. A common scenario is when you are using Date as key figure, the aggregation type is typically Max and not Sum. With BICS connection Date key figure will have aggregation of “Database delegated”, which is not a problem till you build a formula dimension and add it to report. In this case you can generate the universe on the query, modify the aggregation for the key figure in the universe definition from Database delegated to the desired option, and then build the Webi report on the Universe.
- If you have a need to limit the report by a master data attribute which is not available as navigational in the query, you might want to consider generating a universe. This may cause performance issues. You have to decide based on the data set you are working on, if this should be done by changing the inofcube and making the attribute navigational or if the report is to be scheduled to run in background, or the data set is small, it maybe better to generate universe to build the report.
In my experience, the approach has been to use BICS where possible, and when it is not feasible, then consider the old way of creating WEbi reports, generate a universe and develop the report on the universe. I am sure you have encountered other scenarios. Feel free to add them in the comments.