Published on July 29th, 2015 | by Fredrik Knalstad4
Service Manager Intelligence & Analysis: Part 4 “Access the Data with Power BI Desktop”
This is the part 4 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 3 “Access the Data With Power Query”
In part 3, we looked into Power Query and how we can use Power Query to connect to our data source and structure our data into something that can be used for visualization with either Power View, Power Pivot or Power Map. In part 5 we will go more into the process of creating visualization with these tools. In this post we are looking at how to access the same data with Power BI Desktop. (Power BI Designer).
Power BI desktop is built on the same foundation as power query, but it is a standalone tool. The PB Desktop is meant as an editor for Dashboards and Reports to be published to Powerbi.com (SaaS). Power BI Desktop is free and available to download from Microsoft (here).
In part 2 we looked at where the Service Manger DWdata is hiding and in Part 3 we accessed it with Power query. The data source is the same, but now we are going to perform the same transformation of the data with Power BI Desktop. We are still going to use the Incident module as an example. To have another look at the structure of the db schema, check out Part 2 again.
Automaticlly created relationships
Before we start with connecting to our data we need to change a configuration setting in Power BI Desktop. The great thing about the Desktop is that is automatically will try to create relationship on data that is loaded into the data model. Sometimes it will try to create many more relationships that are needed. Service Manager relationships is one of them, so for this exercise we will turn of the relationship detection to get a better picture.
Click “File -> “Options and Settings” -> “Options”.
Under current file -> “Data Load”. We will find he relationship settings. Deselect both of the options. Now every relationship that we create needs to be created manually. Click [OK]
Let’s start to connect to out data, go to “Get Data” and Select [SQL Server].
In the SQL Server Database, select the name of the SQL server that hosts the DWDataMart database.
We are not going to add an custom SQL Statement. Click [OK] when finished.
Add the service account that has the delegated rights to view the information in the database. The delegation of access is done on the SQL server and the account needs read access to the DWDataMart. Click [Connect]
The navigator opens up and lets us select the different view / tables in the database. Since we are still working with the Incident Module example, we are going to connect to the incident views in the db.
1. In the search area type “Incident”, this show all the results for Incident.
2. Select all the views that has the icon and ends with “vw”. ( In part2 you will find an overview over the Incident module tables and relationships).
When the selections has been made, click [Load] to import the data into Power BI Desktop and the Data module.
All the data will now be downloaded in to the data module memory bank. (aka Data Model)
There will be created a connection the different views that has been selected. Every time we click the refresh button, new data will be added to the data module.
When the load is finished, the data is ready to be worked with. On the left side there are three icons that represent the different working areas.
In the “Home” menu click on the [Edit Queries]. This will open up the Query Editor equivalent to the Power Query Editor in Excel.
On the left side all the different tables that we selected earlier are listed. We are going to continue to work with the “IncidentDimvw” that holds the information that we need. But some of the columns are non-readable for the human eye. So in order to get the right values in the different rows we need to merge different tables together to get the right value.
(In part 2 we looked at the db schema, check it out again for a better overview of the Incident module)
So the plan is to merge the different tables together by using unique values, then we can access information across the different table. In the example in his post we will start with the Incident module.
In the workflow bellow the different tables are merged together based on values that is represented in both the “IncidentDimvs” master table and the other facts tables.
Click on the “IncidentDimvw” in the Query Editor.
In the Combine area, select [Merge Queries]
1. Select the Classification_IncidentClassificationid column.
2. Select the “IncidentClassifiationvw” table in the dropdown menu.
3. Select the matching IncidentClassificationid column.
In the Join Kind leave the default, “Left Outer”. This will select all from first table and matching from second table.
When fished click [OK].
There will be a new column created in the right area of the table called “NewColumn”. Click on the arrows in the right corner, to be able to select the right columns.
Select the IncidentClassificationValue column, this is the column that will contain the right values.
Her is the default classification rows, out of the box.
Here is the new row, with the right value. Now we could just hide the other classification columns.
When we are doing constant changes to the data and how it is presented. The different steps are recoded in the “Applied steps” toolbox.
In the applied steps you can track the different changes that has been performed. You can also go back and change the previous steps, but not without a risk.
Removing or alter steps that is not the most recent can force the query to no work.
The different steps can be viewed in detail in the “Formula bar”. Here the command that made the change to step can be viewed and also changed.
To view the entire structure of the Query. Go to “View” then select [Advanced View].
In the Advanced Editor, the entire query can be viewed in behind the scenes commands. Every change to the lines her will affect the output of the query.
In the Image below we can see the steps of merge that we just did, and also the column we selected to be visible in the IncidentDimvw.
Instead of repeating the steps for every merge and transformation in this post, we are going to copy a query that I have created and uploaded t gist.github.com.
Select the entire query and copy the query into the Advanced Editor, it would be a go practice to clean the existing query first.
Click [Done] when finished. Make sure the “No syntax errors have been detected” is showing.
The new changes can now be seen in the new tables to the right in the table. Now we can clean up and remove the columns that we do not want to use when creating Dashboards and Reports.
When finished click [Close & Load]
The query will now be loaded and the transformation of the data will be performed.
Back in the data view we can see all the tables that is available. Since we are only going to use the IncidentDimvw when creating visualizations, we are going to hide the rest of the tables. Hiding the tables makes it much easier to work in the “Data” working area.
Select all of the tables except the IncidentDimvw, right click and select “Hide in Report View”, you may have to repeat the steps on every table.
When we now are going to start creating visualizations we only see the “IncidentDivw” that is the one we are going to work with.
When we click on the “Report” pane again, we can see that “IncidentDimvw” is the only table available.
In this part we looked at how we can use Power BI Desktop to connect to our data source and do a simple data merge. In the next post we will look at how we can create different visualizations of Service Manager data with Power View and Power Map.
Continue to Part 5: -> Service Manager Intelligence & Analysis: Part 5 “Visualization with Power View”.