Integrating Microsoft SQL Server 2000 OLAP and Microsoft Office, Part 1: Creating an Excel PivotTable Report with an OLAP Cube
- Introduction to the Series
- What You Will Need to Complete the Series Tutorials
- Building an Excel PivotTable Report on OLAP Data Source
- Connecting Excel to the OLAP Cube
- Layout and Navigation of the PivotTable Report
- Browsing the Cube
- Drilling to Member Details
- Going Multidimensional
- Next in This Series
Building an Excel PivotTable Report on OLAP Data Source
Office 2000 witnessed the appearance of robust new OLAP reporting features for the desktop user. When a PivotTable report accesses a multidimensional cube, it communicates with the PivotTable Service to return values from an Analysis Server. As the first exercise in this tutorial, we'll create a PivotTable report that accesses the Sales sample cube that comes with Analysis Services. We'll use the PivotTable Wizard found in Excel 2000 in our initial efforts.
The wizard accesses the Microsoft Query application in Excel to build a query file. The file assumes the name of the data source, appending an .oqy extension. Query file creation is a one-time event for any given PivotTable report, and defines the connection between Microsoft Excel and the OLAP cube.