`
wangpengfei360
  • 浏览: 1050198 次
文章分类
社区版块
存档分类
最新评论

《Microsoft SQL Server 2008 MDX Step by Step》学习笔记四:了解表达式(Expression)

 
阅读更多

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

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].AllMembers
} 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
*/

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
*/

在上面的查询中,如果遇到计算成员的位置不正确,将会引发“无限递归(infinite 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]
;

/* 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% 86.17%
*/

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

4、生成复杂表达式

例5-10使用CurrentMember

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

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
)
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 Not applicable
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-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

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
IIF(
[Product].[Product Categories].CurrentMember.Properties("Level_Number",TYPED)=0,
"Not applicable",
([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 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
....
*/

小结:本文是MDX表达式的入门,介绍了几个常用的成员函数。

参考资源:

1、MDX官方教程(http://msdn.microsoft.com/zh-cn/library/ms145506.aspx


邀月注:本文版权由邀月和CSDN共同所有,转载请注明出处。
助人等于自助!
3w@live.cn



分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics