Wednesday, March 28, 2012

ParrallelPeriod Function not working in SSAS 2005

I am trying to use the ParallelPeriod Function in a Calculated Measure to have an aggregation of last years sales Quantity to display along side the Current Years sales quantity. I have done the exact same thing in the SSAS 2000 and it worked fine. I have a separate Table for the Time dimension that contains the Date as the key along with columns for year, quarter and month. There is a date field in my fact table which is referenced to the time demension table by the date. I use the following formula:

PARALLELPERIOD([Date By Day].[the_year - Quarter - the_month - the_date].[the_year],1, [Measures].[Qty] )

This creates the LQty measure which should contain last years sales. But when I process the cube, and browse the results I get a column filled with #Vaue errors. The error message states "Members belong to different hierarchies in the ParallelPeriod function". When I browse the cube for Qty, I can see the correct information aggregated by Year then Quarter then Month. The heirarchies seem to be correct. I have tried eleminating levels in the time dimension but still get the same error.

Are there any suggestions?Not sure what your exact MDX in AS 2000 was; but according to the SQL Server 2005 BOL, the syntax for ParallelPeriod() is:

http://msdn2.microsoft.com/en-us/library/ms145500(en-US,SQL.90).aspx
>>

ParallelPeriod (MDX)

Returns a member from a prior period in the same relative position as a specified member.

Arguments

Level_Expression

A valid Multidimensional Expressions (MDX) level expression.

Numeric_Expression

A valid MDX numeric expression.

Member_Expression

A valid MDX member expression.
...
>>
So, maybe you need an expression something like:

(PARALLELPERIOD([Date By Day].[the_year - Quarter - the_month - the_date].[the_year]),
[Measures].[Qty])

No comments:

Post a Comment