Calculated members are unbelievably slow

In general. Need help what are the top items I must check? My client is Excel Pivot table

I have one cal. member [NAV] from one fact table I am getting POSN TOTL CURR NAV BAL PTFC and OWNSHP PRCNTG

cal. mem [NAV] formula is below

Iif(([Investment].CurrentMember.Level.Name = "Investment Cd" or

[Investment].CurrentMember.Level.Name="Investment Type Cd") and

([Investor].CurrentMember.Level.Name="Investor Type Cd" or

[Investor].CurrentMember.Level.Name="Investor Cd" or

[Investor].CurrentMember.Level.Name="Investor Desc") ,

[Measures].[POSN TOTL CURR NAV BAL PTFC] * [Measures].[OWNSHP PRCNTG] ,

Iif([Investment].CurrentMember.Level.Name="Investment Cd" or

[Investment].CurrentMember.Level.Name="Investment Type Cd" ,

[Measures].[POSN TOTL CURR NAV BAL PTFC] ,

Iif(([Investor].CurrentMember.Level.Name="Investor Type Cd" or

[Investor].CurrentMember.Level.Name="Investor Cd" or

[Investor].CurrentMember.Level.Name="Investor Desc") ,

[Measures].[POSN TOTL CURR NAV BAL FUND] ,

Sum({[Investor].AllMembers}, [Measures].[POSN TOTL CURR NAV BAL FUND]))))

and hand full of simple calculations like this from different fact table

[Measures].[EQ UP 5P]/[Measures].[NAV]

Thank you - Ashok

Couple of suggestions:

1. Get rid of all IIF's and replace them with SCOPE as explained here: http://www.sqljunkies.com/WebLog/mosha/archive/2007/01/28/iif_performance.aspx

2. Revisit whether Sum({[Investor].AllMembers}, [Measures].[POSN TOTL CURR NAV BAL FUND])) is even correct. I think it isn't. It tries to sum up members from all levels of Investor dimension. This cannot be right. Also, it is going to be constant regardless of the current coordinate in Investor dimension, even though the rest of your expression carefully looks at the current coordinate in Investor.

|||

Thanks Mosha,

Because I have more then one IIF and I am new to MDX can I replace my NAV MDX with this ?

SCOPE (([Investment].CurrentMember.Level.Name = "Investment Cd" or [Investment].CurrentMember.Level.Name="Investment Type Cd")
and ([Investor].CurrentMember.Level.Name="Investor Type Cd" or [Investor].CurrentMember.Level.Name="Investor Cd"
or [Investor].CurrentMember.Level.Name="Investor Desc"));
This = [Measures].[POSN TOTL CURR NAV BAL PTFC] * [Measures].[OWNSHP PRCNTG];
SCOPE ([Investment].CurrentMember.Level.Name="Investment Cd" or [Investment].CurrentMember.Level.Name="Investment Type Cd");
This = [Measures].[POSN TOTL CURR NAV BAL PTFC];
SCOPE ([Investor].CurrentMember.Level.Name="Investor Type Cd" or [Investor].CurrentMember.Level.Name="Investor Cd" or
[Investor].CurrentMember.Level.Name="Investor Desc"));
This = [Measures].[POSN TOTL CURR NAV BAL FUND];
SCOPE ([Investor].AllMembers)
This = [Measures].[POSN TOTL CURR NAV BAL FUND];
END SCOPE;

Thank you - Ashok

I just tried this and I am getting syntax error

CREATE MEMBER CURRENTCUBE.[MEASURES].[NAV]

AS SCOPE ([Investor].AllMembers);

This = [Measures].[POSN TOTL CURR NAV BAL PTFC] * [Measures].[OWNSHP PRCNTG];

END SCOPE;,

VISIBLE = 1 ;

|||

Looks like you are still learning the syntax of MDX scripts. Here are two resources which have examples of how to convert IIF to SCOPE:

1. AS2005 Performance Guide http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc. (look for the MDX optimization section)

2. This blog also has an example: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx