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.

Comments (35)

  1. Anonymous says:

    Thanks Thomas!

  2. Anonymous says:

    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

  3. Anonymous says:

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

  4. Anonymous says:

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

  5. Anonymous says:

    Hi, after adding the MP I can no longer report based on the IncidentTierQueues dimension. When analyzing the WorkItems cube in Excel, IncidentDim_IncidentTierQueues is not longer listed as a Dimension under IncidentDim. I can find it when I select all
    fields in the Pivot table picker but it does not enumerate the list of TierQueues.
    Has anyone encounter an issue such as this?

  6. Anonymous says:

    Good Work

  7. Anonymous says:

    This is a thing of beauty!

  8. Anonymous says:

    Hi Chris

    I will add CRs for the Next version of the MP, not sure when.

  9. Anonymous says:

    Hi again, is it possible to fix the sorting of DayOfWeek (shows Friday, Monday, Saturday….) and WeekNumberInYear, that is sorted 1, 10, 11, 12, 13.. The other solution on your post just fixes MonthOrdering..

  10. Anonymous says:

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

    Thanks

  11. Anonymous says:

    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?

  12. Anonymous says:

    Really cool!

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

  13. Anonymous says:

    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.

  14. Anonymous says:

    Cool!!! Thank you very much!

  15. Anonymous says:

    Thank you very much Thomas, Good Article πŸ˜‰

  16. Anonymous says:

    Hi Denis
    Sounds like you already have Atchuts MP deployed. So if you uninstall it and import mine, it will work.

  17. Anonymous says:

    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.

  18. Anonymous says:

    Great post ! very useful ! Thank's

  19. Lasse Wilén says:

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

  20. Tjindarr says:

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

  21. Thomas Ellermann says:

    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?

  22. Heikki Törrönen says:

    Hi Thomas

    After Service Manager R2 upgrade, IncidentCreatedDate doesn't work in WorkItems cube, all the reports are empty. Is there any way to uninstall or re-install these dimensions?

  23. Thomas Ellermann says:

    Hi Heikki

    You just need to run the powershell script again, to extend the cubes.

    /Thomas

  24. Heikki Törrönen says:

    Great, that worked. Now everything is ok! Thanks!!!

  25. Anonymous says:

    Pingback from Date dimensions for Incidents, Service Requests, Change Requests and Activities. – System Center: Service Manager Engineering Team Blog – Site Home – TechNet Blogs

  26. Chris Stelzer says:

    For the CR’s what about “Actual Start Date” & “Actual End Date”? Those would be extremely helpful as well!

  27. Denis says:

    Hi
    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
    error not fix. Please help.

  28. Denis says:

    Thomas Ellermann
    Help please, i look more errors.
    1. scsm console: Cube – UnProcessed. if start Processed, if you run through the process of getting the error MSSQL "Errors in the high-level relational engine. The data source view does not contain a definition for the ‘CRActivityCreatedDateKey’ column in the
    ‘ActivityDim’ table or view."
    2. run in powershell AddCubeDateSlicers.ps1 – "Exception calling "Add" with "1" argument(s): "Another ‘MeasureGroupDimension’ object has the ‘IncidentCreatedDate’ key"

  29. Denis says:

    how I remove all the changes from the server analysts, but server not work (((

  30. Denis says:

    Yes, I deleted the old MP. Errors that I have described above occur with already installed your MP. I deleted and imported your MP, but the changes made ​​in the Server database analysts cause errors. How to completely rollback changes? and try to install
    your MP to clean scsm? give me your email address, I will describe the problem in more detail

  31. JonRunheim says:

    Quite honestly, this is the one MP that makes SCSM reporting fantastic. What can be created with it and Excel2013 is amazing.. For anyone that has some say – please help get this included in the base product in future releases.. Without it, cube reporting
    is completely crippled πŸ™
    Huge thanks for making this MP available!

  32. JonRunheim says:

    I’ve encountered a scenario where these slicers unfortunately seems to fall short – working with SLO’s.
    I have SLO’s deployed and they work fine.

    If I in Excel select IncidentDim-IncidentDimCount and Incidents Meeting All SLOs Percentage.

    Select IncidentDim_IncidentstatusValue and add under Filter, set the filter to resolved.
    I now get the correct SLO value for all resolved incidents.
    If I now select the Incident Dimension – Resolved Date I get the correct SLO state per date as expected. I can also move this to the filters and select a bunch of dates for an aggregated SLO% over a month.

    Now – doing the same with the Date-slicer – which is what I’d prefer – makes the SLO%-values come up empty..?
    The same goes no matter if using the IncidentResolvedDate or IncidentCreatedDate.
    The GrandTotal shows the correct SLO% sum, but the different months/year always show 0%..?
    If moving to be a filter and selecting a specific month / quarter / year it always show 0%..?

    Are others experiencing this as well?

  33. Doug Caldwell says:

    Can these date dimensions be used as Timeline Slicers in Excel? I thought they could but Excel 2013 gives me an error "can’t create a Timeline for this report because it doesn’t have a field formatted as Date". Am I missing something?

  34. David says:

    I’ve just run the script on the DW server and now one of the cubes fails to process. Everything processed normally without errors. The error I am getting is from the "SystemCentreWorkItemsCube" with the following in the event logs;

    Invalid column name ‘IncidentResolvedDateKey’ and Invalid column name ‘IncidentCreatedDateKey’ .

    An error occurred while processing the ‘IncidentDim’ partition of the ‘IncidentDim’ measure group for the ‘SystemCenterWorkItemsCube’ cube from the DWASDataBase database

    Can someone assist please as i really do not want to rebuild the DW server again.

  35. Devon Dieffenbach says:

    I am entirely baffled at how this is not incorporated into Service Manager out of the box. How could Microsoft not think sorting by date ranges would be an important requirement in making a report?