In the last section, we explored creating Analysis Cube reports by using the PivotTable functionality in Excel. For those of us who have used Excel's PivotTable functionality, it is a powerful tool that allows for effortless ad-hoc data analysis. When combined with Analysis Services, PivotTables can provide users with an unparalleled ability to peruse aggregated data and drill down to the underlying data quickly and accurately.

But, for all of its usefulness, some drawbacks do exist for using Excel PivotTable functionality to access data in an Analysis Services database.

First, it's difficult to combine PivotTables in a worksheet containing other PivotTables or Excel objects, such as charts and graphs. The dynamic nature of PivotTables also makes it extremely difficult to control the size of the generated report. Adding and subtracting measures and attributes, or expanding and collapsing hierarchies in the PivotTable, will cause the PivotTable pane to expand or...