SystemCenter 1

Published on July 16th, 2015 | by Fredrik Knalstad


Service Manager Intelligence & Analysis: Part 1 “Where the shoe pinches?”

This week…
I am finally able to start of this blog series of SCSM Analysis with MS BI. But between daily operations (work) and changing diapers on my son. Things often slip between time available and everything that makes the day fly away. Speaking of time flying away this week I have followed the New Horizons journey to Pluto. It’s actually crazy to think about that we are able to collect information for a planet/ “big rock rock of ice” 4.28 – 7.5 billion kilometers (constantly changing)  and then do exiting analysis with the data. Do they use Excel and PowerPivot for the analysis? Maybe we will get a power map of Pluto? Who knows.. But one thing that is sure is that following the shuttle and NASA’s expedition to Pluto and the Kuiper belt is exiting. So in your next coffee break check out  New Horizons: The First Mission to the Pluto System and the Kuiper Belt.


Back to the topic..

After working with Microsoft Data center solutions for quite some years now, I have seen how companies implement new platforms and solutions almost as fast as the speed of the “New Horizon”. Of course this is just a figure of speech, but you get the point. Today companies invest in solutions with one thing in mind “To be Effective” $Effective  = Save money, and save money means a gold star for you. But to actually save the money and to be effective, we need to find those areas where the shoe pinches, right?.

The Framework..

The need to have more depth analysis of the data that has been gathered over time in service Manager, becomes extremely important in order to be more effective and to optimize the processes and services that is being delivered.  Through analysis of the data in Service Manager you will get a new insight of how your users are using the system and potentially find areas that are in the status of improvement. SCSM ( Service Manager) is built with best practices, such as those found in Microsoft Operations Framework (MOF) and Information Technology Infrastructure Library (ITIL). Which includes processes for incident and problem resolution, change control, and asset lifecycle management. In those modules/ processes is where we can find our GOLD and the information that we need to do further improvements.

Collecting the data..

Service Manager is a complex system when it comes down to the different parts and how the data/information is structured. Since data is gathered from other infrastructure systems,  like, SCCM, SCOM, AD, Exchange etc.. Objects or Configuration Items in SCSM contains a lot of information. Since the information about a user object (CI) can come from different systems, the information is merged together by SQL relational tables. Information is then connected through other relationships tables like Incident Request or Service Requests and so it goes on. So the amount of information gathered can become extremely large and complex for an untrained SQL eye.
For more detailed information on Understanding the Service Manager data structure check out part 2 at the bottom of the page.

To get the information out of Service Manager, reporting services and SQL queries can be used to collect datasets to generate Reports. Reporting builder can be used to create custom reports. The drawback is that Reporting services is rather limited in functionality and flexibility when it comes to create good dynamic reports. What to do?..

Introducing Power Query & Power BI Desktop

Since most analysis is done in Excel, (Actually Excel is the most used tool in the world to do analysis of data). The preferred method to collect information about different sources is through Excel for most users. Either you are a Analyst or Technician. So the release for Power Query add-on for Excel 2013 (got Excel 2010?, it’s time for an upgrade..) enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources. “It’s actually so good, so it’s default integrated into Excel 2016”.  In Summer (2015) Microsoft also released Power BI Desktop, a free data visualization tool for creating reports and dashboard then share them on the  Power BI service ( With both the tools in-place the power of self-service BI is starting to shape up.

With Power Query & PowerBI Desktop you can:

  • Find and connect data across a wide variety of sources.
  • Merge and shape data sources to match your data analysis requirements or prepare it for further analysis and modeling by tools such as Power Pivot and Power View.
  • Create custom views over data.
  • Use the JSON parser to create data visualizations over Big Data and Azure HDInsight.
  • Perform data cleansing operations.
  • Import data from multiple log files.
  • Perform Online Search for data from a large collection of public data sources including Wikipedia tables, a subset of Microsoft Azure Marketplace, and a subset of
  • Create a query from your Facebook likes that render an Excel chart.
  • Pull data into Power Pivot from new data sources, such as XML, Facebook, and File Folders as refreshable connections.
  • With Power Query 2.10 and later, you can share and manage queries as well as search data within your organization.

Data Sources

  • Web page
  • Excel or CSV file
  • XML file
  • Text file
  • Folder
  • SQL Server database
  • Microsoft Azure SQL Database
  • Access database
  • Oracle database
  • IBM DB2 database
  • MySQL database
  • PostgreSQL Database
  • Sybase Database
  • Teradata Database
  • SharePoint List
  • OData feed
  • Microsoft Azure Marketplace
  • Hadoop File (HDFS)
  • Microsoft Azure HDInsight
  • Microsoft Azure Table Storage
  • Microsoft Azure Blob Storage
  • Active Directory
  • Microsoft Exchange
  • Facebook

As you can see from the list above Power Query / Power BI Desktop can be used to connect to a lot of different data sources. Which means we can do a lot of analysis against different platforms.  (Yeehaa, this means many future posts on these two babies!)

So let’s get down to business and look at how we can use the tools with SCSM to extract our data and start doing some analysis. But to do this we need to understand more on how the data is structured in Service Manager and where the data is gathered.

For more information on Power Query. Check out  Power Query Q&A
For more information on Power BI Desktop. Check out Power BI Desktop

Continue to  Part 2: -> Service Manager Intelligence & Analysis: Part 2 “DW drilldown”

Tags: ,

About the Author

assist companies in deploying, automating and designing management solutions based on Datacenter | Intelligence | Automation. The latest years I have been working deeply with System Center and developing integrations with systems outside of System Center.This includes planning, design, implementation and using self-service Business Intelligence.

5 Responses to Service Manager Intelligence & Analysis: Part 1 “Where the shoe pinches?”

  1. Pingback: Service Manager Intelligence & Analysis: Part 2 ” DW drilldown” |

  2. Pingback: Service Manager Intelligence & Analysis: Part 3 “Access the Data” |

  3. Pingback: Service Manager Intelligence & Analysis: Overview |

  4. Pingback: Service Manager Intelligence & Analysis: Part 4 “Access the Data With Power BI Desktop” |

  5. Pingback: Service Manager Intelligence & Analysis: Part 5 “Visualization with Power View” |

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top ↑