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.