《Microsoft SQL Server 2008 MDX Step by Step》学习笔记九：导航结构层次

SQL Server 2008中SQL应用系列及BI笔记系列--目录索引

导读:本文介绍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）