设为首页 收藏本站

运维网

 找回密码
 立即注册
查看: 487|回复: 0

[经验分享] 《Microsoft SQL Server 2008 MDX Step by Step》学习笔记四：了解表达式（Expression）

[复制链接]
cike0415

尚未签到
发表于 2015-6-30 15:57:59 | 显示全部楼层 |阅读模式
　　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函数。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pG-KAebjGWvZkgh_IYnYDyGXWxqHYveuv__ftKj-mP9pqzfqEFWK5KzfwQKelPTYEzrbsTpFmzUY/2011-8-15%2015-12-52.png?psid=1
　　调用方式为函数前加一个!，如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
*/
　　
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pFdxLm3Di-WuTTCMfn5UyJI3QCyfQXPEh12owY2QgsqmxlzRjOEwXzrPiD-X95lYFKPcbfqWniy8/2011-8-15%2016-38-12.png?psid=1
　　那么如何解决顺序问题呢？答案是使用“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的值可以从１到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



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）

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表，该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有，请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意，请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息，一经发现立即删除。若您因此触犯法律，一切后果自负，我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集，我们仅提供一个展示、介绍、观摩学习的平台，我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责，亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏，不得用于商业或者其他用途，否则，一切后果均由您自己承担，我们对此不承担任何法律责任
7、如涉及侵犯版权等问题，请您及时通知我们，我们将立即采取措施予以解决
8、联系人Email：admin@iyunv.com 网址：www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址：https://www.yunweiku.com/thread-82007-1-1.html 上篇帖子： 《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(8 下篇帖子： SQL Server 全文目录相关
回复

使用道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群，最新一手资源尽在官方微信交流群！快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:iyunvcom@gmail.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

豫ICP备20007574号-1 Copyright © 2012-2025

关于我们

发展历程

联系我们

关注我们

广告服务

广告案例

隐私条款

免责声明

用户登录

用户注册

版主守则

申请版主

手机版

金币获取

帮助中心

每日签到

Good Good

Study

Day Day

UP


客服E-mail:kefu@iyunv.com 客服QQ：1061981298


QQ群⑦：运维网交流群⑦ QQ群⑧：运维网交流群⑧ k8s群：运维网kubernetes交流群


提醒：禁止发布任何违反国家法律、法规的言论与图片等内容；本站内容均来自个人观点与网络等信息，非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来，其版权均归原作者及其网站所有，我们尊重他人的合法权益，如有内容侵犯您的合法权益，请及时与我们联系进行核实删除！



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表