Usage-Based Optimization in Analysis Services 2005
January 9, 2006
About the Series ...
This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively presenting features and techniques designed to meet specific real - world needs. For more information on the series, please see my initial article, Creating Our First Cube.
Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:
To successfully replicate the steps of the article, you also need to have:
Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.
In this lesson, we revisit usage-based optimization, a subject that we undertook in my article MSAS Administration and Optimization: Simple Cube Usage Analysis, in September of 2003, and MSAS Administration and Optimization: Toward More Sophisticated Analysis in October of 2003. In the earlier articles, we discovered that, among several tools that Microsoft SQL Server 2000 Analysis Services offered us to assist in the maintenance and optimization of our cubes, two of these tools, the Usage Analysis Wizard and the Usage-Based Optimization Wizard, leveraged the usage-based optimization features of Analysis Services. They made it easy to perform basic performance enhancement of our cubes based upon their usage by information consumers. As I mentioned then, my experience is that, regardless of the design effort invested in any given business intelligence application, particularly 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.
In Analysis Services 2000, the Usage Analysis Wizard allowed us to rapidly produce simple, on-screen reports that provided information surrounding a cube's query patterns - information that could be useful in helping us to decide whether to consider making structural changes to optimize cube design. The Usage-Based Optimization Wizard, the descendant of which is the subject of this article, embellished the effectiveness of the Storage Design Wizard, and went significantly farther than the generation of simple reports. It offered 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 passed, and information collected from which meaningful statistics could be derived.
lesson, we will consider the Analysis Services 2005 Usage-Based Optimization Wizard, which combines
some of the features we have seen in the related Analysis Services 2000 wizards
we have previously considered. We will discuss preparation for its use, as
well as the steps involved in making the Usage-Based Optimization Wizard an
effective tool in our Analysis Services administration toolset.