MDX error in WebI after BW upgrade

[arijit_das]

Hi Experts,

We recently upgraded our backend from BW7.01 SP10 to BW7.4 SP3.

There are some WebI reports which were originally created in BOXI3.1 SP3 and later migrated to BO 4.0 SP6. With BO4.0+BW7.01 combination, the reports are working fine. However, after BW upgrade, some objects in universe(unv) have become unusable in report.

These are custom measure objects created in universe. For example,

<EXPRESSION>
     IIF(@Prompt('Select Currency','A',{'Local','USD'},mono,constrained,Not_Persistent,{'Local'},user:3)=Local,
     @Select(Plan LC YTM\Plan LC YTM),
     @Select(Plan FC YTM\Plan FC YTM))
</EXPRESSION>

Only when this measure is used in report, we are getting following error:

Database error: Failed to execute MDX query. Reason: Invalid MDX command with Local. (IES 10901) (WIS 10901)

The same is working fine when the universe connection is pointed to another BW server having BW7.01 SP10.

Any idea?

[arijit_das · Accepted solution]

Finally I am able to find a solution.

Reason of the problem:

The MDX generated by WebI Report is supported in BW 7.0 but not in BW7.4 SP3.

Old MDX:

WITH MEMBER [Measures].[9E87D6DE-967F-4F40-94,AB,5B,FB,69,43,C6,21] AS
 ' IIF(Local=Local,[Measures].[4QYBDSF4MSBRM3B7PP4ND0TVS],[Measures].[4QYBDSUHOPJ6NCE41D9BX4RBC]) ' 
SELECT { [Measures].[9E87D6DE-967F-4F40-94,AB,5B,FB,69,43,C6,21] } ON COLUMNS ,
 NON EMPTY {[0CALMONTH2].DEFAULTMEMBER} ON ROWS
 FROM [MP_CCA/MP_CCA_Q00011]
 SAP VARIABLES [MV_CALM] INCLUDING [0CALMONTH2].[02] [MV_CALY] INCLUDING [0CALYEAR].[2013]

Solution:

I changed the formula of my measure object to:

<EXPRESSION>
     IIF(@Prompt('Select Currency','A:N',{'Local':'1','USD':'2'},mono,primary_key,Not_Persistent,{'Local':'1'},user:3)=1,
     @Select(Plan LC YTM\Plan LC YTM),@Select(Plan FC YTM\Plan FC YTM))
</EXPRESSION>

Now my MDX is:

WITH MEMBER [Measures].[9E87D6DE-967F-4F40-94,AB,5B,FB,69,43,C6,21] AS
 ' IIF(1=1,[Measures].[4QYBDSF4MSBRM3B7PP4ND0TVS],[Measures].[4QYBDSUHOPJ6NCE41D9BX4RBC]) ' 
SELECT { [Measures].[9E87D6DE-967F-4F40-94,AB,5B,FB,69,43,C6,21] } ON COLUMNS ,
 NON EMPTY {[0CALMONTH2].DEFAULTMEMBER} ON ROWS 
FROM [MP_CCA/MP_CCA_Q00011]
 SAP VARIABLES [MV_CALM] INCLUDING [0CALMONTH2].[02] [MV_CALY] INCLUDING [0CALYEAR].[2013]

This is working with BW 7.4 SP3.

Conclusion:

It is not a good practice to integrate SAP BO 4.0 SP6 with BW7.4 SP3 as the PAM for BO 4.0 SP5+ says upto BW7.4 SP2 is supported. There can be other MDX issues too.