Date dimensions for Incidents, Service Requests, Change Requests and Activities.

I’ve had this blog on my todo list for quite a while and finally I found some time to actually write it.

My colleague Atchut Barli created an MP last year that extended the Data Warehouse Work Item cube with a date dimension for when incidents were created. This made some reports a lot more useable. http://blogs.technet.com/b/servicemanager/archive/2013/02/20/3539405.aspx

Together with George Wallace and Chris Jones, we have extended the solution to include more dimensions:

In the SystemCenterWorkItems cube the following date dimensions are now added:

  • IncidentCreatedDate (yes, the one Atchut also added)
  • IncidentResolvedDate
  • ProblemCreatedDate
  • ProblemResolvedDate

In the SystemCenterServiceCatalog cube the following date dimensions are now added:

  • ServiceRequestCreatedDate
  • ServiceRequestCompletedDate
  • SRActivityCreatedDate
  • SRActivityScheduledEndDate
  • SRActivityActualEndDate

In the SystemCenterChangeAndActivityManagement cube the following date dimensions are now added:

  • ChangeRequestCreatedDate
  • ChangeRequestScheduledStartDate
  • ChangeRequestScheduledEndDate"
  • CRActivityCreatedDate
  • CRActivityScheduledEndDate

This opens up to create a new set of interesting reports, created via Analysis Services. The report below is an example of created, solved and active incidents, showing a trend of a growing backlog.

image

Or an example of how many hours in average it takes from a Incident is created until resolved, split pr. month.

image

You can now create similar reports for Service Requests, Change Requests, Problem records and activities.

To Import these date dimesion into your environment, do the following:

  1. Import the AddCubeDataSlicers,MP into your SCSM environment (or seal the XML with your own key if you want)
  2. Run the MPSyncJob on the Data Warehouse and wait until the MP is marked as ‘Completed’ in the list of Date Warehouse Management packs.
  3. Run the AddCubeDateSlicers.ps1 powershell script on the SQL Server Analysis Services. Make sure to update the first line to reflect the database name if not using the default:
    param ( $SSASserver="localhost",$SSASDatabase="DWASDatabase")
  4. Process all the cubes, or wait for automatically processing during the nigth, go to Service Manager Console -> Data Warehouse -> Cubes and click Process Cube.

A zip file is uploaded to Technet Gallery and can be downloaded here: http://gallery.technet.microsoft.com/Date-dimensions-for-007632d4/file/95945/1/AddCubeDateSlicers.zip

The file contains 3 files:

  • AddCubeDateSlicers.MP
  • AddCubeDateSlicers.XML
  • AddCubeDateSlicers.PS1

If the months are sorted alphabetically rather then by calendar Month order, see this blog on how to fix that: http://blogs.technet.com/b/servicemanager/archive/2013/03/11/month-ordering-by-calendar-sequence-when-slicing-measures-by-calendarmonth.aspx

Kudos to George Wallace and Chris Jones for the initiative to add more date dimensions.

If you see a need for other extensions to the the Data Warehouse cubes, please let me know.