By Chris Alliegro [bio]
Posted: Aug. 22, 2005

Microsoft's SQL Server 2005 business intelligence (BI) platform supports data analysis, warehousing, and reporting. It helps organizations cull actionable information from the volumes of raw data stored in operational databases—to understand sales trends and structure promotions accordingly, for instance. Shown here is a typical system that collects data from operational data sources (right) such as retail store point-of-sale systems and marketing databases, consolidates these into a historical database called a data warehouse (center), and distributes the data to users who need to analyze or get regular reports on the data (left).

Microsoft's BI platform provides three main services:

Integration Services supports extract, transform, and load (ETL) data integration processes, which are commonly used to update data warehouses on a periodic basis. In the illustration, Integration Services extracts data from a variety of online data sources, which can include non-Microsoft sources, such as Oracle databases. It combines, cleanses, and summarizes those data; for example, it might standardize the geographic codes used for retail store locations, or the formats of dates. Finally, it populates the data warehouse with the summarized data.

Analysis Services hosts specialized databases (called OLAP cubes) for analyzing the data warehouse using online analytical processing, a method for efficiently summarizing large volumes of historical data. Users work with OLAP data via client tools (such as Excel or other third-party OLAP clients) that interact with Analysis Services' server component, the engine for updating, querying, and managing OLAP cubes. Analysis Services derives from and extends SQL Server's original OLAP Services. Adding to the services and infrastructure for supporting OLAP cubes and applications, Analysis Services also contains features for building data mining applications, which help workers spot hidden correlations and patterns in data (e.g., identifying products whose sales frequently drive the purchase of other products).

Reporting Services, first introduced as an add-on for SQL Server 2000 in Jan. 2004, is a service used to define, generate, store, and manage reports (e.g., a report comparing same-store sales for the last 12 months). Developers create and define Report Definitions, which are stored in a SQL Server database. Reports can be built from a range of data sources, such as the data warehouse or OLAP cubes as shown in the illustration. Users can view reports with a variety of clients, such as Web browsers or third-party reporting clients; Reports can also be delivered to users via e-mail. Clients access reports via Reporting Services server component.

The components of this platform can be used individually or collectively and each is data-source independent—many companies use SQL Server's BI platform (or individual components of the platform) with database management systems other than SQL Server. For example, the data warehousing databases in the illustration could be any OLE DB-compliant database (such as Oracle or Microsoft's SQL Server).