|
| 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_tes
tmetric For '( {
[Measures].[testmetric]} )' As
- cell calculation name
'CalculationPassValu
e(Measures.CurrentMember, -1 , Relative )', --on
what should apply
CONDITION =3D' IIF([Time].currentMember.Level.Name=3D"Quarter", --to see
if the level is Quarter so that it only compares quarters
IIF( ([Time].CurrentMember.PrevMember,[Measures].[testmetric]) >=3D 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 ) =3D (
([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 =3D'2000', FORE_COLOR =3D'11004', CALCULATION_PASS_NUM
BER =3D'2=
',
CALCULATION_PASS_DEP
TH =3D'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],& #91;FORMATTED_VALUE]
,[CELL_ORDINAL],[FORE_COLOR]
---------------------------------------------------------------------------=
=AD----------------------------
Thanks in advance=20
Faraz
|
|