Skip to Content

Performance problems with Navigational Attributes after BW 7.0 upgrade

Hi,

<P>

We have upgraded our BW system to 7.0 EHP1 SP6 from BW 3.5. There is substantial use of SAP BusinessObjects Enterprise XI 3.1 (BOXI) and also significant use of navigational attibutes. Everything works fine in 3.5 and we have worked through a number of performance problems in BW 7.0.

<P>

We have a number of "fat" queries that act as universes for BOXI and it is when BOXI sends a MDX statement that includes certain crossjoins with navigational attributes that things fall apart. This is an example of one that runs in about a minute in 3.5:

<P>

SELECT { [Measures].

[494GFZKQ2EHOMQEPILFPU9QMV], [Measures].[494GFZSELD3E5CY5OFI24BPCN],

[Measures].[494GG07RNAAT6M1203MQOFMS7], [Measures].

[494GG0N4P7I87V3YBRRF8JK7R] } ON COLUMNS , NON EMPTY CROSSJOIN(

CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( [0MAT_SALES__ZPRODCAT].

[LEVEL01].MEMBERS, EXCEPT( { [0MAT_SALES__ZASS_GRP].

[LEVEL01].MEMBERS } , { { [0MAT_SALES__ZASS_GRP].[M5],

[0MAT_SALES__ZASS_GRP].[M6] } } ) ), EXCEPT( { [0SALES_OFF].

[LEVEL01].MEMBERS } , { { [0SALES_OFF].[#] } } ) ),

[0SALES_OFF__ZPLNTAREA].[LEVEL01].MEMBERS ), [0SALES_OFF__ZPLNTREGN].

[LEVEL01].MEMBERS ), [ZMFIFWEEK].[LEVEL01].MEMBERS ) DIMENSION

PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION ON ROWS FROM

[ZMSD01/ZMSD01_QBO_Q0010]

<P>

However in 7.0 there appear to be some master data lookups that are killing performance before we even get to the BW queries. Note that in RSRT terms this is prior to even getting the popup screen withe "display aggregate".

<P>

They were taking 700 seconds but now take about 150 seconds after an index was created on the ODS /BIC/AZOSDOR0300. From what I can see, the navigational attributes require BW to ask "what are the valid SIDs for SALES_OFF in this multiprovider". The odd thing is that BW 3.5 does no such query. It just hits the fact tables directly.

<P>

SELECT "SID" , "SALES_OFF"

FROM (

SELECT "S0000"."SID","P0000"."SALES_OFF"

FROM "/BI0/PSALES_OFF" "P0000" JOIN "/BI0/SSALES_OFF" "S0000" ON "P0000"."SALES_OFF" = "S0000"."SALES_OFF"

WHERE "P0000"."OBJVERS" = 'A' AND "S0000"."SID" IN (

SELECT "D"."SID_0SALES_OFF" AS "SID"

FROM "/BI0/D0PCA_C021" "D" ) UNION

SELECT "S0000"."SID" ,"P0000"."SALES_OFF"

FROM "/BI0/PSALES_OFF" "P0000" JOIN "/BI0/SSALES_OFF" "S0000" ON "P0000" . "SALES_OFF" = "S0000" . "SALES_OFF"

WHERE "P0000"."OBJVERS" = 'A' AND "S0000"."SID" IN (

SELECT "D"."SID_0SALES_OFF" AS "SID"

FROM "/BIC/DZBSDBL018" "D" ) UNION

SELECT "S0000"."SID" ,"P0000"."SALES_OFF"

FROM "/BI0/PSALES_OFF" "P0000" JOIN "/BI0/SSALES_OFF" "S0000" ON "P0000" . "SALES_OFF" = "S0000" . "SALES_OFF"

WHERE "P0000"."OBJVERS" = 'A' AND "S0000"."SID" IN (

SELECT "D"."SID_0SALES_OFF" AS "SID"

FROM "/BIC/DZBSDOR028" "D" ) UNION

SELECT "S0000"."SID" ,"P0000"."SALES_OFF"

FROM "/BI0/PSALES_OFF" "P0000" JOIN "/BI0/SSALES_OFF" "S0000" ON "P0000" . "SALES_OFF" = "S0000" . "SALES_OFF"

WHERE "P0000"."OBJVERS" = 'A' AND "S0000"."SID" IN (

SELECT "D"."SID_0SALES_OFF" AS "SID"

FROM "/BIC/DZBSDOR038" "D" ) UNION

SELECT "S0000"."SID" ,"P0000"."SALES_OFF"

FROM "/BI0/PSALES_OFF" "P0000" JOIN "/BI0/SSALES_OFF" "S0000" ON "P0000" . "SALES_OFF" = "S0000" . "SALES_OFF"

WHERE "P0000"."OBJVERS" = 'A' AND "S0000"."SID" IN (

SELECT "D"."SID_0SALES_OFF" AS "SID"

FROM "/BIC/DZBSDOR058" "D" ) UNION

SELECT "S0000"."SID" ,"P0000"."SALES_OFF"

FROM "/BI0/PSALES_OFF" "P0000" JOIN "/BI0/SSALES_OFF" "S0000" ON "P0000" . "SALES_OFF" = "S0000" . "SALES_OFF"

WHERE "P0000"."OBJVERS" = 'A' AND "S0000"."SID" IN (

SELECT "D"."SID_0SALES_OFF" AS "SID"

FROM "/BIC/DZBSDOR081" "D" ) UNION

SELECT "S0000"."SID" ,"P0000"."SALES_OFF"

FROM "/BI0/PSALES_OFF" "P0000" JOIN "/BI0/SSALES_OFF" "S0000" ON "P0000" . "SALES_OFF" = "S0000" . "SALES_OFF"

WHERE "P0000"."OBJVERS" = 'A' AND "S0000"."SID" IN (

SELECT "D"."SID_0SALES_OFF" AS "SID"

FROM "/BIC/DZBSDPAY016" "D" ) UNION

SELECT "S0000"."SID" ,"P0000"."SALES_OFF"

FROM "/BI0/PSALES_OFF" "P0000" JOIN "/BI0/SSALES_OFF" "S0000" ON "P0000" . "SALES_OFF" = "S0000" . "SALES_OFF"

WHERE "P0000"."OBJVERS" = 'A' AND "P0000"."SALES_OFF" IN (

SELECT "O"."SALES_OFF" AS "KEY"

FROM "/BIC/AZOSDOR0300" "O" ) ) ORDER BY "SALES_OFF" ASC

<P>

I had assumed this had something to do with BOXI - but I don't think this is a MDX specific problem, even though it's hard to test in RSRT as it's a query navigation. Also I assumed it might be something to do with the F4 master data lookup but that's not the case, because of course this "fat" query doesn't have a selection screen, just a small initial view and a large number of free characteristics.

<P>

I've tuned this query as much as I can from the Oracle perspective and checked the indexes and statistics. Also checked Oracle is perfectly tuned and parameterized as for 10.2.0.4. But this query returns an estimated 56 million rows... of course it's slow!

<P>

Does anyone know why BW 7.0 behaves differently in this respect and what I can do to resolve the problem?

<P>

Regards,

<P>

John

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 11, 2010 at 08:10 AM

    John,

    I'm not too conversant with BOBJ universes running over a BW query, but from the BW angle I would check the following:

    1. Check the query settings in RSRT - is the query set to select all results, or just results required for the current navigation?

    2. If you can't get rid of the pesky new joins and they are causing performance problems, could you try creating Indexes on those navigation attribute joins?

    Also - are you running BWA?

    Cheers,

    Tristan

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 09, 2011 at 10:17 AM

    Hi John,

    I have just read your post about problem on navigational attributes in 7.0.

    We too have a big query built on multiprovider and we use lots of navigational attributes; we see that SQL generated by MDX is quite different from the "pure" SQL generated by bex query, assuming bex query and webi report with same structure (one characteristic - 0project - and one restricted KF).

    What we are experiencing is that generated SQL (webi report) is creating lots of select from all the infocube / DSO in the multiprovider to retrieve 0project values, while "pure" SQL (bex query) is just reading one infocube (the one that has the KF).

    According to some oss notes it seems that MDX needs to build all the joins between dimension and SID tables to retrieve "posted values" instead of using "master data values" read mode.

    Some oss notes suggest to use MDX_JOIN_CUBE_DIME parameter in RSADMIN to avoid it, but to our tests it seems not to work.

    Did you experience something like that? Do you know MDX_JOIN_CUBE_DIME parameter? Did you solve your performance problem?

    Thanks in advance for your kind answer

    Lau

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.