在 SSAS 中，跨月和年时无法使 ParallelPeriod 函数起作用

Question

我正在 Analysis Services 2008R2 中针对 Adventure Works 多维数据集构建 MDX 请求。我想要在行中获取月份，在列中获取年份，以便结果看起来像：

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

以下请求工作正常：

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

现在，我想将当月的销售额与上个月的销售额进行比较：

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

我使用以下命令编写了一个请求ParallelPeriod 函数：

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]

问题是我得到的 [Sales Amount Prev Month] 度量值为 NULL。

看起来，尽管每个单元格代表一个月和一年，SSAS 似乎无法确定当前的 [日期].[日历].[月] 成员。然后，ParallelPeriod 无法获取上个月。

关于如何使 ParallelPeriod 函数在这种情况下工作有什么想法吗？非常感谢。

Answer 1

我收到了来自 Microsoft 的以下 MDX 查询：

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]

它相当复杂，但似乎解决了问题。

Answer 2

怎么样：

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]

当您想要上一季度的相应月份时，可以使用平行期间。这里 PrevMember 应该足够了。

更新：根据您的评论，我尝试利用另一个解决方案已经存在的层次结构：

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]