The SystemCenterWorkItem cube in the current release of the Service Manager data warehouse does not have the Date dimension added as a slicer for Incidents or any other dimension. This blog post explains how to add the Date dimension to the SystemCenterWorkItem cube.
This screenshot shows the difference between what the SystemCenterWorkItem cube looks like in an Excel pivot table before and after deploying the management pack described in this blog post. Pivot table A shows Incidentdim Count sliced by IncCreateddate cube dimension (Role Playing Date dimension). It conveys the Incident count for 2012 is 10000 with 1183 Incidents (with titles CustomIR*) in November. Pivot table B without the slicer shows incident counts by date and it will not be possible to directly query the weekly, monthly, quarterly aggregations of Incidents.
The following workaround can be used with the IncidentDIm as an example.
1) Import the attached sealed MP which will create a DateKey for CreatedDate in the IncidentDim.
The MP has the following cube extension element which would add the DateKey to IncidentDim as a Named calculation in the SSAS Database.
<Warehouse>
<CubeExtension ID="AddIncidentCreateDateNC" Target="WorkItemCubeMP!SystemCenterWorkItemsCube"> <NamedCalculations>
<NamedCalculation ID="IncidentCreatedDateKey" Target="IncidentDW!IncidentDim" ColumnType="Int"> <Calculation>isNull(CONVERT(nvarchar(8), CreatedDate,112),'20000101')</Calculation> </NamedCalculation> </NamedCalculations>
</CubeExtension>
</Extensions>
</Warehouse>
2) Run the data warehouse MP Sync Job so that the MP gets deployed, wait for the MP deployment status to be updated to the "Completed " status in the Service Manager Console-> Data Warehouse->Management Packs view.
3) Execute the attached PowerShell Script on the SSAS server. It peforms the following steps:
a) Creates a role playing cube dimension for DateDim
b) Adds a relationship between the cube dimension and the column added by the above MP import
4) Process the Work Items cube from the Service Manager Console -> Data Warehouse -> Cubes view
IncCreatedDim cube dimension( Role Playing date Dimension) is added to the Pivot table field list as slicer to IncidentDim for workitem cube in the Excel report.
Note: rerun the PowerShell script after any major upgrade like SP1 as the upgrade process will redeploy the cube and deletes the relationship.
IncidentSlicerMP.Zip file attached to this blog contains the following files:
1)addCubeDateDim.ps1
2) AddIncidentCreatedDateMP.mp
3) AddIncidentCreatedDateMPrtm.xml
Thanks for this, I've been able to make a really nice report for Incidents now. Is there going to be one for Service Requests as well?
do you know if this will be fixed by Microsoft in future update? I'm a bit reluctant to add custom changes.
will it work with new updates / service packs released?
or can this change be rollback in case it doesn't work?
Hi Daniel,
Its not fixed with SP1, it will work with the service pack or updates other then the PS script need to be rerun as noted in the post.javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$content$ctl00$w_72134$_bf6215$ctl00$ctl00$ctl00$ctl05$bpCommentForm$ctl05$btnSubmit", "", true, "BlogPostCommentForm-ctl00_content_ctl00_w_72134__bf6215_ctl00_ctl00", "", false, true))
Hi,
If I change the id to <ID>ServiceManager.ChangeManagement.Library.Datawarehouse</ID> in the xml, would it work or would i have to take out the token. I would like to slice change requests as well.
We have really been quite happy with this MP in our environment, it helps a lot with reporting. However, I have one concern, especially now that I am designing dashboards. For some reason the months for 2013 are coming in alphabetical order! That is, you get Feb, Jan and then Mar!! So, when I use a Time Intelligence Filter on Dashboard Designer, and when I filter for "Previous Month" for instance, and assume we are in Mar 2013, then the previous month is considered to be Jan in this case!
Is it possible to fix this, PLEASE?!
Thanks
Ashraf
If you use this MP, you'll want to run the PS script below to correct an issue w/ the DateDim and the month ordering when using PerformancePoint time intelligence.
After this MP has been added, If you launch SSMS and connect to the Analysis Server, in the dimensions, you'll see the DateDim dimension. If you right click on that and hit Browse, you can expand the any of the fields and notice that the months are ordered alphabetically. The problems comes in when using PerformancePoint and Time Intelligence to build out metrics and KPIs. Since the months are not ordered properly our time ranges get messed up. For instance, if i wanted to show the previous 72 hours, I'd have to use the following formula: (Month-2).day:day-3. This would have to be updated monthly to keep the data accurate. The blog below explains the correction script (published by the MS DW team).
Script/Blog:
blogs.technet.com/.../month-ordering-by-calendar-sequence-when-slicing-measures-by-calendarmonth.aspx
Management Pack successfully been imported but as it been run the data warehouse MP Syncthe job. After that it is
not being shown in MP Views. Kindly please guide me what to do further to fix it on?
Used:
SQL Server 2008 R2
System Centre Service Manager 2012
Thanks in advance.
M Ahsan
This is great for Incidents. Is there a solution for the other DIMs?