Reporting Options for Analysis Services Cubes: MS Excel 2002 - Page 2
April 7, 2003
Reporting Options for Analysis Services Cubes: Microsoft Excel 2002
In this and subsequent lessons we will explore what has become a popular topic, and which generates many e-mails each week in my inbox, Reporting Options for Analysis Services Cubes. Having connected to Analysis Services cubes with various business intelligence tools, I have found that the steps for establishing connection to the cube data source are similar between various products. We will examine the use of MS Office in this and the next tutorial, and expose some of the options that are available to most of us within the pervasive MS Office suite.
The integration of MS Office with the MSSQL Server 2000 and Analysis Services components make this a comfortable arrangement for many reporting needs. On the other hand, I find myself in client scenarios where more "robustly specialized" or "enterprise-level" reporting tools are mandated by management. We will address a prominent example of this in a subsequent article of this group, Reporting Options for Analysis Services Cubes: Cognos PowerPlay, as well as in another article, where we will focus on accomplishing reporting from Analysis Services cubes with a similar business intelligence solution. My intent is to review the process of establishing connectivity and enabling reporting capabilities for each of the examples, and not to compare the product features themselves, to any significant extent. I will consider providing a similar examination of other reporting products at a later time, based upon any suggestions I receive from readers, if this turns out to be useful.
In this and the next articles, we will explore features that integrate Analysis Services and MS Office to provide a vehicle for client reporting and other business intelligence capabilities. The central focus of the article will be a basic overview of the Excel 2002 PivotTable Report, while the next article will concentrate on the FrontPage 2002 PivotTable List. In the context of the article, we will examine each in its role of "client representative" of the Analysis Server; other roles (example: the PivotTable Report's dual role as creator of, and consumer for, local cubes) will not be examined in the current article, but will be afforded appropriately scoped tutorials at a later date.
We will introduce the PivotTable Report features that are available with Microsoft Excel 2002 in this article. Our examination will include PivotTable Report options that provide for creating robust and flexible reports, and will explore:
Building an Excel PivotTable Report on an Analysis Services Cube
MS Office 2000 witnessed the appearance of robust new OLAP reporting features for the desktop user, most of which were further evolved in the components of MS Office XP. When a PivotTable report accesses a multidimensional cube, it receives data from a specified Analysis Server via the PivotTable Service. As the first exercise in our tutorial, we will create a PivotTable report that accesses the Sales sample cube that comes along with Analysis Services. We will use the PivotTable Wizard found in Excel 2002 in our initial efforts. The procedures we cover are essentially the same for Excel 2000, although some of the terminology used in the latter, as well as the appearance of dialogs / other objects, differ in some cases.
The Wizard accesses the Microsoft Query application in Excel to build a query file. Query file creation is a one-time event for any given PivotTable report, and defines the connection between Microsoft Excel and the Analysis Services cube.