我正在尝试从用于业务的多维数据集中提取一些数据,该多维数据集中有数百万个数据。我正在使用MDX,这是我的新手,我正在尝试复制其中一个数据透视表的输出。我创建了查询,似乎一切都在正确的位置,但是输出似乎反转了(行上有行,行上有列,请查看图片了解详细信息),由于某种原因,我无法弄清楚如何解决它。
此图显示了所需的输出和实际的输出:这是我正在使用的查询:
SELECT NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Dem
Product].[Dem Product Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)}),
Hierarchize({DrilldownLevel({[TheDate].[Fiscal Year].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[TheDate].[Quarter].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[TheDate].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[Running].[Business].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[Reporting].[segment].[All]},,,INCLUDE_CALC_MEMBERS)}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(SELECT
({[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductX],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductY],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductZ],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductA],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductB],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductC],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductD],
{[Reporting].[segment].&[Online telecom],
[Reporting].[segment].&[Online shop],
[Reporting].[segment].&[ground sales],
[Reporting].[segment].&[Retail],
[Reporting].[segment].&[third party]})
ON COLUMNS
FROM [SalesCube])
WHERE
([Reporting.[UPSegment].&[1stDivision],
[Measures].[BusinessRevenue]) CELL PROPERTIES VALUE,
FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
我想要输出的原因是,这样我就可以将数据导入power bi并进行操作以添加运行我的业务所需的临时措施。我可以得到的任何帮助=)谢谢!
试试下面的查询
SELECT NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Dem
Product].[Dem Product Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)}),
Hierarchize({DrilldownLevel({[TheDate].[Fiscal Year].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[TheDate].[Quarter].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[TheDate].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[Running].[Business].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[Reporting].[segment].[All]},,,INCLUDE_CALC_MEMBERS)}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON rows,
[Measures].[BusinessRevenue] on columns
FROM
(SELECT
({[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductX],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductY],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductZ],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductA],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductB],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductC],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductD],
{[Reporting].[segment].&[Online telecom],
[Reporting].[segment].&[Online shop],
[Reporting].[segment].&[ground sales],
[Reporting].[segment].&[Retail],
[Reporting].[segment].&[third party]})
ON COLUMNS
FROM [SalesCube])
WHERE
([Reporting.[UPSegment].&[1stDivision]) CELL PROPERTIES VALUE,
FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
谢谢@mozarub!我得到了我在ssms中寻找的输出。但是,当我使用此代码并写入powerbi来获取其中的数据集时,由于某种原因,它不会提供相似的输出。当我在PBI中运行它时,每个层次结构都有2列:1以[parent_unique_name]结尾,并且1以[hierarchy_unique_name]结尾,其中一路向下是空白单元格,或者是“所有”单元格(即无法按fy19,fy20等过滤)但只能用空格或父级使用[[TheDate]。[Fiscal Year]。[All]”,而对于层次结构则使用[TheDate]。[Fiscal Year]。所有层次结构都相同。否则措施似乎还可以。有什么建议么?
如果它解决了您的问题,则应将其标记为答案。其次,PBi具有其格式化功能。尝试删除“ DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME”
谢谢,MoazRub。这个论坛也很新,但我已将其作为答案=)。我将尝试删除您的建议,并查看它是否在PBi中有效。谢谢!