Skip to Content
0
Former Member
Jan 06, 2012 at 02:49 PM

Universe Designer vs BEx Query - Retrieving all combinations of values

149 Views

Hello experts,

We are developing a set of dashboards based on SAP BW cubes. Those cubes, through multiproviders, are used in BEx queries wich, in turn, serve BusinessObjects universes.

The universes are used as data sources for Web Intelligence reports, with scheduled execution jobs for every morning. The job executions feed the dashboards through BI Services.

These dashboards, when accessing the reports through BI services, filter the dimensions, typically, by Year / Month / Company

In sum, the environment specifications are as follows:

  • SAP BW 3.5

  • BEx Analyzer Vxx

  • SAP BusinessObjects XI 3.1 SP3

Issues are related with some combinations of dimensions in BEx. For instance, we have the following combination of dimensions:

BEx Query screenshot

Here we combine:

  • Exercício (Year) = 2011

  • Período Contábil (Month)

  • Empresa (Company code) = 1000

  • Medida LPM (Action) restricted to two values: MA02.000.000000 and MA03.000.000000

As we can see in this combination of dimensions, with MA03 not all the months in the year are shown. This is because there aren't any metrics with values in certain months. This occurs often throughout the analysis, a fact we deem normal since it's not mandatory that all actions have metric values for all months.

The universe consists of dimensions and metrics, which are imported directly from the BEx query. Other metrics, for Year-to-date calculations, are developed in the universe with resort to MDX expressions.

As such, we can see in the following example the difference between an elemental metric (imported directly from BEx) and a YTD metric (developed in the universe):

  • Elemental:

  • Cabimentos

  • Expression:

[[Measures]].[[ZCABIMENTOS]]

  • YTD:

  • Cabimentos YTD

  • Expression:

Reports that are developed over these universes use combinations of dimensions similar to the ones we see in BEx, as we can see in the following image:

Query editor screenshot

The resulting block exhibits the same results as the BEx query, as shown in the image below:

Report page screenshot

What we would like to produce is a table with all combinations of data, as shown below:

Report page with all combinations screenshot

In order to achieve this, we have to set the following MDX statements in the elemental and aggregate measures at the universe level:

  • Elemental:

  • Cabimentos

  • Expression:

  • YTD:

  • Cabimentos YTD

  • Expression:

But this creates a performance issue in the report execution, since coalescing all values forces results into any possible combination of dimension values. As such, and to try and minimize the impact, we have to either create query filters at the report level or reduce the number of dimensions used in the report, as shown below:

Query editor with all combinations screenshot

This implementation still causes great impact in the report execution, taking much more time to execute.

After this explanation, we have to ask you if there is any BusinessObjects functionality that allows for the automatic retrieval of all the combinations of dimension values, either at the universe or report level, that does not cause such impact in report execution.

Thank you for your time,

Francisco Aguiar