MS SQL Server OLAP - Help with Calculating Moving Average in Cube

Author

Help with Calculating Moving Average in Cube

Stephen Sanders

2006-01-20, 11:24 am

Sorry if this is a simple question, but I'm very new at OLAP.

In SQL 2005 I am trying to calculate a 12-month moving average for a measure
in my cube and I'm having a little trouble. The average seems to reset
itself as it crosses boundaries in the time hierarchy.

I'm using the following calculation in the cube to get my 12-month rolling
average:

Avg ([Time].[CalendarMonth].CurrentMember.Lag(12) :
[Time].[CalendarMonth].CurrentMember,
[Measures].[Revenue Actual])

My cube looks like this:

Time Dimension:
*CalendarYear
**CalendarQuarter
***CalendarMonth

Fact_Revenue
Fulldate, Program_Name, Revenue_Actual, Revenue_Plan

When calculating the averages, it resets itself as soon as it passes a YEAR
or QUARTER boundary. For example, if I look at (2004, Q1, Month 3) it will
show a correct average for only the 2004 Q1 Months 1,2 and 3. It is not
able to figure in the averages of values that fall in a different quarter or
year.

I'm not sure if this is an error with my MDX expression, my Time dimension,
or what. Does anyone have any suggestions on how to do this?

Thanks,

-s

Vladimir Chtepa

2006-01-24, 9:24 am

Hi Stephen

What version of AS do you have?
Is [Time].[CalendarMonth] a hierarchy level or a hierarchy?

Thanks,
Vladimir Chtepa

"Stephen Sanders" <fake@fake.com> schrieb im Newsbeitrag
news:Og76mEeHGHA.1100@TK2MSFTNGP10.phx.gbl...
> Sorry if this is a simple question, but I'm very new at OLAP.
>
> In SQL 2005 I am trying to calculate a 12-month moving average for a
> measure in my cube and I'm having a little trouble. The average seems to
> reset itself as it crosses boundaries in the time hierarchy.
>
> I'm using the following calculation in the cube to get my 12-month rolling
> average:
>
> Avg ([Time].[CalendarMonth].CurrentMember.Lag(12) :
> [Time].[CalendarMonth].CurrentMember,
> [Measures].[Revenue Actual])
>
> My cube looks like this:
>
> Time Dimension:
> *CalendarYear
> **CalendarQuarter
> ***CalendarMonth
>
> Fact_Revenue
> Fulldate, Program_Name, Revenue_Actual, Revenue_Plan
>
>
> When calculating the averages, it resets itself as soon as it passes a
> YEAR or QUARTER boundary. For example, if I look at (2004, Q1, Month 3)
> it will show a correct average for only the 2004 Q1 Months 1,2 and 3. It
> is not able to figure in the averages of values that fall in a different
> quarter or year.
>
> I'm not sure if this is an error with my MDX expression, my Time
> dimension, or what. Does anyone have any suggestions on how to do this?
>
> Thanks,
>
> -s
>
>
>
>

Stephen Sanders

2006-01-24, 1:23 pm

I am using Analysis Server 2005 that was installed with SQL 2005.
[Time.CalendarMonth] is a hierarchy level. My complete time dimension is
like this:

[TIME]
*[CalendarYear]
**& #91;CalendarQuarter]

***[CalendarMonth]
****[FullDate]

Thanks,

-s

"Vladimir Chtepa" <vc.nospam@diacom-systemhaus.nospam.de> wrote in message
news:eqe7LpOIGHA.3728@tk2msftngp13.phx.gbl...
> Hi Stephen
>
> What version of AS do you have?
> Is [Time].[CalendarMonth] a hierarchy level or a hierarchy?
>
> Thanks,
> Vladimir Chtepa
>
> "Stephen Sanders" <fake@fake.com> schrieb im Newsbeitrag
> news:Og76mEeHGHA.1100@TK2MSFTNGP10.phx.gbl...
>
>

Vladimir Chtepa

2006-01-24, 8:24 pm

You should take current member on the hierarchy not on the hierarchy level.
How many hierarchies do you have in the time dimension?

Please, look at this example, that i write for AW sample database.

with

member [measures].[glidingavg] as
avg([Date].[Calendar].currentmember.lag(12):[Date].[Calendar].currentmember,
[Measures].[Internet Sales Amount])

select
{[Measures].[Internet Sales Amount], [measures].[glidingavg]} on 0,
{[Date].[Calendar].[Month].members} on 1
from [Adventure Works]

Vladimir Chtepa

"Stephen Sanders" <fake@fake.com> schrieb im Newsbeitrag
news:%237y0WuQIGHA.2948@TK2MSFTNGP10.phx.gbl...
>I am using Analysis Server 2005 that was installed with SQL 2005.
> [Time.CalendarMonth] is a hierarchy level. My complete time dimension is
> like this:
>
> [TIME]
> *[CalendarYear]
> **& #91;CalendarQuarter]

> ***[CalendarMonth]
> ****[FullDate]
>
> Thanks,
>
> -s
>
>
> "Vladimir Chtepa" <vc.nospam@diacom-systemhaus.nospam.de> wrote in message
> news:eqe7LpOIGHA.3728@tk2msftngp13.phx.gbl...
>
>

Stephen Sanders

2006-01-26, 4:58 pm

That worked nicely! Thank you for your help.

-s

"Vladimir Chtepa" <vc.nospam@diacom-systemhaus.nospam.de> wrote in message
news:uYFz7XVIGHA.1836@TK2MSFTNGP11.phx.gbl...
> You should take current member on the hierarchy not on the hierarchy
> level.

Sponsored Links