- 浏览: 1050198 次
文章分类
最新评论
-
yw10260609:
这么麻烦啊,还有代码操作?我在网上搜了一篇关于这个的教程,上面 ...
数据库同步 -
fengdonngming:
联系方式:steven198717@sina.com
Flex写的一个工作流设计器原型 -
fengdonngming:
我最近也在做一个类似的表单设计器,但是没有思路。可以发一下源码 ...
Flex写的一个工作流设计器原型 -
wanwan209709:
您好,我最近也在研究工作流的东西,您能把源码发给我研究一下吗邮 ...
Flex写的一个工作流设计器原型 -
玲cc:
文章很有哲学的观念。就像苏格拉底曾经说过的,你既然不知道你要寻 ...
这是给开发者的弥天大谎还是至理名言?
《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)
相关推荐
Microsoft SQL Server 2008 MDX Step by Step Teach yourself the Multidimensional Expressions (MDX) query language—one step at a time. With this practical, learn-by-doing tutorial, you’ll build the ...
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+MDX+Step+by+Step 京华志&精华志出品 希望大家互相学习,互相进步 支持CSDN 支持微软
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, ...
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....
Microsoft.Press.Microsoft.SQL.Server.2008.MDX.Step.by.Step.Feb.2009.rar
Smart Business Intelligence Solutions with Microsoft SQL Server 2008 Discover how to: Manage the development life cycle and build a BI team Dig into SQL Server Analysis Services, Integration ...
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, ...
"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...
项目需要用MDX,所以翻译了Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX.pdf中第II部分第三章的内容。有翻译不当之处请联系QQ:185633513或发送EMAIL:yubinsure@gmail.com进行修正。
《SQL Server2012Analysis Services高级教程(第2版)》由Microsoft产品团队的主要成员(哈日那思、菲尔格林等)编写,介绍了如何应用这些新功能来创建复杂的BI解决方案。为了实现这一目的,你将在本书中了解到如何...
MDX step by step CD which including every chapter's sample
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....
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 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....
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 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....
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....
Written by members of the Analysis Services product team at Microsoft, this timely, authoritative book shows you how to use Analysis Services with SQL Server components to provide comprehensive, end-...