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:
In the SystemCenterServiceCatalog cube the following date dimensions are now added:
In the SystemCenterChangeAndActivityManagement cube the following date dimensions are now added:
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.
Or an example of how many hours in average it takes from a Incident is created until resolved, split pr. month.
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:
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:
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.
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!
Does this solution "update" Atchut Barlis solution if you already using that?
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.
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?
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.
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?
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?
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?