Is there any alternate to Parallelperiod function, i am facing
performance problems when the member are large. Can anyone guide me
whats the problem with this query ? or any suggestion to improve the
performance or any alternate possibility to have the same result?

I would specially like to thank Deepuk fore his immense help

Thanks in advance

Faraz
---------------------------------------------------------------------------*----------------------------------

MDX Query
---------------------------------------------------------------------------*----------------------------------


With Cell Calculation MY_CELL For
'( { [Measures].[Sales Count]} )' As
'CalculationPassValue(Measures.CurrentMember, -1 ,Relative )',
CONDITION =' (ParallelPeriod ([Time].[Month Of
Year],1,[Time].CurrentMember),Measures.[Sales Count]) <
(CalculationPassValue( [Measures].[Sales Count], -1,Relative ) +
(ParallelPeriod ([Time].[Month Of
Year],1,[Time].CurrentMember),Measures.[Sales Count]) * 0.05)'
, SOLVE_ORDER ='2000', FORE_COLOR ='11005',
CALCULATION_PASS_NUMBER ='2', CALCULATION_PASS_DEPTH ='1'
SELECT
{ CrossJoin({[Customers].[Country].[USA].CHILDREN},{Measures.[Sales
Count]})} ON COLUMNS ,
{{[Time].[Month Of Year].MEMBERS}} ON ROWS
FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

Hi Faraz,

Maybe the performance problem is with unintended recursion, rather than
with ParallelPeriod() - this version works faster:

'( { [Measures].[Sales Count]} )' As
'CalculationPassValue(Measures.CurrentMember, -1 ,Relative )',
CONDITION =' CalculationPassValue((ParallelPeriod ([Time].[Month],
1,[Time].CurrentMember),Measures.[Sales Count]), -1, RELATIVE) * 0.95
< CalculationPassValue( [Measures].[Sales Count], -1,Relative ) ',
SOLVE_ORDER ='2000', FORE_COLOR ='11005',
CALCULATION_PASS_NUMBER ='2', CALCULATION_PASS_DEPTH ='1'
SELECT
{ CrossJoin({[Customers].[Country].[USA].CHILDREN},
{Measures.[Sales Count]})} ON COLUMNS ,
{{[Time].[Month].MEMBERS}} ON ROWS
FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***