Power BI for SCSM

As part of delivering our managed services at cubesys we are using System Center Service Manager for incident logging and request management. We wanted to enhance the customer experience by providing visually stunning reports and dashboards so we started to investigate at how we could leverage Microsoft Power BI together with Service Manager SQL Analysis. There are a few advantages to using the Service Manager SQL SSAS multidimensional objects vs the traditional Service Manager database such as:

• Cubes
• Perspectives
• Measure Groups
• Measures
• Dimensions
• Dimension Attributes
• Hierarchies including Parent Child

 Getting Started

This blog post is going to cover the requirements needed to connect Power BI to your SCSM environment and will document the steps to create you own Power BI dashboard:

• First make sure you have a Power BI Pro account. If not you can easily create one at http://app.powerbi.com
• You will also need a Power BI Enterprise Gateway to connect from Power BI Online to your SQL Analysis Server
• Next download the Power BI desktop app and sign in with you Power BI account. (Make sure you always have the latest version of Power BI desktop as the product team is regularly improving and updating Power BI)

Once Power BI desktop is open click on Get data and select SQL Server Analyses Services Database. You will have to provide your server name and credentials

As you can see in the screenshot below I was unable to connect Live to my SQL Analysis Server. I could only use the import functionality which means I need to select, one-by-one the different tables I want to import and, most importantly, define the relationships.  Which is really a tedious job….

 

Apparently you can only use the “Connect Live” capability if you are running SQL Enterprise and not if you are using SQL Standard. Power BI issues DAX queries against SSAS and this is not supported in Standard (until SQL Server 2016). So if you hit this issue and need “Connect Live” please upgrade your SQL to Enterprise.

Limitations of Analysis Services live connections

You can use a live connection against tabular or multidimensional instances.

Server version	Required SKU
2012 SP1 CU4 or later	Business Intelligence and Enterprise SKU
2014	Business Intelligence and Enterprise SKU
2016	Standard SKU or higher

• Cell level Formatting and translation features are not supported.
• Actions and Named Sets are not exposed to Power BI, but you can still connect to multidimensional cubes that also contain Actions or Named sets and create visuals and reports

Once you have upgraded to SQL Enterprise you will be able to Connect Live as seen in the picture below:

Select the following cubes to import Incident related information

Build your dashboard

On the right hand side of your Power Bi desktop app you will see the dataset and the different dimensions and measures:

You will see different types of data in your dataset:

Measures, measure groups and KPIs

Measure groups in a multidimensional cube are exposed in Power BI as tables with the ∑ sign beside them in the Fields pane. Calculated measures that don’t have an associated measure group are grouped under a special table called Measures in the tabular metadata.

Parent-child hierarchies

Multidimensional models support Parent-child hierarchies, which are presented as a hierarchy in the tabular metadata. Each level of the Parent-child hierarchy is exposed as a hidden column in the tabular metadata. The key attribute of the Parent-child dimension is not exposed in the tabular metadata.

Custom Power BI visuals

To be able to use the “date” and “calendar” fields in Power BI I had to import a custom Power BI visual. Please have a look here for the all the custom visuals that are available https://app.powerbi.com/visuals/ . Make sure you download the following custom visuals as you will need them in this report:

• Timeline
• Donut Chart
• Circular Gauge

Design your Report

Click on the Timeline visual to add it to the report and add the field labelled Calendardate

Add a table visual and add the following fields

• Incident Dimension
  • IncidentDIm_ID
  • Title
• AffectedUserDim
  • Company
  • Display Name
• IncidentDim_IncidentClassification
  • IncidentClassificationValue
• IncidentDim_IncidentImpact
  • IncidentImpactValue
• IncidentDim_IncidentSource
  • IncidentSourceValue
• IndicentDim_IncidentStatus
  • IncidentStatusValue

Next we want to add a graphic showing us the amount of incidents per classification.

Add a DonutChart visual and add the following fields:

• Legend
  • IncidentClassificationValue
• Primary Measure
  • IncidentAffectedByUserCount

Final result

After a few hours digging around and playing with the visuals this is the final result:

Pretty awesome!

Publish to Power BI online

Now that we have created a report we can publish it to Power BI Service. I’d like to remind you again that you need to install and configure “Power BI Enterprise Gateway” on a machine in your network first.

• Save the current Power BI Desktop report
• Click “Publish” button form ribbon
• Click “Sign in”

Once published to Power BI online you don’t need to configure a refresh schedule if you are using “Connect Live” to query SSAS. Please keep in mind you need Power BI pro to be able use Live query as described here: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/ The visualizations in your dashboard will query the on-premise Power BI gateway to connect to the local SSAS.

With the On-premises Data Gateway, you can issue queries from Power BI to your on-premises data sources. When you interact with a visualization, queries are sent from Power BI directly to the database. Updated data is then returned and visualizations are updated. Because there is a direct connection between Power BI and the database, there is no need to schedule refresh.

Thanks,

Alex