Poor performance with WebI and BW hierarchy drill-down...

Hi

We are currently implementing a large HR solution with BW as backend

and WebI and Xcelcius as frontend. As part of this we are experiencing

very poor performance when doing drill-down in WebI on a BW hierarchy.

In general we are experiencing ok performance during selection of data

and traditional WebI filtering - however when using the BW hierarchy

for navigation within WebI, response times are significantly increasing.

The general solution setup are as follows:

1) Business Content version of the personnel administration

infoprovider - 0PA_C01. The Infoprovider contains 30.000 records

2) Multiprovider to act as semantic Data Mart layer in BW.

3) Bex Query to act as Data Mart Query and metadata exchange for BOE.

All key figure restrictions and calculations are done in this Data Mart

Query.

4) Traditionel BO OLAP universe 1:1 mapped to Bex Data Mart query. No

calculations etc. are done in the universe.

5) WebI report with limited objects included in the WebI query.

As we are aware that performance is an very subjective issues we have

created several case scenarios with different dataset sizes, various

filter criteria's and modeling techniques in BW.

Furthermore we have tried to apply various traditional BW performance

tuning techniques including aggregates, physical partitioning and pre-

calculation - all without any luck (pre-calculation doesn't seem to

work at all as WebI apparently isn't using the BW OLAP cache).

In general the best result we can get is with a completely stripped WebI report without any variables etc.

and a total dataset of 1000 records transferred to WebI. Even in this scenario we can't get

each navigational step (when using drill-down on Organizational Unit

hierarchy - 0ORGUNIT) to perform faster than minimum 15-20 seconds per.

navigational step.

That is each navigational step takes 15-20 seconds

with only 1000 records in the WebI cache when using drill-down on org.

unit hierachy !!.

Running the same Bex query from Bex Analyzer with a full dataset of

30.000 records on lowest level of detail returns a threshold of 1-2

seconds pr. navigational step thus eliminating that this should be a BW

modeling issue.

As our productive scenario obviously involves a far larger dataset as

well as separate data from CATS and PT infoproviders we are very

worried if we will ever be able to utilize hierarchy drill-down from

WebI ?.

The question is as such if there are any known performance issues

related to the use of BW hierarchy drill-down from WebI and if so are

there any ways to get around them ?.

As an alternative we are currently considering changing our reporting

strategy by creating several higher aggregated reports to avoid

hierarchy navigation at all. However we still need to support specific

division and their need to navigate the WebI dataset without

limitations which makes this issue critical.

Hope that you are able to help.

Thanks in advance

/Frank

Edited by: Mads Frank on Feb 1, 2010 9:41 PM