关于MDX

MDX (Multi Dimensional eXpression language) 是非常强大的工具,可以将你的多维数据库/cube 发挥到极致. 本文会覆盖MDX基础,并且希望能使你对MDX查询语法,MDX的强大原因,以及如何在你的B.I. 方案中使用MDX进行计算 有充分的认识 .

就 MSDN 上的定义, MDX的目的就是为了访问多维数据更方便 . 用MDX来查看各种类别(tegories)和聚合(aggregated)的数据再合适不过.  你可能会有这样的疑问,”为什么不直接写个SQL存储过程来查询数据仓库? 我可以从cube获得的数据同样也可以从数据仓库获得!”. 虽然这是实话,但你想一下,你手上有个报表需求,需要计算实际销售(Actual Sales),销售目标(Sales Goals) 以及销售周期(Prior Period Sales). 然后以销售区域(Sales Region), 销售单位(Stores), 年月进行分组. 编写一个存储过程从数据仓库抽取需要的数据,数据量会很大而且散乱,甚至数据仓库没有针对聚合数据报表优化的话,存储的执行效率也会非常地下. 而cube存储的是数据聚合后的结果, MDX查通过多个维度查询聚合(比如区域,销售单位,月份,等)数据要快速高效很多.  简而言之,MDX可以让你用很少的代码来构建及其复杂的查询和报表.不仅仅是查询和报表更容易的创建,他们的执行速度也比直接用SQL查询块的多.

接下来,让我们先回顾下一些术语,在我们编写第一个MDX查询之前必须了解他们.

Basic Terms:

Cube: 一个cube是建立在表格和视图的度量(measures), 事实(facts), 还有维度( dimensions)的集合, 通常来自一个数据仓库. 在一个Cube中,几乎存储了每一个度量的聚合和维度组合. 比如在一个cube中销量是一个度量,你肯能随时想通过产品来查看销量数据. 每产品的销售聚合早已存储在Cube中.这就是基于cube的MDX查询要比直接在数据库中用SQL查询块的多的原因.  在下图中, 你看到一个用图形化表示的含有多个轴的cube,包含Time, Product, 还有 Store 维度.注意 Analysis Services cube指的不是一个立方体. 术语cube 是多维数据库(multidimensional database)的工业标准称呼. SSAS Cube ExampleSSAS Cube Example

Dimension: 维度就是把某一类(角度)的相关数据组织在一起.比如Adventure Works cube有个产品维度 .一个维度可以建立在表格,视图,或者涉及多个表格或视图的SELECT 语句. 

Attribute: 属性可以理解为一个维度性质的描述.例如Product维度的一些属性为种类(Category),型号名( Model Name), 产品(Product), 还有款式(Style) (这里只是举些例子).

Measures (Facts): 度量或事实,表示可以被聚合的量化数据. Adventure Works 里面的  Reseller Sales 度量组 就包含 Reseller Tax Amount, Reseller Freight Amount, 和 Reseller Order Quantity 度量.这些度量可以通过cube中的维度进行聚合计算. 例如 把销量划分(broken down )为 产品,区域,时间来查看,对终端用户来说可能更有价值. 而度量就可以按照这种方式查看. 

Hierarchy: 层级是维度属性的分级结构.层级结构包含多个级别，每个级别又包含多个成员 .下图中是一个层级的图释. 你可以很清楚的看到成员,级别,和层级的构成.

SSAS HierarchySSAS Hierarchy

Level: 正如上面提到的,一个层级包含多个级别. 在 Adventure Works cube 中 Fiscal Year 层级由四个级别组成: 最上面的级别是Year,第二个是Quarter, 第三个是Semester, 第四个是 Month, 最后一个级别是 Date.

Member: 成员是级别中的一个值. 比如Adventure Words cube 中的 Fiscal Year 层级的 Month 级别中的成员 March 就是一个值.

Axis: 可以把轴视为维度的一个线性衍生. 轴和维度交错于cube和度量.

MDX查询最多支持128轴. 单只有5个有别名.

 

  • Rows
  • Columns
  • Pages
  • Chapters
  • Sections

多数 MDX 查询只包含 2 个轴就能把报表搞定了. 另外能一次查看超过2个维度的多维数据库软件并不多. 

Tuple: 元组是使用不同维度属性来标示唯一单元的有序组合(A tuple is an ordered collection of dimension attributes that are not from the same dimension that uniquely identify a cell in a cube .这句吃不太准意思..怕有误解所以原文贴上). 在下面的列子中 , 小括号里面的就是元组,由 Data维度的Fiscal Year属性与Sales Reason维度的Sales Reason 属性组合 .

--A tuple 
([Date].[Fiscal Year].&[2006], [Sales Reason].[Sales Reason].&[9])

Set: 集合是元组的聚集. 下列集合包含两个元组. 一个集合中的元组的维度必须一致,否则你的查询会报错.就是说,维度必须一样,顺序也必须一样. 集合必须用花括号框起来. 其中的每个元组用小括号框起来.

--A collection of tuples
{
     ([Date].[Fiscal Year].&[2006], [Sales Reason].[Sales Reason].&[9]),
     ([Date].[Fiscal Year].&[2006], [Sales Reason].[Sales Reason].&[5])
}

MDX Query Syntax

下图是一条基础的MDX查询语句. 让我们解析一下这个查询的各个部分.

Basic MDX Query exampleBasic MDX Query example

一看到这个查询,你可能觉得很像T-SQL , 不过那也只是部分相似而已.MDX查询语句的功能与T-SQL有很大差异. 

首先是 SELECT 语句. MDX查询中的 Select 语句与SQL 查询的SELECT语句有很大差别. 在T-SQL中, SELECT 语句只能定义查询结果的列布局. 在MDX中SELECT 语句定义多个维度,共达128个不同的轴. 上面的示例中,你可以看到我们指定了日期维度和Fiscal Year 属性,并显示在横(Row)轴上. Internet Order Quanity 度量显示在纵(Column)上.

接着是FROM 语句. FROM 语句指定你要查询的cube. 在 T-SQL中你可以通过FROM 语句连接多个表格,但是MDX没法连接多个cube. 所以想都不要想了.

最后是WHERE 语句. WHERE语句指定一个切片(slicer dimension ) 限制MDX 查询的结果. 例子中我们用了 Product维度Category属性中的一个成员(实际值是Bikes category ) 来限制查询结果.

 

建立基本的查询

我们一些学了一些必要的属于,并且了解了基本的查询语法. 现在,让我们开始写一些MDX.

Important Note: 本例中所有示例使用的都是 Adventure Works 2008 Analysis Services database, 可以从这里下载here. 装好以后,用BIDS打开Analysis Services 项目(默认地址default in c:\Program Files\Microsoft SQL Server\100\Tools\Samples) . 记得修改数据源连接,然后部署项目.

打开 SQL Server Management Studio. 在弹出的 Connect to Server 对话框中,下拉选择Analysis Services.指定Adventure Works 数据库所在的服务器.

连接后右击 AdventureWorksCube 选择 New Query 然后选 MDX.

Open MDX Query windowOpen MDX Query window

现在MDX 查询编辑器窗口已经打开.我们开始写点MDX, 第一个查询非常简单, 它返回Adventure Works cube默认的度量.

SELECT
FROM [ADVENTURE WORKS]
;

结果:

query resultsquery results

如果你执行了上面的查询,结果只返回了一个数字. 默认情况下什么都没指定那么就返回Cube中默认的度量. 在Adventure Works cube中. 默认的是度量是  Reseller Sales Amount. 这个查询返回了 所有产品,所有时间,所有区域…等等等 ..总之就是Reseller Sales 总和 . 所以除非你指定某个要看的度量.否则就返回默认的度量 Reseller Sales Amount.

如果我要看不懂的度量,那就必须使用Where语句指定一个切片(slicer).

SELECT
FROM [ADVENTURE WORKS]
WHERE [Measures].[Reseller Total Product Cost]
;

结果:

Basic Query with slicerBasic Query with slicer

因为我们限定了查询Where语句的查询范围 Reseller Total Product Cost, 所以查询返回Reseller Total Product Cost的总计.

现在让我们指定一些维度,对cube进行切片和分块.

 
SELECT [Date].[Calendar].[Calendar Year] ON COLUMNS,
[Product].[Product Categories].[Category] ON ROWS
FROM [ADVENTURE WORKS]
;

结果:

Basic Query with dimensions on two axesBasic Query with dimensions on two axes

我们在Columns轴和Row轴上都指定了维度. 因为我们没有指定返回的度量,所以默认还是返回Reseller Sales Amount.

最后一个查询,让我们把所有东西都放上去:

SELECT [Date].[Calendar].[Calendar Year] ON COLUMNS,
[Product].[Product Categories].[Category] ON ROWS
FROM [ADVENTURE WORKS]
WHERE [Measures].[Reseller Total Product Cost]
;

结果:

Basic MDX query with dimensions and slicerBasic MDX query with dimensions and slicer

 

MDX 书籍

这里有一些值得推荐的MDX书: 

MDX Solutions – http://www.amazon.com/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080/ref=sr_1_2?ie=UTF8&s=books&qid=1284385194&sr=8-2

Microsoft SQL Server 2008 MDX Step by Step – http://www.amazon.com/Microsoft-SQL-Server-2008-Step/dp/0735626189/ref=sr_1_1?s=books&ie=UTF8&qid=1284385251&sr=1-1

Practical MDX Queries – http://www.amazon.com/Practical-MDX-Queries-Microsoft-Analysis/dp/0071713360/ref=sr_1_1?s=books&ie=UTF8&qid=1284385281&sr=1-1

Fast Track to MDX – http://www.amazon.com/Fast-Track-MDX-Mark-Whitehorn/dp/1846281741/ref=sr_1_1?ie=UTF8&s=books&qid=1284477262&sr=8-1

I hope this article has given you a good starting point so that you can begin writing your own MDX queries. Stay on the look out for my part 2 article in which we will cover more advanced MDX functions and queries. As always, feel free to shoot me a message, leave a comment, or email me at dryan@pragmaticworks.com .

原文地址: http://www.bidn.com/articles/mdx-and-dmx/178/mdx-101-introduction-to-mdx