I am using the following MDX, the Mdx works fine when there are few
members but it is causing performance issues with the increase of
members. Please review this MDX and comment. Is there any other
efficient way to do this or any optimization possible in this MDX?


-------------------------------------------------------------


With Member [Measures].[testmetric] AS '[Measures].[Store Sales
Net]-[Measures].[Store Sales]' --metric defination


Cell Calculation MYCALCULATION_ON_testmetric For '( {
[Measures].[testmetric]} )' As
- cell calculation name


'CalculationPassValue(Measures.CurrentMember, -1 , Relative )', --on
what should apply


CONDITION =' IIF([Time].currentMember.Level.Name="Quarter", --to see
if the level is Quarter so that it only compares quarters


IIF( ([Time].CurrentMember.PrevMember,[Measures].[testmetric]) >= 0 ,
--if for checking positve or negitive value for measure


CalculationPassValue( [Measures].[testmetric], -1,Relative ) < (
([Time].CurrentMember.PrevMember,Measures.[testmetric]) * 0.975) ,
--actual calculation for positive values


CalculationPassValue( [Measures].[testmetric], -1,Relative ) < (
([Time].CurrentMember.PrevMember,Measures.[testmetric]) * 1.025)
--actual calculation for negitive values


), --end of if for Value


CalculationPassValue( [Measures].[testmetric], -1,Relative ) = (
([Time].CurrentMember.PrevMember,Measures.[testmetric]) + 0.975) --
to always return false since I dont want other than quarters to be
evaluated


)' , --end of if for checking if level is quarter


SOLVE_ORDER ='2000', FORE_COLOR ='11004', CALCULATION_PASS_NUMBER ='2',

CALCULATION_PASS_DEPTH ='1'


SELECT { CrossJoin({[Product].[Product
Family].[Drink],[Product].[Product Family].[Food],[Product].[Product
Family].[Non-Consumable]},


{Measures.[testmetric]})} ON COLUMNS ,
{{[Time].[Year].[1997].CHILDREN}} ON ROWS


FROM [Sales for incremental update] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]


---------------------------------------------------------------------------*----------------------------



Thanks in advance


Faraz