New OLAP Features for Excel

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 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 market against top competitors Cognos and Hyperion (which Oracle purchased in Mar. 2007). 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.