Home > SSAS > Enhancing the DateTool Dimension

Enhancing the DateTool Dimension

Technorati Tags: ,

I had this issue in the past and addressed it in a very inelegant manner involving a many-to-many relationship that I won’t reproduce here. For all you poor souls that inherited that approach, I sincerely apologize. I was young (not really) and inexperienced (also, not really) and knew when I implemented it, it was not the right way (definitely true). However, the users liked it, needed it, and once deployed, it was really difficult to retract.

The scenario that I’m addressing here has its roots in that same place. Say that I want to reproduce the Sales Performance Dashboard data visualization that Stephen Few presents in the last chapter of his book, Information Dashboard Design, within a SQL Server Reporting Services Tablix control. An example of this data visualization can be found here. The visualization includes a Sparkline, an Indicator, and a Bullet Graph for various different financial performance metrics. The difficulty in reproducing this visualization in SSRS is that all data used to generate these visualizations must be included in the same dataset. While the indicator and bullet graph are populated by Year-to-Date values, the Sparkline is sourced by current month values over the last twelve months. Therefore, two different date aggregation contexts, Year-to-Date and Last 12 Months, and two different date contexts, the selected month for the Bullet Graph and Indicator, and the component months for the Sparkline need to included within the dataset sourcing the visualization.

To produce a dataset that could be used to generation this data visualization, the DateTool dimension described by Marco Russo in this blog post was enhanced. The dimension will be referred to as the [Date Utility] dimension in the remainder of this blog post. These enhancements included adding new utility attributes to represent the component members involved in a date aggregation. To illustrate using the Adventure Works cube, if [Date].[Calendar].[May, 2008] and [Date Utility].[Aggregation].[Last 12 Months] are specified in the slicer, including the newly added [Date Utility].[Aggregate Month] attribute to the row axis would generate a set equivalent to [Date].[Calendar].[Jun, 2007] : [Date].[Calendar].[May, 2008]. This set represents the time periods that compose the ([Date].[Calendar].[May, 2008], [Date Utility].[Aggregation].[Last 12 Months]) calculated cell with the current cube context.

The first modification to Russo’s Date Utility dimension was to simply Cross Join the [DateTool].[Aggregation] and [DateTool].[Comparison] views with another view that included each possible [Date].[Calendar].[Month] member. (For this case, detail at the granularity of the Day level was not needed.) Even though the approach involves cross joining three views, each has a relatively small number of possibilities. The definition of the [DateTool].[CalendarMonths] and [DateTool].[DateTool] views are given below.

CREATE VIEW [DateTool].[CalendarMonths] AS
	FROM	DateTool.DimDate dt
	WHERE	dt.DateValue		= dt.MonthStartDate 
CREATE VIEW [DateTool].[DateTool] AS
			CAST( ID_Comparison AS VARCHAR ) + ' - ' 
			+ CAST( ID_Aggregation AS VARCHAR ) 
			+ ' - ' + CAST(m.MonthKey AS varchar)  
		) AS Description
	FROM	DateTool.DateAggregation a
	CROSS JOIN DateTool.DateComparison s
	CROSS JOIN DateTool.CalendarMonths m

The second modification to Russo’s Date Utility dimension was to add an Aggregate Year –> Aggregate Quarter –> Aggregate Month natural hierarchy to the [Date Utility] dimension. A Business Information Model of the Date Utility dimension is shown below


The last modification is to define MDX script calculations to return cell values for the Aggregate Month, Aggregate Quarter, and Aggregate Year attributes. These additional calculations are placed between the script defining the Aggregation members and the script defining the Comparison members. This placement allows an aggregated comparison to also be decomposed into its constituent time periods.

The entire solution (at least with the Aggregate Month calculations defined) is available from my Skydrive account. To describe the approach, the definition of the subcube defined by the [Date Utility].[Aggregation].[Last 12 Months] member and [Date Utility].[Aggregate Month].[Aggregate Month] level will be described as shown in the code below. Only when a single member of the [Date].[By Calendar].[Month] dimension is included in the current context will a non-NULL value be returned. If that check passes, the next check is to determine if the current member of the [Date Utility].[Aggregate Month] attribute is within the last 12 months of [Date].[By Calendar].CurrentMember using an Intersect function call. If so, a tuple is returned that assigns the equivalent of LinkMember( [Date Utility].[Aggregate Month].CurrentMember, [Date].[Month] ); otherwise, a NULL value is returned.

    [Date Utility].[Aggregation].[Last 12 Months], 
    [Date Utility].[Aggregate Month].[Aggregate Month] 
    THIS = IIf(
        COUNT(EXISTING { [Date].[By Calendar].[Month].Members } ) = 1,
                { [Date Utility].[Aggregate Month].CurrentMember },
                        [Date].[By Calendar].CurrentMember, 
                        [Date Utility].[Aggregate Month] 
                        [Date].[By Calendar].CurrentMember, 
                        [Date Utility].[Aggregate Month] 
            ).Count > 0,
                    [Date Utility].[Aggregate Month].CurrentMember, 
                [Date Utility].[Aggregation].DefaultMember,
                [Date Utility].[Aggregate Month].DefaultMember

Lastly, to demonstrate its application, consider the following MDX statement which includes a single member of the [Date].[By Calendar] hierarchy and a single member of the [Date Utility].[Aggregation] attribute in the WHERE clause.

        [Measures].[Internet Sales Amount]
        [Date Utility].[Aggregate Month].[Aggregate Month].Members 
    } ON ROWS
    [Adventure Works]
        [Date].[By Calendar].[May, 2008],
        [Date Utility].[Aggregation].[Last 12 Months]

Including the [Date Utility].[Aggregate Month] attribute on the ROW axis returns the [Date Utility].[Aggregation] constituent members as shown below

Aggregate Month Internet Sales Amount
Jun, 2007 $554,799.23
Jul, 2007 $886,668.84
Aug, 2007 $847,413.51
Sep, 2007 $1,010,258.13
Oct, 2007 $1,080,449.58
Nov, 2007 $1,196,981.11
Dec, 2007 $1,731,787.77
Jan, 2008 $1,340,244.95
Feb, 2008 $1,462,479.83
Mar, 2008 $1,480,905.18
Apr, 2008 $1,608,750.53
May, 2008 $1,878,317.51

While this query doesn’t really illustrate why you would include an Aggregate Month enhancement, in a future post, use of the enhanced Date Utility dimension will be used to show how the Stephen Few data visualization including an Indicator, a Bullet Graph, and a Sparkline can be generated using Report Builder.

Categories: SSAS
  1. Ritesh Gaur
    October 23, 2012 at 4:11 AM

    Hi Martin,
    Sorry to contact you on more unformal way.
    Following is link from you where you raised Error in Aggregation when M2M is used.

    Am also facing similar problem and trying to follow your solutions by creating duplicate level and so on.

    But unfortunately, am not able to get it through.
    Therefore, request you to provide me with a example on solving this
    You could reply me on ritesh.gaur@gmail.com

    Thanks and regards

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: