导读:本文介绍MDX中的导航结构层次(Hierarchies)。成员(Member)之间的关系可以用家族来描述。本文以此为基础,介绍了:
■1、访问直系亲属关系(Immediate Relatives)
■2、访问延伸亲属关系(Extended Relatives)
■3、在一个级别(Level)内导航
本文所用数据库和所有源码,请到微软官网下载
成员(Member)之间的关系可以用家族来描述。(其中Siblings是兄弟、旁支的意思,照顾下E文不好的朋友,呵呵)
下面我们以此为类,演示如何访问直系亲属和非直系亲属。
1、访问直系亲属关系(Immediate Relatives)
直系亲属的访问函数如下:
为了更好理解直系亲属关系,准备一个原始例子
例8-1
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Product].[Subcategory].[Subcategory].Members} ON ROWS
FROM [Step-by-Step]
;
我们增加Parent属性:
例8-2
WITH
MEMBER [Measures].[Percent of Parent] AS
([Measures].[Reseller Sales Amount])/
(
[Product].[Product Categories].CurrentMember.Parent,
[Measures].[Reseller Sales Amount]
)
,FORMAT="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Percent of Parent])
} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
;
这个结果还有有一些刺眼,特别是第一条记录这样的。因为除数为0。
我们改进一下:
例8-3
WITH
MEMBER [Measures].[Percent of Parent] AS
IIF(
[Product].[Product Categories].CurrentMember.Parent Is Null,
Null,
([Measures].[Reseller Sales Amount])/
(
[Product].[Product Categories].CurrentMember.Parent,
[Measures].[Reseller Sales Amount]
)
)
,FORMAT="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Percent of Parent])
} ON COLUMNS,
{
[Product].[Product Categories].Members
} ON ROWS
FROM [Step-by-Step]
;
顺便我们介绍一个有用的函数Rank(http://msdn.microsoft.com/zh-cn/library/ms144726.aspx),与SQL中的Rank类似。
例如如下查询:
例8-4
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
;
我们进行排名:
例8-5
WITH
MEMBER [Measures].[Sibling Rank] AS
Rank(
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].CurrentMember.Siblings,
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Sibling Rank])
} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
;
排名有了,再改进一下排序:
例8-6
WITH
MEMBER [Measures].[Sibling Rank] AS
Rank(
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].CurrentMember.Siblings,
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Sibling Rank])
} ON COLUMNS,
{
Order(
{[Product].[Product Categories].Members},
([Measures].[Sibling Rank]),
ASC
)
} ON ROWS
FROM [Step-by-Step]
;
2、访问延伸亲属关系(Extended Relatives)
其中相比直系关系,多了几个Flag:
我们以上面的例8-3为例,首先我们增加一个Ancestor
例8-7
WITH
MEMBER [Measures].[Percent of Parent] AS
IIF(
[Product].[Product Categories].CurrentMember.Parent Is Null,
Null,
([Measures].[Reseller Sales Amount])/
(
[Product].[Product Categories].CurrentMember.Parent,
[Measures].[Reseller Sales Amount]
)
)
,FORMAT="Percent"
MEMBER [Measures].[Percent of Category] AS
([Measures].[Reseller Sales Amount])/
(
Ancestor(
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].[Category]
),
[Measures].[Reseller Sales Amount]
)
,FORMAT="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Percent of Parent]),
([Measures].[Percent of Category])
} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
;
仿上,我们进行改进:
例8-8
WITH
MEMBER [Measures].[Percent of Parent] AS
IIF(
[Product].[Product Categories].CurrentMember.Parent Is Null,
Null,
([Measures].[Reseller Sales Amount])/
(
[Product].[Product Categories].CurrentMember.Parent,
[Measures].[Reseller Sales Amount]
)
)
,FORMAT="Percent"
MEMBER [Measures].[Percent of Category] AS
IIF(
Ancestor(
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].[Category]
) Is Null,
Null,
([Measures].[Reseller Sales Amount])/
(
Ancestor(
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].[Category]
),
[Measures].[Reseller Sales Amount]
)
)
,FORMAT="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Percent of Parent]),
([Measures].[Percent of Category])
} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
;
下面我们尝试穿越“血缘关系”计算Product的百分比贡献。
例8-9
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{([Product].[Product Categories].[Product].[Mountain-200 Black, 42])} ON ROWS
FROM [Step-by-Step]
;
例8-10
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
Ascendants(
[Product].[Product Categories].[Product].[Mountain-200 Black, 42]
)
} ON ROWS
FROM [Step-by-Step]
;
例8-11
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Hierarchize(
{
Ascendants(
[Product].[Product Categories].[Product].[Mountain-200 Black, 42]
)
}
) ON ROWS
FROM [Step-by-Step]
;
例8-12
WITH
MEMBER [Measures].[Percent Contribution Reseller Sales] AS
(
[Product].[Product Categories].[Product].[Mountain-200 Black, 42],
[Measures].[Reseller Sales Amount]
) /
([Measures].[Reseller Sales Amount])
,FORMAT="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Percent Contribution Reseller Sales])
} ON COLUMNS,
Hierarchize(
{
Ascendants(
[Product].[Product Categories].[Product].[Mountain-200 Black, 42]
)
}
) ON ROWS
FROM [Step-by-Step]
;
下面我们组装一个给定分类的后裔集合
例8-13
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Product].[Product Categories].[Category].[Bikes]} ON ROWS
FROM [Step-by-Step]
;
例8-14
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory]
) ON ROWS
FROM [Step-by-Step]
;
例8-15
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory],
AFTER
) ON ROWS
FROM [Step-by-Step]
;
After标志符提供了SubCategory以下的后裔集合
例8-16
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory],
SELF_AND_AFTER
) ON ROWS
FROM [Step-by-Step]
;
SELF_AND_AFTER标志符提供了SubCategory及以下的后裔集合
例8-17
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory],
BEFORE_AND_AFTER
) ON ROWS
FROM [Step-by-Step]
;
BEFORE_AND_AFTER标志符提供了包含SubCategory的上级分类的所有后裔集合
大家有兴趣可以了解一下几个相关的成员函数:
IsAncestor(http://msdn.microsoft.com/zh-cn/library/ms144842.aspx)
IsSibling(http://msdn.microsoft.com/zh-cn/library/ms144749.aspx)
IsLeaf(http://msdn.microsoft.com/zh-cn/library/ms144932.aspx)
例8-18
WITH
MEMBER [Measures].[Number of Children] AS
IIF(
IsLeaf([Product].[Product Categories].CurrentMember),
"N/A",
COUNT(
[Product].[Product Categories].CurrentMember.Children
)
)
SELECT
{[Measures].[Number of Children]} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
3、在一个级别(Level)内导航
在一个级别内导航会用到几个函数:
比如计算月之间的百分比差距
例8-19
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
例8-20
WITH
MEMBER [Measures].[Prior Period Reseller Sales] AS
([Date].[Calendar].CurrentMember.PrevMember,[Measures].[Reseller Sales Amount])
,FORMAT="Currency"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Prior Period Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
例8-21
WITH
MEMBER [Measures].[Prior Period Reseller Sales] AS
([Date].[Calendar].CurrentMember.PrevMember,[Measures].[Reseller Sales Amount])
,FORMAT="Currency"
MEMBER [Measures].[Change in Reseller Sales] AS
([Measures].[Reseller Sales Amount]) - ([Measures].[Prior Period Reseller Sales])
,FORMAT="Currency"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Prior Period Reseller Sales]),
([Measures].[Change in Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
例8-22
WITH
MEMBER [Measures].[Prior Period Reseller Sales] AS
([Date].[Calendar].CurrentMember.PrevMember,[Measures].[Reseller Sales Amount])
,FORMAT="Currency"
MEMBER [Measures].[Change in Reseller Sales] AS
([Measures].[Reseller Sales Amount]) - ([Measures].[Prior Period Reseller Sales])
,FORMAT="Currency"
MEMBER [Measures].[Percent Change in Reseller Sales] AS
([Measures].[Change in Reseller Sales])/
([Measures].[Prior Period Reseller Sales])
,FORMAT="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Prior Period Reseller Sales]),
([Measures].[Change in Reseller Sales]),
([Measures].[Percent Change in Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
如果觉得最后一列格式不够完美,读者可以自行修正一下。
小结:
本文介绍MDX中的导航结构层次(Hierarchies)。成员(Member)之间的关系可以用家族来描述。本文包含了比较多的函数。
参考资源:
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 ...
"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, ...
项目需要用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....
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设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
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-...