Microsoft IT improves business processes with a federated Business Intelligence Portfolio

Business Case Study

Published: January 2014

The following content may no longer reflect Microsoft’s current position or infrastructure. This content should be viewed as reference documentation only, to inform IT business decisions within your own company or organization.

Microsoft Information Technology’s Service Deployment & Operations organization established a federated business intelligence solution that provides application owners early insight into potential operational risks and improves the security, availability, and reliability of their applications. Learn how Microsoft IT saved time, reduced costs, and increased productivity by enabling this solution.


Download Business Case Study, 439 KB, Microsoft Word file





Products and Technologies

Microsoft IT supports hundreds of application owners within Microsoft. Application owners don’t like down time and always want notice when something could or will cause service disruption to their application. Microsoft’s Service Deployment & Operations organization had multiple monitoring services and tools that were able to show outages and potential failures.

Service Deployment & Operations deployed a federated Business Intelligence solution within IT that improved application availability and reduced service outages for application owners.

  • Improved data sharing
  • Easier for user to consume data
  • Increased efficiency
  • Scalable platform
  • Increased ROI on BI
  • Unified data retention
  • Improved application availability
  • Standardized processes
  • Microsoft System Center 2012 Configuration Manager
  • Microsoft System Center 2012 Operations Manager
  • SQL BI Services including Analysis, Reporting and Integration services
  • Microsoft Excel Power Pivot
  • Microsoft SharePoint Power View and Power BI


The Microsoft Information Technology (Microsoft IT) Service Deployment & Operations organization manages 1,200 line-of-business applications and 45,000 servers—and the organization utilizes numerous monitoring systems to identify and gather business intelligence data. The business intelligence, problem management, and patch management functions of the Service Deployment & Operations (SDO) organization provide business intelligence reporting, identify potential issues that might lead to outages, and flag potential service failures. Specifically, business intelligence harvests application and server data to provide an overall view of the data center footprint and also completes analytics and reporting. Problem management proactively conducts investigations that minimize potential risks for service disruptions, engages when there is a service disruption, and completes root cause analysis to determine the single point of failure that caused the disruption. Patch management reports on which servers require patches, tracks issues on unpatched servers, and drives remediation actions through application operational teams, while measuring compliance over time.

Within the Service Deployment & Operations organization, the overarching goal is to deliver business insight to application owners who can use it to identify trends, gain awareness into the operational state of their business, and plan for the future. However, with multiple data points and different monitoring systems at play, ensuring business intelligence (BI) systems generate clean, consistent, reproducible, and actionable data presents its own set of challenges. Specifically, those challenges include:

  • Disconnected and scattered data:  As organizations within Microsoft grew so did their need for speed, agility, and access to their business intelligence—which meant organizations often developed their own BI solutions to solve for their needs. As organizations developed and incorporated cross-functional efforts, they found that without a consolidated business intelligence solution obtaining data from various sources and locations (each with their own unique business rules and processes) presented a time-consuming and resource-consuming challenge. The SDO teams responsible for gathering data and delivering end-to-end analytics were faced with two major issues. First, gathering data required contact with multiple source owners, which presented trust factor issues around who needs and who has authorization to access data and why. Second, with a vast amount of data coming in from disparate sources, it became increasingly difficult to distribute related, actionable data—which meant that valuable information may be missed altogether.

  • Lack of adherence to standards: As business needs and requirements evolve, teams must respond quickly to support their organizations. While this level of support allowed the business continuous foward momentum, it also meant that teams would inadvertantly stray from standards concerning how to apply and use the data. When adherence to standards becomes compromised, the potential to calculate or to infer knowledge on the same topic from different people or teams with varying results manifests. In these situations, the risk is that data may not be organized or presented consistently, and business owners must know with certainty that they are accessing the best data for the types of questions they need to ask. As different people interacted with the data, it was possible that they could interpret different results from what should be similar data sets. Not only would different people interpret information concerning the same data differently, but any model whose data is tied to a specific data set that is protected and not accessable by others tends to not be trusted.

    • As an example, SDO oversees patch management application processes and reporting.  However, because they don’t own the servers they manage, it was difficult to indentify the right contacts to help them gather the data needed to investigate and report on patch requirements. This investigation process was time consuming and required the patch management team to collect data from multiple organizations about who owned which servers, what was on the server, and the time frame for applying the patches. Without an agreed-upon set of standards, it was difficult to share the information with the organizations that owned the servers and to agree on how to approach application of the patches.

    • The SDO Problem Management team works with the various business unit specific problem management teams across the Microsoft IT organization. Each of the business units have their own processes, priorities, and requirements. Without a commonly shared set of standards around compliance and defect management, SDO was hampered in their attempts to improve the experience of our customers through the management of ticket volumes, critical events, and managing identified defects out of the operational environment. In the absence of consistent reporting and standards, the business units likewise were unable to see the impact of efforts conducted on their behalf within SDO Problem Management, resulting in a lack of interest in driving compliance and defect management.

  • Limited data re-use and reporting: Without a consolidated business intelligence solution, it was challenging for SDO to provide reusable data and reports, to generate appropriate report views, and to modify historical data to accommodate each user’s needs.


To alleviate the situation, SDO invested in centralizing business intelligence across teams by developing a federated Business Intelligence portfolio. A federated BI portfolio unites the disparate BI solutions created by individual teams and provides users access to all BI data through a single portal with common branding and processes, even though technically the disparate BI solutions remain their own entity. As SDO embarked on developing a federated business intelligence portfolio, they focused on consolidating information sources in real time, and creating a single data stream for users to access solving the usability and discovery problems for users. This solution was to achieve the following goals:

  • Deliver one, easy-to-use, federated, consolidated source that is avaible in real time, complete with automated business rules, and reporting that includes access to history and trends.

  • Develop a framework that is reusable, extensible, automated, and based on industry best practices.

Solution Architecture

The solution architecture defines the processes and hardware infrastructure required to support the BI portfolio.

  1. Sources SQL Server Integration Services is used to extract and scan data from the available data streams, such as, System Center 2012 Configuration Manager, System Center 2012 Operation Manager, and custom scanning tools and ticketing systems. Collected data is then scanned for any potential, implied risk factors and is stored in a server running SQL server.

  2. Staging Environment (STG) After the data is extracted, it moves to a staging environment where SQL Server Integration Services are employed to clean and transform data.

  3. Operation of Data Store (ODS) The operational data store (ODS) functions as a near-real-time data store. The ODS is responsible for exposing clean data, determining the quality of the data, and identifying if it is about a particular point in time.

  4. Knowledge Base Store (KB) The Knowledge Base store is a data model for end users, and the store provides history and trending (on accumulated) data and ad-hoc analytics for end users, power users, and downstream systems. History is established once per day and combines the available real-time sources with the accumulated data.

  5. For example, the Servers table tracks data about servers from all sources throughout the data center. The KB employed SQL Server Analysis to evaluate the real-time and historical data and completed analytical reporting for problem management, patch management, and other teams.

  6. Reporting and analytics To deliver reporting and analytics, Microsoft IT uses SQL Server Reporting Services to create standardized reports for near-real-time, historical and trending data. Reporting and analytics allows users to pull data directly from the operational data store and the historical and trending data to create their own customized reports.


SDO’s federated business intelligence portfolio provides users with “one version of the truth” to source all of their business intelligence information from. This allows users to access trustworthy data that has been extracted, cleaned, and correlated through one source that is comprised of a dashboard, SQL API, Cubes, and a portal. The dashboard provides users with self-serve, standard reports and custom reports. SQL API enables automation, Cubes provide ad-hoc analytics, and the Portal delivers contextual information and navigation. A portfolio of this nature can make data more actionable, reduce the number of defects, improve business processes, and increase compliance.

With a federated business intelligence portfolio, we can use the data to tell clients how much capacity we delivered, how many apps are in the environment, what those apps are, which ones are critical, what the profit center codes are, and how those relate to all the inventory we have in the space. - Tom McCleery, IT Service Operations Manager, Microsoft Corporation

Making Data Actionable

Using the federated BI portfolio, SDO has been able to make significant strides in their effort to make data actionable. The following examples provide two scenarios in which SDO used BI data to drive action:

  • The SDO Problem Management team used the SQL API to develop a specific automation requirement for a customer that has been able to reduce the time spent on data gathering to support investigations.  Now Problem Management can access data, connect the components needed, and focus on the investigation instead of spending time gathering data.

  • The SDO Problem Management was able to drive down Ticket to Asset Ratios (TTA) across Microsoft IT by showing that servers with higher levels of non-compliance, also generated higher ticketing rates. The configuration data showed that non-compliant servers had a Ticket to Asset ratio of roughly twice that of compliant servers. This also drove alignment of interests between SDO and its customers around compliance and around reduction of disruptions to their service.  

Driving Defects Out of the Environment Through Proactive Engagements

Since implementing the BI portfolio, the Problem Management team has been able to better anticipate and identify issues, resulting in the removal of thousands of defects from the environment.

Figure 2: Server/Defects vs Tickets

Figure 2: Server/Defects vs Tickets

The Servers/Defects vs Tickets chart (above) illustrates a decline in defect volume in year-over-year comparisons and represents a 23 percent drop in the total number of tickets, or an average of 29 percent drop in the monthly Ticket to Asset (TTA) ratio. It is also important to note that at the same time server population has grown.

Business intelligence reporting also allowed SDO to pinpoint the relationship between defect rates and the production of incident tickets. Using BI reporting, SDO developed a report that grouped servers based on the number of defects reported in a Server Health Check database, and SDO then calculated the ticket to asset ratio for each of those server populations. SDO refreshes this report monthly—and for over year, the report has reflected the same reality. Increased detection of defects in configuration management has translated into increased support ticketing.

The Correlations of Risk to Ticket Volume per Asset chart (below) illustrates how SDO is able utilize BI data to share information about the number of support tickets filed in relation to the number of defects per server. By categorizing the number of defects per server, SDO can track the ticket to asset ratio and identify the servers with the most defects also drive the most support ticket volume. SDO can then share that data with the server owners and work proactively to reduce the defects in those servers, thus reducing support ticket volume.

Figure 3: Correlation of Risk to Ticket Volume per Asset

Figure 3: Correlation of Risk to Ticket Volume per Asset

Driving Continuous Improvements Through Improved Business Processes

Using data collected from the BI portfolio, Microsoft IT has been able to identify gaps and to develop specific business processes that drive continuous communication and improvement in the environment as a whole. Microsoft IT has also driven continuous improvement efforts by applying data from one incidence of a breakage in the environment and extending it to the larger environment to drive out defects. As a result, Microsoft has developed three communication programs:

  • Quarterly readiness: This recurring customer engagement focuses on communicating risk and sharing data associated to defect profiles for servers associated to applications critical to quarter financial close. They also schedule work around keeping critical server infrastructure up to date with regard to identified risks and configuration compliance.

  • Deep dives: This customized engagement takes the activities of quarterly readiness and extends it to the server footprint for customers who need a more in-depth risk assessment. The engagement examines not only the server configuration risk but also its dependencies on other areas of infrastructure from which SDO has unique access to data. The focus is on identifying risks that are then prioritized and mitigated.

  •  DPMO: (Defects per Million Opportunities) This program takes the risk data described above, and makes it available to all customers across their entire production server footprint to drive their own remediation efforts. SDO provides a blanket calculation of their compliance risk, and they effect change to improve their calculated DPMO score. The effort focuses on enabling our customers to succeed independent of our targeted efforts.

Using BI to Manage Patches

With the BI portfolio, the Patch Management team can determine if and when there is a new patch available and the number of affected servers. The team can also provide reporting that analyzes servers running older versions that are breaking systems or patches that have been superseded by new patches. This enables the team to work proactively to avoid risk in the future by helping to determine which servers may be at risk. Then, Problem Management can use those reports to work with the owners to remove those risks and to prepare the environment to receive the new patches.

Improving Compliance Through the Stay Current Program

The Stay Current program measures compliance to the Microsoft support life cycle and provides organizations with information they can use to plan upgrades in advance, before becoming non-compliant.

SDO supports the Stay Current Program and models what Microsoft asks its customers to do by staying current on versions of the OS and SQL Server. The new BI solution has reduced the impact of OS and SQL Server versions reaching end of service. Data shows that since FY11, Microsoft IT has steadily increased OS and SQL Server version compliance. Overall compliance for the OS has increased from approximately 63 percent compliant in January 2010 to 94 percent compliant today. SQL Server compliance has increased from approximately 44 percent compliant in January 2010 to 83 percent compliant today.


Microsoft IT has realized the following benefits as a result of employing the federated BI portfolio:

Increased efficiency: Users find the federated BI portfolio environment easier to access and process the data. This one-stop-shop provides clean data and offers different analysis engines and reporting capabilities—all from one portal. Users no longer waste time trying to identify the right data source or have to contact multiple source owners to obtain access.

With the BI portfolio, SDO has realized efficiency gains when investigating a problem. For example, the Problem Management team is no longer required to spend valuable time collecting data because they already have it and it’s up-to-date. BI portfolio data provides context related to their situation, enabling them to make meaningful connections and to associate the right people with the right problem. Conversations between Problem Management and application owners start at a much more progressed state and have changed from a discussion about potential risks to a discussion about what to do about those risks, resulting in faster resolution.

More scalable platform: Automation capabilities included in the BI platform enable SDO to modify the data layers much more quickly than could be done otherwise. When a business group has new requirements and reporting needs, it’s much faster for SDO to complete the request and for the business to receive the outcome. The scalability of the BI platform has also resulted in a dramatic change in information sharing.

Data sharing leads to mutually shared successes: The Problem Management team found they were suddenly more self-sufficient and have become a partner to the BI team where both teams are consumers and partners—working together to provide reporting and information. Using Excel PowerPivot to share data also helps to facilitate a much closer and efficient relationship with customers, creating a socialization of information—in which they can share data and drive mutual goals and that results in shared success. When the Problem Management team meets with customer organizations, they are finding that many customers have already addressed the top issues because now there are mutually shared values. In the past, these were issues that Problem Management had to follow up on.

Standardized processes: The new BI portfolio promotes clear communication around data and enables different teams to easily access data from a single, trusted source. With improved adherence to standards, the interpretation of data can still vary between different people when looking at the model. However, the BI portfolio level sets the conversation to talking about the same data in similar ways.

The BI portfolio has brought regular business processes that drive a continuous improvement in their environment. These business processes also feed back into the business planning process. By identifying defects or versions out of compliance, users know what needs to be fixed in the environment over the next fiscal year and can budget accordingly—thus developing the plan of record for the next year.

Standard processes also drive corollary business review and risk remediation, business planning process, and policy focus regarding how things get implemented in the environment, generating accountability mechanisms to make sure people are implementing things in an operationally sound way.

Best Practices

In consideration of a new federated BI portfolio, SDO developed and implemented these best practices:

Create reusable components: Ensure that every component used in your BI framework is reusable. While this requires more up-front time to develop the standard and processes, track adoption, and usability, the result allows you to reuse your environment instead of re-creating it. This results in a faster solution delivery for new requests. As an example, when SDO receives a new BI request, it can use existing templates to extract and clean the data, and then performs a small number of adaptations to deliver the desired reports.

No human data: All data used to compile the BI portfolio should be programmatically discoverable or machine data. For the solution to be effective, SDO relies on the source systems to provide all data. If there is a data gap for a particular data item, it’s considered to be non-compliant (a defect) since it drives the data quality in the source system. SDO will work with the source systems’ owners and business users to modify the system, and then takes data directly from the source system.

Comply with the external Microsoft Support life cycle: Develop and implement a compliance/defect remediation program. For Microsoft IT, the guiding principle for the Stay Current program is to do at Microsoft what Microsoft tells the world to do. In this case, that is supported by staying compliant to the external Microsoft support life cycle.

Keep data transparent: Access from the top level of the report to the source data should be available so that clients can generate their own reporting if they choose to. SDO provides a standard set of BI reports for broad consumption, but their first deliverable is always the real-time components which enable teams to do their own analytics and to enable ad-hoc reporting.

Listen to customers: Customer input is crucial to the success and adoption of your BI portfolio. Make sure Program Management is prepared with data and reports to share with customers, but they also need to listen to customers to identify their top issues and concerns. For example, there might have been an outage, but Problem Management didn’t see it due to a lack of previous instrumentation in monitoring to detect the failure raised by the customer. By partnering with customers and seeking their feedback, SDO is able to fine-tune or monitor the environment to try to pick up those signals, which improves the overall health of the BI portfolio. In addition, customer feedback is vital to the definition of standards applied in the course of defect management. With positive engagement with the customer, Problem Management and BI are able to accommodate customer requirements into the standards model, and reflect back the impact of their compliance posture to their environment.


Even with the new BI portfolio, SDO sees the potential to do much more for the business and is currently working on a number of initiatives that will improve the ease of access and use. A mobile BI solution is in development and would provide managers to with easy, mobile access to BI data and reports. SDO is also looking at ways to re-use existing framework components to deliver beautiful and visually impacting reporting, enabling users to have presentation ready materials available at their fingertips.

As the business matures, existing information systems and data will be fully integrated, federated systems will be phased out over time and SDO looks forward to a true consolidated BI platform in which new BI solutions are built on.

For More Information

For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Order Centre at (800) 933-4750. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the World Wide Web, go to:

© 2014 Microsoft Corporation. All rights reserved. Microsoft and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.