SQL Server™ 2005 Bible

Chapter 48. Analyzing Data with Excel and Data Analyzer

In This Chapter

• Constructing Excel PivotTables using a multidimensional data source

• Constructing Excel PivotTables using a relational data source

• Creating PivotCharts

• Exposing relational data with Excel data ranges

• Analyzing multidimensional data with Data Analyzer

Reporting Services provides a method to create reports that expose trends, exceptions, and other important aspects of data stored in SQL Server. Reports can be created with a level of interactivity, but even interactive reports only provide a basic level of data analysis capabilities.

The advantage of data analysis is the ability to discover trends and relationships that are not obvious, and to look at data in ways and combinations not normally performed. Often such analysis is a prelude to, or is done in conjunction with, data mining.

This chapter describes data analysis uses of Microsoft Excel and Microsoft Data Analyzer. Excel offers PivotTable and PivotChart views of both relational and multidimensional data, providing a drag-and-drop approach to viewing data. Excel also offers a data range that provides a spreadsheet window into relational data. Data Analyzer focuses on multidimensional data, providing both table and graphical views.

Organizational interest in data analysis tends to be focused among a small population, with the majority of staff satisfied with reports created by others. Interested staff tend to share the following characteristics:

• They perceive the value ...