Published on August 21st, 2015 | by Fredrik Knalstad2
Service Manager Intelligence & Analysis: Part 5 “Visualization with Power View”
This is the part 5 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”
Service Manager Intelligence & Analysis: Part 4 “Access the Data with Power BI Desktop”
In part 3 and 4 we looked at how we can access the data in both Power Query and in Power BI Designer. When working with data and these two tools, we can clearly see that they have the same foundation, when it comes to gather and transform the data. This makes it easy to get started with one tool and move on to the other. One question you may have been asking yourself already is “Why two tools that do almost the same thing?”. Well that answer can be a bit unclear but hopefully over the next posts the answers will be more clearly.
In this post we are going to dive into the world of visualization, by exploring the possibilities in the tools. Working with data transformation is fun, but creating visualizations and see that data come to life is awesome. So let’s look at how we an attack the data with Power View.
The data we are working with is the same as in the earlier posts, the Incident module and the IncidentDimvw table. I recommend that you look at the earlier posts on how we fished out the data of the Service Manager DW, and prepared it for cooking by using Power Query.
Fun facts first, where is Power View?
Power View can be found in the “Insert Ribbon” in Excel, if you cannot find Power View you need to enable it ( it’s an add-on).Guide on how to enable it can be found here :Create a Power View sheet in Excel 2013.
In Excel 2016 (Preview) is nowhere to be found. There is a lot of discussions on this topic, “what is happening and so on”. I’m not going to go into that now, but I’m expecting some changes to Power View in the future. The thing is that Power View is not gone in Excel 2016 (Preview), it’s just removed from the Ribbon menu. So in order to use it we need to add it back again.
Guide on how to enable it can be found here: ->Turn on Power View in Excel 2016 for Windows
Power View is slow….
Well yes, if you have a machine that has HW limitations when it comes down to processing and storing large amount of data in memory, Power view will have slow response. When working with large amount of data (in Data model) and doing analysis of that data, it’s important to be working on a computer that has a fast CPU and in my opinion minimum 8 GB of memory. (When working with service Manager DW I upgraded to 16 GB). That will give you much better performance and an experience when working with the interface. Power View is built on Silverlight, enough said about that…
“I got the data what is next?”
Before we dive into sea of visualization, we need to remember one thing. Structuring the data in both Power Query and in the Power BI Designer, is the key to get good visualizations and graphical structures in both tools. You can always go back and do changes, but having a good structure of your data makes it easy to work with the data and visualize it the way you want.
- Service Manager DW IncidentDimvw has a lot of columns, the columns that you don’t need, remove them in the Power Query editor. Makes it so much easier to find the different columns and less data in the data model.
- Always make sure the right format is configured for the cells you are going to use. Example “1234” should be formatted as number and not text. You will experience this when working with the data in Power View.
- Good practice when working with many columns is to rename them into something that represent the value. When loading the data into Power View the columns will look much cleaner.
When we have connected to the data source and are done with the transformation, we are ready to visualize the data. (You will of course go back and forth to add new measures, when you need one).
To launch Power view. Go to [Insert] -> [Power View], this will add a new power view pane in your Workbook.
The first thing we need to make sure is to load the data into the data model, if no data is in the data model, the data cannot be used in Power View ( or Power Map, Power Pivot).
To load the data, right click on [Load To] and select “Add this data to the Data model”.
When loaded to the data model, the IncidentDimvw and the columns will be available in the Power View Fields. The data is now ready to be used. Here we can see how easy it is to work with the different columns when the titles are structured.
Incidents and their Classification
Since we have merged the tables together, every incident has a classification connected to it (Incident.Classification column). To start with we are going to do a count on that and visualize it with “Stacked Column” design.
In the IncidentDimvw, select Incident.Classification (I have renamed) click the arrow and select [Add to Table]. The Incident.Classification will now be added to the Power View tab. Now the view will only show the classifications available, no count has been done so far.
Click on the arrow again, this time click on the [Add to Table as Count], now the data will be summarized and the values for the different classifications will be added to under count of Incident.Classifiations. So now we have a count of the Incidents and their classification.
The next step for us is to create a Stacked Column out of this table, to do this mark the table and select DESIGN – > Column Chart -> [Stacked Column]. Drag out the corners to resize the visualization.
By default,the Stacked columns don’t look that user friendly so let’s pimp it up a bit.
To show the labels click on LAYOUT -> Data Labels -> [Show]. Labels for the different classifications will now be visible.
If the text size is to large and it don’t look right, select DESIGN – > [Resize] the text.
The title might not be the one we want, to remove it select LAYOUT -> Title -> [None].
To add our own title, click outside the table and select Power View -> [Textbox]. This will add a textbox to the pane, type a text and drag it where it fits the best. Adjust the size and font to fit your needs.
So now we are look in at a simple visualization of All the Incidents by the count of Classification.
The data we have added to the visualization is represented in the Power View fields. Here you can see that it was the Incident.Classification column that we did a count on to create the values. The AXIS is represented by the same field.
From the analysis perspective we can see that most of the Incidents are related to Networking Problems. “Hmm.. need look into that”.
The next thing we are going to do is to add a third option to the Stack Column called “legend”. By adding a legend we are able to get more information out of the same visualization on the data.
We are now going to add Incident.TierQueues (Support Groups) to see the different support groups that are assigned to the different Incident classifications.
In the Power View Field, click on Incident.TierQueues, and select [Add as Legend].
From an analysis perspective again, we can see that there are many Incidents that has not been assigned to support group. hmm.. Need to look into that.
In the Power view pane click out of the Stacked column (don’t mark it), this time we are going to add a new visualization that will be linked to the existing one we have created.
The field that we are going to use now is the Incident.Status. In the Power View Field select [Add to Table] then repeat the step again but this time select [Add to table as count].
The table will now show the Incident status values and the count of them.
Change the table to a “Stacked Column” again. DESIGN -> Column Chart -> [Stacked Column]. Now we can see the status of the Incidents, how many is Active and Resolved.
Since we merged together the information, the two visualizations are linked together. Now when click on the different bars we can see that the other shapes are adjusting their values. This is an excellent way of showing detailed information in the big picture. This works vice versa. By clicking on the IT Operators support group we can see detailed status of that specific support group.
Since we now know the classification of the Incidents and the status, it would be nice to know where the incidents come from. Are they registered in the portal, phone or from Systems like SCOM (System Center Operation Manager). To find this out we will add a new visualization to the pane, this time we will use the Incident.Source column.
Select the Incident.Source then select [Add to Table] repeat the same step and select [Add to Table as Count].
The table will now show the count of the Incident.Source. This time we are going to select a Pie chart, select “DESIGN” -> Other Chart -> [Pie].
In the pie chart the Incident.Source is shown, only this time by different slices.
Looking at the Power view pane now we have a dynamic presentation of our data, that is easy to use for deeper analysis of how the data is handled.
The pane we have created is quite basic, but basic is a good starting point when working with the data in Service Manager DW.
Maps and geographical data
When working with Incident module you can extract information about who the “AffectedUser” of the incident is. The user information is extracted by the AD connector in Service Manager. The information about the user object is then loaded into Service Manager as CI object. This way we have information about the different users that register Incidents in Service Manager and detailed information about them, as the country and city.
With that data we can create geographical visualizations of that data. The concept is the same, we add that table to the Data model and we are ready to create visualizations.
Select the column that contains the geographical data (In this example country). Select [add to table] then repeat the step again, this time use [Add to table as count].
The table will then be countries and the count of the Incidents and the affected user’s country.
Select the table and in the ribbon click on DESIGN -> [MAP].
The table will then shift to a Bing Map, with different circle sizes on the countries depending on the number of incidents that has been registered from that country.
Refresh of Data
The data is loaded into the data model. New data will be added and the visualizations will be updated. To refresh the data while you are in Power View, click on the Refresh button on the Power View ribbon. This will load new data from the DW into the model. Time it takes depends on the amount of data.
In the right corner of the workbook a status will appear when the refresh data is in progress. The refresh of data can also be done in Power Query.
The requirement of performing a refresh of the data is that the computer performing the refresh has connection to the SQL server hosting the DW. The user that opens the workbook also need to have read access to the DW database. More on refresh of data in a later post.
Important to remember that..
..All the related tables that has been used in the merge of the IncidentDimvw must be loaded to the Data Model, this means you need to enable [Load to Data Model] on every table that has been merged. This is because the values in the different tables needs to be loaded in to the data model to view it in Power View.
Share Power View reports
When we create an Excel 2013 workbook with Power View sheets, you can save it to Office 365 or to SharePoint Server 2013 on-premises. Workbook readers can view and interact with the Power View sheets in that workbook in either location. In PowerBI.com we have the ability to upload our Workbooks with our visualizations. Then we can edit and view them with Excel Online, a new feature that has been added in PowerBI.
For more information on how to manage Excel files in PowerBI check out -> Bring whole Excel files into Power BI
Her is an overview of the different sharing options available.
|Workbook is stored||Host configuration is||Workbook is opened in||Power View sheets are|
|On client computer||—||Excel 2013||Editable and interactive|
|On premises||In SharePoint view mode (SharePoint Server configured to render workbooks by using Excel Services)||Excel Services||Interactive|
|On premises||In Office Web Apps Server view mode (SharePoint Server configured to render workbooks by using Office Web Apps Server)||Excel Online||Not visible|
|In Office 365||SharePoint Online||Excel Online||Interactive|
|On OneDrive||—||Excel Online||Not visible|
|PowerBI.com||Powerbi.com||Excel Online||Editable and interactive|
I will post more on the different types of visualization that can be useful to have in Service Manager under the tag #SCSM. Here I will also cover the other modules that are available in the product.
In this post we looked at how we can connect to the different data and create simple visualizations out of that data in Power View. In the next post we are going to look into how we can do the same in Power BI Desktop and share that data with PowerBI.com.
Continue to Part 6: -> Service Manager Intelligence & Analysis: Part 6 “Visualization with Power BI Desktop”