This is the first article of the series, Optimizing MSSQL Server Analysis Services. The primary focus of this series will be the introduction of optimization tools and concepts for MSSQL Server Analysis Services (“MSAS”). The series is designed to provide hands-on application of the fundamentals of MSAS optimization from multiple perspectives.
As we progress through the series, we may reference previous articles and the concepts we have introduced therein. However, one of my objectives is to make each article as “standalone” as possible, meaning that we should not encounter cases where we cannot complete a given procedure without components or objects that we have created in previous articles. This should make it easier for “casual” visitors to join us with any lesson, and still successfully complete that session, given an existing understanding of concepts and principles that we have accumulated up to that point.
What We Will Need to Complete the Series Tutorials
To get the most out of the Optimizing MSSQL Server Analysis Services series, we need to have installed at least the Analysis Services component of MSSQL Server 2000. While the full installation of SQL Server 2000 allows for virtually any exercise we might undertake, most of our sessions center on MSAS and the components that accompany MSAS in a typical installation. Moreover, although the installation of Analysis Services from the Standard Edition of MSSQL Server 2000 will be adequate for many of our activities, installation from the Enterprise or Developer Editions (MSDE) will afford access to all we might undertake in the series.
For purposes of the series, it is assumed that MSAS components are appropriately accessible to / installed on the PC, with the appropriate access rights to the sample cubes provided in a typical installation of MSAS. It is also assumed that the computer(s) involved meet the system requirements, including hardware and operating systems, of the applications we have mentioned.
For purposes of carrying out certain activities, we may occasionally call upon components of the Microsoft Office suite, and other relatively common applications. Should any such software be needed for a given article, we will note that consideration in the introductory paragraphs to minimize interruptions, distractions and disappointments.
NOTE: Service Pack 3 updates are assumed, at this writing, for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.
Introduction and Scope
Optimization of MSAS requires an in-depth knowledge of many factors, including the client/server architecture that embodies it and the manner in which MSAS executes queries. From the perspective of the cubes that MSAS produces, our primary concerns, from a performance standpoint, lie within two main groups: cube processing performance (how fast the cube builds / updates from the source data) and cube query performance (the response time with which consumers’ needs are met with information contained in the cube).
The structure of MSAS cubes themselves hold many considerations, and among the most significant concerns are storage modes and aggregations. MSAS allows for management of these and other factors in numerous ways, including several tools within the application. The purpose of this, and other articles addressing optimization tools, is to address the use of the tools themselves, introducing them for later articles, when we provide hands-on practice in using them to address individual optimization considerations.
This article will focus on the Storage Design Wizard, and, as a natural part of exploring its use, we will acknowledge the importance and potential complexity of storage configuration for MSAS. The detailed topics of storage, aggregation, and a host of other considerations in tuning MSAS are beyond the scope of this article. These and many other performance-related topics will be treated individually, after we have introduced the tools with which we will work in our tuning efforts.
In this lesson, we will do the following:
- Introduce the Storage Design Wizard, discussing its general purposes;
- Explore scenarios where we might use the Storage Design Wizard;
- Practice using the Storage Design Wizard in a basic scenario;
- Review cube processing;
- Discuss how the processing log can be used to focus on storage design effectiveness.
- Explore scenarios where we might use the Storage Design Wizard;
The Storage Design Wizard
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 later article.
A Bit About Storage in MSAS
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 OLAP in an MSAS implementation is that we are offered flexibility in deciding the most appropriate physical storage of these aggregations for our 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 |
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’ll discuss in later articles. Suffice it to say for now that 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.