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
UNION ALL
SELECT
x.productkey, x.salesamount
FROM dbo.FactResellerSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey = y.DateKey
WHERE y.CalendarYear = 2003
) a
GROUP BY a.ProductKey
ORDER BY 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
UNION ALL
SELECT
x.productkey, x.salesamount
FROM dbo.FactResellerSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey = y.DateKey
WHERE y.CalendarYear = 2004
) a
GROUP BY a.ProductKey
) o
ON m.ProductKey = o.productkey
ORDER BY 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
*/
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
UNION ALL
SELECT
x.productkey, x.salesamount
FROM dbo.FactResellerSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey = y.DateKey
WHERE y.CalendarYear = 2003
) a
GROUP BY a.ProductKey
ORDER BY 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
UNION ALL
SELECT
x.productkey, x.salesamount
FROM dbo.FactResellerSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey = y.DateKey
WHERE y.CalendarYear = 2004
) a
GROUP BY a.ProductKey
) o
ON m.ProductKey = o.productkey
ORDER BY 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
*/
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查询都可以借助拖拽来实现。
大家可以参考以下的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 ]
;
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)
邀月注:鉴于读者提出意见,本系列尽量简明,方便读者阅读。