Put local cubes to work in an integrated
MSAS / Office 2003 environment. Join MSAS Architect Bill Pearson in a hands-on
introduction to local cubes and approaches to their creation.
About the Series ...
is the twenty-third article of the series, Introduction to MSSQL Server
2000 Analysis Services. As I stated in the first article, Creating Our First
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, 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
Note: Service Pack 3 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, upon 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 ("MSAS")
(and uses MS Office 2000 and above, in cases where MS Office
components are presented in the article.)
last article, Actions
in Virtual Cubes,
we extended the
examination of Actions that we began in the article before it, Putting
Actions to Work in Regular Cubes, to the creation and use of Actions
within virtual cubes. After considering the differences between
regular and virtual cube scenarios, we created an Action in a sample
virtual cube we cloned from an existing sample. We then performed the import of
an example Action into our cloned virtual cube. Throughout the hands-on
practice exercises, we commented upon the results we obtained, to reinforce our
understanding of the concepts involved.
this article, we will look beyond the confines of Analysis Manager to introduce the creation and use of
local cubes with Microsoft Office 2003 ("MS Office"). In
conjunction with MSAS, we will overview the concepts involved with our topic,
and then we will "drill down" into the practical aspects involved in
putting the functionality to work immediately. This offers us an opportunity
to explore the integration of MSAS OLAP with MS Office, a topic
that is highly valuable to the business community. While continuing to work
with the Analysis Server, we will examine, in this and the subsequent
article, ways that we can conduct much design and development outside MSAS
and within MS Office.
article will focus upon the creation of a local cube from an existing
Excel PivotTable report. In the article that follows this one, we will
shift gears and explore a more MSAS-centric route, through the use of
the OLAP Cube Wizard, to accomplish cube creation in a more flexible
manner. The intent of these articles is not to demonstrate making us
independent of MSAS within the creation phase, but to offer options for more
independence from the perspective of the information consumer. The objective
is also to make the fruits of MSAS OLAP available to enterprise team
members through the conduits of applications that are pervasive in the desktop
population we find in business today.
this lesson, we will:
how they differ from server-based cubes (the focus of virtually all the
articles in this series so far), and some scenarios in which they are
Create a local
cube from an existing
and deployment considerations, and the advantages and other value that local
cubes can provide the organization, both as remote production data sources
and as development prototypes.
An Introduction to Local Cubes
The number of mobile information
consumers in today's business environment is growing at exponential rates. More
consumers are using portable computers than ever before, with the trends
indicating a significant preference for portables over stationary desktops
because they can be transported easily. Many of my clients have built, or are
in the process of building, large sales organizations with mobile computing
platforms, and even those that are not as "sales intensive" have
witnessed a huge increase in the use of mobile PCs by internal staff and
The rise in the
general population of "disconnected users" is placing large demands
on the OLAP technologies that currently exist, and support of these mobile information
consumers requires the capability to grant them access to organizational data
without active connections. Local (or "offline")
cubes offer opportunities for analysts, be they salespeople, distributed
management or the host of other interested parties that cannot always be "wired
in" to the central corporate data stores, to be able to carry and work
with business intelligence applications and perform "disconnected analysis"
from virtually any location.
Understanding Local Cubes
Local cubes provide a means of answering many
of the requirements of the mobile user, as they are appropriate in situations
in which an Analysis Server is unavailable. A local cube
consists of a single, highly portable file that can live as easily on a laptop
computer as it can on a server. Information consumers with local cubes
on their PCs can browse and report from the cubes without the need for a connection
to an Analysis Server, or to the cube's original data source. Of the many
general types of cubes available via Analysis Services, local cubes are
the sole data sources that are truly portable.
MS Office is self-contained in its capacity
to support our needs to create local cubes, as well as to enable us to
use these portable OLAP data sources for analytical purposes. Like the cubes
we typically encounter within Analysis Services, local cubes have
dimensions with members, as well as measures. Local cubes
are, as one would expect, smaller than server-based cubes, and are not designed
with a few of the capabilities that we find in their server cousins. Nevertheless,
local cubes still retain many robust features that make them ideal for the "road
warrior" analyst, as well as for other, less obvious applications,
including the provision of capabilities to work when a network is down or
inordinately slow, and so forth.
Local cubes do not
offer the following features / capabilities that are typically found in server
storage capacity and structures
create parent-child dimensions.
The storage mode
for a local cube can be either multidimensional OLAP (MOLAP) or relational
OLAP (ROLAP). Using the ROLAP mode, the cube structure is
created, and the dimensions populated, but measure data is not
saved locally within the cube. Once created, we still need to be connected to
the cube's data source (and not simply Analysis Services) to
analyze the data it contains. Since ROLAP-mode local cubes store
no measure data, queries against them require more time to execute in
comparison to the same queries based upon MOLAP-mode local cubes.
As one might have guessed
from our discussion so far, the MOLAP variety will be the focus of our
lesson with local cubes. The MOLAP storage option does mean that the
cube takes longer to create, and that more disk space will be required to store
it compared to its ROLAP counterpart. However, the tradeoff is most
likely acceptable when we consider that our cubes have the data we need,
locally accessible and immediately ready for efficient and straightforward
analysis; indeed, this is the largest driver for the presence of the cubes in
the first place. To minimize creation time, as well as the space requirements
that result from our local cube design, we will need to plan judiciously
and be selective in our choices of what data needs to be included in our
Creating a Local Cube
In this and the
subsequent article, we will explore and practice the two primary means of
creating a local cube within Office 2003. Both approaches will
rely upon the PivotTable Service to create the cubes, but we will follow
two significantly different paths to reach the same destination. We will
undertake cube creation following each path, and discuss the differences and
nuances as we go.
Creating a Local Cube from an Existing Server Cube
first approach for creating our local cube, and the focus of this
article, will be to connect to the Analysis Server, as we did in our
Options for Analysis Services Cubes: MS Excel 2002, through the Excel PivotTable
report. We will then create a cube that represents a subset of the
data in a larger, server-based cube. In effect, we will create a PivotTable
report that is based upon source data from an OLAP cube on a server, and
then we will copy the source data to a separate file, called an offline cube
(.cub) file, that will be stored on our local disks.
stated earlier, the local cube will allow us to perform analysis and
write reports on the data in the new cube without being connected to a network,
or, as a variation, to continue working when the OLAP server is unavailable. We
can also use our local cube to make data from the OLAP database available on a network
share so that other users can create reports from it, if such action
We will start by
creating a connection from MS Excel to the data source. This will
parallel the steps we took in our article Reporting
Options for Analysis Services Cubes: MS Excel 2002, and will serve as an excellent "quick
refresher" of the procedure.
Connecting MS Excel to the OLAP Cube
Wizard provides a guided process for connecting MS Excel to the OLAP
cube. We begin by taking the following steps:
1. Open a new MS Excel 2003
2. Click the top left cell (cell A1)
of the new spreadsheet, to ensure that it is selected, before beginning our
procedures with the PivotTable and PivotChart Wizard.
3. Click Data --> PivotTable and PivotChart Report, on the main
menu, to initialize the PivotTable and PivotChart Wizard, as shown in Illustration
Initialize the PivotTable / PivotChart Wizard
The Step 1 of 3
Wizard dialog appears.
4. Select the External Data Source
5. Select the PivotTable radio
button under "What kind of report do you want to create?" (The
default) as shown in Illustration 2.
Illustration 2: The Step
1 0f 3 Wizard Dialog
2 of 3 Wizard dialog appears, as shown in Illustration 3.
Illustration 3: The Step
2 0f 3 Wizard Dialog
specify the source of external data from this dialog. For this practice
session, we will use the sample OLAP cube called Warehouse and Sales (primarily
because it deals with dimensions and measures that might be of interest to
remote sales teams and so forth, such as customer and lead time data).
and Sales cube is one of several great training cubes that are installed
with a Typical installation of MSSQL 2000 Analysis Services.
While virtually all the documentation and training material available these
days seems to focus on the Sales cube, I have found the "undiscovered"
cubes to hold value in many specialized instances.)
Click the Get
this is the first time we have accessed Microsoft Query (which the
button attempts to launch) we may be informed, via a message box, that MS Query
has not been installed. If this is the case, we are presented the option of
installing it - an option that we must take to complete this exercise. If
applicable, give consent to the installation, which occurs relatively rapidly,
then rejoin the exercise at this point.
Query starts, and
presents the Choose Data Source dialog.
Click the OLAP
dialog box appears a shown in Illustration 4.
highlight <New Data Source>.
following into Box 1 of the dialog.
Offline Cube 1
OLE DB Provider for OLAP Services 8.0 in Box 2.
New Data Source dialog appears as shown in Illustration 5.
Illustration 5: The
Create New Data Source Dialog
Click the Connect...
Connection dialog appears.
the Analysis Server radio button is selected as the location of
the multidimensional data source.
In the Server
text box, type the name of the server, as shown in Illustration 6.
Illustration 6: The
Multidimensional Connection Dialog
illustration above, I supplied MOTHER1 (the name of the server I
used for preparing this article) in the Server box. The name localhost
can be supplied if Excel and the cube share the same server machine.
authentication information if required.
second Multidimensional Connection 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 depicted
in Illustration 7.
New Data Source dialog reappears, with the new target data source indicated
to the right of the Connect... button.
Select the Warehouse
and Sales sample cube in Box 4.
options available for selection will depend upon which cubes are present in the
database. The FoodMart 2000 sample database supplies several other
cubes, any of which could be selected here as a data source.
selecting the Warehouse and Sales cube, the Create New Data Source
dialog should resemble that shown in Illustration 8.
Illustration 8: The
Completed Create New Data Source Dialog
return to the Choose Data Source dialog.
the Offline Cube 1 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.
return to the Step 2 of 3 dialog. We note, as depicted in Illustration
9, that "Data fields have been retrieved" now appears to
the right of the Get Data button.
Illustration 9: Data
Fields Have Been Retrieved ...
empty PivotTable report appears, with PivotTable Field List in
front, as shown in Illustration 10, allowing us to begin designing the PivotTable
report we need, as a basis for creating our local cube, immediately.
Illustration 10: An
Empty PivotTable Report Appears
Field List acts, effectively, to "prove" our connection to the
data source (as it displays the dimensions and measures of same),
in addition to providing all we need for report design. The PivotTable
toolbar, if not yet apparent, can be "called" by going to View
--> Toolbars --> PivotTable from the main menu.
Designing & Creating the Local Cube with the PivotTable Report
have a PivotTable report, with its connection to the Warehouse and
Sales server cube, in place, from which we can create our local cube.
The rest of the process is easy, although planning is critical to ascertain the
needs of the information consumers for whose remote use we are designing and
creating the local cube.
more information on the general creation and use of a PivotTable report, see Reporting
Options for Analysis Services Cubes: MS Excel 2002.
the Customers dimension on the PivotTable Field List.
In the lower
section of the PivotTable Field List, select Row Area.
Click the Add
button to add Customers to the Row Area of the PivotTable report.
the Time dimension on the PivotTable Field List.
In the lower
section of the PivotTable Field List, select Page Area.
Click the Add
button to add Customers to the Page Area of the PivotTable report.
the Supply Time dimension on the PivotTable Field List.
In the lower
section of the PivotTable Field List, select Data Area.
Click the Add
button to add Customers to the Data Area of the PivotTable report.
resulting, admittedly minimal, PivotTable report should resemble the one
shown in Illustration 11.
Illustration 11: The
Shell PivotTable Report
use the above as a basis for creating our local cube. We will see that this is
enough to get the process underway in the next steps.
Click the PivotTable
report to select it.
downward pointing arrow on the right side of PivotTable, on the PivotTable
OLAP ... from the dropdown menu, as depicted in Illustration 12.
Illustration 12: Select
Offline OLAP ... from the PivotTable Menu
OLAP Settings dialog appears, as shown in Illustration 13.
Illustration 13: The Offline
OLAP Settings Dialog
Click the Create
Offline Data File button on the Offline OLAP Settings dialog
(circled in red in Illustration 13 above).
Cube File - Step 1 of 4 dialog appears, as depicted in Illustration 14.
Illustration 14: The
Create Cube File - Step 1 of 4 Dialog
The Create Cube File - Step 2 of 4 dialog appears, as
shown in Illustration 15.
Illustration 15: The
Create Cube File - Step 2 of 4 Dialog
comparison to the items on the PivotTable Toolbar will reveal that the Create
Cube File Step 2 of 4 dialog contains all the dimensions that are
available for selection in the cube residing on the server. The checked boxes
indicate the dimensions that appear in the PivotTable report itself.
Note: This avenue of selection, within
the Create Cube File - Step 2 of 4 dialog, means we can do all selection
here, avoiding the need to create an elaborate PivotTable report that includes
all the dimensions and measures we want to incorporate into our local cube.
This is an opportunity to save time, and the reason that we put a minimal
amount of design into the PivotTable report earlier. (There is a
minimal requirement for data to enable the Offline OLAP Settings
option on the dropdown menu, however.)
37. Review the levels of the Customers dimensional hierarchy by expanding the Customers dimension (clicking on the "+" to the immediate left of the Customers dimension in the Create Cube File - Step 2 of 4 dialog).
38. Expand the Product dimension, as well.
We see the hierarchical members with checkboxes.
39. Select the following dimensions, making sure to click the top-level checkbox whether already checked or not, to ensure that the hierarchical members below the dimension are checked, as well:
We see the selected (checked) hierarchical members and their top-level members, as partially displayed in Illustration 16.
Click for larger image
Illustration 16: Partial View of the Create Cube File - Step 2 of 4 Dialog, Expanded Dimension Selections
40. Click Next.
41. Check the following, expanding as a part of the process, to see the underlying members:
- Store Cost
- Store Sales
- Supply Time
- Unit Sales
- Units Ordered
- Units Shipped
- Warehouse Cost
- Warehouse Sales
- Product Family:
- Store Country:
The Create Cube File - Step 3 of 4 dialog resembles that partially shown in Illustration 17.
Illustration 17: Partial View of the Create Cube File - Step 3 of 4 Dialog
This dialog presents the top level name for each dimension that we have, heretofore, selected, and, by allowing us to check more boxes, provides another chance to select from more levels and their expanded member sets. In addition, this is our first shot at selecting members of the Measures dimension (other than those we pre-selected in our initial design of the PivotTable report - in our case, the Supply Time measure only.
42. Click Next.
The Create Cube File - Step 4 of 4 dialog appears.
43. Click Browse, and select an appropriate location in which to store the local cube.
The Save As dialog appears.
44. Type Offline_Whse_&_Sales into the File Name box to name the new local cube file.
45. Click Save.
The Create Cube File - Step 4 of 4 dialog reflects our input, as shown in Illustration 18.
Illustration 18: The Create Cube File - Step 4 of 4 Dialog
Notice that the cube will be given a .cub extension.
46. Click Finish to create the cube.
Cube processing begins, and we are provided general status messages as to the various stages of processing that are taking place, by the Create Cube - Progress message box that briefly appears. When the cube finishes processing, the Progress dialog disappears, and we are left with an altered version of the Offline OLAP Settings dialog, as depicted in Illustration 19.
Illustration 19: The Modified Offline OLAP Settings Dialog
We can alternate at this stage, or at any time, between the original server cube and the local cube file as a data source for the PivotTable report by making our selection via the appropriate radio button on the Offline OLAP Settings dialog. This dialog is always accessible from the PivotTable toolbar, as we saw in beginning the creation of our local cube.
A prominent difference between the dialog at this stage and at the beginning of the cube creation process occurs in the button at its center. What was earlier captioned Create Local Data File is now titled Edit Local Data File; this change in the caption reflects the readiness, at this point of the dialog, to allow us to redefine the structure of our local cube.
We are now at a position to begin reporting from the local cube, just as we did from a server-based MSAS cube in Reporting Options for Analysis Services Cubes: MS Excel 2002. In our next article, we will explore an alternative means of creating a local cube whose structure resembles the one we created in this article, allowing us to contrast the two methods of cube creation as we undertake the new one.
47. Close the Excel worksheet, saving it as appropriate.
In this article, we ventured beyond earlier topics surrounding the retrieval and reporting of data from a server-based MSAS cube, and transitioned into the realm of remote, independent OLAP data source design and creation. We explored approaches to creating local cubes within MS Office, discussing many of the foundational concepts behind the architecture of multidimensional data sources. As a part of a hands-on practice exercise, we then created a local cube from an existing Excel PivotTable report, sourced initially from an MSAS server-based cube.
We explored many practical aspects of putting the functionality to work immediately, discussing ways that local (or "offline") cubes can meet the business requirements of distributed information consumers, and add value to the organization in general. Throughout the hands-on practice exercise we performed, in creating a local cube from an existing server cube, we commented upon the results we obtained, to reinforce our understanding of the concepts involved.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.