导读:前面几篇介绍MDX的基本函数,从本文起,开始介绍一些简单的MDX应用。本文是Cube的增强篇,包括:
■1、构建Cube范围的简单计算成员(Calculated Members)
■2、构建Cube范围的复杂计算成员(Calculated Members)
■3、动态命名集(Dynamic Named Sets)
本文所用数据库和所有源码,请到微软官网下载
另外文尾提供两个项目源码:Chapter10_Begin.Rar和Chapter10_End.Rar,顾名思义,开始和完成。请读者自行修改数据源ds的连接串。
1、构建简单的计算成员(Calculated Members)
每个AS的Cube都包含一个单个的MDX脚本,它由一系列声明(statement)组成,其中绝大多数MDX的第一个声明是CALCULATE。下面我们组建一个最基本的计算成员。
例10-1
WITH
MEMBER [Product].[Category].[All Products].[Bikes & Accessories] AS
([Product].[Category].[Bikes]) + ([Product].[Category].[Accessories])
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Order Count]),
([Measures].[Discount Percentage])
} ON COLUMNS,
{[Product].[Category].AllMembers} ON ROWS
FROM [Step-by-Step]
;
更灵活的方式
例10-2
WITH
MEMBER [Product].[Category].[All Products].[Bikes & Accessories] AS
Aggregate(
{
([Product].[Category].[Bikes]),
([Product].[Category].[Accessories])
},
[Measures].CurrentMember
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Order Count]),
([Measures].[Discount Percentage])
} ON COLUMNS,
{[Product].[Category].AllMembers} ON ROWS
FROM [Step-by-Step]
;
现在我们用create Mmeber在开发环境中设计这个计算成员:
首先,打开vs2008,打开本文提供的示例:MDX Step-by-Step.sln
如下图:
选择Step-By-Step.cube,Calculations选项卡,点击“New Calculated Member”按钮,修改名称为“[Bikes & Accessories]”,
Parent Properties选项,选择“Product.Category”维度,Parent Member选项,选择“[All Products]”,在Expression中输入:
Aggregate(
{
([Product].[Category].[Bikes]),
([Product].[Category].[Accessories])
},
[Measures].CurrentMember
)
最后如下图:
切换到script视图以查看语法:
设计完计算变量后,下一步是部署,步骤如下:
部署完成!
可以通过下例验证刚部署的Cube范围的计算成员
例10-3
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Order Count]),
([Measures].[Discount Percentage])
} ON COLUMNS,
{[Product].[Category].AllMembers} ON ROWS
FROM [Step-by-Step]
;
2、构建复杂计算成员(Calculated Members)
对于以下查询:
例10-4
WITH
MEMBER [Measures].[Reseller Sales Amount Average] AS
([Measures].[Reseller Sales Amount])/
([Measures].[Reseller Transaction Count])
,FORMAT_STRING="Currency"
MEMBER [Measures].[Reseller Sales Amount Variance] AS
(
([Measures].[Squared Reseller Sales Amount]) /
(([Measures].[Reseller Transaction Count])-1)
) -
(([Measures].[Reseller Sales Amount Average])^2)
,FORMAT_STRING="Currency"
MEMBER [Measures].[Reseller Sales Amount Std Dev] AS
([Measures].[Reseller Sales Amount Variance])^(0.5)
,FORMAT_STRING="Currency"
SELECT
{
([Measures].[Reseller Sales Amount Average]),
([Measures].[Reseller Sales Amount Variance]),
([Measures].[Reseller Sales Amount Std Dev])
} ON COLUMNS
FROM [Step-by-Step]
;
例10-4也可以通过计算成员实现
检验后如上部署,
此时,我们在SSMS管理界面,可以看到:
再用下列查询重新实现10-4
例10-5
SELECT
{
([Measures].[Reseller Sales Amount Average]),
([Measures].[Reseller Sales Amount Variance]),
([Measures].[Reseller Sales Amount Std Dev])
} ON COLUMNS
FROM [Step-by-Step]
;
顺便介绍一下NON_EMPTY_BEHAVIOR属性的应用。
在MDX查询器中执行下例:
例10-6
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Sales Amount Average]),
([Measures].[Reseller Sales Amount Std Dev])
} ON COLUMNS,
{[Product].[Subcategory].Members} ON ROWS
FROM [Step-by-Step]
;
在BIDS中如下图设置:
勾选后部署,效果:
3、组装一个命名集(Assembling Named Sets)
看下例查询
例10-7
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
TopCount(
[Product].[Product].[Product].Members,
5,
([Measures].[Reseller Sales Amount])
) ON ROWS
FROM [Step-by-Step]
;
例10-8
WITH
SET [Top 5 Products] AS
TopCount(
[Product].[Product].[Product].Members,
5,
([Measures].[Reseller Sales Amount])
)
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Top 5 Products]} ON ROWS
FROM [Step-by-Step]
;
上面两个例子的查询是一致的。[Top 5 Products]就是一个命名集。
在BIDS界面中如下设置(增加一个命名集):
保存并部署,
再调用该命名集
例10-9
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Top 5 Products]} ON ROWS
FROM [Step-by-Step]
;
结果同上。
提示:在Analysis service的2008版本中提供了“Dynamic Named Sets”,之所以称为动态,是因为这个集并不缓存,而是每次使用时动态调用。区别在于Create后面紧跟一个Dynamic关键字
CREATE DYNAMIC SET CURRENTCUBE.[Top 5 Products]
AS TopCount(
[Product].[Product].[Product].Members,
5,
([Measures].[Reseller Sales Amount])
), DISPLAY_FOLDER = 'Sets';
源码下载:
1、 Chapter10_Begin.rar
2、Chapter10_End.rar
小结:
本文介绍Cube的增强功能,主要包括构建计算成员和动态命名集。下文将继续学习实现动态安全。
参考资源:
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, ...
Microsoft.Press.Microsoft.SQL.Server.2008.MDX.Step.by.Step.Feb.2009.rar
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....
"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...
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, ...
《SQL Server2012Analysis Services高级教程(第2版)》由Microsoft产品团队的主要成员(哈日那思、菲尔格林等)编写,介绍了如何应用这些新功能来创建复杂的BI解决方案。为了实现这一目的,你将在本书中了解到如何...
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 ...
项目需要用MDX,所以翻译了Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX.pdf中第II部分第三章的内容。有翻译不当之处请联系QQ:185633513或发送EMAIL:yubinsure@gmail.com进行修正。
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
MDX step by step CD which including every chapter's sample
利用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....
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
利用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....