In SSAS, cannot make the ParallelPeriod function work when crossing months and years

1
votes

I am building an MDX request against the Adventure Works cube in Analysis Services 2008R2. I want to get months in rows and years in columns so that the result looks like:

          2005 | 2006 | 2007 | ...
January    ... |  ... |  ... | ...
February   ... |  ... |  ... | ...
March      ... |  ... |  ... | ...
...

The following request works fine:

SELECT 
[Date].[Month of Year].CHILDREN ON ROWS,
( [Date].[Calendar Year].CHILDREN, { [Measures].[Sales Amount] } ) ON COLUMNS
FROM [Adventure Works]

Now, I would like to compare the sales of the current month with the sales of the previous month:

               2005                ||      2006                || ...
          Sales | Sales prev month || Sales | Sales prev month ||...
January     ... |              ... ||   ... |              ... || ...
February    ... |              ... ||   ... |              ... || ...
March       ... |              ... ||   ... |              ... || ...
...

I wrote a request using the ParallelPeriod function:

WITH MEMBER [Sales Amount Prev Month] AS ([Measures].[Sales Amount], ParallelPeriod([Date].[Calendar].[Month], 1))
SELECT
[Date].[Month of Year].CHILDREN ON ROWS,
( [Date].[Calendar Year].CHILDREN, { [Measures].[Sales Amount], [Sales Amount Prev Month] } ) ON COLUMNS
FROM [Adventure Works]

The issue is that I get NULL values for the [Sales Amount Prev Month] measure.

It looks like, in spite of the fact that each cell represents a month and year, SSAS does not seem to be able to determine the current [Date].[Calendar].[Month] member. Then, the ParallelPeriod does not manage to get the previous month.

Any idea on how to make the ParallelPeriod function work in that case? Many thanks.

2
ssas

2 Answers

1
votes

I received the following MDX query from Microsoft:

WITH MEMBER [Sales Amount Prev Month] AS ([Measures].[Sales Amount], ParallelPeriod([Date].[Calendar].[Month], 1))
member measures.def2
as
([Measures].[Sales Amount], ParallelPeriod(
    [Date].[Calendar].[Month],
    1,         
    exists(
        descendants([Date].[Calendar].currentmember,[Date].[Calendar].[Month],SELF),
        [Date].[Month of Year].currentmember).item(0)
        )
)
SELECT
[Date].[Month of Year].CHILDREN ON ROWS,
( [Date].[Calendar Year].CHILDREN, { [Measures].[Sales Amount], [Sales Amount Prev Month],def2 } ) ON COLUMNS
FROM [Adventure Works]

It's quite complex but it seems to solve the issue.

0
votes

What about:

WITH MEMBER [Sales Amount Prev Month] AS ([Measures].[Sales Amount], [Date].[Month of Year].CurrentMember.PrevMember),
FORMAT_STRING = "Currency"
SELECT
[Date].[Month of Year].CHILDREN ON ROWS,
( [Date].[Calendar Year].CHILDREN, { [Measures].[Sales Amount], [Sales Amount Prev Month] } ) ON COLUMNS
FROM [Adventure Works]

Parallel period is used when you want the corresponding month in the previous quarter. Here PrevMember should be enough.

Update: following your comment I tried to exploit the hierarchies already present with another solution:

WITH 
MEMBER [Sales Amount Prev Month] AS 
    ([Measures].[Sales Amount], [Date].Calendar.currentMember.prevMember), 
FORMAT_STRING = "Currency"

SELECT
{[Date].[Calendar].[Month].Members} ON ROWS,
(  { [Measures].[Sales Amount], [Sales Amount Prev Month] } ) ON COLUMNS
FROM [Adventure Works]