In SSAS, cannot make the ParallelPeriod function work when crossing months and years
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 ... | ... || ... | 开发者_如何学Python ... || ...
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.
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.
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]
精彩评论