- 浏览: 1056530 次
文章分类
最新评论
-
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)
相关推荐
5G通信行业、网络优化、通信工程建设资料。
5G通信行业、网络优化、通信工程建设资料
5G通信、网络优化与通信建设
299-教育行业信息化与数据平台建设分享.pptx
手写数字和字母数据集binaryalphadigs.mat
5G通信行业、网络优化、通信工程建设资料
PEMFC电堆输出电压模型,可计算效率、输出功率、电流、消耗功率以及等效内阻
1、 设计思路 1、 创建型设计模式 创建型设计模式主要“关注对象的创建”。 1. 单例模式 单例模式:能不用就不用 ,他的目的就是为了让一个类只创建一个实例。 用法:把对象的创建权限关闭,提供一个人公开的静态方法,实现静态方法后将实例存放于静态的字段中,方法中返回。 单例模式会长期持有一个对象不会被释放,而普通实例不用就会被释放(当然必须是GC之后才会被释放)。 单例用途;数据临时存储的地方如静态字典,数据库连接池、线程池、IOC容器实例。 1.1懒汉式 设置构造函数为私有的,避免其他外部类可以对其实例化, 创建静态类来存储实例。 在静态方法中创建实例,避免多个线程同时调用方法,我们可以加线程锁, 在方法中使用双判断语句:最外层判断是为了提高运行速率,检查如果静态字段中已经存在实例了就可以直接return;第二层判断是避免创建多个对象实例。 1.2饿汉式1 静态构造函数:由CLR保证,静态构造函数只会在启动程序时候,由CLR自行创建。并且只会创建一次,相比较于懒汉式创建的更早,并且不需要担心会
5G通信行业、网络优化、通信工程建设资料
论文目录: 第二章 需求分析与系统总体设计 - 5 - 2.1java的特点 - 5 - 2.2技术可行性 - 5 - 2.3可靠性和安全性特点 - 6 - 2.4系统总体设计 - 6 - 2.5JSP技术介绍 - 7 - 2.5.1 什么是JSP - 7 - 2.5.2 JSP技术特点 - 7 - 2.5.3 JSP开发WEB的几种方式 - 8 - 第三章 数据库的设计与实现 - 9 - 3.1数据库的需求分析 - 9 - 3.2数据库的逻辑设计 - 10 - 3.3 数据库的结构创建 - 10 - 第四章 后台系统和数据库的配置 - 13 - 4.1后台服务器配置 - 13 - 4.2后台数据库的配置 - 13 - 4.3后台全局配置文件 - 13 - 第五章 前端网络页面的开发与设计 - 14 - 5.1登录页面 - 14 - 5.2 管理员用户页面 - 15 - 5.3 注册用户页面 - 16 - 5.4主页面 - 17 - 5.5用户注册页面 - 18 - 5.6 规章制度管理页面 - 18 - 第六章 系统的安全性 - 19 - 6.1 session和cookie的安
5G通信行业、网络优化、通信工程建设资料。
99-煤矿安全生产标准化基本要求及评分方法.pdf
Node.js,简称Node,是一个开源且跨平台的JavaScript运行时环境,它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立,旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎,可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型,这使得它非常适合处理大量并发连接,从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外,Node.js使用了模块化的架构,通过npm(Node package manager,Node包管理器),社区成员可以共享和复用代码,极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展,它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等,因此,开发者可以用JavaScript编写全栈应用程序,这一点大大提高了开发效率和便捷性。 在实践中,许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台,如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能,简化了开发流程,并且能更快地响应市场需求。
475现场通讯器用户手册
Node.js,简称Node,是一个开源且跨平台的JavaScript运行时环境,它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立,旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎,可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型,这使得它非常适合处理大量并发连接,从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外,Node.js使用了模块化的架构,通过npm(Node package manager,Node包管理器),社区成员可以共享和复用代码,极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展,它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等,因此,开发者可以用JavaScript编写全栈应用程序,这一点大大提高了开发效率和便捷性。 在实践中,许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台,如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能,简化了开发流程,并且能更快地响应市场需求。
600A钳形电流表使用手册
5G通信、网络优化与通信建设
Node.js,简称Node,是一个开源且跨平台的JavaScript运行时环境,它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立,旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎,可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型,这使得它非常适合处理大量并发连接,从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外,Node.js使用了模块化的架构,通过npm(Node package manager,Node包管理器),社区成员可以共享和复用代码,极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展,它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等,因此,开发者可以用JavaScript编写全栈应用程序,这一点大大提高了开发效率和便捷性。 在实践中,许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台,如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能,简化了开发流程,并且能更快地响应市场需求。
5G通信、网络优化与通信建设
Binomial Self-compensation for Motion Error in Dynamic 3D Scanning