Quick Hacks (Creating a Date Dimension)

If you require a Date Dimension and you are unhappy with the automatically generated sample provided for in the Analysis Services Business Intelligence Wizard, try your own custom table.

The following code will create a list of dates between Jan 1, 1970 and Dec 32, 2015 then insert the content into a table aptly named DimDate.   To use this code you will need to edit as follows :

  • Change the Begin Date from 1970-01-01 to your choice
  • Change the End Date from 2015-12-31 to your choice
  • Add as many additional date attribute fields you require in the Date Dimension.  The provided attributes allow only for a minimalist set of columns to create a sample date hierarchy
  • Change the name of the table INTO DimDate to your own choice
WITH DateLoop as
 (
 SELECT Cast ('1970-01-01' as DateTime) FullDate
 UNION ALL
 SELECT FullDate + 1
 FROM DateLoop
 WHERE FullDate + 1 < = '2015-12-31'
 )
 SELECT
 CAST(CONVERT(varchar(8),FullDate,112) AS int) as DateId
 , FullDate as FullDate
 , CAST(CONVERT(varchar(6),FullDate,112) AS int) as MonthId
 , CAST(CONVERT(varchar(4),FullDate,112) AS int) as YearId

INTO DimDate

FROM DateLoop

OPTION (MAXRECURSION 0)

Once you get this working, try creating your own time (hour of day) dimension by adapting the logic above and customising the code sample provided below.

WITH TimeLoop as
 (
 SELECT Cast (1 as int) HourOfDay
 UNION ALL
 SELECT HourOfDay + 1
 FROM TimeLoop
 WHERE HourOfDay + 1 < = 24
 )
 SELECT
 HourOfDay as HourId

INTO DimTime

FROM TimeLoop

OPTION (MAXRECURSION 0)

A more detailed working example is provided below.  The WeekStartDate function is provided at the end of this article to demonstrate how a function can be used for some of these dynamic date calculations.

WITH DateLoop as
 (
 SELECT Cast ('2012-01-01' as DateTime) FullDate
 UNION ALL
 SELECT FullDate + 1
 FROM DateLoop
 WHERE FullDate + 1 < = '2012-06-30'
 )
 SELECT
 FullDate AS PK_Date
, CAST(CONVERT(varchar(8),FullDate,112) AS int) as Date_Id
, CONVERT(varchar(50),FullDate,106) as Date_Name
, DATEADD(yy, DATEDIFF(yy,0,FullDate), 0) AS [YearStartDate]
, DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,FullDate)+1,0))) AS [YearEndDate]
, CAST(LEFT(CONVERT(varchar(8),FullDate,112),4) AS int) AS Year_Id
, 'Year ' + LEFT(CONVERT(varchar(8),FullDate,112),4) AS Year_Name
, CONVERT(datetime,DATEADD(dd,-(DAY(FullDate)-1),FullDate),103) AS [MonthStartDate]
, CONVERT(datetime,CONVERT(varchar(12),DATEADD(dd,-(DAY(DATEADD(mm,1,FullDate))),DATEADD(mm,1,FullDate)),112),103) AS [MonthEndDate]
, CAST(CONVERT(varchar(6),FullDate,112) AS int) as Month_Id
, CAST(DATENAME(MM,FullDate) as varchar(30)) as Month_Name
, CAST(LEFT(DATENAME(MM,FullDate),3) AS CHAR(3)) AS Month_ShortName
, CAST(DATEPART(dy,FullDate) as int) AS Day_Of_Year
, CAST(DATEPART(dd,FullDate) as int) AS Day_Of_Month
, CAST(DATEPART(mm,FullDate) as int) AS Month_Of_Year
, CAST(DATENAME(dw , FullDate) AS varchar(30)) AS WeekDay_Name
, CAST(LEFT(DATENAME(dw , FullDate),3) as CHAR(3)) AS WeekDay_ShortName
, DATEPART(WEEKDAY, FullDate) AS Day_Of_Week
, dbo.WeekStartDate(FullDate) AS WeekStartDate
, DATEADD(day,6, dbo.WeekStartDate(FullDate)) AS WeekEndDate
, DATEADD(day,-1,dbo.WeekStartDate(FullDate)) AS PreviousWeekEndDate

INTO DimDate

FROM DateLoop

OPTION (MAXRECURSION 0)

Week Start Date Function …


CREATE FUNCTION [dbo].[WeekStartDate] (@MidWeekDate DateTime)
RETURNS DateTime AS

BEGIN
DECLARE @WeekCommence DateTime
 SET @MidWeekDate = DATEADD(dd, DATEDIFF(d, 0, @MidWeekDate),0)
 SET @WeekCommence = DateAdd(d, -((@@DATEFIRST + DatePart(dw, @MidWeekDate) -1) % 7), @MidWeekDate)
 RETURN @WeekCommence
END
GO

Go Forth and Multiply (Deploying MDX Calculations)

There are several ways to deploy your new MDX Calculations from your Analysis Services solution without processing the cube.

First, go and download the BIDS Helper tool for SQL 2008 from Codeplex which also works for SQL 2008 R2 …

BidsHelper

Secondly, it is good practice to set your deployment properties.  Make sure you right click your Analysis Services project, select properties, select the Deployment tab and ensure these 3 settings are set as follows :

  • Processing Option (Do Not Process)
  • Transactional Deployment (False)
  • Deployment Mode (Deploy Changes Only)

Finally open Bids, navigate into the solution until you get to the Cube right click your Cube and select Deploy MDX script.  Light is green, trap is clean…

BidsHelper Deployment

Now go forth and multiply your calculations without processing the cube.

Alternating Row Colours in Reporting Services (SSRS)

One of the most frequently asked questions in Reporting Services is “How do I alternate my row colours?”.   There are ample articles and solutions out there, in Australia we call these “Commodores”, in Virginia you probably call them “RAM 1500’s” and in Seattle  “Lincolns”, so I am going to add one more “Lincoln” but with a more hands on approach.

Firstly, you need to check out Powerview to see how much easier it is to apply professional looking visuals to your report.   For SSRS Report Designer and Report Builder tools though, unfortunately you won’t find a formatting widget on the toolbar to do this.  So lets get started on the procedure by utilising Expressions for our background color property.

If you have a standard table with no grouping, select each column in your detail row (select Ctrl and click each cell as opposed to just selecting the row), and in the Background color property, adapt the following expression…

IIF(RowNumber(Nothing) Mod 2, &quot;Gainsboro&quot;, &quot;White&quot;)

If you have a table with any weird groupings, or you have a Matrix, you are really better off utilising a nice piece of code from WROX

First add the following function to your report (go to your report properties and find the Code section) …


Private bOddRow As Boolean
'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
Function AlternateColor(ByVal OddColor As String, _
ByVal EvenColor As String, ByVal Toggle As Boolean) As String
If Toggle Then bOddRow = Not bOddRow
If bOddRow Then
Return OddColor
Else
Return EvenColor
End If
End Function

Then click on each cell in your detail row…for the very first leftmost cell enter the following code in the expression for the cell’s Background Color property …


=Code.AlternateColor("Gainsboro", "White", True)

For all other cells use this background property expression  instead.


=Code.AlternateColor("Gainsboro", "White", False)

Thanks to the following references.

BrianMcDonald and WROX.

SQL Pass 2012 – Nov 6-9 Seattle WA

SQL Pass Summit dates for 2012 are November 6-9 Seattle, WA.  If you have a yearly training budget of around $3000-$4000, then consider a week at SQL Pass as an alternative to that 5 day training course you were considering.  For roughly $1000 for the event early bird ends Jan 31 2012, this gives you plenty of time to scour for the best value accomodation and flights.

SQLPass 2012 Seattle

SQL Pass Summit is the without doubt the premier event for SQL Server professionals.  This goes for Microsoft Business Intelligence, Database Development or Database Administration professionals alike.  On the Business Intelligence there are quite a few sessions to keep you alert, interested and happy, unless of course, you got back to the apartment at 5am from the previous evening’s activities….

Kimball University Classes 2012

For personal development in the Business Intelligence space you can’t go past Ralph Kimball’s classes for first class techniques, methodologies and of course the opportunity to link with like minded individuals.

For any folks in the Microsoft Business Intelligence space, in particular star schemas and cubes, you would be best looking first at the Dimensional Modelling class.   Anyone who spends most of their time in the ETL space, then the ETL Architecture is an excellent first stop.

For the many people I have been telling to watch out for the 2012 class schedule here it is – go to your manager’s office immediately and book those dates !

Kimball Calendar