Getting Started with SQL Queries for your SCSM Data Warehouse

So you deployed Service Manager. You understand XML, you can write some PowerShell scripts, but this SQL thing is just...well it's something else. You'll get around to understanding it later, right? If you are a Service Manager army of one (or perhaps just a curious IT department member), you may find yourself juggling a host of technical abilities as you design basic management packs, query for certain types of data with PowerShell, but how long can you fend off your management before you're asked to produce reports about all the data flying through SCSM? Incidents, Service Requests, Changes are things you'd want at a minimum.

Unfortunately - SQL isn't your strong suit. Sure you can do some basic queries like...

SELECT id, title, createddate
FROM IncidentDimvw
WHERE createddate > '01/02/2015'

But what about the Affected User? That isn't on the IncidentDimvw table so how do you get it?

"I've seen a few TechNet Gallery publishings do this JOIN or INNER JOIN thing. I just don't get it. It's nothing like PowerShell or other things I've used/seen."

You're right. SQL is a different beast from something like PowerShell, C#, and other programming languages. Because it isn't a programming language, it's a Query Language or to be more precise - a Structured Query Language. So let's go over some SQL basics and then use those to apply to real world, use case scenarios for Service Manager. Before you keep reading, make sure you grab a copy of the DWDataMart schema and have the Incident window selected within it as you read along. The download link is at the bottom of that post.

In the first example, I'm doing something obvious (or not so obvious depending on your SQL knowledge). I'm selecting properties I want from the incident table where the incident's createddate is greater than 01/02/2015. The question I posed shortly thereafter was "What about the Affected User?" If you consult the Incident diagram on the Visio diagram you see directly beneath IncidentDimvw there is the UserDimvw table. If you study both closely nothing really stands out as "Affected User". What gives? Go ahead and flip to the "Work Item" diagram and see if you can find it there.

Huzzah! There's the Affected User! Wait. What else am I looking at?

So you have the Affected User Table and it has arrows forming a relationship between it and the UserDimvw and WorkItemDimvw table. Look closely at the properties of UserDimvw. Looks very Active Directory-ish eh? By having a relationship between the Affected User table and the User table - we can pull the properties about that Affected User. We also have the Work Item table. I know what you're thinking.

Seems kinda limited...

But the Work Item tables has two rather necessary things on it: WorkItemDimKey and EntityDimKey. I'll pause for a moment while you flip back to the Incident diagram and look at the first few properties of the IncidentDimvw table. See where this is going now? As you may already know working within Service Manager an Incident is a type of Work Item. Having just walked through this we can see that:

• The Incident table relates to the Work Item table
• The Work Item table relates to the Work Item Affected User table (and assigned to, created by, etc)
• Work Item Affected User relates to the User table

We now have the conceptual basis for building our first SQL query against the data warehouse. In this example, let's get all Incidents created after 01/02/2015 along with all of their Affected Users.

First, let's use the original query as our starting block.

SELECT id, title, createddate
FROM IncidentDimvw
WHERE createddate > '01/02/2015'

Next we need to establish what the relationship between the Incident and the Work Item is in our query

What?! It's established in the diagram! I mean the database! Why do I have to do it! Doesn't it know!?

Well - yes. Well, no. Ok sort of. I'll get to that I promise. What we need to do is add a JOIN into our query and ALIAS the incident table. Which is exactly what it sounds like, it's just an alias or our notation within our query. A JOIN is a way of doing a SELECT on second table and then joining that data together from your first table to form your result. But first, let's get the ALIAS out of the way

SELECT ir.id, ir.title, ir.createddate
FROM IncidentDimvw as ir
WHERE ir.createddate > '01/02/2015'

That wasn't so bad right? I used "ir" for Incident Request, but you could use anything you like. For example, here's the same exact query just with a different alias.

SELECT dzak.id, dzak.title, dzak.createddate
FROM IncidentDimvw as dzak
WHERE dzak.createddate > '01/02/2015'

Next, let's do our first join to establish a relationship between IncidentDimvw and WorkItemDimvw.

SELECT ir.id, ir.title, ir.createddate
FROM IncidentDimvw as ir
    INNER JOIN WorkItemDimvw on ir.EntityDimKey = WorkItemDimvw.EntityDimKey
WHERE ir.createddate > '01/02/2015'

In the above, you may have seen a pattern emerge. Join the WorkItem table on the IncidentDimvw's EntityDimKey property and say that property equals the WorkItemDimvw's EntityDimKey property. Perhaps the best way of explaining it, is showing it - INNER JOIN TableA on TableB.Property1 = TableA.Property1. Next, let's clean things up with an alias for Work Item

SELECT ir.id, ir.title, ir.createddate
FROM IncidentDimvw as ir
    INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey
WHERE ir.createddate > '01/02/2015'

Now let's join up the Affected User table

SELECT ir.id, ir.title, ir.createddate
FROM IncidentDimvw as ir
    INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey
    INNER JOIN WorkItemAffectedUserFactvw as WIAU on wi.WorkItemDimKey = wiau.WorkItemDimKey
WHERE ir.createddate > '01/02/2015'

Then we need to connect the UserDim table to the Affected User table (this will allow us to pull attributes about the user such as title, department, username, etc)

SELECT ir.id, ir.title, ir.createddate
FROM IncidentDimvw as ir
    INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey
    INNER JOIN WorkItemAffectedUserFactvw as WIAU on wi.WorkItemDimKey = wiau.WorkItemDimKey
    INNER JOIN UserDimvw as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey
WHERE ir.createddate > '01/02/2015'

Finally, modify our original SELECT statement to pull data from all these tables we've done joins on

SELECT ir.id, ir.title, ir.createddate, affectedUser.DisplayName, affectedUser.Department
FROM IncidentDimvw as ir
    INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey
    INNER JOIN WorkItemAffectedUserFactvw as WIAU on wi.WorkItemDimKey = wiau.WorkItemDimKey
    INNER JOIN UserDimvw as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey
WHERE ir.createddate > '01/02/2015'

And for some icing (i.e. making an SSRS report)

DECLARE @StartDate datetime
SET @StartDate = '01-02-2015'
SELECT ir.id, ir.title, ir.createddate, affectedUser.DisplayName as 'Affected User', affectedUser.Department as 'Department'
FROM IncidentDimvw as ir
    INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey
    INNER JOIN WorkItemAffectedUserFactvw as WIAU on wi.WorkItemDimKey = wiau.WorkItemDimKey
    INNER JOIN UserDimvw as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey
WHERE ir.createddate > @StartDate

All in all, hopefully you see what we've done here. We started with an Incident and quickly began hopping around to other related tables. Once we defined how these things were related (by virtue of our INNER JOINS), we did a SELECT to pull the specific properties we wanted from each table by calling out their ALIAS.PropertyName.

It may beg the question to those getting started, just what is an inner join? Are their more types of them? Absolutely! So what are they? To that look no further than the trust W3 Schools with their explanations and trusty diagrams.

• Inner join
• Left join

But this is just for an Incident! Think about Service Requests! Change Requests! LET'S QUERY ALL OF THE THINGS! With the above being said, if you haven't already done so, take a glance at my Affected User Incident report on TechNet where the above is done and then some. Hopefully looking at it makes even more sense given the above!

Get your download on here.