Business Intelligence Architect Bill Pearson takes a look
at Excel 2003 as a reporting option for Analysis Services cubes. He then
provides an overview of the Microsoft Office Excel Add-in for SQL Server
About the Series ...
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
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
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.
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.
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.
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:
Setting up a connection
to an OLAP cube;
navigation of member information and cube data;
down to details of
beyond the " X-Y " dimensions, and making the PivotTable Report
for our Excel 2003 PivotTable Reports;
Using the Excel Add-in for SQL Server
Analysis Services as
another fully featured, reporting and analysis option for Excel 2003 and
Analysis Services cubes.
Reporting Options for Analysis Services Cubes: Microsoft Excel 2003
MS Office 2003 continues to build upon the
robust OLAP reporting features for the desktop user that began to appear, in
earnest, in MS Office 2000. The basic operation of the PivotTable
report has remained the same as with previous versions. We will examine
the PivotTable report in a hands-on fashion in the sections that follow,
to update the information we provided in Reporting Options for Analysis Services Cubes: MS Excel
2002. Next we
will examine another Excel 2003 OLAP reporting option, the Microsoft Office
Excel Add-in for SQL Server Analysis Services, discussing its capabilities
and providing information regarding where to obtain this exciting add-in.
PivotTable Reports in MS Office Excel 2003
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 Warehouse
sample cube that comes along with Analysis Services. We will use the PivotTable
Wizard found in Excel 2003 in our initial efforts. The procedures
we cover are essentially the same for Excel 2000 and 2002,
although some of the terminology used in the latter, as well as the appearance
of dialogs / other objects, differ, to a degree.
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
Connecting Excel to
Wizard walks us through a guided process for connecting Excel 2003 to
a given Analysis Services cube. We begin by taking the following steps:
1. Open a new Excel 2003 workbook.
2. Click Data (top menu), then
select PivotTable and PivotChart Report, to initialize the PivotTable
and PivotChart Wizard, as shown in Illustration 1.
Illustration 1: Initializing
the PivotTable / PivotChart Wizard ...
The PivotTable and
PivotChart Wizard - Step 1 of 3 dialog appears.
3. Select the External Data Source
radio button on the Step 1 of 3 Wizard dialog.
the radio button to the immediate left of "PivotTable" is
selected in the lower half of the dialog, underneath "What kind of
report do you want to create?"
The PivotTable and
PivotChart Wizard - Step 1 of 3 dialog appears as depicted in Illustration
Illustration 2: The Step
1 0f 3 Wizard Dialog, with Settings
and PivotChart Wizard - Step 2 of 3 dialog appears, as shown in Illustration
Illustration 3: The Step
2 0f 3 Wizard Dialog
we specify the source of our data. For this tutorial, we will use the sample Warehouse
cube that accompanies an installation of Analysis Services.
Click the Get
Query starts, and
presents the Choose Data Source dialog.
Click the OLAP
dialog box appears as depicted in Illustration 4.
Illustration 4: The
Choose Data Source Dialog
highlight <New Data Source>, as necessary.
New Data Source dialog appears.
following in Box 1 of the dialog, where we name the
OLE DB Provider for OLAP Services 8.0 in Box 2.
New Data Source dialog appears as shown in Illustration 5.
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.
Multidimensional Connection dialog appears, as depicted in Illustration
6 (where we see the name of one of my servers, MOTHER).
6: The Multidimensional Connection Dialog
NOTE: The name localhost can be supplied,
if Excel and the cube share the same server, according to the Microsoft
The next dialog of the Multidimensional
Connection appears, asking that we select the targeted Analysis Services
database. Here we will select the FoodMart 2000 database that
accompanied the Analysis Server installation, as shown in Illustration
7: Select the FoodMart 2000 Database
NOTE: The databases that appear within the selection list
of the Multidimensional Connection - Select the database ... dialog will
differ, depicting those Analysis Services databases that exist within
your own environment.
The Create New Data
Source dialog reappears, with the new target data source indicated to the
right of the Connect... button.
Select the Warehouse
cube in Box 4
of the dialog, where we notice
that all cubes in the Foodmart 2000 Analysis Services database appear.
After selecting the Warehouse
cube, the Create New Data Source dialog should resemble that depicted in
8: The Completed Create New Data Source Dialog
return to the Choose Data Source dialog.
the Warehouse Cube data source remains selected, as shown in Illustration
Illustration 9: Our
Warehouse Cube Data Source is Selected
Click OK, to return to the Step 2 of 3
dialog, where we left off with the PivotTable and PivotChart Wizard.
2 of 3 dialog appears, as depicted in Illustration 10. Note that a
new message, "Data fields have been retrieved," now appears to
the right of the Get Data button.
10: Indication that Data Fields have been Retrieved (Message Circled)
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
report is composed of the four general sections. We exploit the power of
OLAP in our PivotTable report by simply juxtaposing our
dimensions in the sections in such a way as to present data in the desired
combinations - at the "intersects" of the dimensions, as it were. 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 within the
context of the axes. The four sections of the PivotTable report are
shown in Illustration 11.
Dimensions and measures are presented as items
on the PivotTable Field List, which, for our current example, appears in
12: The Items of the PivotTable Field List
As we can see, 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"
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 illustrations of the PivotTable Report depicted
throughout the article), and 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 "candidate" 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
Let's begin a basic Browse
process to illustrate the steps involved:
Drag the Warehouse Sales
item (a measure) from the PivotTable Field List to the Data
section (the portion of the PivotTable area where "Drop Data
Items Here" appears 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
Drop the Warehouse Sales
item in the "Drop Data Items Here" (the "Data")
section of the PivotTable "map."
Warehouse Sales appears within the PivotTable as depicted
in Illustration 13.
Illustration 13: The
Warehouse Sales Measure in the Data Section (with Docked, Partial PivotTable
Field List in Place)
of the section highlights disappear, as shown, and the PivotTable report
displays the Total of Warehouse Sales in the cube.
Select the Warehouse
Area selected in the selector under the Field List, click the Add
To button to its immediate left.
report now appears as shown in Illustration 14, where we see that Warehouse
Country has become the row header label.
Illustration 14: The
Warehouse Dimension in the Row Axis
Drag the Time
dimension to the column axis of the PivotTable report (an
alternative means to using the selector / Add To button for placing Field
List items), where the empty cell appears to the right of "Warehouse
Sales" and above "Total," in the area marked "X"
in Illustration 15.
Illustration 15: Drop
Point for the Time Dimension in the Column Axis
mouse pointer becomes an icon, to indicate when we are in the correct position
Drop the Time
item in the cell indicated.
1997 and 1998 (both Year levels of the Time
dimension) appear as column header labels within the PivotTable, as
depicted in Illustration 16.
Illustration 16: The
Time Dimension in the Column Axis
Drag the Product
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
Illustration 17: The
Product Dimension in the Page Axis
center the column headings to enhance the appearance of the new report.
click Table Options from the PivotTable menu on the PivotTable
toolbar, as depicted in Illustration 18.
Selecting the Table Options Dialog
Options dialog appears. Many
formatting and other global settings are represented within this dialog.
Leaving all other
settings at default, click the checkbox to the immediate left of Merge
Options dialog appears, with our modification, as shown in Illustration
Illustration 19: The
PivotTable Options Dialog
NOTE: For information regarding the
purposes of the other settings, see the online Help and other
Click OK to
save our change, and to dismiss the PivotTable Options dialog.
report appears, with the column labels now centered, as depicted in Illustration
Illustration 20: Format
Changes Appear in the PivotTable Report
Viewing Member Details
capability to "explode" the hierarchical levels of our dimensions to member
children enhances data analysis by allowing business users 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
supporting any summary value. This allows the analyst to see the details that make
up the value upon which he / she drills down. The beauty of
multidimensional analysis becomes clear, with this interactive ability to find
root causes for changes in activity over time (among myriad other values).
Information consumers can also return to higher levels within hierarchies to
view summary information.
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:
Within the Warehouse
column of the existing report, double-click the cell containing USA.
The report drills down to the Warehouse State Province, the immediate children of
the Warehouse Country, as shown in Illustration 21.
the Washington (WA) Warehouse State Province to drill to its children.
The children of the Washington Warehouse State Province appear, as depicted in Illustration
Children of the Washington Warehouse State Province
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 Warehouse Country
level, where we began drilling earlier.
the USA Warehouse Country once again.
The resulting dataset should resemble that depicted in Illustration
Illustration 23: Zoom Up
to the USA Warehouse Country
Double-click USA again, to expose the three Warehouse
State Provinces once more.
to highlight) the Warehouse State Province level heading (above the CA
member, and to the right of the Warehouse Country column.
Click the Show
Detail button on the PivotTable toolbar (another means of drilling
down to the immediate children), as shown in Illustration 24.
Illustration 24: Another
Means of Drilling Down - All Members of a Level Simultaneously
children of all members of the State Province level appear, as depicted in Illustration
Children of the Entire State Province Level
let's assume that we want to hide the "USA" column of
the report (logical enough, if all our stores are located in the United States - the "USA"
level is somewhat redundant, and takes up useful real estate.)
the Warehouse Country level heading.
Levels in the context menu that appears, as shown in Illustration 26.
Illustration 26: The
Context Menu - Hide Levels Option
leaves us with a view that is more compact, similar to that depicted in Illustration
Illustration 27: The
PivotTable Report, sans the USA Warehouse Country Column
are many other options for 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 settings 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
will take the a few steps to demonstrate how we can further 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 when we intersect
dimensions on a single axis, and therefore delivers the full impact of
multidimensional data in the "two-dimensional world" of the typical
will begin by making Product a part of the row axis to make
analysis of Warehouse members more powerful, yet more compact.
Drag the Product
item from the page axis (upper left hand corner of the PivotTable
report) to the right of the currently appearing Warehouse City column.
Dropping the Product
item to the right of City.
result set should resemble that partially shown in Illustration 28.
easily tell that only the Product Family 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 Warehouse and Product dimensions), as well as multiple levels
of a given dimension (here, the Warehouse State and Warehouse City levels of the Store
dimension), can co-exist on a single axis. The possibilities that emerge
are far reaching, indeed.
let's add another measure - Units Shipped - to the report, to
present information about Warehouse shipping activity that coincides
with Warehouse sales.
Drag the Units
Shipped item from the PivotTable Field List to the Data
section of the PivotTable report (the column under the Year
heading and 1997 in our present report).
Drop the Units
Shipped item on the top cell in the column, as depicted in Illustration
Illustration 29: Drop
Point for the Units Shipped Item
we drop the measure, the PivotTable report appears as shown in Illustration
30: 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. "Data" itself represents
a "measures dimension," as it were.
Drag the Data label to
the column cell just above its present position, as depicted in Illustration
Illustration 31: Drag the
Data Label Up to Swap Axes ...
Drop the Data label in
the cell above its present position.
We have placed the
label in the column axis, which could be more along the lines of our
presentation needs. The PivotTable report now appears as partially
shown in Illustration 32.
32: 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.
Drag the Year
dimension item to the page axis in the top row of the PivotTable
report appears as partially depicted in Illustration 33, once we
make this final change.
Illustration 33: The
PivotTable Report with Final Changes
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.
can see, making a PivotTable report truly multidimensional is both
straightforward and intuitive, once we get a good understanding of the basics.
We can further improve the appearance of our report by making any of a myriad
of adjustments within the standard Excel formatting options, or from the AutoFormat
choices that we can access from the Format Report button on the PivotTable
toolbar. We can experiment with these to find a style that approaches the
needs of the information consumers within virtually any reporting and analysis
-> Exit to close Microsoft Excel 2003,
saving the PivotTable report to a convenient location, as appropriate.
examined the PivotTable report for Excel 2003 to the extent of
familiarity with which we explored it for its predecessor version, we will take
a brief look at another exciting OLAP reporting option for Excel in the section
Excel Microsoft Office Excel Add-in for SQL Server Analysis Services
The Microsoft Office Excel Add-in for SQL Server Analysis Services, available for free download from Microsoft at this writing, has proven itself to be a clever reporting and analysis tool within itself. I also recommend it to clients and readers who wish to learn Analysis Services, and particularly MDX, concepts from a practical, user-friendly perspective. The Add-in provides flexible reporting and analytical capabilities for data imported into Excel from Analysis Services cubes, where Analysis Services acts as a middle-tier server for OLAP and data mining.
The Analysis Server still manages the cubes and provides rapid client access to cube information, so the Add-in effectively provides another reporting option with Excel as the client. The Add-in for SQL Server Analysis Services provides integration between Microsoft Office XP and Office 2003 editions and Analysis Services (Service Pack 2).
The Add-in for SQL Server Analysis Services is particularly useful in that it allows us to put our existing Excel skills to work to quickly and easily create reports that meet the business needs of our respective environments. This tool provides an excellent means of equipping one's organization with a refined capability to identify opportunities, and to respond to business conditions, as well as to perform in-depth analysis and reporting within the realms of planning, forecasting and budgeting, as well as sales and marketing and a host of other arenas that impact our businesses. It allows us to easily access the potentially large volumes of data that our cubes contain by providing a means to import data from one or more Analysis Services cubes, and to then use a combination of Analysis Services and Excel techniques and functionality for a wide range of analysis, manipulation and presentation capabilities to support corporate decision making.
The capabilities we can leverage with the Add-in for SQL Server Analysis Services are many. They include:
- Importing data from multiple Analysis Services cubes;
- Combining data from multiple dimensions to obtain a multidimensional perspective;
- Performing drillthrough to see underlying source data (the transactions that make up balances, etc.);
- Specification of filter conditions;
- Performing ranking;
- Performing writeback to cubes (excellent client input application for budgets, forecasts and the like);
- Performing what-if analysis (in conjunction with writeback and otherwise);
- Leveraging actions resident in targeted cubes;
- Using page filters (in addition to the more pedestrian row and column filters) to display subsets of data;
- Leveraging server-side formatting;
- Using enhanced navigation capabilities;
- Creating free-form and structured reports (the two primary types within the Add-in);
- Using Excel formatting and calculation features.
The Microsoft Office Excel Add-in for SQL Server Analysis Services is available at the following link:
Documentation and white papers that are also available on this page provide a great introduction to this powerful add-in. I have used the Excel Add-in for SQL Server Analysis Services recently in providing client writeback capability within an engagement where no other client existed that was adequate for this purpose. In addition, I have found the reporting capabilities worthwhile for scenarios where a full-blown OLAP reporting solution, such as SQL Server Reporting Services, was either not required, or where an interim need existed for which an integrated, industrial strength solution was being developed for later implementation. Moreover, the Microsoft Office Excel Add-in for SQL Server Analysis Services has often served me as a convenient means of independent verification of results obtained in Reporting Services or other reporting applications, and as an overall tool to support such peripheral needs.The add-in provides two reporting modes, free-form and structured, each with its own features. For more information, see Free-form and structured reports.
In this article, we explored the integration of Analysis Services and MS Office Excel 2003, to present updated options for client reporting and other business intelligence capabilities. The central focus of the article was a basic, hands-on review of the Excel 2003 PivotTable Report. Within the context of the article, we examined the role of Excel 2003 as the "client representative" of the Analysis Server, and examined ways to leverage the multidimensional reporting and analysis capabilities supported by Analysis Services cubes.
Our examination of the PivotTable Report features available in Excel 2003 included PivotTable Report options that provide for creating robust and flexible reports. As part of our exploration, we performed hands-on practice that included setting up a connection to an OLAP cube, as well as navigating member information and cube data. We drilled down to details of reporting summaries, and ventured beyond the " X-Y " presentation of dimensions, creating a page "slice" and working with dimension combinations to make our PivotTable report even more multidimensional. We also examined formatting options within the report.
In addition to our examination of the once-solitary Excel option for OLAP reporting and analysis, the Excel 2003 PivotTable Report, we provided a brief summary of another Excel 2003 OLAP Reporting option, the Microsoft Office Excel Add-in for SQL Server Analysis Services, as well as direction as to where the Add-in might be obtained.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.