Reporting against
a Cube with Excel
This lesson focuses on getting you ready to start using Excel to perform and deliver ad hoc
analysis with data and features from your Analysis Services cubes. Excel is the most widely
used client for business intelligence and ad hoc analysis because of its powerful features, rich
formatting, and native integration with SQL Server and Analysis Services. In this lesson we
will walk through accessing your data and performing common types of analysis.
We will be working with the Data tab in the Office ribbon menu at the top of your screen in
Excel. Take a few minutes and get familiar with this tab: even if you don’t know what the but-
tons do, just read through the captions to get an idea of the kinds of things you will be doing
from here. You can connect to and retrieve data from data sources, sort and filter, implement
grouping, and even perform some “what if” analysis using your cube data.
It’s time to browse this AdventureWorks2008 cube together and see what you can do with it.
You start with an empty worksheet and connect to your cube as shown in Figures 31-1, 31-2,
31-3, and 31-4.
889633c31.indd 285 8/16/10 9:12:08 PM
LESSON 31 RepoRting against a Cube with exCel
When you connect to a cube using Excel, you are making an ODBC connection that will be stored in an
.odc file on the user’s machine. By default, these files are stored here: C:\Users\User name\Documents\
My Data Sources. The connection wizard is the standard ODBC Connection Wizard you have seen
elsewhere in Microsoft Ofce and Visual Studio.
First you will need to decide what type of authentication to use (Figure 31-2). Since Analysis Services
supports only integrated authentication, or Active Directory Windows-based authentication, this is
the one we will choose. If you’re running a typical installation of the AdventureWorks2008 sample
database on your local machine, your server name will likely be <localhost>.
Click Next to pick the Analysis Services database and specific cube or perspective you want to connect
to. You can see your options in Figure 31-3.
You will see a number of options to choose from. Here is not only the entire cube, but some indi-
vidual perspectives that were created in the cube. Perspectives are non-security-bound views of the
cube that limit the user to a selected number of dimensions and measures to make her analysis more
concise and deliver it faster. For this analysis you want to have everything at your disposal so choose
the Adventure Works Cube and click Finish. Next select PivotTable Report as in Figure 31-4.
Before you click OK there is one other thing you should look at.
Excel charts and tables are data-bound, so they have a number of settings to configure data refresh pref-
erences. You can choose to refresh on file open; configuring either a set refresh schedule or completely
manual refreshes is possible according to the needs of your organization. Click the Properties button in
the lower left of the window in Figure 31-4 and you should get a dialog like the one in Figure 31-5.
This dialog is where you can configure some of the settings for the
specific connection you are about to make. This connection will cre-
ate the local .odc file to store the ODBC Connection information.
Much of this will be based on user preference, and the connection
can be configured not to automatically try to refresh in case you’re
away from the network, or don’t have access to the data source.
Here you can also choose to honor the extra features from the OLAP
Server, including fill color, text color, and so on. You can control
drill-through volume and specify a language preference if transla-
tions are set up in Analysis Services. This language feature means
the LocaleID property is read from Windows and applied to any
889633c31.indd 286 8/16/10 9:12:08 PM

Get Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint® now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.