PerformancePoint线图的MDX查询

Question

我在使用基于自定义MDX查询的仪表板设计器在PerformancePoint中获得正确的线条图时遇到了困难。

我们正试图显示本年度的最后6个月，而去年同期为6个月。我编写了查询，它返回我们期望的数据。

SELECT  NONEMPTY([Invoice Date].[Fiscal Week of Year].[Fiscal Week of Year])
                 ON COLUMNS
        ,NONEMPTY({[Invoice Date].[Hierarchy].[Fiscal Year] 
                        * [Measures].[Amount]})  ON ROWS
FROM    [Sales]

这将返回正确的数据。

我们创建一个线条图来显示数据。我们得到y轴和一年中的一周的Amount .一年中的一周基本上只是数字，所以当前的周数是22 (因为我们使用的是非日历的财政年度)。然后我们有一个基于年份的时间序列，它允许我们查看今年的22周数据，而前一年的第22周数据。

我们的问题是，过去的6个月跨越了一年的“边界”。正因为如此，我们的x轴没有一个“适当”的时间顺序。我们的底部轴看起来像(我简化了周数)：

1

注意22到48之间的跳跃。这在数字上是正确的，但按时间顺序，这条线图应该是：

48

我知道为什么会这样。年度财政周不是我们的发票日期层次结构的一部分。

我们的等级是财政年度=>财政季度=>财政月份=>财政日

因此，我们修复了这个问题，并将这一周添加到层次结构中。

财政年度=>财政季度=>财政月份=>年度财政周=>财政日

新的MDX查询如下：

SELECT  NONEMPTY([Invoice Date].[Hierarchy].[Fiscal Week of Year])
                 ON COLUMNS
        ,NONEMPTY({[Invoice Date].[Fiscal Year].[Fiscal Year] 
                        * [Measures].[Amount]})  ON ROWS
FROM    [Sales]

这个解决了我们的问题，因为现在一年中的每周都有关于22周属于哪一周的背景。但这带来了一个不同的问题

由于这种新的上下文，我们的图表现在不会重叠两个不同的时间序列，而是以背靠背的形式呈现出来，因为它们不再共享一个简单的数字，它们现在有一个年份+一个星期(尽管图表只显示星期数)。

因此，图表现在看起来如下所示

48

注意排序的时间顺序是正确的，但是现在沿着x轴并排出现了价值两年的数周。

希望这个冗长的解释能帮助解释这个问题。似乎我们可以有重叠的时间序列与“错误”的排序，或我们可以有适当的排序与周，但失去了能力的比较年复一年。

我想要的是让我们找到合适的类型，所以：

48

但还可以有两个不同的年份相互重叠。

或者换句话说，我希望一年中的一周根据年份进行排序，但在返回数据时也要“忘记”这个上下文，这样行图将允许周数重叠。我搞不懂怎么做到这一点。

Answer 1

好的，我有一个问题的答案。下面的MDX查询将返回行上带有财政年度的金额，每一列都将是一年中的一周。一年中的一周将按周的时间顺序“排序”。这真是个可怕的描述更好的看到它。

WITH SET [Weeks] AS
EXTRACT([Invoice Date].[Dashboard Hierarchy].[Fiscal Week of Year], [Invoice Date].[Dashboard Hierarchy])
SET [Week of Year] AS
INTERSECT(STRTOSET("{" + GENERATE(  [Weeks]
                    ,"[Invoice Date].[Fiscal Week Of Year].&[" + [Weeks].CURRENT.NAME + "]"
                    ,",") + "}"), [Invoice Date].[Fiscal Week Of Year].MEMBERS)
SELECT  [Week of Year] ON COLUMNS
    ,{([Invoice Date].[Fiscal Year].[Fiscal Year], [Measures].[Amount])}   ON     ROWS
FROM    [Sales]

当您运行查询时，您将得到如下结果

​

​

请注意，列为51、52、53、1、2、3，这对于我选择的数据范围是正确的(到目前为止的6个月)。在我的例子中，6个月前是那年的第51周。

还请注意，2012年和2013年第51周的数据有重叠之处。如果您希望PerformancePoint内容中的线条图实际比较这些数据点，这一点非常重要。这是最终的图表。

​

​

以下是查询所做的工作

WITH SET [Weeks] AS
EXTRACT([Invoice Date].[Dashboard Hierarchy].[Fiscal Week of Year], [Invoice Date].[Dashboard Hierarchy])

这是使用提取函数从日期层次结构中删除年度财政周级别的所有成员。这将返回一个可以在下一个计算成员中使用的集合。

INTERSECT(STRTOSET("{" + GENERATE(  [Weeks]
                ,"[Invoice Date].[Fiscal Week Of Year].&[" + [Weeks].CURRENT.NAME + "]"
                ,",") + "}"), [Invoice Date].[Fiscal Week Of Year].MEMBERS)

大部分工作都是在这里进行的。所以从“内部”开始我们的出路。

Generate函数根据先前创建的数周计算成员生成逗号分隔的字符串。此字符串实际上是构建成类似于仅包含周的财政年度周成员，而不是日期层次结构的一部分。这一点很重要，因为您不能将周作为层次结构的一部分返回到线条图中，或者因为它们是不同的成员而不允许第51周(例如，2012年和2013年的周)重叠。

然后使用StrToSet函数将新的逗号分隔字符串转换为一个集合。

最后但并非最不重要的是，因为可能有多周51，例如，我交叉的结果，以返回一个独特的列表。

然后，新计算的成员周将返回列。

对于如何完成同样的任务，我总是持开放态度，但是更简单，因为看起来我确实创建了一个查询，但是我只是在整个互联网上找不到关于如何完成这个任务的任何例子。

Answer 2

我看到了两个可能的解决方案:要么保留原来的“年少周”，然后对它们应用订单，要么与第二个解决方案一起使用，只在查询结果中保留最新的周，并在计算中引用前几年的周。

第一个解决方案的工作方式如下(我使用了多维数据集，并使用了2007年11月至2008年2月)：

SELECT Extract(
      (
       [Date].[Calendar].[Month].&[2007]&[11] : [Date].[Calendar].[Month].&[2008]&[2]
      )
      * [Date].[Calendar Week of Year].[Calendar Week of Year].members
      , [Date].[Calendar Week of Year])
      on COLUMNS,
      [Date].[Calendar].[Calendar Year].Members
      on ROWS
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount]

第二项工作如下：

WITH Member [Measures].[PrevYearAmount] as
     (
     ParallelPeriod([Date].[Calendar Weeks].[Calendar Year], 1, [Date].[Calendar Weeks].CurrentMember),
     [Measures].[Internet Sales Amount]
     )
SELECT [Date].[Calendar Weeks].[Calendar Week].&[44]&[2007] : [Date].[Calendar Weeks].[Calendar Week].&[10]&[2008]
      on COLUMNS,
      {
      [Measures].[Internet Sales Amount],
      [Measures].[PrevYearAmount]
      }
      on ROWS
FROM [Adventure Works]

如果您愿意，当然可以使用多维数据集计算成员，而不是WITH子句。