Authoring Friday

Every friday, adventures in System Center authoring

A “little” SCSM DW journey from the “basics” al the way onto the “lastmodified by” property. Part 1 – “Basic Queries”

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. Winking smile

To keep it simple all they wanted to know is :

  1. What work items are open?
  2. Date/time when the work items where logged?
  3. Duration of the work items being in one state?
  4. Which people are responsible?
  5. When was the last time the work item was modified?
  6. 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! Angry smile

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! Hot smile

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:

image image
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.
image image
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:

image

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:

image

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:

image

The query:

  1. Select Id, Title, CreatedDate

    From IncidentDimvw 

  2. The result:
  3. image
  4. You can select any of the columns in your select statement!
  5. Easy right?
  6. You can now already write a simple query to collect any information in a view!
  7. 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 below:

Now let’s take a look at the Status of an incident.

image

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
image image

Notice the IncidentStatusvw which has the same columns as the IncidentDimvw.

IncidentDimvw

image

IncidentStatusvw

image

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, I.Status

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:

image

Smile Cool this is the result we wanted! Smile

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:

image

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:

image

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

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:

image

hmmm now we get the result in every language! This is not the result we want Sad smile

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! :

image

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:

image

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

image

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

1 Comment “A “little” SCSM DW journey from the “basics” al the way onto the “lastmodified by” property. Part 1 – “Basic Queries””

  1. Hi,
    You can download Expit EnSight which is an advanced dashboard and analytics platform for SCSM 2012.

    http://expit.com/expit-ensight-for-scsm-2012/m/60

    Regards,
    Subhan

    Comment by Subhan — December 23, 2013 @ 17:23

RSS feed for comments on this post. TrackBack URL

Leave a Response