A Summary of Microsoft's Business Intelligence Strategy

Phil Haack
Solien Technology, Inc.

November 2002

Summary: Outlines the major Microsoft technologies involved in a Business Intelligence solution.

Introduction

Although the concept of Business Intelligence (BI) has been around for some time, Microsoft has recently put it in the forefront of their strategy. Their BI solutions, consisting of a loose configuration of products, are being marketed as a necessity to improve a business's bottom line. This paper seeks to examine what exactly BI is and how the Microsoft Tools support this concept.

What Exactly Is Business Intelligence?

An article on the Microsoft site defines business intelligence as "the process of extracting data from a database and then analyzing that data for information that you can use to make informed business decisions and take action."[1] However, this definition does not explicitly address the big picture goal of BI: to help businesses improve the bottom line.

I define BI simply as:

A metrics approach to making business decisions. It turns Data into Knowledge.

Although analyzing data collected in vast data-warehouses is an important component of BI, asking the right questions of the data is even more important. Also important is understanding what conclusions may be reasonably drawn from data analysis.

Where Does Microsoft Fit In?

Microsoft's tools will not tell a business which questions to ask, nor will it confirm or deny conclusions drawn from data analysis (although they have several frameworks for specific industries and business practices that may give a business a head start on even these tasks).

Microsoft Tools are especially suited to gathering, presenting, and analyzing data. Collaboration is also a key feature of Microsoft tools.

Business Intelligence Terminology

Before we begin a discussion of the overall BI architecture, we define several terms. The following definitions were extracted from an article on the Microsoft site[2].

Terminology has changed at least as much as technology in this field. Many of the terms are interchangeable. Here are a few of the common terms:

Data warehouse

A data warehouse is a non-volatile store of cleansed, possible summarized data used for analysis purposes. Another term for this is the relational data store.

Data warehousing

The process of collecting, cleansing, sifting, and storing data from a variety of operational sources, and delivering that information to end users for reporting and analysis. While a data warehouse is a repository, data warehousing is an end-to-end process, reaching from the source systems to the end user.

ETLM

Tools or processes to Extract, Transform, Load operational data into a relational data store, and manage these processes. Data Transformation Services (DTS) is the name of the Microsoft component for ETLM. DTS is included with SQL Server 2000 and SQL Server 7.0.

OLAP

Online analytical processing, or OLAP, is a term coined in 1993 by Dr. E.F. Codd. He defines OLAP applications as applications that deliver fast analysis of shared, multidimensional information.

OLAP Data Model

In the OLAP data model, information is conceptually viewed as cubes consisting of descriptive categories (dimensions) and quantitative values (measures). A cube may have many more than the three dimensions associated with a geometric cube. Typical dimensions in a sales information cube would include time, geography, product, channel, and organization. Typical measures include sales, inventory, headcount, income, and expense. Within each dimension, data can be organized in a hierarchy. For example, the time dimension may have years, quarters, months, and days. Users move up and down between levels to see more or less detailed information.

Cube

Multi-dimensional view of aggregated data.

 

Overall Architecture of a Business Intelligence Solution

Figure 1: Elements of a BI Data Flow[3]

The above diagram outlines the key architectural components to a BI solution.

1.       Data may be contained in various source systems that may consist of standard OLTP databases, CRM systems, etc. These don't have to be Microsoft storage systems. Interoperability based on industry standards is a vital component to the Microsoft BI architecture.

2.       ETLM tools (such as Microsoft DTS) gather and transform data from the various source systems into a data warehouse. Typically, the warehouse is a SQL Server 2000 Enterprise database with a normalized relational data model.

3.       SQL Server Analysis services are employed to build OLAP Cubes. Cubes contain aggregated data and may be queried using MDX, a query language optimized for OLAP queries. This allows users to view data in ways that makes it easier to ask if the business is more profitable today than it was yesterday.

4.       Various query, reporting, and analysis tools allow end users to slice, dice, and analyze data in a variety of ways to transform the collected data into knowledge.

5.       Missing from the diagram is the collaboration component to BI. Microsoft Portal Server provides a platform for sharing analysis reports as well as unrelated documents core to a business.

Microsoft Business Intelligence Products

Microsoft SQL Server Analysis Services 2000

Microsoft SQL Server Analysis Services is built into Microsoft SQL Server Enterprise Edition. To access these services, use the Analysis Services Manager MMC snap-in. It is the Analysis Services counterpart to SQL Enterprise Manager.

The services contain the following components:[4]

          The relational database for the relational data store or data warehouse (the SQL Server process itself).

          The OLAP engine (Analysis Server).

          Data Transformation Services (DTS): a tool to extract, transform and load data from operational systems to the relational data store.

          Administration tools to manage both the relational database and the OLAP engine.

          Data mining capabilities (integrated in Analysis Services 2000).

          Meta Data services (formerly Microsoft Repository).

          English Query for natural language queries.

A typical implementation uses these tools to build and query OLAP cubes containing intelligently aggregated business data. However a full end-to-end solution goes one step further.

Microsoft Data Analyzer

The Microsoft Data Analyzer is a new offering from Microsoft in the Office XP family of products. It is a standalone Office application used to graphically analyze data contained in an OLAP cube. This product fits into the query, reporting, and analysis tools category of tools. Its goal is to help users identify hidden problems, opportunities, and trends by presenting data at an executive summary level of detail. It presents a complete overview on a single screen, replacing masses of grids, graphs, and reports.

Designed for the knowledge worker, the tool is designed for ease of use. Using the tool, workers can create and save views of an OLAP cube. When creating a view, a worker may simply create a connection to a cube (a file with the .cub extension) from the network, the local file system (cubes may be exported to a static file), or from a website. When the cube is updated, the view is automatically updated.

In creating a view, the user selects the desired dimensions the view should contain. The view presents the data in a highly graphical interface that allows the user control over how to view the data. Graphs are color coded to present positive ratios (as defined by the worker) in green and poor ratios in red. Users may zoom in and out on data and change the underlying graph types.

Data Analyzer also has a Business Center feature that helps guide users in exploring data with real world questions in English and the multi-dimensional query to answer the question.

Data Analyzer views and reports may be exported to html as well as Office product formats such as Excel and Powerpoint.

Microsoft Excel

While Data Analyzer focuses on the high level overview, Excel may be used for more detailed examination of data. It is a tool that is familiar to most every day users. Excel PivotTables and PivotCharts provide an interactive, multi-dimensional view of data. This tool may be used in concert with the Data Analyzer. Excel also contains a powerful calculation and charting engine.

 

Office Web Components

The Office Web Components allow developers to leverage the functionality of Excel spreadsheets, charts, PivotTables and PivotCharts as Web Parts. These parts may be used within custom web-based intranet applications, dashboards, and BI portals. Users can manipulate data in the browser as if they were working in Excel, but have the data processing and number crunching occur on the server.

Microsoft Map Point

Microsoft Map Point provides tools to enable a company to visualize its business data on maps, in conjunction with geographic and demographic information. MapPoint can plot data from Excel, Access, or any OLAP data source against local, regional, or national maps.

Microsoft SharePoint Portal Server

SharePoint Portal Server is Microsoft's platform for building custom portals quickly and efficiently. It may be used to build intranet portals that host BI tools and document repositories. Pages can be configured to display up to date metrics from web parts containing graphs and data from Data Analyzer, MapPoint, etc. These pages may be customizable by end users to present data meaningful to the users.

Business Intelligence Frameworks and Tools

Microsoft has several frameworks and tools built on top of their line of BI products that may help to speed the implementation of a BI solution.

Microsoft SQL Server Accelerator For Business Intelligence

SQL Server Accelerator is a combination of tools and best practice guidelines for developers to help speed the implementation of a BI solution. It focuses on the process of configuring, tailoring, and creating the analytical application based on a customized data model configured using the Analytics Builder Workbook tool. It can help design the ETLM process to pull in data as well as the building of OLAP cubes.

Microsoft offers two free templates for the SQL Server Accelerator. Developed by experienced consultants, templates for the SQL Server Accelerator for BI serve as a starting point for developing BI solutions that meet customers' needs. The demos contain full presentation scripting and the starter templates consist of relational and multidimensional database schemas, dimensions, measures, key performance indicators (KPIs), and client views.

Please note that these templates are not supported solutions for the BI Accelerator but are meant to be used for demonstration purposes and to jump start your engagements.

Manufacturing. The manufacturing data model addresses the need to measure shop floor performance to quickly identify production problems, understand their causes, and quickly take action to correct them. Some of the areas coved by this data model include analysis of labor cost, defect and scrap rates, production cost, and equipment servicing needs.

Financial services. The financial services data model focuses on the needs of retail banking to minimize costs and increase profitability while continuing to provide superior customer service. With this data model, banks can analyze their company-wide financial position, identify their best customer segments, improve their channel effectiveness, and more.

Balanced Scorecard Framework

If you can't measure it, you can't manage it. This is the philosophy behind the balanced scorecard methodology, a performance management approach developed by Robert Kaplan and David Norton. Balanced scorecard methodology is at the heart of today's successful businesses.

The Microsoft Balanced Scorecard Framework provides a technology environment and an integrated set of tools used to derive maximum value from its balanced scorecard solution. The framework is not a packaged application. Instead, it integrates a variety of Microsoft packaged applications and industry standards to automate a Balanced Scorecard. It consists of a set of tools and methods to help both business users and developers get to success faster and more reliably so software is no longer a hurdle to overcome in scorecard development, but an asset to speed development.

Conclusion

Microsoft's extensive offerings in the BI area are sufficient to create very rich end to end BI solutions. However, it is important to grasp which technologies apply for any particular customer and at which point in the BI dataflow a tool comes into play.

For a Microsoft Partner entering the BI arena, the partner should focus most of its energies on gaining expertise with the fundamental technology of BI, Microsoft SQL Server Analysis Services 2000, while also obtaining a familiarity with the end user reporting, visualization, and query tools. Once this expertise is established, the partner may want to gain expertise in the whole array of end user BI tools, especially in the area of custom implementations.

 

Feedback and Support

Questions? Comments? Suggestions? For feedback on this article, please send an e-mail message to phil@solien.com.

More Information

Analysis Services 2000: Powerful and Accessible Analysis for Business (http://www.microsoft.com/sql/evaluation/BI/AnalysisSvcWP.asp).

Data Analyzer Home Page (http://www.microsoft.com/office/dataanalyzer/default.asp)

SQL Server Accelerator for BI Overview (http://www.microsoft.com/solutions/BI/evaluation/overview/default.asp)

Balanced Scorecard Framework (http://www.microsoft.com/office/business/intelligence/scorecard/framework.asp)