About the Series …
This is the fifteenth
article of the series, Introduction to MSSQL Server 2000 Analysis
Services. As I stated in the first article, Creating Our
First Cube, the primary focus of this series is an introduction to
the practical creation and manipulation of multidimensional OLAP cubes. The
series is designed to provide hands-on application of the fundamentals of MS
SQL Server 2000 Analysis Services ("MSAS"), 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.
Introduction
Microsoft
SQL Server 2000 Analysis Services offers us many tools to assist in the
maintenance and optimization of our cubes. Two of these tools highlight the usage-based
optimization features of Analysis Services, and the ease with which we
can enhance performance of our cubes based upon their usage by
information consumers. My experience has been that, regardless of the design
effort invested in any given business intelligence application, specifically
within the context of anticipating the patterns of use of that application by the
intended consumers, nothing can quite equal history as a guide to future human
activity.
The two
tools to which I refer are the Usage Analysis Wizard and the Usage-Based
Optimization Wizard. The Usage Analysis Wizard allows us to rapidly
produce simple, on-screen reports that provide information surrounding a cube’s
query patterns. This information can be useful in helping us to decide whether
to consider making changes to cube design to optimize it (say, prior to taking
it from development to production). The cube activity metrics generated by the
wizard have a host of other potential uses, as well, such as the provision of a
"quick and dirty" means of trending cube processing performance over
time after the cube has entered a production status.
The Usage-Based
Optimization Wizard embellishes the effectiveness of the Storage Design
Wizard, and equips us to go significantly farther than the generation of
simple reports. The wizard offers us the capability to base aggregation design
upon a given cube’s usage statistics, in combination with other factors, and to
make subsequent adjustments to our existing aggregation design and storage mode
as time passes, and information is collected from which meaningful statistics
can be derived. The Usage-Based Optimization Wizard lies beyond the
scope of this lesson, but we will introduce it in a later article, where we can
devote the time it deserves to cover its various facets.
In this lesson, we will first
put the Usage Analysis Wizard to work in its simplest form,
examining the initial reporting options that it offers us. We will then
explore the ways we can modify the on-screen reports at run time, selecting
from a fixed set of filters to limit the results they present. Next, we will
modify one of the standard reports to examine how we can tailor members of the
set to meet our needs more closely. Finally, we will discuss the virtually
inevitable requirement for going beyond the on-screen reporting set, and
producing more sophisticated analysis reports, looking forward to the subject
of our next lesson.
