Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design

About the Series ...

This article is a member of the series Introduction to MSSQL Server 2000 Analysis Services. 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 Cube.

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 ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.

Overview

Optimization of MSAS requires an in-depth knowledge of many factors, including the manner in which MSAS executes queries within the client/server architecture that embodies it. 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 span many considerations; 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. This article will focus on the Storage Design Wizard, and, as a natural part of exploring its use, we will consider and 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 in other articles of my various series.

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.