15 Seconds in BW - 140 more in BO... Lots of Key Figures...

[cuneyt_kavaslar]

Hello, I'm trying to understand the BO performance on complex BEx queries... I have a BEx query that runs in about 20 seconds on the WEB with 25 restricted/calculated key figures...

WebI report based on a universe created on the BEx query above takes about 160 seconds... When I followed up steps to capture the MDX statement and used MDXTEST to see the time in BW, I can see that it comes back in 15 seconds... So, performance issue is not in the BW or in the generated MDX statement...

Any idea about where BO spends the remaining 140 seconds??? I can think of data transfer from BW into BO, preparing the output in WebI display but 140 seconds seems too much for all this...

When I drop the number of key figures to 15, total runtime goes down to about 70 seconds - a gain of 90 seconds... So, I'm pretty sure it has something to do with the number of key figures... But, if the heavy load is on the BW side to do the work and it takes 15 seconds, it sounds there is something wrong in BO and I'm wondering if anybody has any ideas to improve performance...

We do have BIA installed... That's probably why MDX takes 15 seconds since the cubes behind the multi provider have millions of records...

Thanks in advance!

Cuneyt

[Former Member · Accepted solution]

Go to transaction RSRT, select your query, click on the properties tab and tick the selection box "Use Selection of Structure Elements". This is recommended practice where there are lots of key figures.

[cuneyt_kavaslar · Accepted solution]

Thanks Khaled, I've already done this.

[Former Member · Accepted solution]

Check Statistics,, If they are not there build one