Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

SSAS Event Type Calculation Dimension

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:

image

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.

Event Type Image

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.

Comments

Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...