Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
September 12, 2005
About the Series ...
This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.
Note: Current Service Pack updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, within which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). Our focus in this article is Microsoft Excel 2003 in conjunction with Analysis Services, which will require that Microsoft Excel 2003 be installed on the PC we use to undertake the steps of the practice exercise.
Through, and together with, Microsoft Excel 2003, we will use Microsoft Query to create an Excel PivotTable report based upon an OLAP cube as a data source. Microsoft Query provides the capability for establishing connections to our cubes, among other functions. Because it is an optional Microsoft Office component, we will need to ascertain the existence of Microsoft Query on our PCs. If this is the first time Microsoft Query is being accessed on the machine, it may be a good idea to consult the appropriate Office 2003 online documentation for installation instructions.
We will also need the Microsoft OLAP Provider, included in a typical Excel 2003 installation, which consists of the Data Source Driver and the client software needed to access cubes created by Microsoft SQL Server 2000 Analysis Services.
About the Reporting Options for Analysis Services Cubes Articles ...
When I began the Reporting Options for Analysis Services Cubes in March, 2003, I set out to explore what had become a popular topic, as well as a subject of countless e-mails and forum questions: what were the options for reporting from Analysis Services cubes? Since then, the field of options has widened considerably, with the number of applications that can access our cubes increasing in response to Analysis Services' success in the market. I noted in my introductory article, Reporting Options for Analysis Services Cubes: MS Excel 2002 that, having connected to Analysis Services cubes with various business intelligence tools, I had found that the steps for establishing connection to the cube data source are similar between various products. We then examined the use of MS Office (Excel 2002 in the introductory article, and Microsoft FrontPage 2002 in a subsequent article), exposing some of the options that were available within the pervasive MS Office suite.
As I noted then, the integration of MS Office with the MSSQL Server and Analysis Services components make this a comfortable arrangement for many reporting needs. Moreover, having examined Microsoft Excel 2002 as a reporting option for Analysis Services cubes, I continue to receive questions regarding the use of Excel 2003 for the same purpose. While the general concepts in my previous article still apply, and changes to the appearance of the various dialogs and displays involved have been minimal, I thought it might be useful to address specific settings using Excel 2003 to report from an Analysis Services cube. The widespread use of Excel compels me to keep my readers up to date with the most recent version, and so I have revisited its use in accomplishing similar objectives to that of the previous article. This article will serve as that update, therefore, "synchronizing" the features of Excel 2003 with the process of establishing connectivity and enabling reporting capabilities that we considered in the Excel 2002 article, all within an independent article.
In this article, we will explore features that integrate Analysis Services and MS Office Excel 2003, 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 2003 PivotTable Report. Within the context of the article, we will examine the role of Excel 2003 as the "client representative" of the Analysis Server; other roles (for instance, the PivotTable Report's dual role as creator of, and consumer for, local cubes) will not be examined in this article. In addition, we will provide a brief summary of another Excel 2003 OLAP Reporting option, the Microsoft Office Excel Add-in for SQL Server Analysis Services.
We will examine the PivotTable Report features that are available with Microsoft Excel 2003 in this article. Our examination will include PivotTable Report options that provide for creating robust and flexible reports, and will explore: