Professional SQL Server™ Analysis Services 2005 with MDX

15.1. Microsoft Excel Pivot Tables

If you have been using Excel you might be familiar with the pivot table feature, especially considering that pivot tables date back to Excel Version 5. The pivot table feature in Excel is used to create reports for Excel users, which help them analyze data with ease. The pivot table feature can work on data stored in Excel or some other data source that can be accessed by Excel. The only requirement to use the pivot table in Excel with Analysis Services is that Excel should connect the Analysis Services instance. In such a case, Analysis Services becomes a data source for Excel. However there is a tight integration between Excel and Analysis Services. Excel is well aware of the Analysis Services models and objects and presents them effectively to the end users. As you might expect, creating a pivot table can be accomplished through the use of a wizard. The wizard has some smarts to it and even creates what it calculates to be the best resulting layout.

As for the capabilities of a pivot table, they are similar in nature to the cube browser seen in BIDS where you can drag and drop dimensions and measures to analyze the data. You can analyze data using Excel pivot tables in a similar fashion. Not only can you arrange data to best surface the information contained in it, but also the pivot table technology will sum the appropriate columns for you automatically. It is quite common for people to construct pivot tables to some planned configuration ...