说明：本文提供了一些常见的MDX查询语句示例，供有兴趣者作为学习MDX的参考。由朋友Junes原创，在此首发。
环境：SSAS2008及Codeplex的Adventure Works2008示例DW/OLAP
目录：
1. 指标分段统计
2 .查询成员属性
3. 在MDX查询语句中调用VBA函数
4. 按成员属性对指标进行分段或分类统计
5. 指标排名
1. 指标分段统计
目的：将待考察成员按照指标取值分段统计，比如，统计全国各省份销售额位于指定各个区间的客户数
分析：具有三个个性化方面（分段的指标及分段定义；考察的维度及层次机构；该次查询待考察维度的当前级别）。
示例：
WITH
MEMBER [Measures].[大于的客户数]
AS
‘Count(Filter(DESCENDANTS(
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Customer]
),
[Measures].[Internet Sales Amount]>=1000
))’
MEMBER [Measures].[500到的客户数]
AS
‘Count(Filter(DESCENDANTS(
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Customer]
),
[Measures].[Internet Sales Amount]>=500
AND [Measures].[Internet Sales Amount]<1000
))’
MEMBER [Measures].[小于的客户数]
AS
‘Count(Filter(DESCENDANTS(
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Customer]
),
[Measures].[Internet Sales Amount]<500
))’
SELECT
{
[Measures].[大于的客户数],[Measures].[500到的客户数],[Measures].[小于的客户数]
}
ON 0,
{
[Customer].[Customer Geography].[State-Province].Members
}
ON 1
FROM
[Adventure Works]
2. 查询成员属性
描述：查询客户年收入属性
分析：定义一个属性成员
示例：
WITH
MEMBER [Measures].[Yearly Income]
AS
‘[Customer].[Customer].Properties( “Yearly Income” )’
SELECT
{
[Measures].[Yearly Income]
}
ON 0,
{
[Customer].[Customer Geography].[Postal Code].&[90706]&[Bellflower]&[CA].Children
}
ON 1
FROM
[Adventure Works]
3. 在MDX查询语句中调用VBA函数
描述：调用VBA中的IIF函数
分析：由于MSQLServer Analysis Service中的函数与VBA中的函数同名，调用时最好 用[ ]将调用的VBA函数名括起来。
示例：
WITH
MEMBER [Measures].[TestVBA]
AS
‘VBA’
SELECT
{
[Measures].[TestVBA]
}
ON 0,
{
[Customer].[Customer Geography].[City].members
}
ON 1
FORM
[Adventure Works]
补充：VBA参考文档的获取
1） 打开Excel（2007）
2） 选择左上脚Office按钮，在弹出窗口中选择Excel选项
3） 勾选功能区显示”开发工具”选项卡
4） 在工具栏中选择Visual Basic按钮，进入VBA项目开发窗口
5） 选择帮助文档，参考VB帮助
4. 按成员属性对指标进行分段或分类统计
描述：将待考察成员按照客户属性统计，比如，统计到2004年一月份为止各国新，老客户数量
示例1：
WITH
MEMBER [Measures].[DateFormatINeed]
AS
‘CDate(TAIL(DESCENDANTS(
[Date].[Calendar].CurrentMember,
[Date].[Calendar].[Date]
),
1
).item(0).Properties(“Name”)
)’
MEMBER [Measures].[Date of First Purchase]
AS
‘VBA!DateDiff(
“m”,
[Measures].[DateFormatINeed],
[Customer].[Customer].CurrentMember
.Properties(“Date of First Purchase”, TYPED)
)’
MEMBER [Measures].[新客户]
AS
‘Count(Filter(DESCENDANTS(
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Customer]
),
[Measures].[Date of First Purchase]<=3
))’
MEMBER [Measures].[老客户]
AS
‘Count(Filter(DESCENDANTS(
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Customer]
),
[Measures].[Date of First Purchase]>3
))’
SELECT
{
[Measures].[新客户],[Measures].[老客户]
}
ON 0,
{
[Customer].[Customer Geography].[Country].Members
}
ON 1
FROM
[Adventure Works]
WHERE
([Date].[Calendar].[Month].&[2004]&[1])
5. 指标排名
描述：按照互联网销售量对各省份排名
示例1：仅计算排名
WITH
MEMBER [Measures].[saRank]
AS
‘Rank(
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Country].&[United States].children,
[Measures].[Internet Sales Amount]
)’
SELECT
{
[Measures].[Internet Sales Amount],[Measures].[saRank]
}
ON 0,
{
[Customer].[Customer Geography].[Country].&[United States].children
}
ON 1
FROM
[Adventure Works]
示例：排名，并按照名次排序
WITH
MEMBER [Measures].[saRank]
AS
‘Rank(
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Country].&[United States].children,
[Measures].[Internet Sales Amount]
)’
SELECT
{
[Measures].[Internet Sales Amount],[Measures].[saRank]
}
ON 0,
{
Order(
[Customer].[Customer Geography].[Country].&[United States].children,
[Measures].[saRank])
}
ON 1
FROM
[Adventure Works]
示例：同时求前期排名、本期排名，及名次变化
WITH
MEMBER [Measures].[排名]
AS
‘Rank(
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Country].&[United States].children,
[Measures].[Internet Sales Amount]
)’
MEMBER [Measures].[上期排名]
AS
‘(
[Measures].[排名],
[Date].[Calendar].CurrentMember.PrevMember
)’
MEMBER [Measures].[上期值]
AS
‘(
[Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.PrevMember
)’
MEMBER [Measures].[排名变化]
AS
‘[Measures].[排名]-[Measures].[上期排名]‘
SELECT
{
[Measures].[Internet Sales Amount],
[Measures].[上期值],[Measures].[排名],
[Measures].[上期排名],[Measures].[排名变化]
}
ON 0,
{
Order(
[Customer].[Customer Geography].[Country].&[United States].children,
[Measures].[排名]
)
}
ON 1
FROM
[Adventure Works]
WHERE
([Date].[Calendar].[Month].&[2004]&[2])
相关文章：
当前没有评论！
第一个在本文留言。