This is the second article of the series, Optimizing Microsoft SQL Server Analysis Services. The primary focus of this series is the introduction of optimization tools and concepts for Microsoft SQL Server Analysis Services (“MSAS”). The series is designed to provide hands-on application of the fundamentals of MSAS optimization from multiple perspectives.
As we progress through the series, we may reference previous articles and the concepts we have introduced therein. However, one of my objectives is to make each article as “standalone” as possible, meaning that we should not encounter cases where we cannot complete a given procedure without components or objects that we have created in previous articles. This should make it easier for “casual” visitors to join us with any lesson, and still successfully complete that session, given an existing understanding of concepts and principles that we have accumulated up to that point.
For important information concerning the applications you need to have in place to obtain the most benefit from the articles of this series, please see “What We Will Need to Complete the Series Tutorials” in our introductory article, Optimization Tools: The Storage Design Wizard.
Introduction and Scope
Among the many tools that MSAS offers to assist in the maintenance and optimization of our cubes, two of these tools highlight the usage-based optimization features of Analysis Services. These tools provide straightforward guidance in the enhancement of cube performance, based upon the ways that information consumers use the cube. My experience has been that, regardless of the design effort invested in any given business intelligence application, especially 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 (see the first article of this series, Optimization Tools: The Storage Design Wizard, and equips us to go significantly further than the generation of simple reports. This wizard offers us the capability to base aggregation design upon a given cube’s usage statistics, in combination with other factors. We can then use our findings to guide us in subsequent adjustments to our existing aggregation design and storage mode as time passes, and as 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 article, we will become familiar with the use of the Usage Analysis Wizard from multiple perspectives. We will accomplish this as we do the following:
-
Put the Usage Analysis Wizard to work in its simplest form, examining the initial reporting options that it offers us.
Explore the ways we can modify the pre-defined on-screen reports at run time, selecting from a fixed set of filters to limit the results they present.
Modify one of the pre-defined reports to examine how we can tailor members of the set to more closely meet our needs.
Finally, as a part of our concluding remarks, we will discuss the inevitable requirement for going beyond the on-screen reporting set, and producing more sophisticated analysis reports, looking forward to the subject of future articles.
The Usage Analysis Wizard as a Tool for Monitoring Query Performance
The Usage Analysis Wizard provides us a quick means of examining key cube activities by producing our choice of six pre-defined, on-screen reports. As we will see in later sections, the data upon which the reports draw resides in the query log that is generated / populated automatically, as a part of cube processing, based upon setpoints that we can adjust. We can learn about the reports that we can generate easily through a hands-on look at the wizard in action.
Putting the Usage Analysis Wizard to Work
We can call the Usage Analysis Wizard to generate a report directly from inside Analysis Manager. The entire operation takes little more than a few clicks of the mouse.
- Start Analysis Manager.
- Expand the Analysis Servers folder by clicking the “+” sign to its immediate left.
Our server(s) appear.
- Expand the desired server (mine appears as MOTHER1 in the illustrations).
Our database(s) appear, in much the same manner as shown in Figure 1.
Figure 1: A Sample Set of Databases Displayed within Analysis Manager
- Expand the FoodMart2000 database.
- Expand the Cubes folder.
The sample cubes appear, as shown in Figure 2.
Figure 2: The Sample Cubes in the FoodMart2000 Database
NOTE: Your cube tree may differ, depending upon the activities you have performed since the installation of MSAS (and the simultaneous creation of the original set of sample cubes). Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For simple instructions, see the Microsoft SQL Server 2000 Books Online.
Continues…