Performance problems with Navigational Attributes after BW 7.0 upgrade

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi Tristan,

Absolutely, I've checked both those things. The query is set to type H which should be the right type. Also I've created optimal indexes for these joins and manually updated DB stats in DB20 to be sure to be sure.

The problem as I see it is that it is doing a UNION between tables that total 56m rows. This is an expensive CPU operation and it is this which takes the time, and ensures that the DB cache doesn't make it faster after one run. UNION (rather than UNION ALL) is required because the list needs to be unique.

But how do I avoid the UNION... given that the optimizer is a black box.

Regards,

John

[Former Member · Accepted solution]

Hi John,

It looks like it is looking for all instances of SALES_OFF that have been posted in the (large number of) InfoProviders comprising the MultiProvider. I'm not sure why it would be doing that when you're not performing an F4 operation, but this could be some peculiarity of the MDX engine. One way to fix this would be to look at the SALES_OFF characteristic settings in RSA1 and ensure that both F4 settings are set to "select from Master Data". This might stop the MDX searching for all SALES_OFFs posted in the disparate InfoProviders.

Hope this helps,

Tristan

[Former Member · Accepted solution]

Hey Tristan,

Nice idea but it doesn't make any difference at all. Which suggests that it's not the regular F4 activity but something MDX specific. Hmmmm.

Regards,

John

[Former Member · Accepted solution]

Hi John,

the same happens to us (BW 7.01 Sp 7), did you find a solution for this problem?

Thanks for any help

[Former Member]

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