SAP BW OLAP Universe performance issue

[Former Member]

Hi,

Based on BO recommendation, I created a universe on top of a big BEX query which included all characteristics and key figures from a cube. I then created a webi report with 5 characteristics and 1 key figure from this universe. I also create the same report in BEX query designer(same number of objects). I ran both. The Bex query completed under a minute and the webi report took more than 12 minutes to run. I did a bunch of other test with different number of objects combination and saw similar differential in query performance.

I did a trace using 'sm50' and saw the open SQL submitted to BW from BEx was very different from what got submitted from the webi report. Here is what I saw in pseudo code.

Bex:

select dimension1.blah, dimension2.blah, dimension3.blah, dimension..... sum(measure)

from dimension1, dimension2, dimension3, dimension..... factTable

where dimension1.SID = factTable.d1SID

and dimension2.SID = factTable.d2SID

and ......

and (query filters)

OLAP Universe

select dimension1,blah, dimension1.SID

from dimension1, factTable

where dimension1.SID = factTable.d1SID

select dimension2,blah, dimension2.SID

from dimension2, factTable

where dimension2.SID = factTable.d2SID

select dimension3,blah, dimension3.SID

from dimension3, factTable

where dimension3.SID = factTable.d3SID

.........

It seems the OLAP universe was querying the BW cube one dimension at a time and then somehow piece the result sets together to form the final result set. Dimension tables join to the fact table without any filter definitely causes performance issue. Beside, I have no idea why the query requests are submitted to the BW system like that.

I looked in varies forums and found no similar issues posted by others. Has anyone had any performance problem with there OLAP universe? Is this a matter of configurations?

My envrionment:

SAP BW 3.5

BOXI 3.0

ORACLE DB (version ??)

[Former Member · Accepted solution]

We are having the same issue in 3.1. Do you know if there is a FixPack for this issue?

Versions:

Business Objects 3.1

SAP BW 7.0

Regards,

Raelene

[Former Member · Accepted solution]

Hi,

The fix I mentioned in my previous post has been delevoped for a specific customer and has been delivered to this customer end of October.

It is planned to be available for all customers in FP1.2 for end December 2008, or later in SP2 for June 2009.

Didier

[Former Member · Accepted solution]

Hi,

Does any one know if these SAP BW optimisation measures made it to FP1.2 or are they to be included in SP2 for June 2009.

Also will these changes improve performance to Crystal Reports as well as Webi reports?

Thanks,

Penny

[Former Member · Accepted solution]

There are 3 SAP BW fixes in FP 1.2 - one for high memory consumption for some WebI rpts, one for linked hierarchies not returning data, and one for WebI performance when here are several BW hierarchies. The FP 1.2 documentatation is on the SAP Support portal under the Business Objects download area.

From the description of the enhancements, I thought those things were in Enhancement Pkg 1 changes set for June/July.

[IngoH · Accepted solution]

it requires NW 7.01 (Enhancement Package 01) and on the BusinessObjects side FixPack 1.3 for the XI 3.1 release

Ingo

[Former Member · Accepted solution]

Hi Ingo

I have also been facing the same kind of Performance issue while querying on OLAP Universes built on Infocubes. Could you please provide me with the link from where we can download the SP2 for BO XI 3.0 Enterprise.

Regards

Chetan

[IngoH · Accepted solution]

Hi,

the software is available on Service marketplace.

Ingo

[Former Member · Accepted solution]

Hi

I have been searching for the SP2 on the Service Market Place, But i could only get the Fix pack 3 for BO XI 3.0. Could you please help me with the link for SP2.

Thanks

Chetan

[former_member142702 · Accepted solution]

Hi,

the service pack is available as a patch for BO XI 3.1. Go to [http://service.sap.com/patches]. Navigate to Support Packages and Patches - Entry by Application Group ->SAP BusinessObjects packages and products->SBOP ENTERPRISE->BOBJ ENTERPRISE XI 3.1 and choose your OS.

You still have to upgrade your installation to XI 3.1 first.

Regards,

Stratos

PS: Please upgrade your int.Kit for SAP solutions installation also to XI 3.1 SP2

[Former Member · Accepted solution]

Hi

Thanks for your reply

But if you scroll up this thread you would see that there is a SP2 for BO XI 3.0, Isnt that available. The link that you have given is not showing me the Patches for BO Enterprise, I am getting only the BO Edge Series and Bo Integration Options.

Could you please suggest any alternative as this has been long pending.

Regards

Chetan

[former_member142702 · Accepted solution]

Hi

SP2 applies to BO XI 3.1. IF you are not able to see the path you have to issue an OSS message to SAP support (http://service.sap.com/support]) in order to get access to the packages.

Are you using BO Edge or BO Enterprise?

Regards,

Stratos

[Former Member · Accepted solution]

Hi

I am using BO Enterprise. Is there any such kind of package for BO XI 3.0 Enterprise version which enhances the performance of Webi Reporting on OLAP Universes?

[former_member142702 · Accepted solution]

Hi,

the latest corrections and imporvement are in SP2. I would recommend to upgrade your system. The only option you hav eright now if to install FixPack 3 for BO XI 3.0 but I am not aware of any real performance improvements there regarding WebI and SAP OLAP universes.

Regards,

Stratos

[Former Member · Accepted solution]

Hi

I have tried installing SP2 for BO XI 3.0 and still the performance is the same. I am afraid i dont have the time nor resources to upgrade the system to BO XI 3.1

Is there anything else that can be done?

Regards

Chetan

[former_member142702 · Accepted solution]

Did you apply Service Pack 2 or Fix Pack 2???!!!!

Regards,

Stratos

[Former Member · Accepted solution]

Hi

I have applied Fix Pack 3 for BO XI 3.0

Regards

Chetan

[former_member142702 · Accepted solution]

Hi

as far as I know there are no performance imporvements regarding OLAP universes in Fix Pack 3.

I would suggest to open a new thread here [; containing more information regarding the query you are using your setup (Backend version) and the problem you are exactly facing.

But the recommendation will be to upgrade all the way up to 3.1 SP 2

Regards,

Stratos

[IngoH · Accepted solution]

Hi,

the fixes in regards to the performance require at a mininum

BusinessObjects XI Release 3.1 (not 3.0) with FixPack 1.3

NW 7.0 Enhancement Packge 1 with SP03

Ingo

[Former Member]

Hi,

You cannot compare a BEx query and a universe query by just comparing the trace.

BEx query makes ABAP calls and universe query makes MDX calls;

Moreover there is only one MDX call sent to SAP, what you have noticed is that there is one MDX call to retrieve member for a given characteristic whereas a prompt has been set on.

Last, Web Intelligence consume only flattened data (row sets) wheras BEx consume data sets.

That means that there is a flattening operation between SAP OLAP engine and the data sent to the Web Intelligence document.

A fix have been developed for a customer to improve performance on Web Intelligence queries, this fix will be available for all customers in SP2 planned in June 09.

Here is a a brief summary of what the fix provide:

- Provide data directly from SAP server to Web Intelligence document. Avoid unnecessary conversion steps: row set --> data set --> row set

- Eliminate redundant sort operations

- Other optimization operations

Didier

Edited by: Didier Mazoue on Nov 20, 2008 8:38 AM