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