Universe Designer vs BEx Query - Retrieving all combinations of values

[Former Member]

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

[Henry_Banks · Accepted solution]

Hi,

The best performance gains you will get, with your version of BObj, will be achieved by doing the following:

1) Use BW 7 Enhancement Pack 1 SP04 and above. Most reference customers use SP09 currently on this codeline (7.01.9)

these are many new optimized function modules delivered over and above BW 3.x to work with the OLAP engine and MDXinterface, including GZIP XML compression for datatransfer.

2) You should enable Query Stripping in WEbintelligence in the Query panel and Document properties.

3) You should design you Dashboard to consume data from scheduled report instances, so that they show the latest cached data, not to hit the database 'on demand'

I hope this helps.

Regards,

H

[Former Member · Accepted solution]

Hello Henry,

Thanks for your quick reply!

I have some notes to add regarding your reply:

1) Use BW 7 Enhancement Pack 1 SP04 and above

We are currently unable to upgrade the BW platform. There is an upgrade pending for sometime in this year, but we can't precise the date and as such we cannot take that into account to resolve this issue.

Are you aware of any relation between the behavior i described and the BW version i'm using? And if so, can you pinpoint resolutions in the 7 version?

2) You should enable Query Stripping in WEbintelligence

Thanks for the suggestion. Query stripping is already enabled in WebI (both spots).

3) You should design you Dashboard to consume data from scheduled report instances

This strategy is already implemented - Reports run early in the morning so that the dashboards only consume data from report instances. By policy, we avoid the "on demand" requests.

Thanks again,

Francisco

[Henry_Banks · Accepted solution]

Hi,

Unfortunately, what you are seing is how the OLAP engine's dataset is abstracted by the MDX Interface, into a GroupingSet that WebIntelligence can consume and store into its local (rowset) microcube. it's not pretty.. but that's how the architecture 'fits'

With regards to Ehp1, the gains are typically : optimized code calls, improved memory management, stream compression, and new MDX operators. All of the above add up to significant perf. gains over the BW 3.5 offering.

Regards,

H

p.s. Note 1373094 - How can it be determined that SAP NW Enhancement Pack 1 optimizations are being leveraged by BusinessObjects WebIntelligence reports using OLAP Universes?

.. may be of interest

[Former Member · Accepted solution]

Thanks Henry.

The note you pointed out will certainly be very helpful further along the road.

For now, i'll have to ask:

Do you know of any documentation where i can confirm this architectural connection between the two platforms?

Again, thank you for your time

Francisco

[Henry_Banks · Accepted solution]

Hi Francisco,

I'm sorry, i've never seen a detail diagram showing all the intricacies ... i'm going by the troubleshooting tiers, the breakpoints from experience..

perhaps this book would be useful ? http://www.sap-press.de/download/dateien/1928/sappress_mdx_reporting.pdf

i 've never consulted it, but it looks like the real deal .

Regards,

H

[Former Member · Accepted solution]

Henry,

I'll have to take a closer look at the book to find out it it has the information i need.

I'll let you know as soon as i can.

Thanks,

Francisco