A modern Service Desk is driven by data. It has a huge amount of data, but that data is very hard to grasp for the human mind. The smart Service Desk Manager visualizes this data to make it consumable and prove assumptions with the visualized insights.

The Getting Started with Microsoft Power BI for your Service Desk article provides you with a Step by Step guide to gather and visualize your Service Desk data with the free Microsoft Power BI Desktop Tool.

Where can I get Microsoft Power BI and what does it cost?

First, it’s free! Yes, you read correctly – Microsoft Power BI Desktop doesn’t cost a dime. The product is on the market for about 18 months now and has been free from the beginning. It’s the natural evolution of Microsoft Excel and Microsoft PowerPivot to provide you and easy and intuitive tool to generate insights through your data.

Download it at https://powerbi.microsoft.com/en-us/get-started/ and get started for free. You just need a machine running Windows 7 or higher and that’s it.

Can I use Microsoft Power BI on my mobile devices?

Absolutely! Microsoft made a great deal on making data insights available on your mobile devices like your Apple iPhone or your Microsoft Surface.

This is because of the times when you need data. In my experience this is in the evening, sitting on the couch and coming up with a great idea. Yeah! But now I need to prove it and I have no intention jumping on my Notebook and crunch some numbers. I want to have it fast and convenient on my tablet. The Power BI Apps do exactly that.

But here comes the downside – you need an active Power BI Subscription to be able to consume it on your Apps, because they get it out of the cloud. Just give it a try with the free Office 365 trial at https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-signing-up-for-power-bi-with-a-new-office-365-trial/

Get to know your Incidents

Step 0 – Requirements

For this sample you need rights to read on your Matrix42 Workspace Management Database. This is usually named M42STORE or M42Production or U4USuite.

Step 1 – Open Power BI and connect it with your Matrix42 Workspace Management Database

After you downloaded and installed Microsoft Power BI, just open it and click on the little down arrow next to the big “Get Data” button. The select “SQL Server” as your DataSource.

Insert your Database Server Full Qualified Domain Name (FQDN) and enter the Name of your Matrix42 Workspace Management Database. In my case it’s M42STORE.

Import vs DirectQuery

I selected DirectQuery in this case which requests the data always directly from the database. Import would download the data into the Power BI file (.pbix) and let you further edit the data. We don’t need this right now, since we’re only extending the data but not altering it.

If you want to know more about it, you should check out this article https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/

Step 2 – Select the tables you need

The next one is simple. Just look into the dbo schema and search for SPSActivityClassBase, SPSActivityClassIncident, SPSAsset¥ClassBase, SPSComputerClassBase and SPSUserClassBase. You can even hit the “Select Related Tables” button on the buttom to get even more related tables, but those 5 are enough for now.

Next just click on “Load” on the lower right.

Now Power BI Desktop will create a connection to your data and get all the columns and the schema. It will also automatically create the relations that are modeled in the data for you in Power BI. This will come in really handy – you’ll see in a sec

This is the canvas you see after the wizards finishes. On the right side are all your controls and the tables with your fields. On the top you find some more advanced stuff we don’t need for now.

Step 3 – Check your Relationships

No you do not need to call your wife or husband now – I mean you can. It’s actually a good time to take a little break and call her!

Welcome back =) After letting the learnings settle a little, you can now click on “Manage Relationships” in the top navigationbar. As you can see Power BI did a pretty decent job and already found your relations between your data. At the moment Power BI can only handle one relationship per table pair and therefore only some of the detected relationships are active. If you try to activate another one for a table that already has an active relationship, you’ll her a warning.

Please activate the relationships SPSActivityClassBase (Initiator), SPSActivityClassIncident (Asset) and SPSAssetClassBase (AssignedUser) to be able to follow the article further.

Step 4 – Drag&Drop some data on the canvas

Next one is super simple. Just grab the Initiator field from the Fields section on the right in the SPSActivityClassBase Table and drop it on the white canvas in the middle of the screen. You can also just check the checkbox and a table is being created for you showing all values in the database for that field.

You can play around a little with the Visualizations section and its controls if you like.

Now search for FirstName and LastName in the SPSUserClassBase on the right side and drag them to the “Values” area on the right, next to the fields section. If you don’t see the Visualizations section out the filters than make sure that you actively selected the table you just created. The fields will automatically be added to the Filters as well which is pretty cool and spares you a lot of time.

Step 5 – Cumulate data

We now have a table with 3 columns with the InitatorID, FirstName and LastName. This shows us that the relationships are working and we get the Users from the Initiator relationship – awesome!

We now want to create a viz (visualization) for the amount of Incidents that have an active Imitator present in the system. So let’s go =)

Remove the Initiator field from the table by unchecking it in the fields area. The check the ID field in the SPSActivityClassBase with the table control on your canvas active – just click the table and you should see the handles to resize it. This shows you that the table control is active and you’ll have the Values and Filters section to configure, available on the right. Again, check the ID in SPSActivityClassBase and it will appear in the Values and Filters section. Click on the little down arrow next to it on the Values section and select “Count”.

Magical!

The table will change and show the amount of Incidents per User, but the total seems strange and there is also a blank value at the top or bottom depending on your sorting!? We’ll solve that right away

In the filters section open the FirstName and the LastName and click “Select All” and after that uncheck the “(Blank)” item. This will only show us Incidents with an active relation to an Initiator and therefore all the Incidents that have an Initiator set.

Step 5 – Pie Chart and relations between charts

We now want to create a Pie Chart from the table, create a new Bar Chart with all Priorities used in the Incidents and then make them clickable so they interact with each other.

Create a Pie Chart

Just click on the table and then on the right in the visualizations section on the Pie Chart icon – boom! That’s it and that’s where the “Power” in Power BI is coming from

Now we click the little brush roll between the visualization and values area to configure the look&feel of the pie chart. We’ll see the position of the legend to the right, change the legend name to “Initiator” and set the Title to “Incidents by Initiator”.

So now we create a new column, because just having the First- or LastName as the legend is pretty boring. Right-click on the SPSUserClassBase Table on the right and select “New column” from the dropdown.

Between the top menu bar and your canvas with the pie chart you now see a new line to enter code that says “Column = “. Here you fill it with some simple DAX code to concatenate the FirstName with the LastName Field.

FullName = [FirstName] & " " & [LastName]

If you want to learn more about DAX then you should check out this article https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-quickstart-learn-dax-basics/

Now let’s replace the FirstName and LastName field in the Filters, Legend and Details with the new FullName Column that we just created.

Create a Bar Chart

This time we create the chart not by dragging some data to the canvas, but by simply clicking on the bar chart icon in the Visualizations section over the Values section.

Now take the Priority field from the SPSActivityClassBase and put it to the Legend Area in the Values section after selecting the Bar Chart on the canvas.

Next put the ID field Incident Table into the Value Area and select “Count” in its dropdown in the Value area. We need to filter out again all Incidents that have no Initiator assigned by Selecting All in the FullName Filter and then unchecking the (Blank) attribute.

Because we now have an interactive Dashboard that can be synched to the cloud and has an out of the box relation, we’re super happy – right!? =) Just click on a bar in the bar chart and the pie chart will filter the data according to that – super cool!

I think you already figured that we love Power BI at Matrix42

Microsoft Power BI is a free tool to generate insights. Together with SQL Server 2016 you have an awesome and mighty Business Intelligence Solution that can even do almost the same things that the Power BI cloud has to offer. Due to the great feature set I personally switched completely from several other BI tools to Microsoft Power BI and actively participate in the Microsoft Power BI User Group Frankfurt that is hosted at Matrix42. You should really consider coming next time.

We’re organized in a meet up group at https://www.meetup.com/de-DE/Power-BI-User-Group-Frankfurt-am-Main/
Just signup and visit our next User Group =) We’re happy getting to know you and introducing you to Microsoft’s Power BI awesomeness!