MDX查询语言

最新推荐文章于 2021-04-25 16:11:11 发布

charles66

最新推荐文章于 2021-04-25 16:11:11 发布

阅读量1.2k

点赞数 3

分类专栏：大数据文章标签： MDX

本文链接：https://blog.csdn.net/u013265988/article/details/80666507

版权

大数据专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MDX查询
1、基本结构
MDX查询与SQL查询类似，都是用SELECT FROM
MDX使用SELECT [维度名称].[层次名称].[级别].Members ON COLUMNS,[Measures].[度量名称] ON ROWS FROM [CUBE名称]
2、常用查询
（1）一个维度和一个度量：WITH SET [~COLUMNS] AS [维度名称].[层次名称].[级别].Members SET [~ROWS ] AS [Measures].[度量名称] SELECT [~COLUMNS] ON COLUMNS,[~ROWS ] ON ROWS FROM [CUBE名称]
（2）一个维度和多个度量：WITH SET [~COLUMNS] AS [维度名称].[层次名称].[级别].Members SET [~ROWS ] AS {[Measures].[度量名称1]，[Measures].[度量名称2]} SELECT [~COLUMNS] ON COLUMNS,[~ROWS ] ON ROWS FROM [CUBE名称]
（3）多个相同层次维度和多个度量：WITH SET [~COLUMNS] AS {[维度名称].[层次名称].[级别1].Members，[维度名称].[层次名称].[级别2].Members} SET [~ROWS ] AS {[Measures].[度量名称1]，[Measures].[度量名称2]} SELECT [~COLUMNS] ON COLUMNS,[~ROWS ] ON ROWS FROM [CUBE名称]
（4）多个不同层次维度和多个度量：WITH SET [~COLUMNS] AS CrossJoin({[维度名称1].[层次名称1].[级别1].Members，[维度名称1].[层次名称1].[级别2].Members},{[维度名称2].[层次名称1].[级别1].Members，[维度名称2].[层次名称2].[级别2].Members}) SET [~ROWS ] AS {[Measures].[度量名称1]，[Measures].[度量名称2]} SELECT [~COLUMNS] ON COLUMNS,[~ROWS ] ON ROWS FROM [CUBE名称]
（5）多个不同层次维度和多个度量：WITH SET [~COLUMNS] AS CrossJoin({[维度名称1].[层次名称1].[级别1].Members，[维度名称1].[层次名称1].[级别2].Members},{[维度名称2].[层次名称1].[级别1].Members，[维度名称2].[层次名称2].[级别2].Members}) SET [~ROWS ] AS {[Measures].[度量名称1]，[Measures].[度量名称2]} SELECT [~COLUMNS] ON COLUMNS,[~ROWS ] ON ROWS FROM [CUBE名称]
（6）使用NOT EMPTY去掉对应行（列）度量为空的数据：WITH SET [~COLUMNS] AS [维度名称].[层次名称].[级别].Members SET [~ROWS ] AS [Measures].[度量名称] SELECT NOT EMPTY [~COLUMNS] ON COLUMNS,NOT EMPTY [~ROWS ] ON ROWS FROM [CUBE名称]
（7）使用HAVING过滤：WITH SET [~COLUMNS] AS [维度名称].[层次名称].[级别].Members HAVING [Measures].[度量名称]>1000 SET [~ROWS ] AS [Measures].[度量名称] SELECT NOT EMPTY [~COLUMNS] ON COLUMNS,NOT EMPTY [~ROWS ] ON ROWS FROM [CUBE名称]
（8）使用WHERE条件：WITH SET [~COLUMNS] AS [维度名称].[层次名称].[级别].Members SET [~ROWS ] AS [Measures].[度量名称] SELECT NOT EMPTY [~COLUMNS] ON COLUMNS,NOT EMPTY [~ROWS ] ON ROWS FROM [CUBE名称] WHERE [维度名称].[层次名称].&[具体值]
（9）使用WHERE条件将不同层次结构的多个成员添加到 WHERE 子句：WITH SET [~COLUMNS] AS [维度名称].[层次名称].[级别].Members SET [~ROWS ] AS [Measures].[度量名称] SELECT NOT EMPTY [~COLUMNS] ON COLUMNS,NOT EMPTY [~ROWS ] ON ROWS FROM [CUBE名称] WHERE ([维度名称1].[层次名称1].&[具体值],[维度名称2].[层次名称2].&[具体值])
（10）使用WHERE条件将同一层次结构的多个成员添加到 WHERE 子句, 需要在 WHERE 子句中包括一个集：WITH SET [~COLUMNS] AS [维度名称].[层次名称].[级别].Members SET [~ROWS ] AS [Measures].[度量名称] SELECT NOT EMPTY [~COLUMNS] ON COLUMNS,NOT EMPTY [~ROWS ] ON ROWS FROM [CUBE名称] WHERE ({[维度名称1].[层次名称1].&[具体值],[维度名称1].[层次名称2].&[具体值]},[维度名称2].[层次名称2].&[具体值])
（11）IIF函数判断：

WITH MEMBER [Product].[Beer and Wine].[BigSeller] AS IIf([Product].[Beer and Wine] > 100, "Yes","No") SELECT {[Product].[BigSeller]} ON COLUMNS, Store.[Store Name].Members ON ROWS FROM Sales