Skip to Content
0
Jun 11, 2010 at 07:07 AM

Performance problems with Navigational Attributes after BW 7.0 upgrade

111 Views

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