By Chris Alliegro [bio]
Posted: Apr. 16, 2007

The following an excerpt of a Research Report published by Directions on Microsoft, an independent research firm focused exclusively on Microsoft strategy & technology. More samples of our content, as well as a list of upcoming articles and reports are also available.

Microsoft provides two kinds of products for BI. Shown here is a hypothetical BI system that enables an organization to analyze historical data from operational applications (such as the core transaction processing and customer relationship management applications in a financial institution). The SQL Server BI platform (bottom) enables design and management of the databases for BI, and development of BI applications. Microsoft BI applications (top) enable end users to view and analyze data hosted by the BI platform.

Microsoft's BI platform consists of three main components:

Integration Services is a platform for data integration scripts, which cleanse, summarize, and transfer data in bulk between data sources. In the BI system shown here, Integration Services performs the extract, transform, and load (ETL) process, which regularly pulls data from the organization's applications, standardizes the data (e.g., standardizing date formats and column names), and loads the data into a consolidated historical database, the bank's data warehouse.

Analysis Services manages specialized analytic databases (called cubes) that efficiently store and summarize large volumes of historical data. Analysis Services supports interactive analysis (a process called online analytic processing, or OLAP) and automated analysis to identify trends and patterns (a process called data mining). The cubes shown here are built from the organization's data warehouse, but cubes in SQL Server 2005 can also be built directly from application data, and Analysis Services clients can "drill through" summarized data in cubes to retrieve detailed data from applications.

Reporting Services enables users to define and view reports, and supports scheduled Web refresh and e-mail delivery of reports. For BI, Reporting Services can build reports directly from application data, a data warehouse, or Analysis Services cubes.

Note that the application data and the data warehouse in the system shown here do not have to be managed by SQL Server; Integration Services, Analysis Services, and Reporting Services all can work with database management systems from other vendors, and Microsoft delivers specific support for IBM DB2 and Oracle.

To analyze the data, the organization uses three Microsoft BI applications:

SharePoint Server (formerly SharePoint Portal Server) is Microsoft's product for corporate portals and enterprise content management, among other functions. The latest version, SharePoint Server 2007, provides many BI features, including distribution of Reporting Services reports and Web-based calculation and distribution of Excel spreadsheets.

PerformancePoint Server, currently in beta testing, provides business performance analysis (via scorecards), budgeting, and financial analysis, using Analysis Services cubes to store financial and business performance data. Users can access the product from an Excel plug-in or with a browser via a Web site (here hosted by SharePoint Server). PerformancePoint Server will eventually incorporate two existing Microsoft BI applications: Business Scorecard Manager, released in 2005, which provides Web-based business performance analysis; and ProClarity Analytics, acquired in 2006, which provides OLAP and Web-based reporting for Analysis Services cubes.

Excel, the spreadsheet application of the Office suite, supports OLAP and other forms of ad hoc reporting and analysis for application databases, data warehouses, and cubes, and also serves as an analysis client for PerformancePoint Server. Excel 2007, the latest version, has many new features for working with Analysis Services cubes.