Reporting Options for Analysis Services Cubes: MS Excel 2002

Monday Apr 7th 2003 by William Pearson

Discover options for effectively reporting from MSSQL Server 2000 Analysis Services cubes. Author Bill Pearson begins a group of practical tutorials that expose the use of different reporting tools to effectively present cube data to information consumers. In this article, we will explore integrating Analysis Services cubes with Microsoft Office Excel 2002 to report data with the new and improved PivotTable Report.

About the Series ...

This is the tenth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features 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.

In addition to MSSQL Server 2000 and MSSQL Server 2000 Analysis Services, of which we have made repeated use in the previous articles of the series, further application considerations apply for this and subsequent tutorials because of their MS Office orientations. For those joining the series at this point because of a desire to work with Analysis Services and its components from the Office perspective, it is assumed that Analysis Services is accessible to / installed on the PC, with the appropriate access rights to the sample cubes (which are provided in a Typical installation of Analysis Services).

Through, and together with, Microsoft Excel 2002, 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 2002 online documentation for installation instructions.

We will also need the Microsoft OLAP Provider, included in a typical Excel 2002 installation, which consists of the Data Source Driver and the client software needed to access cubes created by Microsoft SQL Server 2000 Analysis Services.


In the first article of the series, we used the Cube Wizard to build an initial cube with the assistance of the Dimension Wizard. We progressed through subsequent articles, creating similar dimensions to those we built with the Wizard, focusing largely in our second article on using the Dimension Editor to illustrate options for building a more customized cube. We continued this examination of dimensions in Article Three, where we recreated the calendar time dimension, this time focusing on the process through which the Dimension Wizard converts existing time / date fields to a time dimension, along with its hierarchy of levels and members. Article Three also exposed ways to customize the predefined, time-related properties that the wizard establishes in building the time dimension, suggesting options for customization of these properties to enhance the cube, from the dual perspectives of user-friendliness and the reporting needs of the organization. We created an example of an alternate time dimension for fiscal time reporting, and then we discussed some of the considerations surrounding the simultaneous housing of both hierarchies in the same OLAP cube structure.

In Article Four, we examined another special type of dimension, the ParentChild dimension, and explored the attributes that make it different from a regular dimension. We discussed the considerations that surround Parent-Child dimensions, such as the recursive nature of their data sources, and various actions that must be handled differently in their creation and maintenance. We created a Parent-Child dimension using the Dimension Wizard, within which we worked with levels and properties. Finally, we enabled values at the Parent level of our newly created Parent-Child dimension. In Article Five, Working with the Cube Editor, we reviewed, summarized and integrated many of the concepts and components that we had previously constructed individually in earlier lessons. We undertook a complete cube build "from scratch," pulling together all that we had learned, to demonstrate the assembly of a cube more sophisticated than the cube we had generated in our first lesson with the Cube Wizard.

In Article Six, Exploring Virtual Cubes, we introduced the concept of virtual cubes, and practiced their creation and use. We discussed the options that virtual cubes provide, from the often-intermingling perspectives of consolidation of multiple data sources, presentation enhancement and control, and other functionality. Through the use of hands-on illustrations, we demonstrated some of the options that virtual cubes offer to extend the functionality and capabilities of individual OLAP cubes

In Articles Seven and Eight, which comprise the two-part tutorial, Custom Cubes: Financial Reporting, we established as our primary objective the construction of a simple cube to meet some illustrative business requirements, revolving around basic Income Statement financial reporting. We expanded upon many of the concepts we have introduced at some level in earlier lessons, involving the integration of cubes, as well as a host of information about cube components and general cube design and creation. We discussed some of the challenges that accompany cube design for financial reporting. In addition, we explored the use of Custom Members as an alternative approach to "merging" cubes, using cubes that we created under the scenario of a realistic business constraint - the absence of a single fact table that contained "all that we needed in one place" to meet the objectives of the cube's design.

We navigated the process of Parent-Child dimension creation to practice the steps, and introduced various new concepts that we had not encountered in the series up to this point, including the use of Custom Members and the handling of rollup and aggregation considerations. Among other concepts we discussed and put into action, we made use of a UNION ALL query to prepare a "virtual" fact table for more effective cube creation, introduced methods of sign and data type control within our presentation, and addressed formatting and other presentation considerations as we created a Financial Reporting cube that focused upon the Income Statement.

In our last lesson, Article Nine, we continued our series within Analysis Services through the now-familiar interface and associated dialogs, and expanded our exploration by grafting in, as an alternative approach, the creation of basic MDX expressions and queries for use with multidimensional data sources. Our focus was the establishment of the drillthrough capability within our cube models, from two distinct approaches, while examining some of the valuable uses of the drillthrough functionality from an information consumer perspective.

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:

  • Setting up a connection to an OLAP cube;
  • General navigation of member information and cube data;
  • Drilling down to details of reporting summaries;
  • Venturing beyond the " X-Y " dimensions, and making the PivotTable Report truly multidimensional;
  • Select formatting options for our Excel 2002 PivotTable Reports.

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.

Connecting Excel to the Cube

The PivotTable Wizard walks us through a guided process for connecting Excel 2002 to a given Analysis Services cube. We begin by taking the following steps:

1.      Open a new Excel 2002 workbook.

2.      Click Data (top menu), then select PivotTable and PivotChart Report, to initialize the PivotTable and PivotChart Wizard, as shown below:

Illustration 1: Menu Item for Initializing the PivotTable / PivotChart Wizard

The Step 1 of 3 Wizard dialog appears.

3.      Select the External Data Source radio button, as shown in the following illustration:

Illustration 2: The Step 1 0f 3 Wizard Dialog

  1. Ensuring that the "kind of report" selection is set to "PivotTable," click Next.

The Step 2 of 3 dialog appears, as shown below:

Illustration 3: The Step 2 0f 3 Wizard Dialog

Here we specify the source of our data. For this tutorial, we will use the sample OLAP cube called Sales.

5.      Click the Get Data button.

Microsoft Query starts, and presents the Choose Data Source dialog.

6.      Click the OLAP Cubes tab.

The dialog box appears as shown in Illustration 4 below.

Illustration 4: The Choose Data Source Dialog

7.      Click and highlight <New Data Source>.

8.      Click OK.

9.      Type Sales Cube in Box 1.

10.  Select Microsoft OLE DB Provider for OLAP Services 8.0 in Box 2.

The Create New Data Source dialog appears as shown below.

Illustration 5: The Create New Data Source Dialog

11.  Click the Connect... button.

The Multidimensional Connection dialog appears.

12.  Ensure that the Analysis server radio button is selected as the location of the multidimensional data source we wish to access.

13.  In the Server text box, type the name of the server, as shown below.

Illustration 6: The Multidimensional Connection Dialog

In the illustration above, I supplied MOTHER (the name of my server PC) into the Server box. Optionally, the name localhost can be supplied, if Excel and the cube share the same server, according to the Microsoft documentation.

14.  Click Next.

The Multidimensional Connection Select the database ... dialog appears, asking that we select the target database / OLAP Data Source. Here we will select the FoodMart 2000 database that accompanied the Analysis Server installation, as we see below.

Illustration 7: Select the FoodMart 2000 Database

15.  Click Finish.

The Create New Data Source dialog reappears, with the new target data source indicated to the right of the Connect... button.

16.  Select the Sales cube in Box 4.

The FoodMart 2000 sample database supplies several other cubes, any of which could be selected here as a data source.

After selecting the Sales cube, the Create New Data Source dialog should resemble the illustration below.

Illustration 8: The Completed Create New Data Source Dialog

17.  Click OK.

We return to the Choose Data Source dialog.

18.  Ensuring that the Sales Cube data source remains selected, (as shown in Illustration 9 below), click OK to return to the Step 2 of 3 dialog, where we left off with the PivotTable and PivotChart Wizard.

Illustration 9: Our Sales Cube Data Source is Selected

Once we return to the Step 2 of 3 dialog, notice, as in the illustration below, that "Data fields have been retrieved" now appears to the right of the Get Data button.

Illustration 10: Indication that Data Fields have been Retrieved

  1. Click Finish.

An empty PivotTable report appears, allowing us to begin browsing the cube / designing the report immediately. In addition to the PivotTable report template, the PivotTable toolbar and the PivotTable Field List appear; the PivotTable Field List provides a selection of report building components (we discuss these in the next section). The Analysis Server is now providing the dimension and measures information to the PivotTable report directly from the cube.

Layout and Navigation of the PivotTable Report

The PivotTable report is composed of the four general sections, as shown in Illustration 11 below. We exploit the power of OLAP in our PivotTable report by simply placing our dimensions in the sections in such a way as to present data in the desired combinations. As we drag and drop the dimensions and their members into different positions, the measures we have placed in the Data Items section change to match the new placement of the combinations. Values are therefore presented in the context of the axes.

Illustration 11: The PivotTable "Map"

Dimensions and measures are presented as items on the PivotTable Field List, which, for our current example, appears below. Each item is paired with one of two types of icons that represent dimensions and measures respectively. The dimension icons appear as tiny "reports" or "tables;" the measure icons contain a characteristic "01 10" pattern.

Illustration 12: The Items of the PivotTable Field List

The PivotTable Field List items are the main ingredients of the Excel PivotTable report. The PivotTable Field List can be anchored to either side of the Excel window ("docked") simply by dragging it to the desired location, making it a fixed target (something I find easier to handle than the "floating" approach. I have docked mine in the illustration of the PivotTable Report displayed in Illustration 13 below, as well as others), where it can be made to disappear and reappear easily with the rightmost button (default position) on the PivotTable Toolbar. The same is true for the PivotTable Toolbar, except that it can be docked at the top, bottom, or sides of the window, along with the other toolbars, and can be retrieved from hiding with the View ` Toolbars ` PivotTable selection sequence from the top menu.

Browsing Our Cube Data

A PivotTable report is highly flexible in that it serves as both a browser and a report writer. As we have seen, the dimension and measure components of the PivotTable report appear on the PivotTable Field List. We are restricted to dragging dimensions to the axes, and measures to the Data section, so potential confusion is eliminated to a large extent. Indications as to the nature / identity of the toolbar objects are a fringe benefit of our connection to the OLAP cube, as we shall see.

Let's begin a basic Browse process to illustrate the steps involved:

1.      Drag the Store Sales item (a measure) from the PivotTable Field List to the Data section (the portion of the PivotTable area with "Drop Data Items Here" appearing in gray). A tiny icon appears in the image of the four-part map of the PivotTable area. The "data" section of the icon image is blue, indicating that the item we are dragging is a measure.

2.      Drop the Store Sales item in the "Drop Data Items Here" (the "Data") section of the PivotTable "map."

Illustration 13: The Store Sales Measure in the Data Section (PivotTable Field List Docked)

Most of the section highlights disappear, as shown above, and the PivotTable report displays the Total of Store Sales.

3.      Drag the Store dimension to the row axis of the PivotTable report, where "Total" appears at present for Store Sales (the icon that appears as we drag will indicate the only allowed drop points, as the icon indicates when the dimension is in "drop territory.").

The PivotTable report now appears as shown below. We see Store Country has become the row header label.

Illustration 14: The Store Dimension in the Row Axis

4.      Next, drag the Time dimension to the column axis of the PivotTable report, where the empty cell appears to the right of "Store Sales" and above "Total," as shown in Illustration 15. (Once again, the icon indicates when we are in the correct position for dropping).

Illustration 15: The Time Dimension in the Column Axis

5.      Drag the Store Type dimension to the page axis of the PivotTable report (the blue outlined area at the top left corner of the worksheet, which probably still indicates "Drop Page Fields Here"), as shown in Illustration 16.

Illustration 16: The Store Type Dimension in the Page Axis

Let's center the column headings to enhance the appearance of the new report.

6.      Select and click Table Options from the PivotTable menu on the PivotTable toolbar, as shown below.

Illustration 17: Selecting the Table Options Dialog

The PivotTable Options dialog appears (as shown in Illustration 18). Many formatting and other global setpoints appear here. We will click the Merge labels checkbox.

For information regarding the purposes of the other setpoints, see the online Help and other documentation.

Illustration 18: The PivotTable Options Dialog

7.      Click OK.

Compare the results set to that shown below.

Illustration 19: Format Changes Appear in the PivotTable Report

We see that the labels are now centered.

Viewing Member Details

The capability to "explode" the hierarchical levels of our dimensions to member children enhances business user data analysis by allowing them not only to view the reports to which they have become accustomed (for example, a trending of monthly organizational expenses), but also to drill down to the details below any summary value. This allows the analyst to see the details that make up the value that he / she drills down upon. The beauty of multidimensional analysis becomes clear, with this interactive ability to find root causes for changes in activity over time. Information consumers can also return to higher levels within hierarchies to view summary information.

The PivotTable report / cube combination provides the ability to view various levels of activity for members as a group, as well as to analyze the details of summary values on an individual member basis, as we will see. We can explore the drill-down capabilities of the PivotTable report by performing the following actions:

8.      Double-click the USA Store Country in the leftmost column of our existing report.

The report drills to the Store States, the immediate children of the Store Country, as shown below.

Illustration 20: Drilling Down to the Children of the Store Country USA

9.      Double click the Washington (WA) Store State to drill to its children.

The children of the Washington Store State appear, as shown below.

Illustration 21: Children of the Washington Store State

We can always reverse a drill-down action ("drill up," as it were) by double-clicking the original drill-down object. We will short-circuit the process and zoom up to the original Store Country level.

Double-click the USA Store Country once again. The result set should resemble that depicted below.

Illustration 22: Zoom Up to the USA Store Country

10.  Double-click USA again, to expose the three Store States once more. Click (once, to highlight) the Store State level heading (just above the CA member) on the leftmost side of the report, and then click the Show Detail button on the PivotTable toolbar (see Illustration 23 below for the Show Details and Hide Details button pictures). This provides another means of drilling down to the immediate children.

Illustration 23: The Show Details and Hide Details Buttons

The children of all members of the Store State level appear, as illustrated below.

Illustration 24: Children of the Entire Store State Level

Next, let's assume that we want to hide the "USA" column of the report (logical enough, if all our stores are located in the US - the "USA" level is somewhat redundant, and takes up useful real estate.)

11.  Right-click the Store Country level heading, and click Hide Levels in the context menu that appears, as shown below.

Illustration 25: The Context Menu - Hide Levels Option

This leaves us with a view that is more compact. Compare the result to that shown in Illustration 26.

Illustration 26: The PivotTable Report, sans the USA Store Country Column

There are many other options in browsing our cubes, as well as with formatting the views we generate. It pays to invest some time experimenting with the plethora of available choices, and determining the combination of setpoints needed to get information to the targeted consumers in a fashion that will be most useful to them. Next, we will delve a bit further into the concepts of adding multidimensionality to our reports, and demonstrate a straightforward approach to leveraging even more of the power of our OLAP cube to deliver analysis-focused data.

"Going Multidimensional" in Cube Reporting

We will take the a few steps to demonstrate how we can unleash the multidimensional power of our cubes, by making multiple dimensions share the same axis in our PivotTable report. The PivotTable report provides a full realization of the information presentation potential of the multidimensional cube by intersecting dimensions on a single axis, and therefore delivers the full impact of multidimensional data in the "two-dimensional world" of the typical reporting environment.

We will begin by making the Store Type a part of the row axis to make analysis of Store members more powerful, yet more compact.

1.      Drag the Store Type item from the page axis (upper left hand corner of the PivotTable report) to the left of the Store State column, dropping it to the left of Store State. The result set should resemble that shown in Illustration 27.

Illustration 27: Combining the Store Type and Store Dimensions in the Row Axis

We can easily tell that only the Store Type label represents the top level of a dimension, as a drop-down arrow only appears at top levels. We can see in this simple scenario that multiple dimensions (in our case the Store Type and Store dimensions), as well as multiple levels of a given dimension (here, the Store State and Store City levels of the Store dimension), can co-exist on a single axis. The possibilities that emerge are far reaching, indeed.

Now let's add another measure to the report - Unit Sales - to give us more information about store performance.

2.      Drag the Unit Sales item from the PivotTable Field List to the Data section of the PivotTable report (the column under the Year heading in our present report). Once we drop the measure, the PivotTable report should resemble the illustration below.

Illustration 28: Dual Measures now appear in the PivotTable Report (Partial Illustration)

A new Data column appears, and in accordance with its default behavior, represents what appears to be a fourth row dimension.

3.      Drag the Data label to the column cell just above its present position. The PivotTable report now appears as illustrated below.

We have placed the label in the column axis, which is probably more along the lines of our presentation needs.

Illustration 29: The PivotTable Report with a Second Column Dimension

We will move the Time dimension to the page axis to make our presentation a bit less confusing for its audience.

4.      Drag the Year dimension item to the page axis in the top row of the PivotTable report.

The report appears as shown in Illustration 30, once we make this final change. Making a PivotTable report truly multidimensional is both straightforward and intuitive, once we get a good understanding of the basics.

Illustration 30: The PivotTable Report with Final Changes

Keep in mind that hiding either measure is as simple as clicking the drop-down arrow next to the Data dimension button and clearing the respective check box.

We can further improve the appearance of our report by making any of a myriad of format adjustments from the standard Excel formatting options, or from the AutoFormat choices that we can access from the Format Report button on the PivotTable toolbar. Experiment with these to find a style that approaches the needs of the information consumers.

Next in Our Series ...

In this lesson, Reporting Options for Analysis Services Cubes: Microsoft Excel 2002, we explored features that integrate Analysis Services and Excel 2002 to provide a vehicle for client reporting and other business intelligence pursuits. The central focus of the article was a basic overview of the Excel PivotTable Report functionality. We examined the PivotTable Report in its role of local "client representative" of the Analysis Server.

We exposed PivotTable Report features that are available with Microsoft Excel 2002 (most of which were available in Excel 2000) for creating robust and flexible reports. We explored setting up a connection to an OLAP cube, creation of the PivotTable Report, and the general navigation of member information and cube data. We practiced Drilling down to (and zooming up from) details of reporting summaries, then exposed the use of intersected dimensions to make the PivotTable Report truly multidimensional. We discussed a few formatting options at relevant junctures in our exploration of the Excel 2002 PivotTable Report.

In our next lesson, Reporting Options for Analysis Services Cubes: Microsoft FrontPage 2002, we will examine some of the options offered by the Office PivotTable List (the FrontPage "equivalent" of the Excel PivotTable Report) for report building with Analysis Services cubes. As we did with the Excel PivotTable Report in the first of our Reporting Options articles, we will present an introduction to using FrontPage 2002 to retrieve and display information from our cubes, first discussing the steps needed to define our data source, and to establish a connection to the cube. Next, we will expose the layout of the PivotTable List and its navigation, and explore its use in browsing and reporting our cube data. We will discuss the intersection of dimensions in the PivotTable report axes to achieve multidimensional reporting within the classical two-dimensional presentation of print media and PC screens, as well as general formatting considerations at relevant points in the tutorial.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved