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
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
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.
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
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.
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:
up a connection to an OLAP cube;
navigation of member information and cube data;
down to details
of reporting summaries;
beyond the " X-Y " dimensions, and making the PivotTable Report
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
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:
Open a new Excel 2002
Click Data (top menu),
then select PivotTable and PivotChart Report, to initialize the
PivotTable and PivotChart Wizard, as shown below:
1: Menu Item for Initializing the PivotTable / PivotChart Wizard
The Step 1 of 3 Wizard dialog
Select the External Data
Source radio button, as shown in the following illustration:
2: The Step 1 0f 3 Wizard Dialog
- 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
Click the Get Data
Microsoft Query starts,
and presents the Choose Data Source dialog.
Click the OLAP Cubes
The dialog box appears
as shown in Illustration 4 below.
4: The Choose Data Source Dialog
Click and highlight <New
Type Sales Cube
in Box 1.
Select Microsoft OLE DB
Provider for OLAP Services 8.0 in Box 2.
The Create New Data
Source dialog appears as shown below.
5: The Create New Data Source Dialog
Click the Connect...
Connection dialog appears.
Ensure that the Analysis
server radio button is selected as the location of the multidimensional
data source we wish to access.
In the Server text box,
type the name of the server, as shown below.
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.
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
7: Select the FoodMart 2000 Database
The Create New Data
Source dialog reappears, with the new target data source indicated to the
right of the Connect... button.
Select the Sales cube in
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
Illustration 8: The
Completed Create New Data Source Dialog
We return to the Choose
Data Source dialog.
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.
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
10: Indication that Data Fields have been Retrieved
- 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
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.
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"
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
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:
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.
Drop the Store Sales item
in the "Drop Data Items Here" (the "Data") section
of the PivotTable "map."
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.
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
14: The Store Dimension in the Row Axis
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).
15: The Time Dimension in the Column Axis
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.
16: The Store Type Dimension in the Page Axis
Let's center the column headings to enhance the appearance of the
Select and click Table
Options from the PivotTable menu on the PivotTable toolbar, as shown below.
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.
regarding the purposes of the other setpoints, see the online Help and
18: The PivotTable Options Dialog
Compare the results set
to that shown below.
19: Format Changes Appear in the PivotTable Report
We see that the labels are now centered.
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
Double-click the USA Store
Country in the leftmost column of our existing report.
report drills to the Store States, the immediate children of the Store
Country, as shown below.
20: Drilling Down to the Children of the Store Country USA
Double click the Washington
(WA) Store State to drill to its children.
children of the Washington Store State appear, as shown below.
21: Children of the Washington Store State
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
the USA Store Country once again. The result set should resemble
that depicted below.
22: Zoom Up to the USA Store Country
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.
23: The Show Details and Hide Details Buttons
The children of all
members of the Store State level appear, as illustrated below.
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.)
Right-click the Store
Country level heading, and click Hide Levels in the context menu
that appears, as shown below.
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: 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.
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
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.
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.
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
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
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.