cancel
Showing results for 
Search instead for 
Did you mean: 

Troubleshooting SAP BW OLAP Universe

Former Member
0 Kudos

Hi,

Please advise how to approach troubleshooting an OLAP universe created on a SAP BW query. The BW query is simple with no calculated or restricted key figures, but has a lot of characteristics (about 50). An OLAP universe was created (default MDX code, no custom objects). The Webi report almost works fine, but here is a summary:

Environment:

SAP BI 7

BusinessObjects 3.1 (SP2) BOE and client

Universe -> BEx query -> Multiprovider -> DSO (data volume = 1600 rows)

Webi report:

1) Issues start when the number of result objects in the Webi query panel approach 50 to 60. At the high end of 60, the query takes much longer and returns "No Data to fetch". Removing some objects, the query will return data back again. No filters or any logic from the additional object should be causing the "No Data to fetch".

2) Certain combinations of objects causes MDX errors. The universe MDX code is default and the BEx query works fine. Avoiding the object combination and the report will run again.

3) The BEx query works fine. Although it has large number of characteristics, the objects are mostly flags and simple data stored on the DSO. Minimal Master Data or navigation attributes.

4) I tried to split the query into two in Webi and merge it back on the report level. I was expecting for the two queries to run separately (they work fine separately), but when using the "Run for all queries", it takes very long and does not finish.

Any information would be much appreciated. Thank you.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

In Web Intelligence, if you are splitting a single queriy in multiple queries, you have to notice that those queries are no run in parallel but one by one.

Concerning the MDX error or no data to fetch, it is hard to provide an explanation unless you copy the generated MDX and run it SAP frontend using MDXTEST transaction.

Using MDXTEST you will be able to see how SAP returns (or not) data.

To obtain the generated MDX, you need to activate ODA (Olap Data Access) traces. Find in attachement documents that will guide you on how to activate traces. Last, you have to pay attention that the traces will impact the query performance, so please remove traces if don't need them.

On the other hand, we have widely improve the performance since BOE XI3.1 FP 1.2. Please check which version you are using.

Regards,

Didier

Former Member
0 Kudos

Hi,

Thank you for the response. Besides turning on a MDX trace, what other troubleshooting options are there? My point is the universe will continue to generate the MDX code so even if there is a way to run the MDX code correctly, how can the universe be updated besides the single object MDX select code? The universe we are developing is large (320 objects) with 50 result objects in Webi report, but all of them are standard BEx generated objects. With relational universes, the SQL code can be analyzed and the universe can be changed to generate the desired code, but this is different in OLAP universe where the MDX is automatically generated. Any information would be appreciated, thank you.

Former Member
0 Kudos

Hi,

Yes, as you noted the MDX is automatically generated by the OLAP Data Access component and there is not a lot you can do to heavily influence this. You are able to modify the Select syntax for individual dimensions for example to influence whether you are returning the database posted value (syntax: [dimension].[LEVELXX].[TECH_NAME] ; [0CALMONTH].[LEVEL01].[TECH_NAME]), or the Name attribute for a dimension. Below are further details on this.

So, in essence you do not have as much control for MDX generation as you do with SQL in relational universes - this is good and bad. Good in the sense that it keeps it simple and allows for the backend (BW in this case) to optimize the query when translating MDX to physical data selection in SQL (or accessing in BWA). Bad in the sense you cannot heavily influence it. This in turn means more time needs to be spent on optimizing the BEx Query, InfoProvider Data Model, aggregates, WebI Query definition to ensure best possible performance. In short, limit overall number of dimensions/measures in WebI Query as much as possible while providing necessary scope of analysis, leverage Drill mode to drill out of initial scope of analysis, and focus on tuning as much in the backend. The best method for tuning and troublehsooting is typically to capture the generated MDX syntax and run it in MDXTEST to debug, view runtime statistics, etc.

To modify the syntax:

1. Open the Universe in Designer.

2. Double click the key Detail object you want to modify.

3. In the Select text box on the "Definition" tab of the "Edit Properties" dialog box, change the syntax to refer to the NAME attribute of the SAP characteristic.

For example, for the object L01 Customer Key, change the generated select syntax:

[Z_CUSTOM].[LEVEL01].[[2Z_CUSTOM]].[Value] to refer to the NAME attribute: [Z_CUSTOM].[LEVEL01].[NAME]

4. Click OK to save the changes.

5. Follow the same steps for the name object. Change the syntax to refer to the DESCRIPTION attribute of the SAP characteristic.

For example, for the object L01 Customer Medium Name, change the generated select syntax:

[Z_CUSTOM].[LEVEL01].[[1Z_CUSTOM]].[Value] to refer to the DESCRIPTION attribute: [Z_CUSTOM].[LEVEL01].[DESCRIPTION]

I hope that helps.

Thanks,

Tanner Spaulding

Former Member
0 Kudos

Hi Didier,

I try to find documentation about this issue:

"In Web Intelligence, if you are splitting a single queriy in multiple queries, you have to notice that those queries are no run in parallel but one by one."

Can you help me?

It will be possible in future run in parallel queries?

This is a very critical point for certain scenario.

IngoH
Active Contributor
0 Kudos

Hi Samuele,

in cases where you require this on a reqular base I would suggest that you setup a multiprovider instead of merging the dimensions on the Web Intelligence side.

ingo

Former Member
0 Kudos

Hi Ingo,

in our solution, we have two "heavy" queries based on a multiprovider, and we've syncronized them on the WebI.

When we execute Webi report, they are executed sequentially. Is it possible execute them in parallel? (we have 2 queries BO and 1 query BW based on multiprovider)

I'd like to undestand if there are any system configuration or solutions to apply between BW layer and Business Objects layer, actually or in the future.

Thanks.

Former Member
0 Kudos

Hi,

We currently are not able to run queries in parallel but it is definitely part of our plans for the future.

At this time no date has been yet committed.

Didier

Former Member
0 Kudos

Hi Didier,

thank you very much for your quickly answer.

That's a pity, this is a big limit in certain scenario.

In your plan there is also the possibility to limit the DB Access for certain key figures?

For example we have big performance problems because we've a query BEX with 10 key figures (some are basis key figures and other calculated key figures from master data).

In Webi query, also I've only one basis key figure, the BEX query access to all key figures with obvious performance problem.

Former Member
0 Kudos

Hi,

The issue you mentioned is a known issue on SAP BW backend and this has been solved in SAP BW.

As I don't remember which fix pack contains the fix I recommend to install SAP BW 7.01 SP5.

Didier

Answers (0)