- 浏览: 728671 次
- 性别:
- 来自: 上海
-
最新评论
-
TheUniqueGirl：
Tomcat系统架构与设计模式：http://www.doci ...
Tomcat -
aykjy：
...
UML常见工具之NetBeans(downmoon) -
不老肖邦：
谢谢提供的简单示例
JAVA toString（）
《Microsoft SQL Server 2008 MDX Step by Step》学习笔记三：了解结果集（Sets）
SQL Server 2008中SQL应用系列及BI笔记系列--目录索引
导读:本文介绍结果集（Sets）的基础内容，已经了解的读者可以直接略过。
本文将包括以下内容：
■1、使用基本技巧组装Sets
■2、使用Sets从一个查询中提取多个Cell
■3、了解Select语句的基本组件
本文所用数据库和所有源码，请到微软官网下载
1、Sets的基本技巧
在Analysis Service中，Set代表元组（Tuples）的集合。在一个Set内部，独立的元组被用逗号隔开，如下：
{
([Product].[Category].[Accessories]),
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components])
}
下面我们组装一个基本的Sets
打开MDX查询编辑器，如下：
例4-1
我们增加一个元组([Product].[Subcategory].[Mountain Bikes])，如下：
例4-2
我们使用相同维数的用户层次结构[Product Categories]，修改如下:
例4-3
SELECT
{
([Date].[Calendar].[CY 2002],
[Geography].[Country].[United States]),
([Date].[Calendar].[CY 2003],
[Geography].[Country].[United States]),
([Date].[Calendar].[CY 2004],
[Geography].[Country].[United States])
} ON COLUMNS,
{
([Product].[Product Categories
].[Accessories]),
([Product].[Product Categories
].[Bikes]),
([Product].[Product Categories
].[Clothing]),
([Product].[Product Categories
].[Components]),
([Product].[Product Categories
].[Mountain Bikes])
} ON
ROWS
FROM [Step-by-Step]
;
/* CY 2002
CY 2003 CY 2004
United States United
States United States
Accessories $61,263.90
$151,136.35 $76,027.18
Bikes $14,716,804.14
$16,139,984.68 $7,951,335.55
Clothing $317,939.41
$495,443.62 $197,590.92
Components $2,526,542.06
$3,284,551.84 $1,137,105.72
Mountain Bikes $6,970,418.73
$5,832,626.02 $2,539,198.92
*/
下面这个查询有类似错误：
例4-4
正确应为：
例4-5
我们可以这样改变顺序，并增加一个行：
例4-6
SELECT
{
([Geography].[Country].[United States],
[Date].[Calendar].[CY 2004]),
([Geography].[Country].[United
States], [Date].[Calendar].[CY 2003]),
([Geography].[Country].[United States], [Date].[Calendar].[CY 2002])
} ON COLUMNS,
{
([Product].[Product
Categories].[Accessories]),
([Product].[Product
Categories].[Accessories]),
([Product].[Product Categories].[Bikes]),
([Product].[Product Categories].[Clothing]),
([Product].[Product Categories].[Components]),
([Product].[Product
Categories].[Mountain Bikes])
} ON ROWS
FROM [Step-by-Step]
;
查询结果如下：
/* United States United States United States
CY 2004 CY 2003 CY 2002
Accessories $76,027.18 $151,136.35 $61,263.90
Accessories $76,027.18 $151,136.35 $61,263.90
Bikes $7,951,335.55 $16,139,984.68 $14,716,804.14
Clothing $197,590.92 $495,443.62 $317,939.41
Components $1,137,105.72 $3,284,551.84 $2,526,542.06
Mountain Bikes $2,539,198.92 $5,832,626.02 $6,970,418.73
*/
多出的重复行怎么办？我们可以使用distinct函数（http://msdn.microsoft.com/zh-cn/library/ms146033.aspx ）
例4-6
SELECT
{
([Geography].[Country].[United States],
[Date].[Calendar].[CY 2004]),
([Geography].[Country].[United States],
[Date].[Calendar].[CY 2003]),
([Geography].[Country].[United States],
[Date].[Calendar].[CY 2002])
} ON COLUMNS,
DISTINCT
(
{
([Product].[Product Categories].[Accessories]),
([Product].[Product Categories].[Accessories]),
([Product].[Product
Categories].[Bikes]),
([Product].[Product Categories].[Clothing]),
([Product].[Product Categories].[Components]),
([Product].[Product Categories].[Mountain Bikes])
}
) ON ROWS
FROM [Step-by-Step]
;
/* United States United
States United States
CY 2004 CY
2003 CY 2002
Accessories $76,027.18
$151,136.35 $61,263.90
Bikes $7,951,335.55
$16,139,984.68 $14,716,804.14
Clothing $197,590.92
$495,443.62 $317,939.41
Components $1,137,105.72
$3,284,551.84 $2,526,542.06
Mountain Bikes $2,539,198.92
$5,832,626.02 $6,970,418.73
*/
2、理解Select语句
select语句（http://msdn.microsoft.com/zh-cn/library/ms146002.aspx ）的结构如下图：
3、使用函数生成Sets
下面了解几个非常useful的函数。
（一）Member函数（http://msdn.microsoft.com/zh-cn/library/ms144851(v=SQL.105).aspx ），返回某个维度、级别或层次结构中的成员集。
我们看两个查询：
例4-7
SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON
COLUMNS,
{
([Product].[Category].[Accessories]),
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components])
} ON ROWS
FROM
[Step-by-Step]
WHERE [Geography].[Country].[United States]
;
例4-8
SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON
COLUMNS,
{[Product].[Category].[Category].Members}
ON
ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United
States]
;
这两个MDX查询的结果是一样的，都是：
/* CY 2002 CY 2003 CY 2004
Accessories $61,263.90 $151,136.35
$76,027.18
Bikes $14,716,804.14 $16,139,984.68 $7,951,335.55
Clothing
$317,939.41 $495,443.62 $197,590.92
Components $2,526,542.06 $3,284,551.84
$1,137,105.72
*/
请大家结合例子体会Member函数的用途。
我们略微修改一下例4-8 ，如下：
例4-9
SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON
COLUMNS,
{[Product].[Category].Members}
ON
ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United
States]
;
/* CY 2002 CY 2003 CY 2004
All
Products $17,622,549.51 $20,071,116.48
$9,362,059.37
Accessories $61,263.90 $151,136.35
$76,027.18
Bikes $14,716,804.14 $16,139,984.68
$7,951,335.55
Clothing $317,939.41 $495,443.62
$197,590.92
Components $2,526,542.06 $3,284,551.84
$1,137,105.72
*/
我们再修改，
例4-10
SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON
COLUMNS,
{[Product].[Product Categories
].Members}
ON ROWS
FROM
[Step-by-Step]
WHERE [Geography].[Country].[United States]
;
/* NOTE: First 10 Row Members Presented
CY
2002 CY 2003 CY 2004
All Products $17,622,549.51
$20,071,116.48 $9,362,059.37
Accessories $61,263.90
$151,136.35 $76,027.18
Bike Racks (null)
$57,382.62 $36,937.90
Hitch Rack - 4-Bike (null)
$57,382.62 $36,937.90
Bike Stands (null)
(null) (null)
All-Purpose Bike Stand (null)
(null) (null)
Bottles and Cages (null)
$2,186.60 $1,554.90
Mountain Bottle Cage (null)
(null) (null)
Road Bottle Cage (null)
(null) (null)
Water Bottle - 30 oz. (null)
$2,186.60 $1,554.90
*/
（二）MeasureGroupMeasures函数（http://msdn.microsoft.com/zh-cn/library/ms145485(v=SQL.105).aspx ），它返回属于指定度量值组的一组度量值。
例4-11
SELECT
{MeasureGroupMeasures("Reseller Sales")} ON
COLUMNS,
{[Product].[Category].Members} ON ROWS
FROM
[Step-by-Step]
;
/* Reseller Sales Amount Reseller Order
Quantity Reseller Extended Amount Reseller Tax Amount Reseller Freight Cost
Discount Amount Reseller Total Product Cost Reseller Standard Product
Cost
All Products $80,450,596.98 214,378
$80,978,104.87 $6,436,047.61 $2,011,265.92
$527,507.93 $79,980,114.38
$26,693,830.57
Accessories $571,297.93
25,839 $577,985.95 $45,703.83
$14,282.52 $6,688.03 $375,505.33
$71,122.89
Bikes $66,302,381.56
75,015 $66,797,022.19 $5,304,190.40
$1,657,560.05 $494,640.65 $67,293,081.45
$22,039,062.39
Clothing $1,777,840.84
64,497 $1,798,805.33 $142,227.25
$44,446.19 $20,964.51 $1,545,417.42
$294,511.26
Components $11,799,076.66
49,027 $11,804,291.40 $943,926.12
$294,977.15 $5,214.74 $10,766,110.18
$4,289,134.03
*/
（三）Crossjoin函数（http://msdn.microsoft.com/zh-cn/library/ms144816.aspx ）,返回一个或多个集的叉积。
例4-12
例4-13
SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON
COLUMNS,
Crossjoin
(
{[Product].[Category].[Category].Members},
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Order
Quantity])
}
) ON ROWS
FROM
[Step-by-Step]
WHERE ([Geography].[Country].[United
States])
;
/*
CY 2002 CY 2003 CY 2004
Accessories
Reseller Sales Amount $61,263.90 $151,136.35
$76,027.18
Accessories Reseller Order Quantity 3,426 6,848
3,125
Bikes Reseller Sales Amount $14,716,804.14 $16,139,984.68
$7,951,335.55
Bikes Reseller Order Quantity 16,079 19,728
9,121
Clothing Reseller Sales Amount $317,939.41 $495,443.62
$197,590.92
Clothing Reseller Order Quantity 11,284 18,028
7,250
Components Reseller Sales Amount $2,526,542.06
$3,284,551.84 $1,137,105.72
Components Reseller Order Quantity
9,602 14,503 5,323
*/
注意这里有四个产品分类和两个度量，因而共有4*2=8个元组(tuples)。
当然，下面是一种用法，
例4-15
SELECT
CROSSJOIN
(
{
([Date].[Calendar].[CY
2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
},
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales
Amount])
}
)
ON COLUMNS,
{[Product].[Category].[Category].Members}
ON ROWS
FROM
[Step-by-Step]
WHERE [Geography].[Country].[United States]
;
其结果为：
/* CY 2002 CY 2002 CY
2003 CY 2003 CY 2004 CY
2004
Reseller Sales Amount Internet Sales Amount Reseller
Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales
Amount
Accessories $61,263.90 (null)
$151,136.35 $293,709.71 $76,027.18
$407,050.25
Bikes $14,716,804.14 $6,530,343.53
$16,139,984.68 $9,359,102.62 $7,951,335.55
$9,162,324.85
Clothing $317,939.41
(null) $495,443.62 $138,247.97
$197,590.92 $201,524.64
Components
$2,526,542.06 (null) $3,284,551.84
(null) $1,137,105.72 (null)
*/
4、限定Sets
（一）Auto-exists
例4-16
注意提示：Cell set consists of 6 rows and 7 columns.实际返回的只有5行
例4-17
SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
}*
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
}
ON COLUMNS,
{[Product].[Category].[Category].Members}
*
{[Product].[Subcategory].[Subcategory].Members}
ON ROWS
FROM
[Step-by-Step]
WHERE [Geography].[Country].[United
States]
;
/*
CY
2002 CY 2002 CY 2003 CY
2003 CY 2004 CY
2004
Reseller Sales Amount Internet Sales
Amount Reseller Sales Amount Internet Sales Amount Reseller Sales Amount
Internet Sales Amount
Accessories Bike Racks
(null) (null) $57,382.62
$16,440.00 $36,937.90 $22,920.00
Accessories Bike
Stands (null) (null)
(null) $18,921.00 (null)
$20,670.00
Accessories Bottles and Cages
(null) (null) $2,186.60
$23,280.27 $1,554.90 $33,517.92
Accessories
Cleaners (null) (null)
$3,379.09 $3,044.85 $2,232.34
$4,173.75
Accessories Fenders
(null) (null) (null)
$19,408.34 (null) $27,211.24
Accessories
Helmets $49,586.49 (null)
$61,131.18 $92,583.54 $24,542.87
$132,752.06
Accessories Hydration Packs
(null) (null) $19,561.85
$16,771.95 $10,639.64 $23,535.72
Accessories
Lights (null) (null)
(null) (null) (null)
(null)
Accessories Locks $6,339.46
(null) $3,890.52 (null)
(null) (null)
Accessories Panniers
(null) (null) (null)
(null) (null) (null)
Accessories
Pumps $5,337.95 (null)
$3,262.37 (null) (null)
(null)
Accessories Tires and Tubes (null)
(null) $342.13 $103,259.76
$119.54 $142,269.56
Bikes Mountain Bikes
$6,970,418.73 $1,562,456.76 $5,832,626.02
$3,989,638.48 $2,539,198.92 $3,814,691.06
Bikes Road
Bikes $7,746,385.41 $4,967,886.77
$7,838,944.52 $3,952,029.21 $3,042,999.81
$2,920,267.67
Bikes Touring Bikes
(null) (null) $2,468,414.14
$1,417,434.93 $2,369,136.82 $2,427,366.12
Clothing
Bib-Shorts $64,077.06 (null)
$42,248.34 (null) (null)
(null)
Clothing Caps $6,338.69
(null) $7,200.98 $7,956.15
$2,779.52 $11,731.95
Clothing Gloves
$59,284.03 (null) $61,502.08
$14,228.69 $7,570.49 $20,792.01
Clothing
Jerseys $74,165.33 (null)
$150,755.38 $70,370.46 $71,246.39
$102,580.22
Clothing Shorts
$31,632.12 (null) $108,658.99
$30,445.65 $67,982.00 $40,874.16
Clothing
Socks $2,642.10 (null)
$7,935.99 $2,229.52 $5,513.87
$2,876.80
Clothing Tights
$79,800.08 (null) $52,561.35
(null) (null) (null)
Clothing
Vests (null) (null)
$64,580.51 $13,017.50 $42,498.65
$22,669.50
Components Bottom Brackets
(null) (null) $16,433.12
(null) $11,589.91 (null)
Components
Brakes (null) (null)
$22,966.98 (null) $11,054.70
(null)
Components Chains (null)
(null) $3,065.40 (null)
$1,906.61 (null)
Components
Cranksets (null) (null)
$62,408.73 (null) $42,709.90
(null)
Components Derailleurs (null)
(null) $21,731.33 (null)
$12,608.56 (null)
Components Forks
$32,047.57 (null) $19,191.24
(null) (null) (null)
Components
Handlebars $38,615.98 (null)
$55,997.42 (null) $17,002.14
(null)
Components Headsets $23,991.39
(null) $16,164.84 (null)
(null) (null)
Components Mountain Frames
$1,019,984.65 (null) $1,311,493.01
(null) $501,825.49 (null)
Components
Pedals (null) (null)
$57,437.40 (null) $34,089.08
(null)
Components Road Frames $1,100,779.19
(null) $1,020,543.04 (null)
$209,069.30 (null)
Components
Saddles (null) (null)
$20,562.60 (null) $9,715.37
(null)
Components Touring Frames (null)
(null) $492,424.75 (null)
$285,534.67 (null)
Components Wheels
$311,123.28 (null) $164,131.99
(null) (null) (null)
*/
注意这个结果是从37*4=148个行中，只返回了39行。
我们再修改下查询：
例4-18
SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
}
*
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
}
ON COLUMNS,
{[Product].[Category].[Category].Members}
*
{[Product].[Color].[Color].Members}
ON ROWS
FROM
[Step-by-Step]
WHERE ([Geography].[Country].[United
States],
[Product].[Subcategory].[Mountain
Bikes])
;
/* CY 2002 CY
2002 CY 2003 CY 2003 CY
2004 CY 2004
Reseller Sales Amount Internet
Sales Amount Reseller Sales Amount Internet Sales Amount Reseller Sales
Amount Internet Sales Amount
Bikes Black $3,927,415.56
$842,059.40 $3,265,300.39 $1,962,003.53
$1,265,049.27 $1,775,241.11
Bikes Silver
$3,043,003.18 $720,397.36 $2,567,325.63
$2,027,634.94 $1,274,149.65 $2,039,449.95
*/
（二）Exists函数
例4-19
SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
}
*
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
}
ON COLUMNS,
EXISTS
(
{[Product].[Category].[Category].Members}
* {[Product].[Color].[Color].Members},
{([Product].[Subcategory].[Mountain Bikes])}
)
ON ROWS
FROM
[Step-by-Step]
WHERE [Geography].[Country].[United
States]
;
/* CY 2002 CY
2002 CY 2003 CY 2003 CY
2004 CY 2004
Reseller Sales Amount Internet
Sales Amount Reseller Sales Amount Internet Sales Amount Reseller Sales
Amount Internet Sales Amount
Bikes Black $6,314,977.53
$1,728,251.55 $6,358,849.63 $3,775,240.23
$2,446,852.45 $2,809,810.02
Bikes Silver
$3,043,003.18 $720,397.36 $2,567,325.63
$2,027,634.94 $1,274,149.65
$2,039,449.95
*/
请注意上两个查询的不同。
（三）NON EMPTY关键字和Exists函数的其他形式
例4-20
例4-21
SELECT
{[Measures].[Reseller Sales Amount]} ON COLUMNS,
NON EMPTY
{[Date].[Month].[May 2002]} * {[Employee].[Employee].Members} ON ROWS
FROM
[Step-by-Step]
;
/* Reseller Sales
Amount
May 2002 All Employees $2,269,116.71
May 2002
David R. Campbell $115,901.80
May 2002 Garrett R.
Vargas $172,622.35
May 2002 Jillian
Carson $464,167.31
May 2002 Jos?Edvaldo.
Saraiva $212,020.26
May 2002 Linda C. Mitchell
$401,687.56
May 2002 Michael G. Blythe $172,429.58
May
2002 Pamela O. Ansman-Wolfe $252,116.05
May 2002 Shu K.
Ito $103,508.43
May 2002 Tsvi Michael.
Reiter
$374,663.36
*/
关于Sets的学习到此为止，下文我们将要了解的概念是表达式。相信这节和下节对于熟悉SQL的同学而言是轻松愉快的，只不过查询结果变成了多维的，而不是我们通常看到的二维(行列）格式，不是吗？
参考资源：
1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx ）
发表评论
相关推荐
### Microsoft SQL Server 2008 MDX Step by Step (Feb 2009) #### 知识点一：Multidimensional Expressions (MDX) 基础 **Multidimensional Expressions (MDX)** 是一种用于查询多维数据集的语言。MDX 提供了一种...
《Microsoft SQL Server 2008 Analysis Services Step by Step》随书光盘包含了大量实践性的文件，旨在帮助读者深入理解并掌握SQL Server 2008的Analysis Services（简称SSAS）这一强大的数据仓库和商务智能工具。...
《Microsoft SQL Server 2008 MDX Step by Step》是一本专为SQL Server数据库管理员、数据分析师和开发人员设计的教程，旨在深入探讨多维表达式（Multidimensional Expressions，简称MDX）在SQL Server 2008中的应用...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
《SQL Server 2008 Step by Step》是微软出版社发布的一本针对SQL Server 2008的全面教程，适合初学者和有一定基础的数据库管理员。这本书通过逐步指导的方式，帮助读者掌握SQL Server 2008的核心功能和操作技巧。 ...
《Microsoft SQL Server 2008 MDX 步步为营》是微软出版社于2009年2月推出的一本专门针对SQL Server 2008中的多维表达式（Multidimensional Expressions，简称MDX）技术的实战指南。这本书详细介绍了如何在SQL ...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
电子书《Microsoft SQL Server 2008 Analysis Services Step by Step》很可能是逐步教程，涵盖了从安装配置到实际操作的整个过程。书中可能涉及以下内容： 1. **安装与配置**：如何在服务器上安装SQL Server 2008，...
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
7. **工具支持**：了解如何在Microsoft Excel和SQL Server Management Studio（SSMS）中使用MDX，以及如何配合Power Pivot等工具进行更高级的数据分析。 提供的资源，如PPT、视频和文档，将为学习者提供丰富的交互...
《精通微软SQL Server 2008管理》 在IT领域，数据库管理是不可或缺的一环，而微软的SQL Server 2008作为一款广泛使用的数据库管理系统，深受企业和开发者的青睐。本文将深入探讨SQL Server 2008的核心管理知识，...
它起源于微软在1998年的SQL Server Analysis Services 7.0版本，现在已经成为一个标准化的语言，被其他OLAP（在线分析处理）供应商如Microstrategy Intelligence Server、Hyperion Essbase Server和SAS的Enterprise ...
通过"MDX step by step"教程，读者将逐步学习如何构建这些查询，理解如何在多维环境中提取、分析和展示数据。书中每章的示例会帮助读者加深对MDX语法的理解，通过实际操作来探索不同场景下的应用。例如，可能有练习...
SQL Server 2008是微软公司推出的一款关系型数据库管理系统，它在企业级数据管理、分析和报告方面表现出色。这款教程适用于初学者，旨在帮助用户掌握SQL Server 2008的基础知识和核心功能。 一、SQL Server 2008...
《Microsoft SQL Server 2012 Analysis Services 高级教程》是针对数据库管理员、数据分析师以及IT专业人士的一本深入指南，旨在帮助读者掌握SQL Server 2012中的Analysis Services（简称SSAS）的各项高级功能。...
"Practical DMX Queries for Microsoft SQL Server Analysis Services 2008" contains more than 250 downloadable DMX queries you can use to extract and visualize data. The application, syntax, and results...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...