Excel 2007 will offer new features for browsing and creating reports from data stored in SQL Server Analysis Services, Microsoft’s online analytic processing (OLAP) platform. In addition, Excel 2007 will support new SQL Server 2005 features, such as key performance indicators, which businesses use to track metrics against predefined goals. Combined, Excel 2007 and SQL Server 2005 Analysis Services could help extend Microsoft’s lead in the OLAP server market against top competitors Hyperion and Cognos. However, better Excel support for Analysis Services will also squeeze several Microsoft partners that have developed add-ins to plug gaps between the two products.

OLAP, SQL Server, and Excel

OLAP is a general term for techniques and technologies used to summarize, query, and analyze historical data to answer questions related to business performance. For example, an analyst might use OLAP tools to quickly generate a report of sales for a particular product line by region and month. OLAP data are stored in special databases called OLAP cubes, which summarize raw transaction data (such as individual sales records from an enterprise resource planning application) to facilitate reporting and analysis. OLAP client applications typically access a cube’s data through an OLAP services tier, which extracts cube data and performs the calculations and summarizations requested by OLAP clients. (For an overview of cubes and related concepts, see the illustration “What Is a Cube?“.)

Excel 2007 offers new features for browsing and creating reports from data stored in SQL Server Analysis Services. In addition, Excel 2007 supports new SQL Server 2005 features, such as key performance indicators (KPIs), which businesses use to track metrics against predefined goals. Combined, Excel 2007 and SQL Server 2005 Analysis Services could help extend Microsoft’s lead in the OLAP server market against top competitors Hyperion and Cognos. However, better Excel support for Analysis Services will also squeeze several Microsoft partners that have developed add-ins to plug gaps between the two products.

Many of the features described here require SQL Server 2005 SP2, which Microsoft recommends for customers who want to use Excel 2007 as an Analysis Services client.

Excel Still a Dominant Client

Microsoft released a general-purpose OLAP client called Data Analyzer in 2001, but it gained little visibility. Instead, Microsoft’s own Excel application remains one of the most widely used client tools for OLAP-Excel allows users to connect to Analysis Services cubes, and Excel’s PivotTable feature enables users to visualize and navigate data in those cubes. In addition, a separate add-in for Excel 2002 and Excel 2003 (called the Excel Add-in for Analysis Services) provides more advanced cube reporting capabilities than are available with pivot tables. For example, the add-in provides three special functions that extract data from cubes. Cube functions can be placed in any spreadsheet cell, which allows users to build tabular, free-form reports that combine data from multiple cubes and other data sources (pivot tables can include data from a single cube only) and provide more control over report layout and formatting than do pivot tables, whose layout is tied to the structure of the cube.