PowerBI and System Center Service Manager: Mobile Decision Making

Author by Nathan Lasnoski

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. 1. PowerBI Sample   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: 10. Excel Demo   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 12. PowerView   I then dragged the various elements onto the sheet that I was interested in. 13. Powerview   After I was satisfied with the PowerView image I then uploaded it to my PowerBI site. 14. PowerBI Upload   I launched the PowerBI Windows App 15. Launch PowerBI App I then added my PowerBI SharePoint site in Office365 as a target by browsing 16. Find your site I browsed into my site and selected my report as a "favorite", which then causes it to show up on the main screen. 17. Mark as Favorite 18. Save as Favorite   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. 19. Sample PowerBI Output 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
Author

Nathan Lasnoski

Chief Technology Officer