《Microsoft SQL Server 2008 MDX Step by Step》学习笔记一：MDX查询第一课

13432878738

　　SQL Server 2008中SQL应用系列及BI笔记系列--目录索引
　　导读:本文介绍MDX查询的基础内容，已经了解的读者可以直接略过。
　　本文将包括以下内容：
　　■1、使用MDX查询编辑器构建并执行MDX查询。
　　本文所用数据库和所有源码，请到微软官网下载
　　
　　1、SQL查询与MDX查询示例比较
　　在MdxStepByStep数据库中（后缀名为.SQL）查询


USE MdxStepByStep
SELECT
m.EnglishProductName, o.TotalSalesAmount
FROM dbo.DimProduct m
INNER JOIN ( -- TOP 10 PRODUCTS OF 2003
SELECT TOP 10
a.ProductKey, SUM(a.SalesAmount) AS TotalSalesAmount
FROM (
SELECT
x.productkey, x.salesamount
FROM dbo.FactInternetSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2003
UNIONALL
SELECT
x.productkey, x.salesamount
FROM dbo.FactResellerSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2003
) a
GROUPBY a.ProductKey
ORDERBY TotalSalesAmount DESC
) n
ON m.ProductKey=n.ProductKey
LEFT OUTER JOIN ( --PRODUCT SALES IN 2004
SELECT
a.ProductKey, SUM(a.SalesAmount) AS TotalSalesAmount
FROM (
SELECT
x.productkey, x.salesamount
FROM dbo.FactInternetSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2004
UNIONALL
SELECT
x.productkey, x.salesamount
FROM dbo.FactResellerSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2004
) a
GROUPBY a.ProductKey
) o
ON m.ProductKey=o.productkey
ORDERBY n.TotalSalesAmount DESC
/*
EnglishProductName TotalSalesAmount
Mountain-200 Black, 38 1327957.4077
Mountain-200 Black, 42 1139429.4872
Mountain-200 Silver, 38 1136622.4928
Mountain-200 Black, 46 1011074.3685
Mountain-200 Black, 38 NULL
Mountain-200 Silver, 42 1011486.1762
Touring-1000 Blue, 60 835290.1559
Road-350-W Yellow, 48 897217.9635
Mountain-200 Silver, 46 1029170.7639
Road-350-W Yellow, 40 840970.6467
*/　　
　　而在维度数据库中对应的MDX查询（后缀名为.MDX）如下：


WITH
MEMBER [Measures].[Total Sales Amount]AS
([Measures].[Internet Sales Amount]) + ([Measures].[Reseller Sales Amount])
SET[Top 10 Products of 2003]AS
TOPCOUNT(
{[Product].[Product].[Product].Members},
10,
([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY 2003])
)
SELECT
{([Measures].[Total Sales Amount])} ON COLUMNS,
{[Top 10 Products of 2003]} ON ROWS
FROM[Step-by-Step]
WHERE ([Date].[Calendar Year].[CY 2004])
;
/*
Total Sales Amount
Mountain-200 Black, 38 $1,327,957.41
Mountain-200 Black, 42 $1,139,429.49
Mountain-200 Silver, 38 $1,136,622.49
Mountain-200 Black, 46 $1,011,074.37
Mountain-200 Black, 38 (null)
Mountain-200 Silver, 42 $1,011,486.18
Touring-1000 Blue, 60 $835,290.16
Road-350-W Yellow, 48 $897,217.96
Mountain-200 Silver, 46 $1,029,170.76
Road-350-W Yellow, 40 $840,970.65
*/　　
　　从上面的这个例子我们领略了SQL与MDX语法的大致区别。
　　2、用MDX查询编辑器实现一个最简单的MDX的编写。
　　在SSMS界面登录Analysis Services，选择MDX Step By Step数据库：
　　对于MDX新手来说，最令人鼓舞的莫过于大部分MDX查询都可以借助拖拽来实现。
http://09yuxg.blu.livefilestore.com/y1p5P710_Co3468JtifYyFf34PvjYMxNcoWiuXSxet8Wdj_bicd0sl7ubPBkHTcpeQQpAigA2b01EL3QGZZe6EIuw8WMMPWlZZv/2011-6-12%2017-09-43.png?psid=1
http://09yuxg.blu.livefilestore.com/y1pvIgoxV_Wr22vznZX203S5RmbQbdv6AKjuPHLL_hhttj28Cl-4MQ-Pd91vSYdUEPVOpyE7DEDrbp71ApA7_rj8IP-ppIF5UBe/2011-6-12%2017-18-35.png?psid=1
http://09yuxg.blu.livefilestore.com/y1pvIgoxV_Wr23WbObsR7nByj1fao86ehOp27r0DymBxtuG00fj90PexbaR5D7E67_J9BqF4ODMHy0ikFoMY7497vK4pZktE-4L/2011-6-12%2017-21-24.png?psid=1
http://09yuxg.blu.livefilestore.com/y1pNxtD1IDqD_VUzmBQOkkN-iAE7xe_eG2eNxAKSRoqwCNCYudNRZYS0mrBtxJnkKb0Oc9WH4BmYOPuxgvpxcZoEPJxyjcNag21/2011-6-12%2017-24-49.png?psid=1
http://09yuxg.blu.livefilestore.com/y1pPNH_Q0K_klPYJ8PWu-i07OhWn-JMMoh1-UXEU_BpUWkX0-e3SwJCFu0YO7DR6qsRTk0SHCFTJKzEaF66mgQWflhQCrrN0S63/2011-6-12%2017-28-38.png?psid=1
http://09yuxg.blu.livefilestore.com/y1p_ItuBQbviBhcBcBuXETZeOt1c7kiMBoZ658osrmMXmT7h_EFK19DkEGQ2NelTZINBsGRftONqnpC3rr5dUQN12HlCrW8v5ZH/2011-6-12%2017-30-07.png?psid=1
　　大家可以参考以下的MDX脚本完成本文开始提供的MDX查询。


WITH
MEMBER [Measures].[Total Sales Amount]AS
[Measures].[Internet Sales Amount]
SELECT
FROM[Step-by-Step]
WHERE[Date].[Calendar Year].&[2004]
;
WITH
MEMBER [Measures].[Total Sales Amount]AS
[Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
SELECT
FROM[Step-by-Step]
WHERE[Date].[Calendar Year].&[2004]
;
WITH
MEMBER [Measures].[Total Sales Amount]AS
[Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
SET[Top 10 Products of 2003]AS
TOPCOUNT( [Product].[Product],10)
SELECT
FROM[Step-by-Step]
WHERE[Date].[Calendar Year].&[2004]
;
WITH
MEMBER [Measures].[Total Sales Amount]AS
[Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
SET[Top 10 Products of 2003]AS
TOPCOUNT( [Product].[Product].[Product].Members, 10,
([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY 2003]))
SELECT
FROM[Step-by-Step]
WHERE[Date].[Calendar Year].&[2004]
;
WITH
MEMBER [Measures].[Total Sales Amount]AS
[Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
SET[Top 10 Products of 2003]AS
TOPCOUNT( [Product].[Product].[Product].Members, 10,
([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY 2003]))
SELECT
    {([Measures].[Total Sales Amount])} ON COLUMNS,
    {[Top 10 Products of 2003]} ON ROWS
FROM[Step-by-Step]
WHERE[Date].[Calendar Year].&[2004]
;　　
　　参考资源：
　　1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx）
　　邀月注：鉴于读者提出意见，本系列尽量简明，方便读者阅读。