Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design - Page 2

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.

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 Books Online if you need to take this route.)