I find that one of the most essential elements to success in IT service management engagements is presenting data that helps people make decisions. This is the case whether your decision maker is the business owner, the CIO, the IT director, or an analyst in the field. In order to provide information directly to the decision makers Microsoft has developed some fantastic tools for providing visualization to data that are available not just to System Center, but almost any data you can throw at them.
The two tools we are using most often are PowerView and an upcoming tool called PowerBI. In this series I will introduce you to the second of the two, PowerBI, which facilitates mobile decision making. We'll go through a two part series on demonstrating what PowerBI is and how it can be used in your System Center implementation.
PowerBI is Microsoft's mobile and HTML5 based decision making platform based in Office365. It's goal is to facilitate easy access to information for decision makers by surfacing data in the places they need it. To understand how useful PowerBI is, simply load up the Windows store (there will also be an app for the iPad) and search for "Power BI". It will install as a modern app, from which you can test various out-of-box examples, such as medals from an Olympics, or bar tabs. Here is a quick shot of an out-of-box example.
I thought,
"how can I use this with System Center?". In the first part of this series I will demonstrate how to take an existing System Center implementation and produce a PowerBI compatible data dump to open and demonstrate. In the second part of the series I will demonstrate how to utilize the out-of-box PowerBI gateway to bring data from on-premise as a data source for PowerBI data which requires no manual refreshing.
The first action is to create a PowerBI test instance. These instances are presently in preview, so you can't add it to an existing Office365 instance (unfortunately, as we are on Office365). You'll be able to add this later once it is out of preview. To do this, browse here, and select "
Preview PowerBI". You will be asked to setup an Office365 test tenant.
I created a tenant for myself using my Windows Live account, which was easy enough. I then configured in the administrative center the PowerBI features for my user (you need to assign the licenses).
This is easily accomplished with the Getting Started Guide.
I then loaded up the default shared document site. Now all I needed was some data.
I decided to start with something simple, so I queried my System Center Service Manager operational database content for the current tickets in the system. This could just as easily be done with the DW. I used a set of queries I developed a while ago which returned the active incidents in the system. Here is a
blog where I describe this process briefly.
Christopher Mank also had a
great series which took these queries to a deeper level.
I decided I wanted to get all the closed incidents in the system, their classifications, and their average completion statistics, so I ran this query in SQL Management Studio against the Service Manager database:
SELECT [Title_9691DD10_7211_C835_E3E7_6B38AF8B8104] as Title
,AssignedToUser.[DisplayName] as AssignedToUser
,AffectedUser.[DisplayName] as AffectedUser
,IncidentClassification.[DisplayName] as IncidentClassification
,IncidentStatus.[DisplayName] as IncidentStatus
,[Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794] as Priority
,[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] as CreatedDate
,[ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E] as ResolvedDate
,[Escalated_525F1F92_CEB3_079D_C0A5_E7A06AC4D6A5] as Escalated
FROM [ServiceManager].[dbo].[MT_System$WorkItem$Incident]
INNER JOIN [ServiceManager].[dbo].[Relationship] AssignedToUserRel ON
[ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AssignedToUserRel.[SourceEntityId]
AND AssignedToUserRel.[RelationshipTypeId] = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AssignedToUser ON
AssignedToUserRel.[TargetEntityId] = AssignedToUser.[BaseManagedEntityId]
INNER JOIN [ServiceManager].[dbo].[Relationship] AffectedUserRel ON
[ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AffectedUserRel.[SourceEntityId]
AND AffectedUserRel.[RelationshipTypeId] = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AffectedUser ON
AffectedUserRel.[TargetEntityId] = AffectedUser.[BaseManagedEntityId]
INNER JOIN [ServiceManager].[dbo].[DisplayStringView] IncidentClassification ON
[ServiceManager].[dbo].[MT_System$WorkItem$Incident].[Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA] = IncidentClassification.LTStringId
AND IncidentClassification.LanguageCode = 'ENU'
INNER JOIN [ServiceManager].[dbo].[DisplayStringView] IncidentStatus ON
[ServiceManager].[dbo].[MT_System$WorkItem$Incident].[Status_785407A9_729D_3A74_A383_575DB0CD50ED] = IncidentStatus.LTStringId
AND IncidentStatus.LanguageCode = 'ENU'
AND IncidentStatus.DisplayName = 'Closed'
OR [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[Status_785407A9_729D_3A74_A383_575DB0CD50ED] = IncidentStatus.LTStringId
AND IncidentStatus.LanguageCode = 'ENU'
AND IncidentStatus.DisplayName = 'Resolved'
WHERE DATEDIFF (DAY, [CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688], CURRENT_TIMESTAMP) < 30
The output looked like this:
I then created a PowerView dashboard within Excel by selecting the PowerView button. This generates a second sheet with the dashboard capabilities. You can create multiple PowerView sheets and they will be shown in your PowerBI site later
I then dragged the various elements onto the sheet that I was interested in.
After I was satisfied with the PowerView image I then uploaded it to my PowerBI site.
I launched the PowerBI Windows App
I then added my PowerBI SharePoint site in Office365 as a target by browsing
I browsed into my site and selected my report as a "favorite", which then causes it to show up on the main screen.
The output looked as follows, which looks very beautiful, functions great with a touch screen (and a perceptive pixel I might add) and will work on an iPad. This data could be easily refreshed monthly for executive reports.
In the next series I'll connect PowerBI to the on-premise world. Also, a collegue of mine Tom Driscoll will be building a blog on using PowerView with tabular reports.
Happy reporting!
Nathan Lasnoski