Hello

I need to setup a cube that will calculate rolling totals based on a month.
In other words, I need to calculate year to date, last 12 months, and last 36
month totals for 3 measures on a fact table.

Here is my setup. It is pretty basic.

Fact Table
Time_key
Dim_key2
Dim_key3
Measuse1
Measure2
Measure3

Dimension_Time -- table
Time_key
Year string
Month string

How can I set the cube up so it will calculate the rolling totals
automatically?

Thanks.

http://groups-beta.google.com/group/...rver.olap/msg/
16519fcd135e1c93
From: Deepak Puri <deepak_p... (AT) progressive (DOT) com>
Date: Thu, 23 Sep 2004 17:11:39 -0700

Subject: Re: too many calculated members when calculating last year
figures

Maybe a Time Analysis utility dimension will help you:

http://groups.google.com/groups?q=ti...puri&hl=en&lr=
&ie=UTF-8&group=microsoft.public.sqlserver.olap&selm=OB8uB Cf*PEHA.1048%4
0tk2msftngp13.phx.gbl&rnum=3

..
- Tom Chester's web-site has this sample database, which creates a
calculation dimension with a YTD member:

http://www.tomchester.net/articlesdo...dimension.html


- Here is a Microsoft support article, using Foodmart 2000 Sales cube:


http://support.microsoft.com/default...;EN-US;q304118

INF: How To Perform Time Series Calculations (Q304118)
..


- And here is a post from George Spofford, author of "MDX Solutions":

http://groups.google.com/groups?q=sp...nsion&hl=en&lr...

A time analysis utility dimension has no all level and 1 real member in
a dimension table named something
like "Current" with a key value like 0 or 1. You can create and process
the dimension as usual. Bring the
dimension into the cube, join it to the fact table on any column of the
fact table, and set the member
key in the cube the constant 0 or 1 (matching Current's key).

Add calculated members to the cube on this dimension:

CREATE MEMBER [Time Series].[YTD] AS
'Aggregate (
PeriodsToDate (
[Time].[Year],
[Time].CurrentMember
),
([Time Series].[Current])
)'

CREATE MEMBER [Time Series].[QTD] AS
'Aggregate (
PeriodsToDate (
[Time].[Quarter],
[Time].CurrentMember
),
([Time Series].[Current])
)'

A query for YTD sales at August 2002 is a query for the tuple
([Measures].[Sales], [Time Series].[YTD], [Time].[Aug 2002])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!