Transferring Incident Action Logs to the Data Warehouse

The Incident Action Logs are not transferred to the Data Warehouse in SCSM 2010 or 2012 by default.

Therefore, when data grooming ocurrs, based on your data rentention settings, you will lose this information.

For some people, this is ok, they don’t require the incident logs for historical or reporting reasons, but if you want to keep this data read on…

It is possible to define additional facts (relationship data) and dims (class data) in the Data Warehouse for existing classes and relationships or any custom ones that you have defined. This includes those for the incident logs.

I’ve seen some posts showing how to do this, but often these only add the System.WorkItem.TroubleTicket.ActionLog class and System.WorkItem.TroubleTicketHasActionLog relationship.

There are actually 3 log classes and 3 relationship types involved (the Guids are shown in brackets):

Relationships

System.WorkItem.TroubleTicketHasActionLog (a860c62e-e675-b121-f614-e52fcbd9ef2c)
System.WorkItem.TroubleTicketHasAnalystComment (835a64cd-7d41-10eb-e5e4-365ea2efc2ea)
System.WorkItem.TroubleTicketHasUserComment (ce423786-16dd-da9c-fb7b-21ab5189e12b)

Classes

System.WorkItem.TroubleTicket.ActionLog (dbb6a632-0a7e-cef8-1fc9-405d5cd4d911)
System.WorkItem.TroubleTicket.AnalystCommentLog (f14b70f4-878c-c0e1-b5c1-06ca22d05d40)
System.WorkItem.TroubleTicket.UserCommentLog (a3d4e16f-5e8a-18ba-9198-d9815194c986)

Defining the Dimensions (Dims)

You need one Dimension defined for each class that you want to transfer to the Data Warehouse, so we need 3. Note the highlighted targets for each class:

Definining the Relationships (Facts)

We also need to define 3 fact tables to transfer the relationship data between these 3 classes and the incident class.

I have highlighted the source class (SourceType), source dimension (SourceDimension),  relationship (RelationshipType) and target dimension (TargetDimension) for this fact the we previously defined. “IncidentDim” is the out-of-the-box dimension for the IncidentDimvw view.

Note that the names (ID) for the new facts and dims do not end with “vw” as Service Manager will automatically add “vw” to these. If you suffix these with “vw” you’ll end up with view names ending in “vwvw” :)

To keep names consistent, you should suffix your relationship facts with “Fact” and your dimensions with “Dim”.

You’ll also need some additional references for the Data Warehouse:

There are some other bits and pieces that are needed, such as your language strings, all of this can be seen by downloading the complete Management Pack from here:

Download the Management Pack

Lastly, you’ll need to seal your management pack using fastseal.exe so that it will transfer to the Data Warehouse.

Notes

  • You need to wait for MPSync and then successful ETL processing before the new views will appear in the DWDataMart database.
  • Only data created after you import these changes will be transferred, existing data will not appear. Therefore, you should decide very early on if you need this data or not.
  • Please test this in a test environment and confirm that all class and relationship data you require is transferred to the Data Warehouse.
This entry was posted in Data Warehouse, Management Packs and tagged , , . Bookmark the permalink.