Share via

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

  • Article

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. https://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: https://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: https://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

  • Anonymous
    January 01, 2003
    Thanks Thomas!

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    This is great stuff Thomas, really took this to the next level.

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    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?

  • Anonymous
    January 01, 2003
    Good Work

  • Anonymous
    January 01, 2003
    This is a thing of beauty!

  • Anonymous
    January 01, 2003
    Hi Chris

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

  • Anonymous
    January 01, 2003
    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..

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

  • Anonymous
    January 01, 2003
    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?

  • Anonymous
    January 01, 2003
    Really cool! Does this solution "update" Atchut Barlis solution if you already using that?

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    January 01, 2003
    Cool!!! Thank you very much!

  • Anonymous
    January 01, 2003
    Thank you very much Thomas, Good Article ;)

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

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    January 01, 2003
    Great post ! very useful ! Thank's

  • Anonymous
    September 11, 2013
    Great Work Thomas. this will come in Handy!!!

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

  • Anonymous
    October 18, 2013
    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?

  • Anonymous
    November 18, 2013
    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?

  • Anonymous
    November 19, 2013
    Hi Heikki You just need to run the powershell script again, to extend the cubes. /Thomas

  • Anonymous
    November 19, 2013
    Great, that worked. Now everything is ok! Thanks!!!

  • Anonymous
    December 29, 2013
    Pingback from Date dimensions for Incidents, Service Requests, Change Requests and Activities. - System Center: Service Manager Engineering Team Blog - Site Home - TechNet Blogs

  • Anonymous
    April 30, 2014
    For the CR's what about "Actual Start Date" & "Actual End Date"? Those would be extremely helpful as well!

  • Anonymous
    September 05, 2014
    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.

  • Anonymous
    September 05, 2014
    The comment has been removed

  • Anonymous
    September 05, 2014
    how I remove all the changes from the server analysts, but server not work (((

  • Anonymous
    September 05, 2014
    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

  • Anonymous
    November 20, 2014
    The comment has been removed

  • Anonymous
    January 09, 2015
    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?

  • Anonymous
    January 23, 2015
    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?

  • Anonymous
    August 29, 2015
    The comment has been removed

  • Anonymous
    January 28, 2016
    The comment has been removed