SSAS 2008 Time Calculation Dimension (Calculated Members)

Over the past couple of weeks I have been working on setting up a PPS 2007 dashboard and came across a Time Calculation Dimensions in SSAS 2008. At first I didn't truly understand the true purpose or functionality but after time I gained respect as I created new Time Calculation Calculated Members. This article will explain the steps needed to setup a Time Calculation dimension and add multiple Calculated Members that are stored in the Time Dimension.

1. Go to your dsv of the cube and add a new Named Calculation field to your time dimension, Make sure to call it PeriodID and the expression should be " N'Current Period' " (make sure to drop the double quotes
   
2. Create a new Dimension with the Dimension Wizard
3. Select use an existing table
4. tap into your current time dimension, in my case I am using DimWeek
5. Make sure to have the primary key of the dimension selected and no other fields
6. Name the new dimension "Time Calculation"
7. Now that the dimension is created bring it up
8. Drag over the PeriodID (the Name Calculation you created on step 1) to the Attributes section of the Dimension Structure tab
9. Rename the field to Time Calculation and you should have something like this
10. Now open the cube up and go to the Calculation
11. Create a new Calculated Member
12. Name: [Prior Year]
13. Parent hierarchy: Time Calculation. Time Calculation
14. Expression: (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period])
15. 
16. Now you need to setup your Dimension Usage
17. Setup the Measure Group, Referenced, Intermediate Dimension (Week in this case), Reference Dimension attribute (Time Calculation), Intermediate Dimension attribute (PeriodID)
18. 
19. Build and deploy cube
20. Now in your cube browser you will see a new dimension call Time Calculation
21. Add the new dimension and drag a metric and the time dimension and you will see the power of the Time Calculation Dimension

Here is a list of some other Time Calculations:
CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior Year]
AS (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period]),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date]
AS aggregate(crossjoin([Time Calculation].[Time Calculation].&[Current Period], PeriodsToDate([Week].[FiscalCalendar].[Fiscal Year], [Week].[FiscalCalendar].CurrentMember))),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change]
AS [Time Calculation].[Current Period] - [Time Calculation].[Prior Year],
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change %]
AS Case
When ISEMPTY([Time Calculation].[Prior Year]) Or [Time Calculation].[Prior Year] = 0
Then IIF([Time Calculation].[Current Period] > 0, 1, 0)
Else
[Time Calculation].[Year to Year Change] / [Time Calculation].[Prior Year]
End,
FORMAT_STRING = "Percent",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 3 Periods]
AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(3) :
[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 6 Periods]
AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(7) :
[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date %]
AS [Time Calculation].[Time Calculation].&[Current Period] /[Time Calculation].[Time Calculation].[Year to Date],
FORMAT_STRING = "Percent",
VISIBLE = 1;