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
management alike.

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
cubes:

aggregations

partitions

member properties

shared dimensions

virtual dimensions

permanent write-back
storage capacity and structures

capability to
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
proposed cubes.

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.