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.

Introduction

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:

The Step 1 of 3 Wizard dialog appears.

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

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

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

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.

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.

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.

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.

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.

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.

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.

19. 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.

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.

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."

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.

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).

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.

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.

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.

7.      Click OK.

Compare the results set to that shown below.

We see that the labels are now centered.