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 CfPEHA.1048%40
tk2msftngp13.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...on&hl=en&lr=&i
e=UTF-8&oe=UTF-8&selm=3D66285F.F69BE1E5%40dsslab.com&rnum=1

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])

HTH
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***