A really common requirement for SCSM reporting is the ability to report on a daily or weekly basis on the “state” of the total open incidents, changes and service requests.
IT managers want to be in control to be able to “manage” the different processes which are one of the core reasons for their existence.
To keep it simple all they wanted to know is :
- What work items are open?
- Date/time when the work items where logged?
- Duration of the work items being in one state?
- Which people are responsible?
- When was the last time the work item was modified?
- Who did the last modification?
and lastly “Why are these work items still open?” which is an open question they will ask the process managers after they receive the report.
They don’t want to run these reports themselves so we need to design a report which does the job and can be scheduled.
Well this shouldn’t be to hard, right?
Introduction
Let me point out the Little in the title is a big understatement!
You need to combine / join allot of views in the DW to come up with the information they request but you will eventually get all of them except “LastModified by”.
And in the end they will ask for more fields in the report instead of 6 we pointed out.
To fully explain the issue I will write the entire Stored procedure, step by step.
The procedure will be part of a series of Blog Posts on reporting in SCSM!
After these series you are able to update the DW with extra dimensions and write advanced stored procedures and reports! Stay Tuned!
Not only this but since allot of people struggle with writing a report in general I will go from the basics to in the end the hard stuff!
Explanation: Retrieving the correct information
First I will explain how to retrieve data from the SCSM DWDataMart Database.
Views
When it concerns SCSM reporting you need to use the views, these are ideal to report against and a best practice is to use the views.
To start you can review the information and different views to give and understanding of the information in there:
 |
 |
| You can navigate the views of the DWDataMart database to understand all the different views. | You can expand the view to show the columns in the views. |
 |
 |
| If you want to show the records in the views simply right click and select “Select Top 100 Rows”. | Here you can view the actual rows and information in the view. You can see most information is very cryptic. |
You can now “browse” the information in the DWDataMart database and look at all the information in there.
Next I will show how to write a simple query to retrieve simple information.
Retrieve Simple Incident Information
First start a new query and make sure you select the correct database:
We need to retrieve information on incidents so we start with the view IncidentDimvw
Start of with a simple query to select All columns from the IncidentDimvw:
Select *
from IncidentDimVW
This will result in all information from the IncidentDimvw:
Let’s be more specific and select only the following common columns :
- Incident ID
- Incident Title
- Incident Create Date
Notice the query editor will automatically select “possible” columns:
The query:
Select Id, Title, CreatedDate
From IncidentDimvw
The Result:
You can select any of the columns in you select statement!
Easy right?
You can now already write a simple query to collect any information in a view!
Try a couple of different queries and views to make yourself familiar with this concept.
Soon you will discover information for lists is stored by it’s internal ID instead of a readable string like Status .
Now let’s take a look at the Status of an incident.
Now this is where it gets tricky the status is shown as the Internal ID, this is not the wanted result!
You need to “translate” the internal ID to a readable string. The next section will exactly show you how.
Retrieve Incident Status
Basically all data in the DWDatamart is stored in the same way. So once you understand how to retrieve the actual displayname and why you need to join 2 tables you can retrieve any data in the DWDataMart Database.
Let’s take a closer look at the incident views in the DWDataMart and in particular IncidentStatusvw:
| IncidentDimvw | IncidentStatusVw |
 |
 |
Notice the IncidentStatusvw which has the same columns as the IncidentDimvw.
IncidentDimvw
IncidentStatusvw
Now because this information is in two views we want to join these 2 views in the end result.
In order to combine views you need to have a common identifier in both views.
For the IncidentDimvw and IncidentStatusvw this would be these columns. We are going to use Status_IncidentStatusId and IncidentStatusId.
Now how to combine the views and retrieve the information:
The query until now:
Select I.Id, I.Title, I.CreatedDate, I.Status
From IncidentDimvw I
Notice I have added a “I” after the view IncidentDimvw!
By placing the I after IncidentDimvw you simply tell SQL to use “I” as a replacement for the IncidentDimv. You can use any character or multiple characters for identifying the view. Use something which makes sense since you need to re-use these when the query becomes more complex.
So now we can refer to IncidentDimvw by I.
Joining the 2 views in the Query:
Select I.Id, I.Title, I.CreatedDate, StatusEnum.IncidentStatusValue
From IncidentDimvw I
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
This translates to:
Join view IncidentsStatusvw and name the IncidentStatusvw StatusEnum. (As StatusEnum).
Join the view ON IncidentStatusvw column IncidentStatusId is similar to InicdentDimvw column Status_IncidentStatusId.
Now you can use the combination of the two views:
Cool this is the result we wanted!
Well that’s it right?
Not really we need to do one more join in order to get the “Display Name” of the enumeration!
Why? Well Since SCSM is bilingual you want to be able to translate the report as well. When you run the solution in a non English environment they need to be translated.
Also not every Enumeration has a value as the status if you review the classification of an incident IncidentClassificationvw you will notice the value column is not the wanted result:
In order to get the displayname of an enumeration (list) object in DWDataMart you will need the view DisplayStringdimvw.
The results of the DisplayStringvw contains the translation in all supported languages:
But how to join?
Well every object in SCSM has a BaseManagedEntityId or Guid to identify an object.
We need to join the StatusDimvw with the DisplayStringvw on this column to join the two views!
There is a little catch here since the BaseManagedEnityId in the DisplayStringvw is the same as EnumTypeId in the IncidentStatusvw.
The query including the join on DisplayStringvw:
Select I.Id, I.Title, I.CreatedDate, StatusEnum.IncidentStatusValue, StatusDS.DisplayName
From IncidentDimvw I
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
Notice we name the DisplayStringvw to StatusDS and do a similar join on the two identical columns.
When you now use the new join in your select:
hmmm now we get the result in every language! This is not the result we want
There is one more step to retrieve only the display strings of the language we are interested in let’s use Dutch as an example.
Remember there is a language column in the DisplayStringvw view? We are going to add an extra expression on the DisplayStringvw join.
Since we tell to do the join of the DisplayStringvw when BaseManagedEnityId equals EnumTypeId we simply add an AND to include the Language column as well:
Select I.Id, I.Title, I.CreatedDate, StatusEnum.IncidentStatusValue, StatusDS.DisplayName
From IncidentDimvw I
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = ‘NLD’
Translate: Join DisplayStringvw on EnumTypeID equals BaseManagedEntityID and column LanguageCode equals NLD which is the language code for dutch.
Result only the Dutch displaystrings are shown! :
You can change the language code to your prefered lanuage. If you want to know the codes? Simply take a look at the DisplayStringvw results:
Now let’s pimp the query some more to give the column names a meaningfull name and order them by createdate (desc or asc):
Select I.Id, I.Title, I.CreatedDate, StatusDS.DisplayName as Status
From IncidentDimvw I
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = ‘NLD’
Order by I.CreatedDate desc
Now you should be able to follow the similar procedure to retrieve other values from the IncidentDimvw in the same way:
- IncidentClassificationvw – Classification of an incident
- IncidentImpactvw – Impact of an incident
- IncidentResolutionCategoryvw – Resolution Category of an incident
- IncidentSourcevw – Source of an incident
- IncidenTierQueuesvw – Supportgroup of an incident
- IncidentUrgencyvw – Urgency of an incident
Recap
Part 1 showed you how to create simple queries to retrieve data from the DWDatamart database. You are now able to pull already allot of information from the Datawarehouse and these are the basics for creating and understanding SCSM reports.
Although this is specific to SCSM you can use the same knowledge to query data from any database!
The next part will explain how to get to the other objects like related CI’s etc.
Stay tuned for part 2 – “Getting those Relationships!”
Regards,
Oskar Landman
