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.
