Published on July 23rd, 2015 | by Fredrik Knalstad4
Service Manager Intelligence & Analysis: Part 3 “Access the Data With Power Query”
This is the part 3 of Service Manager Intelligence & Analysis series.
Service Manager Intelligence & Analysis: Part 1 “Where the shoe pinches?”
Service Manager Intelligence & Analysis: Part 2 ” DW drilldown”
Service Manager Intelligence & Analysis: Part 4 “Access the Data With Power BI Desktop
In the last post we did a quick dive in to the Service Manager DW database and had a quick look at how the data is structured. It’s important to understand how the different dimensions( classes) are connected through relationships. So in this part we are going to start working with the data and do some transformation of the data.
In the “Where the Shoe pinches”(Part1) I talked briefly about Power Query and Power BI Desktop designer. Both tools support connecting to SQL databases and do local modulation of the data. So I will cover both the tools in this blog post series. Since Power BI now is in GA, feel the need to cover both the tools in this series.
In this blog post I will focus on the Incident module of Service Manager. Incident module collects the different Incidents from users and systems. This can either be done manually from the Service Manager portal, Operation Manager, Orchestrator or from Powershell. This means that you can cover quite high percentage of the Incidents (events) that are happening in your environment. Defining what is an Incidents and how to categorize the different areas, is out of this scope of this post. So in our demo environment I have done some simulations of some of the areas that is typically Incidents in an production environment.
So in the process of diving in to the Incident module we need to look at the different dimensions that are of interest.
How access the data
What we are going to do now is to look at how we can structure the Incident data together by using Power Query in Excel. Power query is an excellent tool for accessing the data and make transformations and calculations of that data.
So the Incident Dimension is outlined in the image below. We can see that the IncidentDimvw is kind of the master table that the others connect to. So IncidentDimvw is the table that we always are going to use as our connecting point.
The first thing we do is selecting the Service Manager DW database to connect to.
“New Query” -> “From Database” -> [From SQL Server Database]
Then we enter the name of the SQL server hosting the database and the name of the database DWDataMart.
You have the option to select SQL query statement, we are not going to use this.
The navigator opens up and shows us all of the Databases tables and views. Here we are going to do some modifications, we are only working with the views ( They got this Icon ).
1. In the search field type “Incident“. (All the Incident related views are available ends with vw).
2. Check the [Select multiple items].
3. Mark all the Incident view (total of 12).
In the right pane you can see a preview of the different rows and columns that are available in the different views.
Continue by clicking “Load” -> [Load to].
In the load to section select “Only Create Connection”. (This means that the data will not be loaded into Excel worksheet or the Data Model, there will only be a connection to the source).
Uncheck the “Add this data to the Data Model”. By enabling this checkbox the data from the DW will be loaded in to the local Data model of the workbook. We are going to do this later on, but depending on the amount of data you have in your DW, we will create the relationships first then load the data.
Now we can see all of the tables available in Workbook Queries.
Before we continue now we need to have a look at how this is structured.
We have the dimension “IncidentDimvw” that is the “master view” with the the other tables connecting to it. What we need to do is to connect each and every one to the IncidentDimvw, by merging the views together.
To do this select the “IncidentDimvw” Connection and select [Edit]. This will open up the Power Query Editor, where we do all of the data transformation.
In the Editor you can see all of the columns that are stored in the IncidentDimvw. When you look at the information in the table you ca see that some of the data are readable and others not that quite easy to understand. Later on we are going to filter out what we need and hide all the other that are not that important for us. Now let’s look at how we can start to merge the tables together.
looking at the Classification column, here we can see that the column contains an non readable rows. It shows the internal enum name, we don’t want that. If we look to the right we can see that there is another column called Cassification_IncidentClassificationID this rows there is a number that represents an ID in the IncidentClassificationvw that we loaded earlier. This means that we need to create the relationship between the tables by using the ID.
To create a relationship between the two tables select the [Merge Queries] from the menu.
First mark the Classificaion_IncidentClassifiationid column. Then from the dropdown menu select the IncidentClassificationvw (view) and select IncidentClassifiationid.
You can see at the bottom how many rows are matching the ID.
Do not select the “Only include matching rows” this will remove all the rows that don’t have the Classification configured.
In my environment , you can see that I have two rows that don’t have a value that matches the ID. For more precise reports and visualizations, the rows (Incidents in Service Manager) needs to be updated with an value (id) or removed in power query.
Click [OK] to load the merge.
When the load is done, there will be new column the table called “NewColumn”.
Here we have the option to select which columns we want to show in IncidentDimvw.
Select the “IncidentClassificationValue”. (This is the readable value for classification.) Click [OK]
The new column has been added and shows the Classification value for each of the Incidents. This column can now be renamed into something that is more readable.
Now we have added a new column with the right readable text for Classifications. Now we can hide the other Classification column, we don’t need them anymore.
So what we actually did now was to merge the two tables (views) together, by using an ID value. Then we removed the two columns and are left with the right readable information.
When we do different actions in the Power Query Editor, we are actually performing different commands (formulas) in the background. This is the Power Query Formula Language.
Under Query settings we find the APPLIED STEPS box, every step that you perform in Power Query will be “recorded”. This way you can see the different steps that has been applied.
By selecting a step you can either remove the step or change the formula.
When adding steps in power Query Editor, the formulas can be viewed in the Formula bar.
Here the power query formula for the specific step can be viewed.
To view all the steps added in the Workbook, we need to open the Advanced Editor. The advanced editor is the Power Query emulator. In the editor you can change steps and add new ones using the Power query formula language.
For more information about the Power Query Formula Language. Check out ->Learn about Power Query formulas.
In the editor all the steps that are performed will be added, by different lines.
Bellow we can see the steps performed when merging the IncidentDimvw with IncidentClassificationvw that we just did.
The commands used can easily be copied and shared with other workbooks. This way we don’t have to do every step manually.
Since we now only has merged one of the tables(views) together, we are going to do the rest by copying from a reference query I have added on Gist (Github).
The only thing you have to do is change the name of the database.
So let’s try this.
Open a new worksheet or clear the steps in the Advanced Editor. Make sure there is no existing code, then paste the code above in the editor.
Make sure the “No syntax errors have been detected.” then it’s all good. Again remember to change the database.
Then click [Done]
Back in the editor we can see all the merges and the new vales available. Now you can hide all the columns that you don’t want to have in your workbook.
Click [Close & Load] to exit the Power Query Editor.
Now it’s time to load the data into Excel, right click on the “IncidentDimvw” and select [Load To]. This will open up the load options.
By selecting the Load to [Table] option all the information will be loaded in to Excel worksheet. The data will then be added as standard columns and rows in a spreadsheet.
You can also see how many rows that has been loaded. Every time you refresh the queries, new data will be loaded from the DWDataMart database.
Selecting the “Only Create Connection” and enabling the [Add this data to the Data Model] all the data will be loaded in to memory and available for us to work further on with the data in Power View, Power Pivot and Power Map.
In this post we have looked at how we connect to the data in the Service Manager Data Warehouse with Power Query. In the next part we are going to look at how we can access the same data with Power BI Desktop.
Continue to Part 4: -> Service Manager Intelligence & Analysis: Part 3 “Access the Data with Power BI Desktop”