Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles