Dimension Modeling: Creating Business Friendly Dashboards with CURRENT attributes in Date Dimension

Date Dimension is the most commonly used dimension in any BI implementation. Often used as role playing dimension, there is practically no BI Implementation without date dimension. Adding current indicator attributes with Y/N values to date dimension like current year, current quarter, current month, current week and today enhances dashboarding capabilities and provide better navigation for business users.

Since the current indicator attributes are dynamic in nature and for reusability purposes it’s best to create these attributes in a database view (as shown below) or as named calculation in SSAS DSV. This is a better alternative to materializing new columns in date dimension table.

CREATE VIEW [dbo].[V_DIM_DATE]
AS
SELECT DATE_SK,
      (CASE WHEN YEAR(DATE) = YEAR(GETDATE()) THEN 'Y'
		ELSE 'N'
      END) AS CURRENT_CAL_YEAR_IND,
      (CASE WHEN (MONTH(DATE) IN (7,8,9,10,11,12) AND YEAR(DATE) = YEAR(GETDATE()) )THEN 'Y'
			WHEN (MONTH(DATE) IN (1,2,3,4,5,6) AND YEAR(DATE) = YEAR(GETDATE())+1) THEN 'Y'
		ELSE 'N'
      END ) AS CURRENT_FISCAL_YEAR_IND,
	  (CASE WHEN YEAR(DATE) = YEAR(GETDATE()) AND DATEPART(qq,DATE) = DATEPART(qq,GETDATE()) THEN 'Y'
		ELSE 'N'
		END) AS CURRENT_CALENDAR_QUARTER_IND,
      (CASE WHEN (MONTH(DATE) IN (7,8,9,10,11,12) AND YEAR(DATE) = YEAR(GETDATE()) AND DATEPART(qq,DATE) = DATEPART(qq,GETDATE()))THEN 'Y'
			WHEN (MONTH(DATE) IN (1,2,3,4,5,6) AND YEAR(DATE) = YEAR(GETDATE())+1 AND DATEPART(qq,DATE) = DATEPART(qq,GETDATE())) THEN 'Y'
		ELSE 'N'
      END ) AS CURRENT_FISCAL_QUARTER_IND,

	  (CASE WHEN YEAR(DATE) = YEAR(GETDATE()) AND MONTH(DATE) = MONTH(GETDATE()) THEN 'Y'
		ELSE 'N'
		END) AS CURRENT_MONTH_IND,
	  (CASE WHEN YEAR(DATE) = YEAR(GETDATE()) AND MONTH(DATE) = MONTH(GETDATE()) AND DATEPART(ww,DATE) = DATEPART(ww,GETDATE()) THEN 'Y'
		ELSE 'N'
		END) AS CURRENT_WEEK_IND,
		DIM_DATE.ALL_MATERIALIZED_ATTRIBUTES
FROM dbo.DIM_DATE
GO

Having the current indicator attributes is date dimension makes it easier to present information sliced by different role playing time dimension in one single dashboard page without using multiple time filters. Screen Capture 1 shows PerformancePoint dashboard page displaying information for sliced by different role playing date dimensions for current fiscal year -Internet Sales based on Order Date, Sales Order based on Delivery Date, Freight Cost based on Shipment Date etc. Notice there is no date filter to select the current fiscal year on the dashboard page. The page automatically refreshes cube information when the fiscal year moves forward without any user intervention.

Screen Capture 1 – Current Fiscal Year Stats by different Role Playing Date Dimension

Although role playing dimension share the same definition, they are differentiated by the way they are addressed in MDX queries. For example in the above example the date dimension would have to be addressed as [Order Date].[Fiscal Year].&[2013], [Delivery Date].[Fiscal Year].&[2013] and [Ship Date].[Fiscal Year].&[2013] and this present a challenge to dashboarding. In the absence of the current fiscal year indicator, either multiple date filters would be required on the same dashboard page or multiple dashboard pages would be required to cater to each date slicer. Neither of which is a business friendly option.

By defining current indicator attributes in date dimension, current fiscal year can be used as slicer in the MDX query of each report thereby avoiding the need to define multiple date filters or multiple dashboard pages (Screen Capture 2).

Screen Capture 2 – Current Fiscal Year Indicator as Slicer instead of Filter

In this instance the current fiscal year is still uniquely addressed [Order Date].[Current Fiscal Year Ind].&[Y], [Delivery Date]. [Current Fiscal Year Ind].&[Y] and [Ship Date].[Current Fiscal Year Ind].&[Y] because of the role playing date dimensions. However since this member is now part of the MDX slicer whose value never changes, it presents a business friendly dashboard.

Benny Austin

About these ads