导读:在AS中,最重要的一个维度莫过于Time,围绕Time,MDX提价供了一系列函数来满足复杂的业务分析需求。本文主要内容包括:
■1、计算累积总和(Calculating an Accumulating Total)
■2、计算滚动平均值(Calculating Rolling Averages)
■3、执行阶段至阶段(Period-over-Period)的分析
■4、组合时间指标(Combining Time-Based Metrics)
本文所用数据库和所有源码,请到微软官网下载
1、计算累积总和(Calculating an Accumulating Total)
关于Time维度,最核心的一个层次是calendars,它允许你从更高级的颗粒度,如年,或者更小的颗粒度季、月、天等进行钻取。
基于标准Calendar的一个用户定义层次。
关于累积总和,我们首先用到一个函数PeriodsToDate(http://msdn.microsoft.com/zh-cn/library/ms144925.aspx)
例9-1
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Month].[April 2002]
)
} ON ROWS
FROM [Step-by-Step]
;
例9-2
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
例9-3
WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
例9-4
WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
MEMBER [Measures].[Quarter to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Quarter],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales]),
([Measures].[Quarter to Date Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
MDX还提供了一系列的函数来进行PeriodsToDate的简化运算,如
Ytd(http://msdn.microsoft.com/zh-cn/library/ms146039.aspx)
Qtd(http://msdn.microsoft.com/zh-cn/library/ms145978.aspx)
Mtd(http://msdn.microsoft.com/zh-cn/library/ms144753.aspx)
Wtd(http://msdn.microsoft.com/zh-cn/library/ms144930.aspx)
所以,例9-4也可以这样实现:
例9-5
WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
Ytd([Date].[Calendar].CurrentMember),
([Measures].[Reseller Sales Amount])
)
MEMBER [Measures].[Quarter to Date Reseller Sales] AS
Aggregate(
Qtd([Date].[Calendar].CurrentMember),
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales]),
([Measures].[Quarter to Date Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
结果同上。
period-to-date返回一个特定边界(如一季、一年等)的值。有时,你想计算所有统计日期内的一个精确值,这就是所谓的初始日期(Inception-to-Date)值。你可以引用Null成员,例如Null: [Date].[Calendar].CurrentMember将强制AS使用前一个成员到当前时间成员所在级别上的第一个成员的边界(Range),下面的查询与上例类似
例9-6
WITH
MEMBER [Measures].[Inception to Date Reseller Sales - PTD] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[(All)],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
MEMBER [Measures].[Inception to Date Reseller Sales - Range] AS
Aggregate(
NULL:[Date].[Calendar].CurrentMember,
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Inception to Date Reseller Sales - PTD]),
([Measures].[Inception to Date Reseller Sales - Range])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
2、计算滚动平均值(Calculating Rolling Averages)
MDX中计算滚动平均值,使用LastPeriods(http://msdn.microsoft.com/zh-cn/library/ms145588.aspx)
例9-7
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
LastPeriods(
3,
[Date].[Calendar].[Month].[January 2002]
)
} ON ROWS
FROM [Step-by-Step]
;
前推三个月。
例9-8
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
下面我们计算滚动平均值
例9-9
WITH
MEMBER [Measures].[Three Month Avg Reseller Sales Amount] AS
Avg(
LastPeriods(
3,
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Three Month Avg Reseller Sales Amount])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
3、执行阶段至阶段(Period-over-Period)的分析
在统计中我们常听到两个概念:同比、环比。所谓同比就是今年第n月与去年第n月比。环比就是今年第n月与第n-1月或第n+1月比。在AS中,我们可以用ParallelPeriod(http://msdn.microsoft.com/zh-cn/library/ms145500.aspx)进行运算。
例9-10
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
上例中我们用到了前文提到过的Descendants(http://msdn.microsoft.com/zh-cn/library/ms146075.aspx),一个计算后裔的函数。计算的是以[CY 2003]为基础,在月等级上,返回指定的所有结果集
下面我们构造一个虚拟Period
例9-11
WITH
MEMBER [Measures].[x] AS
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
在此基础上,我们实现了同比计算。
例9-12
WITH
MEMBER [Measures].[Prior Period Reseller Sales Amount] AS
(
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Reseller Sales Amount]
)
,FORMAT="Currency"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Prior Period Reseller Sales Amount])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
下面我们做一个小小的改动,以显示同比增长率
例9-13
WITH
MEMBER [Measures].[Prior Period Reseller Sales Amount] AS
(
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Reseller Sales Amount]
)
,FORMAT="Currency"
MEMBER [Measures].[Prior Period Growth] AS
(
([Measures].[Reseller Sales Amount])-
([Measures].[Prior Period Reseller Sales Amount])
) /
([Measures].[Prior Period Reseller Sales Amount])
,FORMAT="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Prior Period Reseller Sales Amount]),
([Measures].[Prior Period Growth])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
一个忠告:到目前为止,我们再一次领会到基于时间的函数并不是时间感知,而是为了具体的功能而简单地进行导航。
我们通过Cousin(http://msdn.microsoft.com/zh-cn/library/ms145481.aspx)
Ancestor(http://msdn.microsoft.com/zh-cn/library/ms145616.aspx)
Lag(http://msdn.microsoft.com/zh-cn/library/ms144866.aspx)
重新实现上例9-11。
例9-14
WITH
MEMBER [Measures].[x] AS
Cousin(
[Date].[Calendar].CurrentMember,
Ancestor(
[Date].[Calendar].CurrentMember,
[Date].[Calendar].[Calendar Year]
).Lag(1)
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
输出结果同例9-11
例9-15
WITH
MEMBER [Measures].[x] AS
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2002],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
出现Null的原因是对应的2001年的统计数据是从July开始的,所以产生了整体的错位。这个问题的解决方案在哪里?目前还真没有。惟一能提醒大家的是:在计算类似功能时一定要先控制好数据的边界范围。
3、组合时间指标(Combining Time-Based Metrics)
我们基于一个实例来说明。
例9-16
SELECT
{
([Measures].[Reseller Sales Amount])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
例9-17
WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
,FORMAT="Currency"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
以上例为基础,可以很轻易地计算同比的“差额”,请细心观察下例的用法。
例9-18
WITH
MEMBER [Measures].[Prior Period Year to Date Reseller Sales] AS
(
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Year to Date Reseller Sales]
)
,FORMAT="Currency"
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
,FORMAT="Currency"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales]),
([Measures].[Prior Period Year to Date Reseller Sales])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
最后,顺便介绍一下两个日期函数OpeningPeriod(http://msdn.microsoft.com/zh-cn/library/ms145992.aspx)和ClosingPeriod(http://msdn.microsoft.com/zh-cn/library/ms145584.aspx)。
例9-19
WITH
MEMBER [Measures].[First Child Rate] AS
(
OpeningPeriod(
[Date].[Calendar].[Date],
[Date].[Calendar].CurrentMember
),
[Measures].[End of Day Rate]
)
,FORMAT="Standard"
SELECT
{
([Measures].[First Child Rate]),
([Measures].[End of Day Rate])
} ON COLUMNS,
{[Date].[Calendar].Members} ON ROWS
FROM [Step-by-Step]
WHERE ([Destination Currency].[Destination Currency].[Euro])
;
小结:
本文介绍Time相关的函数及其应用。
参考资源:
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 ...
Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, learn-by-doing tutorial, you get the guidance you need to build flexible and powerful ...
Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, learn-by-doing tutorial, you get the guidance you need to build flexible and powerful ...
Microsoft+SQL+Server+2008+MDX+Step+by+Step 京华志&精华志出品 希望大家互相学习,互相进步 支持CSDN 支持微软
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 ...
Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, learn-by-doing tutorial, you get the guidance you need to build flexible and powerful ...
"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...
项目需要用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....
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....
利用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-...