Calculation dimensions in SSAS are used to store calculations that can be applied to any measure in the cube. For example, we can define a ‘Year to Date’ calculation in a calculation dimension and it will work for any measure. Alternatively, we could define a new ‘Year to Date’ calculated member for every current measure in the cube, but this would cause our list of members to expand significantly and create clutter. Therefore, calculation dimensions provide a nice option to minimize the number of calculated members in a cube; yet, still allow users to perform the necessary analysis.
In this example, we will create a calculation dimension to calculate measures based on a warranty claim event type. Users need the ability to calculate various metrics based on when a warranty claim is created, resolved and closed.
In the cube we have a Date dimension and additional role-playing Date dimensions that are named ‘Created Date’, ‘Resolved Date’ and ‘Closed Date’. Each role playing dimension has a relationship to the various measure groups. The calculations we create for the ‘Event Type’ dimension will map the standard Date dimension to the appropriate role-playing Date dimension so measures are evaluated based on the date associated with the desired event.
To create a calculation dimension, we need to do the following:
1. Create a new dimension (or add a hierarchy to an existing dimension) that will serve as the calculation dimension or hierarchy. In this example, we will create a new dimension called ‘Event Type’.
2. Create an attribute called Event Type. In this example, the Event Type dimension will be based off of the Date dimension. The date dimension has a text column called ‘Dimension Anchor’ and the value of this column is always set to ‘Current Period’. The Event Type attribute uses the ‘Dimension Anchor’ column for its Name and Key column. The IsAggregatable property of the Event Type hierarchy is set to ‘False’, so there is no All member. In addition, the DefaultMember property is set to [Event Type].[Event Type].[Current Period], so this member will be implicitly or explicitly selected for every query executed.
3. We will now create three calculated members called Created, Resolved and Closed. The parent hierarchy for these members is [Event Type].[Event Type]. Each calculated member is initially set to NULL. An example is shown below:
4. We now must update the MDX script in the cube to perform calculations that map from the standard date dimension (called ‘Date’) to the appropriate role-playing dimension.
The MDX is shown below:
SCOPE([Event Type].[Event Type].[Created]);
THIS = AGGREGATE((([Date].[Fiscal Calendar].[All],StrToMember(“[Date Created].[Fiscal Calendar].[" + [Date].[Fiscal Calendar].CurrentMember.Level.Name + “].[" + [Date].[Fiscal Calendar].CurrentMember.Name + “]”)),[Event Type].[Event Type].[Current Period]));
END SCOPE;
SCOPE([Event Type].[Event Type].[Resolved]);
THIS = AGGREGATE((([Date].[Fiscal Calendar].[All],StrToMember(“[Date Resolved].[Fiscal Calendar].[" + [Date].[Fiscal Calendar].CurrentMember.Level.Name + “].[" + [Date].[Fiscal Calendar].CurrentMember.Name + “]”)),[Event Type].[Event Type].[Current Period]));
END SCOPE;
SCOPE([Event Type].[Event Type].[Closed]);
THIS = AGGREGATE((([Date].[Fiscal Calendar].[All],StrToMember(“[Date Closed].[Fiscal Calendar].[" + [Date].[Fiscal Calendar].CurrentMember.Level.Name + “].[" + [Date].[Fiscal Calendar].CurrentMember.Name + “]”)),[Event Type].[Event Type].[Current Period]));
END SCOPE;
A few notes regarding the MDX:
- The Scope assignment is necessary to ensure our calculation is only applied to the desired region in the cube.
- We use the StrToMember function in conjunction with string concatenation to map from the Fiscal Calendar hierarchy in the Date dimension to the appropriate role-playing date dimension ([Date Created], [Date Resolved], or [Date Closed]).
- We need to include [Date].[Fiscal Calendar].[All], so we do not slice by the standard Date dimension and instead slice by the appropriate role playing Date dimension.
- We must also include [Event Type].[Event Type].[Current Period] so our calculation works for any measure in the cube.
The following is an example of a Performance Point chart that uses the new ‘Event Type’ dimension in conjunction with the Date dimension.
The chart series represent a count of Warranty Claims broken down by the Event Types: Created, Resolved and Closed. The bottom axis represents the fiscal month of year from the Date dimension.
