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:
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):
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:
The resulting block exhibits the same results as the BEx query, as shown in the image below:
What we would like to produce is a table with all combinations of data, as shown below:
In order to achieve this, we have to set the following MDX statements in the elemental and aggregate measures at the universe level:
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:
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,