《Microsoft SQL Server 2008 MDX Step by Step》学习笔记四：了解表达式（Expression）

netxdiy

浏览: 728441 次
性别:
来自: 上海

最近访客更多访客>>

u012363178

smzyyyjyrctl

88250

abiandbel

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

SQL Server 2008中SQL应用系列及BI笔记系列--目录索引

导读:本文介绍表达式（Expression）的基础内容，已经了解的读者可以直接略过。

本文将包括以下内容：

■1、使用tuples, members, sets和常量组建表达式

■2、使用表达式解释上下文概念（expression context）

■3、调用context生成动态表达式

本文所用数据库和所有源码，请到微软官网下载

1、表达式基础

MDX查询中的基本运算符，看MSDN（http://msdn.microsoft.com/zh-cn/library/ms144766.aspx）,大多数与普通的SQL 运算符类似。

Analysis Service支持大量内置的VBA函数。

调用方式为函数前加一个!，如VBAMDX!Left( "ABC", 1)，将查询ABC的第一个字母。

另外一个常用的函数是IsEmpty

2、计算成员

下面我们演练最基本的计算成员

打开MDX查询编辑器，如下：

例5-1

SELECT
{
([Date].[Calendar Year].[CY 2003]),
([Date].[Calendar Year].[CY 2004])
} ON COLUMNS,
{
([Product].[Category].[Accessories]),
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components])
} ON ROWS
FROM [Step-by-Step]
;

/* CY 2003 CY 2004
Accessories $296,532.88 $161,794.33
Bikes $25,551,775.07 $13,399,243.18
Clothing $871,864.19 $386,013.16
Components $5,482,497.29 $2,091,011.92
*/

这个查询很像上一节的普通查询。我们增加一个计算成员，如下：

例5-2

WITH
MEMBER [Product].[Category].[All Products].[X] AS
1+1
SELECT
{
([Date].[Calendar Year].[CY 2003]),
([Date].[Calendar Year].[CY 2004])
} ON COLUMNS,
{
([Product].[Category].[Accessories]) ,
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components]),
([Product].[Category].[X])

} ON ROWS
FROM [Step-by-Step]

/* CY 2003 CY 2004
Accessories $296,532.88 $161,794.33
Bikes $25,551,775.07 $13,399,243.18
Clothing $871,864.19 $386,013.16
Components $5,482,497.29 $2,091,011.92
X 2 2
*/

注意X列是被计算，而不是被存储的。

这里我们顺便看一下AllMembers函数的用法。还记得上节我们提到的Members用法么？

例5-3

WITH
MEMBER [Product].[Category].[All Products].[X] as
1+1
SELECT
{
([Date].[Calendar Year].[CY 2003]),
([Date].[Calendar Year].[CY 2004])
} ON COLUMNS,
{
[Product].[Category].[Category].Members
} ON ROWS
FROM [Step-by-Step]
;

/* CY 2003 CY 2004
Accessories $296,532.88 $161,794.33
Bikes $25,551,775.07 $13,399,243.18
Clothing $871,864.19 $386,013.16
Components $5,482,497.29 $2,091,011.92
*/

注意我们没有用行名，而用了Members，有点像SQL中的"*”。比较AllMembers语法：

例5-4-1

/* CY 2003 CY 2004
Accessories $296,532.88 $161,794.33
Bikes $25,551,775.07 $13,399,243.18
Clothing $871,864.19 $386,013.16
Components $5,482,497.29 $2,091,011.92
X 2 2
*/

3、生成动态表达式

在例5-2的基础上略作改进，

例5-4-2

WITH
MEMBER [Product].[Category].[All Products].[X] as
([Product].[Category].[Bikes])+1
SELECT
{
([Date].[Calendar Year].[CY 2003]),
([Date].[Calendar Year].[CY 2004])
} ON COLUMNS,
{
([Product].[Category].[Accessories]) ,
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components]),
([Product].[Category].[X])
} ON ROWS
FROM [Step-by-Step]
;

/* CY 2003 CY 2004
Accessories $296,532.88 $161,794.33
Bikes $25,551,775.07 $13,399,243.18
Clothing $871,864.19 $386,013.16
Components $5,482,497.29 $2,091,011.92
X $25,551,776.07 $13,399,244.18
*/

当然，也可以继续改进：

例5-5

WITH
MEMBER [Product].[Category].[All Products].[Bikes & Accessories] as
([Product].[Category].[Bikes]) + ([Product].[Category].[Accessories])
SELECT
{
([Date].[Calendar Year].[CY 2003]),
([Date].[Calendar Year].[CY 2004])
} ON COLUMNS,
{
([Product].[Category].[Accessories]) ,
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components]),
([Product].[Category].[Bikes & Accessories])
} ON ROWS
FROM [Step-by-Step]
;

/* CY 2003 CY 2004
Accessories $296,532.88 $161,794.33
Bikes $25,551,775.07 $13,399,243.18
Clothing $871,864.19 $386,013.16
Components $5,482,497.29 $2,091,011.92
Bikes & Accessories $25,848,307.95 $13,561,037.52
*/

在上面的查询中，如果遇到计算成员的位置不正确，将会引发“无限递归（inﬁnite recursion）”错误。

例5-6

WITH
MEMBER [Product].[Category].[All Products].[Bikes & Accessories] as
([Geography].[Country].[United States]) + ([Geography].[Country].[Canada])
SELECT
{
([Date].[Calendar Year].[CY 2003]),
([Date].[Calendar Year].[CY 2004])
} ON COLUMNS,
{
([Product].[Category].[Accessories]) ,
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components]),
([Product].[Category].[Bikes & Accessories])
} ON ROWS
FROM [Step-by-Step]
;

/* CY 2003 CY 2004
Accessories $296,532.88 $161,794.33
Bikes $25,551,775.07 $13,399,243.18
Clothing $871,864.19 $386,013.16
Components $5,482,497.29 $2,091,011.92
Bikes & Accessories #Error #Error
*/

那么如何解决顺序问题呢？答案是使用“SOLVE_ORDER”属性。

例5-7

WITH
MEMBER [Product].[Category].[All Products].[Percent Bikes] as
([Product].[Category].[Bikes])/([Product].[Category].[All Products])
,FORMAT_STRING="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
{[Product].[Category].AllMembers} ON ROWS
FROM [Step-by-Step]
;
/* Reseller Sales Amount Internet Sales Amount
All Products $80,450,596.98 $29,358,677.22
Accessories $571,297.93 $700,759.96
Bikes $66,302,381.56 $28,318,144.65
Clothing $1,777,840.84 $339,772.61
Components $11,799,076.66 (null)
Percent Bikes 82.41% 96.46%
*/

例5-8

WITH
MEMBER [Measures].[Combined Sales Amount] as
([Measures].[Reseller Sales Amount])+([Measures].[Internet Sales Amount])
MEMBER [Product].[Category].[All Products].[Percent Bikes] as
([Product].[Category].[Bikes])/([Product].[Category].[All Products])
,FORMAT_STRING="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount]),

([Measures].[Combined Sales Amount])

} ON COLUMNS,
{[Product].[Category].AllMembers} ON ROWS
FROM [Step-by-Step]
;

/* Reseller Sales Amount Internet Sales Amount Combined Sales Amount
All Products $80,450,596.98 $29,358,677.22 $109,809,274.20
Accessories $571,297.93 $700,759.96 $1,272,057.89
Bikes $66,302,381.56 $28,318,144.65 $94,620,526.21
Clothing $1,777,840.84 $339,772.61 $2,117,613.45
Components $11,799,076.66 (null) $11,799,076.66
Percent Bikes 82.41% 96.46% 178.87%
*/

怎么回事？178.87%????,我们没有指定SOLVE_ORDER（http://msdn.microsoft.com/zh-cn/library/ms145539(v=SQL.105)）

例5-9

WITH
MEMBER [Measures].[Combined Sales Amount] as
([Measures].[Reseller Sales Amount])+([Measures].[Internet Sales Amount])
,SOLVE_ORDER=1
MEMBER [Product].[Category].[All Products].[Percent Bikes] as
([Product].[Category].[Bikes])/([Product].[Category].[All Products])
,FORMAT_STRING="Percent"
,SOLVE_ORDER=2
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount]),
([Measures].[Combined Sales Amount])
} ON COLUMNS,
{[Product].[Category].AllMembers} ON ROWS
FROM [Step-by-Step]
;

其中，SOLVE_ORDER的值可以从１到65535。关于FORMAT_STRING的用法，参看MSDN（http://msdn.microsoft.com/zh-cn/library/ms146084.aspx）

4、生成复杂表达式

例5-10使用CurrentMember（http://msdn.microsoft.com/zh-cn/library/ms144948%28v=sql.105%29.aspx）

WITH
MEMBER [Measures].[Parent Member Name] as
[Product].[Product Categories].CurrentMember.Parent.Name
SELECT
{
([Measures].[Parent Member Name])
} ON COLUMNS,
{[Product].[Product Categories].AllMembers} ON ROWS
FROM [Step-by-Step]
;
/* NOTE: First 10 members only displayed
Parent Member Name
All Products (null)
Accessories All Products
Bike Racks Accessories
Hitch Rack - 4-Bike Bike Racks
Bike Stands Accessories
All-Purpose Bike Stand Bike Stands
Bottles and Cages Accessories
Mountain Bottle Cage Bottles and Cages
Road Bottle Cage Bottles and Cages
Water Bottle - 30 oz. Bottles and Cages
...
*/

例5-11

例5-12

WITH
MEMBER [Measures].[Parent Member Name] as
IIF(
[Product].[Product Categories].CurrentMember.Properties("Level_Number",TYPED)=0,
"Not applicable",
[Product].[Product Categories].CurrentMember.Parent.Name
)
MEMBER [Measures].[Percent of Parent] as
([Measures].[Reseller Sales Amount])/
([Product].[Product Categories].CurrentMember.Parent, [Measures].[Reseller Sales Amount])
,FORMAT_STRING="Percent"
SELECT
{
([Measures].[Parent Member Name]),
([Measures].[Reseller Sales Amount]),
([Measures].[Percent of Parent])
} ON COLUMNS,
{[Product].[Product Categories].AllMembers} ON ROWS
FROM [Step-by-Step]
;/* NOTE: First 10 members only displayed
Parent Member Name Reseller Sales Amount Percent of Parent
All Products Not applicable $80,450,596.98 1.#INF
Accessories All Products $571,297.93 0.71%
Bike Racks Accessories $197,736.16 34.61%
Hitch Rack - 4-Bike Bike Racks $197,736.16 100.00%
Bike Stands Accessories (null) (null)
All-Purpose Bike Stand Bike Stands (null) (null)
Bottles and Cages Accessories $7,476.60 1.31%
Mountain Bottle Cage Bottles and Cages (null) (null)
Road Bottle Cage Bottles and Cages (null) (null)
Water Bottle - 30 oz. Bottles and Cages $7,476.60 100.00%
....
*/

改进后，

例5-13

;/* NOTE: First 10 members only displayed
Parent Member Name Reseller Sales Amount Percent of Parent
All Products Not applicable $80,450,596.98 Not applicable
Accessories All Products $571,297.93 0.71%
Bike Racks Accessories $197,736.16 34.61%
Hitch Rack - 4-Bike Bike Racks $197,736.16 100.00%
Bike Stands Accessories (null) (null)
All-Purpose Bike Stand Bike Stands (null) (null)
Bottles and Cages Accessories $7,476.60 1.31%
Mountain Bottle Cage Bottles and Cages (null) (null)
Road Bottle Cage Bottles and Cages (null) (null)
Water Bottle - 30 oz. Bottles and Cages $7,476.60 100.00%
....
*/

另外有三个常用的成员函数

DefaultMember （http://msdn.microsoft.com/zh-cn/library/ms146050.aspx）

UnknownMember（http://msdn.microsoft.com/zh-cn/library/ms144853.aspx）

DataMember（http://msdn.microsoft.com/zh-cn/library/ms145608.aspx）

例5-14

SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Number of Products])
} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
;/* NOTE: First 10 members only displayed
Reseller Sales Amount Number of Products
All Products $80,450,596.98 397
Accessories $571,297.93 397
Bike Racks $197,736.16 397
Hitch Rack - 4-Bike $197,736.16 397
Bike Stands (null) 397
All-Purpose Bike Stand (null) 397
Bottles and Cages $7,476.60 397
Mountain Bottle Cage (null) 397
Road Bottle Cage (null) 397
Water Bottle - 30 oz. $7,476.60 397
....
*/

例5-15

WITH
MEMBER [Measures].[Number of Products] as
Count(
EXISTING [Product].[Product Categories].[Product].Members
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Number of Products])
} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
;/* NOTE: First 10 members only displayed
Reseller Sales Amount Number of Products
All Products $80,450,596.98 397
Accessories $571,297.93 35
Bike Racks $197,736.16 1
Hitch Rack - 4-Bike $197,736.16 1
Bike Stands (null) 1
All-Purpose Bike Stand (null) 1
Bottles and Cages $7,476.60 3
Mountain Bottle Cage (null) 1
Road Bottle Cage (null) 1
Water Bottle - 30 oz. $7,476.60 1
....
*/