I enjoy visualizing data. I believe it gives a different layer of insight to the data that we already have and bubbles up information we may have either felt like we knew already, or demonstrate to us something new entirely. It’s really a matter of bringing broad understanding to something that is otherwise foreign and nearly unusable.

For these exact reasons I’m really excited about the potential of Power BI and Power BI Desktop, but even more so the combination between the two.

Today, I’m going to show you have to bring data directly out of the SCSM DW datamart database, and manipulate the query using Power Query in order to create a flat human readable table of incidents that we can use for reporting. Following that we’ll upload our table to Power BI online and build a new dashboard.

Linking to the Data

The first step before all else is to install Power BI Desktop, open it up and take a look at the start screen.

From here we can select the data source type that we want to use..

From here we are going to select SQL Database…

We’ll enter the server name and the database name. Your server name will vary from mine as you should be targeting the DataWarehouse SQL server, but your database name is most likely the same…

From here we are presented with a list of all the tables and views in the database. We want to find the Incident information, so first things first, we will do a search for Incident and select all the views that we are interested in.

We are also going to want to pull in the User table as well, so that we can use the affected and assigned users real names and information…

Then finally we are going to pull in the Relationship table, as that will contain all of our relationship data, as far as how each users links to each incident.

After we select all of our table queries that we want, we are going to select edit. This gives brings us to the query editor, rather than attempting to load all the tables into memory. Rather we are presented with queries so we can edit them..

From the query editor we are going to select the IncidentDimvw table and this is where we will do the vast majority of our edits.

From here our first step will be to clean up the table and remove all the columns that we have no interest in so we can make the table easier to work with. We can remove columns easily by click the ‘Choose Columns‘ button at the top under the ‘Home‘ tab…

What columns you remove are primarily up to you, but there are a few pointers you should be aware of. First, there will be multiple columns for enumeration lists. They give you both the ‘name’ and the ‘id’ for the enumeration. The problem with the name is that it’s the internal name (IncidentStatusEnum.Active) rather than the display name, and that’s not really what we want, so I remove that Status column and leave the Id column as we will use that to merge with the StatusEnum table and the display strings table later.

Speaking of the display strings table, lets take a second to learn how to add a new query after that fact, and in this care we are going to add that table. To add a new query via the query editor simply click ‘Recent Sources‘ at the top and select the source we just used…

From here we will have our list of tables again and we are simply going to filter that by ‘display’ and then choose the ‘DisplayStringDim‘

Once that’s accomplished we will see that query in our list of queries on the left and available for our use.

Now that we have all of our queries in place, we are going to merge our first column in order to transform the data into something more readable. Lets start with the Status column we mentioned above.

At the top of the screen click  and this will bring up the merge form. From here we are going to select the Status_IncidentStatusId  column on the top, then the IncidentStatusvw table below and the IncidentStatusId column from that table. We want to keep the default JOIN KIND (left outer) and click OK.

This will give us a new column that is a TABLE column, we now need to flatten this out and can do that by clicking the ‘<>’ in the column header.

We are then going to select just the EnumTypeId as that is going to give us the GUID of the specific ID that we can look up in the DisplayStringDim

Click OK and we will get a column with all the values in it

We are ready to do the last step in the merge now, and this will give us our actually Status and then we can rename our column. So lets click the  button again and this time we are going to select our EnumTypeId column, the DisplayStringDim table, and the BaseManagedEntityId column

We now have our TABLE column again, but before we expand this one we have to make a tweak to our DisplayStringDim, so lets click over to that one on the left hand pane and make some modifications.

Right now the table contains values from all the supported lanuages in SCSM, but we need to filter this down to only those that are in ENGLISH for our use case. For that the language code is ENU so we are going to click the filter arrow on the LanguageCode column, deselect all of them and then select only ENU

This will filter the table, and now we can click back over to our IncidentDimvw and expand the Status TABLE column

Select the DisplayName value from the list and deselect the rest of the items and then click OK

Now we have the correct value in for the Status of the Incident! So lets right click the column, rename it to Status, and then select ‘Choose Columns‘ and deselect our StatusId and EnumTypeId columns as those are no longer needed. This will leave us with this!

Now we can follow the same procedure for the other enumeration values except that we don’t need to filter out the DisplayStringDim again, as it is already filtered.

So lets move along and do the rest of the enumerations and in our next post we will work on bringing the Assigned and Affected Users into our table!