SSAS-在行上显示度量 [英] SSAS - Show Measures on Rows

问题描述

总体而言，我对MDX和SSAS还是陌生的.我正在尝试以一种很好的表格形式获取所有度量和日期.像这样:

I am very new to MDX and SSAS in general. I am trying get all measures and dates in a nice tabular form. So something like:

╔═════════╦══════╦═══════╗
║ Measure ║ Date ║ Value ║
╠═════════╬══════╬═══════╣
║  Meas1  ║   D1 ║     3 ║
║  Meas1  ║   D2 ║     8 ║
║  Meas1  ║   D3 ║     9 ║
║  Meas2  ║   D1 ║     7 ║
║  Meas2  ║   D2 ║     4 ║
╚══...════╩═══..═╩═══...═╝

我考虑过在行轴上进行交叉联接，但是不确定在列轴上放置什么.

I thought of doing a cross join on the rows axis, but not sure what to put on the columns axis.

有什么建议吗?

推荐答案

您可以将不使用的任何层次结构的默认成员放在列轴上.如果您在立方体中e. G.具有默认成员[Geography].[City].&[All]的层次结构[Geography].[City]，您的查询可能如下所示:

You can put the default member of any hierarchy that you are not using otherwise on the columns axis. If you have in your cube e. g. a hierarchy [Geography].[City], which has the default member [Geography].[City].&[All], your query could look like this:

SELECT [Geography].[City].&[All]
       ON COLUMNS,
       { [Measures].[Meas1], [Measures].[Meas2] }
       *
       { [Date].[Month].[D1], [Date].[Month].[D2], [Date].[Month].[D3] }
       ON ROWS
  FROM [YourCube]

实际上，问题在于MDX中的轴必须具有正确的顺序，并且如果未包含之前的所有轴，则查询中可能没有轴.并且由于列轴是第二个轴(编号为1，从列轴的编号从0开始)，所以列轴必须在那里.因此，问题在于您必须将非空集放入列轴.而且，由于始终存在一个隐式切片器轴，其中包含查询中未提及的所有层次结构的默认成员(通常为All成员)，因此此解决方案的想法是将其中的任意成员移动到column轴以使它是一个非空集，而不会更改结果-如果您使用e会发生这种情况. G. [Geography].[City].Members，然后这些值将按城市显示.

Actually, the problem is that the axes in MDX have to be in the correct order, and no axis may be in a query if not all the axes before are contained. And as the columns axis is the second (numbered 1, as the numbering starts at 0 with the columns axis), the columns axis must be there. So the problem is that you must put an non empty set into the columns axis. And as there is always an implicit slicer axis containing the default members (which normally are the All members) of all hierarchies not mentioned in the query, the idea of this solution is to move any arbitrary of these to the columns axis to make it an non empty set without altering the result - which would have happened if you would have used e. g. [Geography].[City].Members, as then the values would have been shown broken down by cities.

如果您希望将度量和日期放入列轴，这将更加容易，因为这是第一个轴.然后查询可能只是:

If you would have wanted to put the measures and dates into the columns axis, this would have been much more easy, as this is the first axis. Then the query could just have been:

SELECT { [Measures].[Meas1], [Measures].[Meas2] }
       *
       { [Date].[Month].[D1], [Date].[Month].[D2], [Date].[Month].[D3] }
       ON COLUMNS
  FROM [YourCube]

如果不需要这些值，则可以在这样的列上使用空集:

And if you would not have needed the values, you could have used an empty set on the columns like this:

SELECT {}
       ON COLUMNS,
       { [Measures].[Meas1], [Measures].[Meas2] }
       *
       { [Date].[Month].[D1], [Date].[Month].[D2], [Date].[Month].[D3] }
       ON ROWS
  FROM [YourCube]

但是那样的话，您将只拥有行标题，而在列中也没有value列.

But then you would just have had the row headers, and nor value column in the columns.

请注意，根据MDX的概念，查询可以具有零，一，二，三或更多个轴.仅包括SSMS在内的大多数客户端工具都难以显示具有两个以上轴的结果集.但这是对客户端工具的限制，而不是MDX的限制.

Note that a query can have zero, one, two, three, or more axes from the conception of MDX. Just most client tools including SSMS have difficulties displaying result sets with more than two axes. But this is a restriction of the client tools,not of MDX.

这篇关于SSAS-在行上显示度量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！