• Illustration: What Is a Cube?
• Illustration: Pivot Tables Help Visualize Cubes
• Illustration: Excel 2007 Pivot Table Client—KPIs
• Illustration: Excel Free-Form Excel Cube Report

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?".)

Microsoft entered the OLAP market with the 1998 release of SQL Server 7.0, which included features for creating and storing cubes as well as services and APIs that allowed client applications to access those cubes. Microsoft has refined its OLAP features in subsequent SQL Server releases—the collection of development tools, programming interfaces, and run-time services for building OLAP applications is called SQL Server Analysis Services—and the company has become the OLAP market's leading platform vendor, according to The OLAP Report, an independent research publication devoted to the OLAP industry.

Despite its lead in the OLAP platform market, however, SQL Server does not have client applications for working with Analysis Services cubes. Until recently, that gap has been filled by ISVs. Some, such as Panorama and ProClarity (acquired by Microsoft in Apr. 2006) produce full-fledged OLAP client applications; others, such as IntelligentApps and XLCubed, market lighter-weight OLAP add-ins for Microsoft's Excel spreadsheet and charting application.

Microsoft released a general-purpose OLAP client called Data Analyzer in 2001, but it has 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 create charts and reports from a cube. In addition, a separate Microsoft add-in (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 allows users to build free-form reports into spreadsheets. In free-form reports, users can combine data from multiple cubes and other data sources into a single report (pivot tables can include data from a single cube only) and have more control over report layout and formatting than they do with pivot tables.

New features and usability improvements in Excel 2007 pivot tables and improved free-form reporting capabilities will make Excel a better tool for browsing Analysis Services cubes and building reports from them. The popularity of Excel, combined with the advances promised in Excel 2007, fortifies Microsoft's broader business intelligence (BI) strategy, part of which aims to widen the user audience for technologies such as Analysis Services. (Microsoft refers to this as "BI for the masses.") If the strategy is successful, the benefits to Microsoft will be twofold—it will drive additional deployments of SQL Server Analysis Services and encourage companies to upgrade to Office 2007 to give workers a familiar and reasonably effective client tool for analyzing Analysis Services data.

Pivot Tables Support SQL Server 2005

Excel 2007 improves the usability of pivot tables and expands support for Analysis Services, including support for several features introduced in SQL Server 2005. In addition, Excel 2007 pivot tables will provide some capabilities that are now available only in the Excel Add-in for Analysis Services.

Notable updates to Excel 2007pivot tables include the following:

Simpler creation, setup. A simpler set of dialog boxes and menus will make Excel pivot tables easier to create and populate. For example, a single dialog box allows users to connect to and create a pivot table based on an Analysis Services cube; previously, users were required to run a cumbersome, multistep wizard.

Populating Excel 2007 pivot tables will also be more straightforward. A task pane will present users with a list of fields in the data source. For an Analysis Services cube, the pane will indicate which fields correspond to the cube's dimensions and which to its measures. To populate the pivot table, users will select fields from the list; based on the type of field selected, Excel will determine where data appear in the pivot table (for example, cube dimensions become pivot table column or row labels while measures are placed in the table's cells). Simplifying the pivot tables is crucial to Microsoft's attempt to position Excel 2007 as a simple way to work with Analysis Services cubes.

(For an example of a cube-based Excel pivot table, see the illustration "Pivot Tables Help Visualize Cubes".)

Support for Analysis Services key performance indicators (KPIs). Excel 2007 will provide users with a simple way of representing and visualizing the various components of Analysis Services KPIs, used to track business metrics, such as quarterly sales in a region. A new feature in SQL Server 2005 allows developers to use data stored in Analysis Services cubes to construct KPIs; those KPIs will typically include a basic business metric, a target value (or goal) for the metric, status of the metric (which is used to provide a qualitative indicator of a metric's current value versus the goal), and a trend for the metric.

(For a screen shot of an Excel 2007 pivot table displaying Analysis Services KPIs, see the illustration "Excel 2007 Pivot Table Client—KPIs".)

Support for Analysis Services perspectives. SQL Server 2005 gives developers greater flexibility in building and structuring Analysis Services cubes. For example, a cube can now be built from multiple data sources instead of a single data source (a limitation in earlier SQL Server versions). This could simplify cube administration and maintenance by replacing large numbers of disconnected cubes with single, integrated cubes; however, such cubes could be complex and difficult for users to navigate.

Excel 2007 will support a feature in SQL Server 2005 Analysis Services that allows developers to specify cube perspectives—logical cube subsets that can simplify or provide role-specific views of complex cubes. For example, an Analysis Services developer could create regional perspectives on a worldwide sales cube. From the standpoint of an Excel 2007 user, the perspectives will appear as individual cubes—users will be able to connect to them and create pivot tables based on their data as they would any other cube.

Support for Analysis Services actions. Excel 2007 pivot tables will support Analysis Services actions, which give Analysis Services developers a way to associate supplemental information or activities with a cube or its components (such as its dimensions and measures). Actions consist of static instructions and parameters that are stored with the cube; client applications read the actions' instructions and parameters and take the appropriate steps to execute them.

Excel 2007 supports several predefined action types provided by Analysis Services—for example, the URL action allows an Analysis Services developer to attach a Web link to a cube or its components; a developer could use the proprietary action to reference an external application, such as an enterprise resources planning (ERP) application. Another action, called drillthrough, allows a developer to point to a separate data source, typically containing the detailed data on which a cube was built. Client applications that support actions typically give users the option of executing those actions. For example, Excel 2007 will allow users to examine the individual records behind the sales summary data stored in a cube (and presented in a pivot table) if the appropriate drillthrough action is defined. Previous Excel versions supported only the URL action and drillthrough, and only via the Excel Add-in for Analysis Services.

Server-based and conditional formatting. Excel 2007 will honor formatting for data or values in cubes specified by Analysis Services developers, giving those developers a central way to control the way cube data are represented. For example, a developer could specify a currency format and color scheme for a set of financial data (e.g., values should appear as a dollar amount, to two decimal places, with positive values in black and negative values in red). In addition, Excel 2007 will provide new data visualization features that can be applied to cells in a pivot table. For example, histogram bars and "heat maps" can be displayed within cells to provide users a fast visual representation of the relative values of a group of numbers.

Better control, performance. Excel 2007 will allow users to choose when a pivot table is updated. In current versions of Excel, pivot tables are updated immediately following any change—for example, each time a user changes a row or column in an Excel 2003 pivot table based on an Analysis Services cube, Excel immediately queries the cube and updates the pivot table. With Excel 2007, users will be able to make multiple changes to a pivot table before it is updated. This could make working with large cubes faster and less cumbersome compared with previous versions of Excel.

New Functions for Free-Form Reports

Microsoft will likely provide built-in support for free-form reports in Excel 2007 rather than through a separate add-in. Most notably, Excel 2007 will replace the functions provided by the Excel Add-in for Analysis Services—those functions allow workers to extract data from cubes and insert those data into any cell in a spreadsheet, giving them greater control over report layout than they have using pivot tables.

(For a screen shot of an Excel 2007 free-form report, see the illustration "Excel Free-Form Excel Cube Report".)

Excel 2007 will include the following updates for free-form reports:

New cube functions. Excel 2007 will ship seven new functions for working with cube data in spreadsheets. The new cube functions will subsume the three basic functions provided by the Excel Add-in for Analysis Services, which allowed users to select dimensions and measures from a cube and insert those in a spreadsheet, and will give Excel users more flexibility when building free-form reports. For example, Excel 2007's new functions will support aggregate operations (e.g., returning sets of data from a cube, and ranking and sorting the data) and new Analysis Services features, such as KPIs.

Converting pivot tables to free-form reports. Excel 2007 will let users convert pivot tables to free-form reports in a single step, which replaces the pivot table's row, column, and cell values with the appropriate cube function. The feature will benefit users wanting to customize or extend reports based on an Analysis Services cube, obviating the need to manually create many functions to access the cube. Instead, users will be able to create a pivot table based on the cube, convert the pivot table to a free-form report, and then customize or extend the free-form report as needed (adding rows or columns from a data source other than the cube, for instance).

Improved connection management. Excel 2007 can better manage connections to external data sources, such as Analysis Services cubes or SQL Server databases. New dialog boxes list data source connections, help users configure connection properties, and can provide a cell-by-cell list of where data source connections are used, the formulae or functions that call the connections, and the value those functions return. These changes will benefit users working with and managing free-form reports, particularly those reports with connections to multiple data sources.

Overlaps, Partner Risks

The combination of Excel 2007's improved support for cube browsing and reporting and new features in Analysis Services could help Microsoft push those products farther into the mainstream for business decision-making, particularly among casual or ad hoc users.

However, several considerations complicate the picture for customers and partners.

Technology overlap. A number of different tools and technologies with similar functions could confuse businesses implementing OLAP solutions. For example, a new Excel server technology (called Excel Services) stores spreadsheets and performs calculations on a Web server, delivering results to users through a browser. The technology is intended for distributing spreadsheets for analysis, reporting, and charting to multiple users on an ongoing basis. However, the Excel 2007 client's ability to create reports based on cubes coupled with Excel Services' ability to centrally manage and distribute those reports overlaps the managed reporting niche carved out by SQL Reporting Services, which can also be used to build and distribute reports based on cubes.

Furthermore, Excel's support for creating reports based on SQL Server KPIs overlaps with the mission of the Office Business Scorecard Manager, a Web server application from the Office Applications business unit that also supports business performance analysis using KPIs. Companies will need to determine which of these technologies is best suited to particular applications—for example, an Excel-based solution could be the best choice for relatively advanced analyses requiring charting and mathematical computation, while Business Scorecard Manager may be better suited to applications that require less user interaction, such as executive scorecard or dashboard applications.

Partner risks. With its improved support for Analysis Services and because it is already familiar to most corporate users, Excel 2007 will be a natural first choice for many organizations attempting to make OLAP a more widely used business analysis tool. Undoubtedly, this will shrink opportunities for Microsoft partners that market Excel add-ins for viewing and navigating Analysis Services cubes, although partners will likely still find opportunities to create custom reporting or analysis add-ins for specific vertical industries.

Excel 2007 will likely not displace more advanced client applications among OLAP power users. However, in Apr. 2006 Microsoft announced that it planned to acquire ProClarity, a top vendor of advanced OLAP client software. With that purchase, Microsoft closes the remaining gap in its OLAP portfolio and cuts off one of the remaining opportunities for partners to complement its OLAP strategy. (For more information on the ProClarity deal, see "ProClarity Acquisition Nets OLAP Client".)

Not backward compatible. Excel 2007 will subsume the Excel Add-in for Analysis Services' functions for extracting data from cubes and placing those data in free-form reports. The release will also include new functions for working with cube data. Thus, free-form reports created with previous versions of the Excel Add-in for Analysis Services are not compatible with Excel 2007 and those created with Excel 2007 will not work in earlier Excel versions.

Complex concepts. Because pivot tables and cubes are inherently complex tools, they are not used as extensively today as Microsoft would like. Although Excel 2007 will make this easier, users not versed in OLAP concepts and language will likely still find them challenging. Consequently, without some training, many nontechnical users may find it difficult to extract useful intelligence from those tools, which could dampen enthusiasm for Microsoft's "BI for the masses" pitch. However, Excel 2007 supports a number of new features in SQL Server 2005 Analysis Services that expert developers can use to control cube presentation, which could help them guide users to productive analyses.

Availability and Resources

The first Excel 2007 beta (part of the larger Office 2007 beta) was released in late 2005. A second beta is planned for the second quarter of 2006 with final release planned for Nov. 2006.

The Microsoft Office 2007 site is www.microsoft.com/office/preview/default.mspx.

More information about SQL Server 2005 Analysis Services and other components of Microsoft's business intelligence platform is at www.microsoft.com/sql/solutions/bi/default.mspx.

Microsoft's Business Scorecard Manager site is office.microsoft.com/en-us/FX012225041033.aspx.

Analysis Services in SQL Server 2005 is described in "Analysis Services Matures in SQL Server 2005" on page 3 of the Feb. 2005 Update.

The OLAP Report is at www.olapreport.com.

The new Excel server component is described in "Excel 12 to Get Server" on page 25 of the Nov. 2005 Update.

Reporting Services in SQL Server 2005 is outlined in "Reporting Services Updated for SQL Server 2005" on page 15 of the July 2005 Update.

For more detail on Business Scorecard Manager, see "Maestro to Direct Business Scorecards" on page 27 of the June 2005 Update.