How to Slice Incidents by Days, Months, Quarters

How to Slice Incidents by Days, Months, Quarters

  • Comments 17
  • Likes

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

 

 

 

Attachment: IncidentslicerMP.zip
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • 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.

  • Hi,

    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?

  • We've just imported it. had to rename one file to remove rtm from the end otherwise it gives error before import. Initiated the MPsyncjob from warehouse as well.

    Its been 1 hour. However it is still not listed as management pack in Data Warehouse. It only shows in the Administrative view of all management packs in console where from it was imported. Is that normal timings. Should we proceed with powershell script execution ?

  • Hi

    I have to agree with Daniel, I really hope this will be implemented in Service Manager in a future update. This is basic functionality that everyone needs, and should be implemented for all DIMs

    :-) Arne

  • Hi!  After importing the management pack, getting an error:

    Deployment Infrastructure has retried the maximum number of times and is giving up on this MP deployment operation.

    MP name: AddIncidentCreatedDateMP

    MP version: 7.5.1561.0

    Operation: Install

    Error message: The .Net Framework Data Providers require Microsoft Data Access Components(MDAC).  Please install Microsoft Data Access Components(MDAC) version 2.6 or later.

    Deployment Execution Infrastructure encountered an error while executing a deployer.  

    MP element ID: AddIncidentCreateDateNC

    MP name: AddIncidentCreatedDateMP

    MP version: 7.5.1561.0

    Operation: Install

    Error message: Microsoft.SystemCenter.ResourceAccessLayer.InfraGeneralException: The .Net Framework Data Providers require Microsoft Data Access Components(MDAC).  Please install Microsoft Data Access Components(MDAC) version 2.6 or later.

    System.InvalidOperationException

      at System.Data.OleDb.OleDbConnectionInternal.GetObjectPool()

      at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) ...

    My configuration:  SCSM 2012 SP1 Rollup2, SQL 2008 R2 SP2, OS on the servers Windows Server 2008 R2 Sp1. How to solve the problem?

  • Hi,

    After creating and deploying AddIncidentResolvedDateMP.mp and addCubeIncResolvedDateDim.ps1, IncResolvedDate filter is working

    but filter IncResolvedDate stopped working (when install a filter there are no results).

    How to fix it?

  • Thanks for the great tips, very much appreciated. I'll definitely be saving this page for reference later when I review with my team. Currently looking into: IT asset management software from AlphaPoint Technology

  • I have a problem with my cubes and need to remove this MP, the MP is removed but the references are still in the analys service tables, do you have a Powershell script to remove these references.

    Regards

  • Hi Geert,
    if you wish to uninstall the MP, I think the best way would be to restore to a point where it was not installed?

    1) take a backup of the current DB
    2) restore to the DB point where the script was not run
    3) run the dw maintenance job
    4)run the cube processing jobs

    do you have a back up of the Analsysis server DB?