Thomas Ellermanns view on System Center

This blog will discuss features, functions and views on Operations Manager and Service Manager

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

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

  • Comments 23
  • Likes

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.

Comments
  • Great Work Thomas. this will come in Handy!!!

  • This is great stuff Thomas, really took this to the next level.

  • This is amazing, this is what all Service Management tools do out of box.  Hopefully in the next version this comes out of box....

  • This is a thing of beauty!

  • Cool!!! Thank you very much!

  • Really cool!

    Does this solution "update" Atchut Barlis solution if you already using that?

  • Hi Peter-Nordqvist,

    It doesn't change the date slicer from Atchut as I use another name for the Incident Created key. So you can install my solution and combine it with Atchut, if you already have created spreadsheets that uses it.

    Or you can install my solution and remove Atchuts Management Pack.

  • Thanks Thomas!

  • Great post ! very useful ! Thank's

  • If i already implemented the incidentcreatedate by atchut do i need to remove that MP and run all cube jobs Before i implement this?

  • Hi Tjindarr

    No, you can leave Atchuts MP in your system if you already have reports using his date slizer. He named in IncCreatedDate where I'm using IncidentCreatedDate.

    Otherwise remove his MP, import mine and process the cubes.

  • Hi Thomas, im trying to import your MP, though as i said were already using Atchuts solution in reports.

    When i try to import i get this:

    The management pack import failed.

    Errors (1):

    The named calculation is not unique.  The property defined already exists in the entity or another named calculation with the same property name was defined.  Named Calculation: IncidentCreatedDateKey

    It seems the key name is the same?

    Thanks

  • Hi Peter, I'm sorry to hear that, I thought there wouldn't be a conflict. But if you remove his MP, start the MPSyncjob and then import my MP, it will work.

    Otherwise I can create a special edition where the IncidentCreatedDate isn't included in my MP?

  • OK, will i lose anything if i remove his MP? I mean the reports that we already use?

    Thanks

  • Hi Peter

    Yes, you will need to go into the reports and point to the new IncidentCreatedDate instead of the one the old solution created.

    Are you OK with that?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment