Sql – MDX, SSAS 2005 calculating SPLY (Same Period Last Year)

mdxsql serverssas

I'm trying to achieve the following: I have a measure: [Measures].[Sales] and a time dimmension: [Time].[Year – Month – Day]. I would like to add new measure: [Measures].[Sales SPLY] to work in the following way:

MONTH—————–Sales————–Sales SPLY

2009 January——–120 000,00——–110 000,00

2009 February ——100 000,00——–90 000,00

2009 March———-120 000,00——–110 000,00

TOTAL:—————340 000,00——–290 000,00

The value of [Sales SPLY] is calculates using the following way: I created a new Sum measure, and in a Cube Script I replace it with the following code:

([Measures].[Sales SPLY], Leaves([Time])) = (PARALLELPERIOD([Time].[Year - Month - Day].[Year],1,[Time].[Year - Month - Day].currentmember), [MEASURES].[Sales])

It's working, however is just very, very slow. Does anyone know a better way of writing this MDX? using only the parrarel period in a calculated member will result in a bad value of the Total in SPLY measure. Please, I googled a lot and couldn't find any thing better than this.

Best Answer

I tend to just create a member like

WITH
MEMBER [Measures].[Sales SPLY] as ([Measures].[Sales], ParallelPeriod([Time].[Year - Month - Day].[Year],1,[Time].[Year - Month - Day].currentmember))
SELECT {[Measures].[Sales], [Measures].[Sales SPLY]} ON 0, ....

And it works fine.

How are you using it in your query overall?

Rob

Related Topic