首次学习编写自定义 MDX 查询

• 下载 MDX 查询 - 3.1 KB

引言

在本文中，我们将介绍编写 MDX 查询时使用的一些基本概念和术语，我们将深入探讨 MDX 查询的“为什么”、“是什么”和“怎么做”。

在我们进行谷歌搜索时，可以找到一些关于此主题的优秀文章，但当我作为初学者在这方面进行搜索时，我没有找到一篇包含我所需所有内容的文章。因此，我迈出了这一小步，写下了这篇文章并与大家分享，以便您可以轻松学习自定义 MDX 并乐在其中。

多维表达式 (MDX)

MDX 查询语言用于检索存储在各种技术（如 Microsoft SQL Server Analysis Services (SSAS)、Oracle、Teradata 等）创建的 OLAP Cube 中的信息。MDX 和 T-SQL 的关键区别在于，MDX 查询构建数据的多维视图，而 T-SQL 构建关系视图。SQL 查询旨在仅处理二维表格数据。而 MDX 可以在查询中处理更多维度。

MDX 也用于为 Power Pivot 中的自定义计算编写表达式，并用于创建 OLAP Cube 中的计算成员。

在查询中，“维度”是什么意思？一般来说，我们可以说维度是具有相关成员详细信息的实体，您计划使用它们来研究和分析 OLAP Cube 中的数据。

MDX 查询中使用的基本概念简介

我们需要在脑海中清晰地了解在处理 MDX 查询时使用的各种概念和术语。起初，当我刚接触这些内容时，我发现理解所有这些非常困难，但我不想让您也卡在这里，所以让我们开始吧。

立方体

OLAP Cube 是多维数据存储的基本单位，我们可以在上面分析存储的数据并研究各种模式。您可以通过这篇名为 **在 SSAS 中创建第一个 OLAP Cube** 的文章进一步了解 OLAP Cube 的创建。

尺寸

维度的主要功能是为您的数据提供筛选、分组和标记。维度表包含有关业务主题的文本描述。总的来说，我们可以说维度是具有相关成员属性的主实体，我们可以使用它们来快速有效地研究 OLAP Cube 中存储的数据。

度量 与度量组

存储在事实表中的指标值称为度量。度量用于分析业务绩效。度量通常包含数值数据，可以根据相关维度的使用情况进行聚合。度量组包含一组相关的度量。

要快速了解数据仓库，请参考文章 创建第一个数据仓库。

请看下面一张图，它代表了上面讨论的术语。

OLAP 数据库是 Cube 的容器。在开始编写查询之前，确定 Cube 名称很重要。然后，我们需要从适当的度量组中选择度量并使用相关的维度。

层次结构、成员、级别 简介

让我们简要了解一下 MDX 查询中常用的术语。

信号强度

通常，维度下的属性被视为级别，它们也被称为 **属性层次结构**。

例如，日期维度有各种级别，如季度、半年、周、年等。

成员

MDX 查询的关键组成部分是成员。每个级别包含一个或多个成员。

例如，**年度季度** 包含各种成员，如 CY Q1、CY Q2、CY Q3、CY Q4。

用户定义层次结构

我们在设计 OLAP Cube 时，通常会根据它们的关系来创建这种层次结构。您可以在下图所示的日期层次结构中看到。

此层次结构也包含各种级别，**默认级别 0** 保留给 **[ALL]**。

背景

如果您对使用 Microsoft Business Intelligence 创建数据仓库和 OLAP Cube 更感兴趣，请参阅我之前的文章。

在这里，我们将使用 Microsoft SQL Server 2008 R2（标准版、企业版）。

Using the Code

准备我们的测试环境

1. 在这里，您需要从 CodePlex 网站下载 **Adventure Works 数据仓库**。

• 下载 Adventure Works 数据仓库 2008 R2

2. 还要从 CodePlex 网站下载使用此 AdventureWorksDW2008 R2 创建的 **Analysis Services 解决方案**。

• 下载 Adventure Works 2008 R2 Analysis Services 项目

3. 在 Services.msc 中检查 SQL Server Analysis services 是否已启动并正在运行。

4. 配置上述 SSAS 解决方案中的连接字符串并部署您的 Cube。

5. 现在打开 Microsoft SQL Server Management Studio (SSMS)，并使用 **Windows 身份验证** 连接 **Analysis Services**。

选择 **服务器类型**：Analysis Services-->指定您的 SQL **服务器名称**：例如 mubin-pc\fairy 或 localhost -->单击：**连接**

6. 成功连接到 SQL Server Analysis Server 后，您可以查看已部署的 OLAP Cube，只需单击 + 按钮进行展开。

7. 打开 **新建 MDX 查询** 编辑器窗口

**右键单击** 数据库名称（Adventure Works DW 2008 R2）--> 选择 **新建查询** --> 单击 **MDX**

8. 现在我们可以开始在查询编辑器窗口中处理 MDX 查询了。

MDX 查询中的轴简介

MDX 查询可以在 `SELECT` 语句中包含 0、1、2 或最多 129 个查询轴。每个轴的行为方式完全相同，这与 SQL 不同，SQL 中行和列的行为方式存在显著差异。

请参阅下表以了解保留的轴编号及其别名

使用 SQL Server Management Studio (SSMS)，我们只能浏览两个轴上的值，**列**（轴 **0**）和 **行**（轴 **1**）。

开始使用 MDX

1. 从简单的 MDX 查询开始

语法:

Select From [您的 Cube 名称] ;

这将为您提供结果窗格中所示的聚合结果。MDX 不是区分大小写的，除非是在维度中定义的成员键。此查询将使用所有维度中定义的默认成员，并使用 OLAP Cube 设计师定义的默认度量。

您可以将左侧窗格中的 Cube 名称、维度成员拖放到查询窗口，而不是手动输入。此查询也称为无轴查询。

Select From [Adventure Works];

2. 将维度拖放到轴上

如果我们不为维度和度量指定轴，当设计发生变化时，可能会导致结果错误。

示例

从 Adventure Works Cube 的列中检索所有客户名称。

Select Dimension.Member on Column From [OLAPCubeName ]
或
Select Dimension.Member on 0 From [OLAPCubeName ]

Select [Customer].[Customer].[Customer] on 0 
From [Adventure Works];
 
Select [Customer].[Customer].[Customer] on columns 
From [Adventure Works]; 

正如您注意到的，如果您的维度未与度量组关联，您可能会在每个结果单元格中看到相对于每个客户的相同值。

3. 使用两个轴（行和列）

您可以在任何轴上选择维度或度量。

示例

检索互联网销售金额（按客户）。换句话说，我们可以说显示客户在互联网购买期间花费的金额的详细信息。

Select [Measure] on Columns,
[Dimension].[Members] on Rows From [Cube Name] ;

或者

Select [Measure] on Rows,
[Dimension].[Members] on Columns From [Cube Name] ;

Select [Measures].[Internet Sales Amount] on Columns,
[Customer].[Customer].[Customer] on Rows
From [Adventure Works]; 

在这里，您可以看到度量值（互联网销售金额）已根据客户正确分配。

4. MDX 查询中 .members 和 .children 简介

.Members

如果您将其与层次结构级别一起使用，它将检索其下的所有值，并以 [ALL] 的形式显示其聚合。

语法

Select [Dimension].[Hierarchy].members on Columns from CubeName
或
Select [Dimension].[Hierarchy].[Level].members on Columns from CubeName

Select [Measures].[Internet Average Sales Amount] on Columns,
[Product].[Category].members on Rows
From [Adventure Works];

.Children

当我们要检索某个维度特定级别下的所有成员值时，我们使用 **.children**，这将排除结果集中的聚合值 [ALL]。

语法

Select [Dimension].[Hierarchy].[Level].children on Columns from CubeName

Select [Measures].[Internet Average Sales Amount] on Columns,
[Product].[Category].children on Rows
From [Adventure Works];

5. 元组和集合简介

元组:

当我们需要在轴上放置一个维度的多个成员或该维度的层次结构时，元组就出现了。元组用花括号 {} 括起来；对于单个元组，括号是可选的。

我们可以说元组用于通过您的维度成员在 Cube 中识别特定位置。元组将定义您的 Cube 的切片。元组可以包含一个或多个成员，但不能包含来自同一维度的成员。

这是同一日期维度成员的元组示例。多个元组的组合构成一个集合。

示例

使用元组查看 2005 年至 2007 年之间的互联网销售金额明细。

select {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007]} on rows,
[Measures].[Internet Sales Amount] on columns from
[Adventure Works];

Set

集合是零个、一个或多个元组的有序集合。集合最常用于在 MDX 查询中定义轴和切片器维度。

元组或多个元组的组合会形成集合。当您要包含范围时，您可以使用 **:** 而不是用逗号分隔元组成员，前提是它们属于同一维度成员。

或者

语法

{[Date].[CY 2008] : [Date].[CY 2005]} 或 {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007]}
或
( [Date].[Calendar Year].members , [Product].[Product].members )

示例

查看 2005 年至 2008 年之间的互联网销售金额明细

select {[Date].[CY 2008] : [Date].[CY 2005]}  on rows,
[Measures].[Internet Sales Amount] on columns from
[Adventure Works];

要使用来自不同维度的元组组合，我们必须使用交叉联接，我们很快就会学到。

6. 使用 CROSS JOIN

Cross Join 函数返回一个或多个集合的交叉积。

每当我们想组合同一维度或不同维度的多个成员时，就可以使用交叉联接。可以使用 **\*** 符号来实现维度成员之间的交叉联接。

select {[Product].[Category].children * [Product].[Subcategory].children} on rows,
[Measures].[Internet Sales Amount] on columns from
[Adventure Works];

我们也可以使用 Cross Join 函数来实现不同维度成员之间的交叉联接，但如果您使用 **\*** 或 `CrossJoin` 函数，结果将保持不变。

select CrossJoin([Product].[Category].children,[Product].[Subcategory].children) on rows,
[Measures].[Internet Sales Amount] on columns from
[Adventure Works]; 

7. 使用 Non Empty 或 NonEmpty

要从结果集中排除 `Null` 值，我们可以使用 `NonEmpty()` 或 Non Empty。现在，我将不讨论 Non Empty 和 `NonEmpty` 函数之间的区别。

`NonEmpty` 函数首先被评估，因此如果第一个度量没有数据，它将删除行。让我们看下面的例子，如何从结果集中删除 `null` 值。

Non Empty 或 `NonEmpty()` 函数可用于任何轴。

示例

让我们来看一下下面例子中应用的交叉联接，在这里您可以看到我们如何通过将多个度量放在花括号 {} 中来检索它们。

在使用以下 MDX 查询时，您可以看到结果集中有 `null` 值。

Select 
CrossJoin([Product].[Category].children,[Product].[Subcategory].children,
[Product].[Product].children) on rows,
{[Measures].[Internet Sales Amount],[Measures].[Internet Freight Cost]} on columns 
from [Adventure Works]; 

现在使用 Non Empty 从结果集中消除这些 `Null` 值。

Select 
non empty CrossJoin([Product].[Category].children,[Product].[Subcategory].children,
[Product].[Product].children) on rows,
{[Measures].[Internet Sales Amount],[Measures].[Internet Freight Cost]} on columns 
from [Adventure Works]; 

8. 使用 Where 子句应用切片

我们可以使用 **Where** 子句对 Cube 进行切片，这类似于 T-SQL 中的“where”子句。

示例

我想查看 2007 年每个产品的互联网销售金额明细。

select [Measures].[Internet Sales Amount] on columns,
[Product].[Product].[Product].members on rows
from [Adventure Works]
where [Date].[Calendar Year].[CY 2007];

示例

如果我想查看 2007 年和 2009 年每个产品的互联网销售金额明细。

select [Measures].[Internet Sales Amount] on columns,
[Product].[Product].[Product].members on rows
from [Adventure Works]
where {[Date].[Calendar Year].[CY 2007],[Date].[Calendar Year].[CY 2009]};

9. 使用 Filter 函数对数据应用筛选

Filter 函数也将用于根据指定的布尔条件对指定集合中的成员应用筛选。

语法

Filter( <Set>, Boolean Condition)

select  [Measures].[Internet Sales Amount] on columns,
filter([Product].[Product].[Product].members ,
[Measures].[Internet Sales Amount]>5000
)
on rows
from [Adventure Works]
where {[Date].[Calendar Year].[CY 2007]};

示例： 如果我想检索名称以“A”开头且互联网销售金额小于 5000 的产品。

select  [Measures].[Internet Sales Amount] on columns,
filter([Product].[Product].[Product].members ,
([Measures].[Internet Sales Amount]<19000 and _
left([Product].[Product].currentmember.name,1)="A")
)
on rows
from [Adventure Works]
where {[Date].[Calendar Year].[CY 2007]};

10. 使用 **Order** 函数对数据应用排序

要对数据进行排序，您可以使用 order 函数，使用此函数可以覆盖 Cube 设计中指定的默认顺序。

语法

Order(<set>, Context, Asc | Desc|Bsc|Bdesc)

select  [Measures].[Internet Sales Amount] on columns,
order([Product].[Product].[Product].members ,[Measures].[Internet Sales Amount],desc)
on rows
from [Adventure Works]
where {[Date].[Calendar Year].[CY 2007]}