Featured Database Articles

The Storage Design Wizard

Introduction and Overview

The
MSAS Storage Design Wizard provides us a guided, user-friendly approach
to configure, both initially and on an on-going basis, storage options
for our cubes. In addition, the wizard affords us a means of adding, modifying
and removing cube aggregations. Moreover, the Storage Design Wizard
lets us manage aggregations on a partition-by-partition basis when working with
a multi-partitioned cube, as we shall see when we address partitioning in a
subsequent article.

The
primary purpose in life for a cube is to provide a data source from which data
can be retrieved rapidly by enterprise information consumers. Aggregations,
or predefined summaries of data values, support this rapid retrieval of data.
One of the strengths of an MSAS OLAP implementation is that it offers us flexibility
in deciding the most appropriate physical storage of these aggregations, within
the context of our individual business and technical environments.

We can
select from three options (referred to as modes) for storage of
aggregations in MSAS. The options differ mainly in the physical location where
detail, or low level dimensional members, and aggregated values are stored. The
three storage modes from which we can select are shown in Table 1.

Storage Mode

Physical Storage Locations

Detail Values

Aggregated Values

ROLAP

RDBMS
data source

RDBMS
data source

(MSAS-maintained)

HOLAP

RDBMS
data source

Cube

MOLAP

Cube

Cube

Table 1: Storage Modes Available in MSAS

As an
example, a cube developer might decide that monthly financial system balances
for the last two operating years belong in MOLAP, where they can be
accessed quickly by the enterprise on a daily basis. The historical data, more
than two years old, might be relegated to ROLAP. Information consumers
would never know the difference, as all would appear to be coming from the same
source (the cube), at the level of retrieved data, unless there is a sudden
need to do intensive reporting upon historical data (the speed of retrieval of
which might be impacted). Design in this area, if based upon realistic data
access and usage requirements, can typically afford consumers a single view of
the data, regardless of the underlying storage mode(s) chosen.

Different
storage modes can be set up for different portions of the cube, as in the
example above, based upon partitions, a concept we explore in other
articles of the series. Put simply, MSAS storage modes allow us flexibility to
meet the needs of our business environment - and settings that can be easily
modified if those circumstances change.

Considerations and Comments

I began working heavily
with MSSQL Server in the days of version 6.5, upon which I initially
certified. (I had worked with the predecessor version a bit, upgrading it
several times and so forth, but version 6.5 was my first in-depth exposure as a
DBA / developer). It was my observation, upon the advent of MSSQL Server 7.0
and MSSQL Server 2000, and their complementary OLAP Services and Analysis
Services components, respectively, that many "old hands" from
database environments including MSSQL Server 6.5, Oracle, and other RDBMS', tended
to sneer at the use of wizards in performing maintenance and optimization
procedures for the RDBMS and / or the OLAP components that accompanied them. This
was often, as with other "assistance" features in MSSQL Server,
because many more practitioners were immediately admitted to the "DBA club"
(for better or for worse), and because the road to performing many redundant processes
was made more open (and less mystical). It is clear that many of these assistance
tools, such as the Storage Design Wizard, help us to perform more
efficiently, and with less tendency to make errors. Using a sophisticated
algorithm to do its work, this wizard is, like most such tools, highly
effective when used with proper training and a sufficient understanding of MSAS
structural fundamentals.

Usage patterns and
other variables enter the tuning equation, for which we have additional tools
and procedures, as we have seen, and shall continue to see, in other articles. However,
for initial storage configuration, and flexible modification as the
irresistible march of time affects our data environments, the Storage Design
Wizard offers much in the way of effective, efficient OLAP storage
management.

Because
the procedure we take in this article will alter the structure of one of the
sample cubes that ships with MSAS, we will make a copy of the cube to avoid
making changes to the original. (Many MSAS practitioners have created other
example objects within, and made other illustrative changes to, the original
sample cubes, and wish to keep these "customized samples" intact.)
This brief preparatory step will leave you able to revisit the sample cube in
its original / current state in the future, without having to undo any steps
that we take in the following sections, or otherwise "put things back as
they were." (You can always restore the database to bring back the sample
cubes exactly as they appeared at installation, as well. See the BooksOnline if you need to take this route.)