Usage-Based Optimization in Analysis Services 2005

Monday Jan 9th 2006 by William Pearson
Share:

Business Intelligence Architect Bill Pearson introduces simple Usage-Based Optimization in MSSQL Server 2005 Analysis Services. In this article, we practice employing the Usage-Based Optimization Wizard to fine tune our aggregation design based upon cube usage statistics.

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:

  • Microsoft SQL Server 2005 Database Engine

  • Microsoft SQL Server 2005 Analysis Services

  • Business Intelligence Development Studio

  • Microsoft SQL Server 2005 sample databases

  • The Analysis Services Tutorial sample project and other samples that are available with the installation of the above.

To successfully replicate the steps of the article, you also need to have:

  • Membership within one of the following:

    • the Administrators local group on the Analysis Services computer

    • the Server role in the instance of Analysis Services.

  • Read permissions within any SQL Server 2005 sample databases we access within our practice session, if appropriate.

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.

Introduction

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.

In this 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.

The Usage-Based Optimization Wizard

Overview and Discussion

The Usage-Based Optimization Wizard provides us a quick means of creating aggregations to improve cube processing performance. While the Storage Design Wizard serves us well when we initially design storage for our cubes, and allows us to specify parameters to achieve a tradeoff between storage requirements and query response time that is appropriate to our business environments, the Storage Design Wizard is designed to assume that "all queries are equal," with regard to the resource requirements they place upon the system, and with regard to the likelihood of their selection by information consumers. Once the cube is designed, deployed and processed, and once it becomes a data source for a potentially diverse range of consumers, it often transpires that some queries are executed more than others, and that various parts of the cube structure are utilized more heavily than others.

The Usage-Based Optimization Wizard enables us to fine-tune the aggregations within our cubes so that recurring queries obtain quicker response times than infrequent or "one-off" queries. The Wizard allows us to do this through its analysis of queries that have been submitted by various applications to Analysis Services. The Usage-Based Optimization Wizard allows us to go as far as to even select the specific queries for which we wish to optimize, if that is desirable. Just as we find to be the case with the Storage Design Wizard, the data aggregations designed by the Usage-Based Optimization Wizard are created when the respective partition, its measure group, or its cube is processed.

We can instruct the Wizard, through a series of dialogs, to create aggregations based upon a flexible combination of several cube usage characteristics, including:

  • a date range of cube use;
  • the users querying the cube;
  • the frequency with which a query was executed;
  • response time for the query;
  • storage mode of the data involved.

We will examine each of these parameters, as we work through a practice session with the Usage-Based Optimization Wizard in this article. We will examine the operation of the Usage-Based Optimization Wizard within a context of aggregation design, as we accomplish the following:

  • Create a copy of a sample Analysis Services database for use in our practice exercise;

  • Enable the Analysis Server Query Log to capture query statistics;

  • Prepare the cube further by processing and manipulating data / creating Query Log entries;

  • Perform a practice exercise, using the Usage-Based Optimization Wizard, to set aggregations for our practice cube;

  • Examine each of the possible settings that are available to us, as we proceed through the guided steps of the Wizard;

  • Comment upon general optimization concepts as we proceed through our practice example.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. These samples include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed. The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation.

Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in the references I have noted.

Hands-On Procedure

Let's get some hands-on practice with Usage-Based Optimization. To prepare, we will create a new Analysis Services database, based upon the existing Adventure Works DW sample database, to insulate the original sample database from modifications we will make. We will accomplish the creation of the "clone" database from within the SQL Server Management Studio, and then enable query logging to prepare for our work with the Usage-Based Optimization Wizard.

Preparation

Create a Clone Analysis Services Database in SQL Server Management Studio

We will begin our preparation within SQL Server Management Studio, where we will create a clone of the sample Adventure Works DW database, which can be installed by anyone installing MSSQL Server 2005 Analysis Services.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.

Click for larger image

Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name / instance, if appropriate) into the Server name selector.

6.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears, with the appropriate input for our local environments, similar to that depicted in Illustration 2.


Illustration 2: The Connect to Server Dialog, with Representative Settings

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  Within the Object Explorer (the leftmost pane of the Studio, by default), expand the server in which we are working, if necessary, by clicking the "+" sign to its immediate left.

9.  Expand the Databases folder that appears underneath the expanded server.

10.  Right-click the Adventure Works DW database.

11.  Select Back Up... from the context menu that appears, as shown in Illustration 3.


Illustration 3: Right-click the Adventure Works DW Database – Select Back Up ...

The Backup Database – Adventure Works DW dialog appears.

12.  Replace the default name that appears in the Backup file box with the following:

ANSYS043 Adventure Works DW.abf

13.  Uncheck the Apply compression setting in the Options section.

14.  Uncheck the Encrypt backup file setting that immediately follows.

The Backup Database – Adventure Works DW dialog appears, as depicted in Illustration 4.


Illustration 4: The Backup Database – Adventure Works DW Dialog Appears

15.  Click OK to begin the backup.

The Backup Database – Adventure Works DW dialog grays, as the Executing symbol in the Progress pane (lower left corner of the dialog) becomes active. The process may run several minutes depending upon the resources available on the local PC. Once completed, the dialog closes, returning us to the Management Studio.

We will next restore the same backup under a different name, to create a copy of the existing sample database – a copy wherein we can make modifications without impairing the existing sample, which we may wish to use to complete tutorials included with MSSQL Server 2005 or elsewhere.

16.  Within the Object Explorer, right-click the Databases folder underneath the Adventure Works DW database.

17.  Select Restore... from the context menu that appears, as shown in Illustration 5.


Illustration 5: Right-click the Databases Folder – Select Restore ...

The Restore Database dialog appears.

18.  Click the Browse button to the right of the box (second from the top) labeled From backup file.

The Locate Database Files dialog appears.

19.  Navigate to the following backup file (where we located it in our backup steps above):

ANSYS043 Adventure Works DW.abf

20.  Click the file within the Select the file window, to place the file name into the File name box, as depicted in Illustration 6.


Illustration 6: Locate Database Files Dialog with Our Input ...

21.  Click OK to accept the file path / name, and to close the Locate Database Files dialog.

We return to the Restore Database dialog, where we see the file we have selected appear in the From backup file box.

22.  Type the following into the Restore database box immediately above the From backup file box:

ANSYS043 Adventure Works DW

The Restore Database dialog, with our input, appears as shown in Illustration 7.


Illustration 7: The Completed Restore Database Dialog

23.  Click OK to initiate the restoration.

The Restore Database dialog grays, as the Executing symbol in the Progress pane, once again, becomes active. The process runs, and, once completed, the dialog closes, returning us to the Management Studio. Here we see the new ANSYS043 Adventure Works DW database appear in the Object Browser, as depicted in Illustration 8.


Illustration 8: The New Database Appears ...

NOTE: If the new database does not appear immediately, right-click the Databases folder and select Refresh from the context menu that appears, as shown in Illustration 9.


Illustration 9: Refreshing as Required ...

Having created the ANSYS043 Adventure Works DW database, we can now proceed with the practice portion of our session, and get some hands-on experience with the Usage-Based Optimization Wizard in Analysis Services 2005. In the next session, we will take an additional preparatory step: enabling the logging of query statistics, upon which the Wizard bases its optimization strategy.

Enable Query Logging to Gather Statistics

Usage-based optimization is based upon usage. We therefore must capture usage statistics to have a basis for optimization. We do this by enabling query logging within the Analysis Server, as we shall see in the steps that follow.

1.  Right-click the Analysis Server with which we are working.

2.  Select Properties from the context menu that appears, as depicted in Illustration 10.


Illustration 10: Select Properties from the Context Menu ...

The Analysis Services Properties dialog appears. A busy place, indeed, many settings of a default and operational nature are maintained here. (My advice is to learn all that we can about each entry within this table-like dialog, so as to know of its existence when the time comes to address the utility that it offers). First, we need to enable the logging of query statistics.

3.  Scroll down to the Log \ QueryLog \ CreateQueryLogTable entry in the table within the dialog.

4.  Change the Value setting for the entry, which is defaulted to false, to true, via the selector provided, as shown in Illustration 11.

Click for larger image

Illustration 11: Changing the CreateQueryLogTable Setting Value to True ...

We next need to direct Analysis Services as to where we wish to host the Query Log. Analysis Services 2005 offers us a great deal of flexibility (an improvement over Analysis Services 2000's provision of an MS Access database for this purpose, which could be migrated to MSSQL Server). We will direct that the Query Log database be created within the sample AdventureWorksDW relational database, which underlies our new clone Analysis Services database, ANSYS043 Adventure Works DW. This is a logical place to put the Query Log for this session, but we are free to put it into any OLE-DB / .NET compatible data source.

5.  In the Log \ QueryLog \ QueryLogConnectionString row, immediately below the CreateQueryLogTable row (where we assigned the value of true above), click the Value box, and then click the box that appears to its immediate right (marked "..."), as shown in Illustration 12.


Illustration 12: Beginning Connection Setup for the Query Log Data Source

Connection Manager appears.

6.  Leave the Provider setting at its default of Native OLE DB \ SQL Native Client.

7.  Select / type the appropriate Server or Server / Instance combination into the Server name box.

Mine is MOTHER1\M1MSSQL2K5, as we see in illustrations throughout recent articles of the series.

8.  In the Log on to the server section of the Connection Manager dialog, make the authentication selections appropriate to your environment.

I am using Windows Authentication, and therefore select the respective radio button.

9.  In the Connect to a database section in the lower half of the dialog, in the selector box labeled Select or enter a database name, select the AdventureWorksDW relational database.

The Connection Manager dialog appears, with our input, similar to that depicted in Illustration 13.


Illustration 13: Connection Manager Dialog, with our Input

10.  Click the Test Connection button to ascertain connectivity to the database.

Assuming correct settings, confirmation of connectivity appears in a message box, as shown in Illustration 14.


Illustration 14: "Test Connection Succeeded"

11.  Click OK to close the message box.

12.  Click OK to accept settings and to close the Connection Manager dialog.

We are returned to the Analysis Services Properties dialog, where we see that the Value box of the Log \ QueryLog \ QueryLogConnectionString row now contains a connection string, courtesy of the Connection Manager. We have enabled the collection of query statistics and established the location of their collection. Next, we will make a couple of additional adjustments before putting the Usage-Based Optimization Wizard, which depends upon the Query Log to perform its work, into action.

13.  In the Log \ QueryLog \ QueryLogSampling row, which appears three rows below the Log \ QueryLog \ QueryLogConnectionString row, change the Value from the default of 10 to 1.

Here we are merely increasing the sampling rate from "capture statistics from every tenth query" to "capture statistics for each query," a step similar to those we took in previous articles with the Usage-based Optimization Wizard as it existed in Analysis Services 2000. (Although the setting was managed a bit differently, in the Write to log once per [number] box within Analysis Services 2000, our intent then, as it is now, was to simply allow the log to capture enough data to make the procedural steps of our practice exercise meaningful.)

14.  In the Log \ QueryLog \ QueryLogTableName row, which appears in the row immediately below the Log \ QueryLog \ QueryLogSampling row, modify the Value from its default of OLAPQueryLog to the following:

MSASQueryLog

Our settings, within the rows of the Analysis Services Properties dialog we have visited, appear as depicted in Illustration 15.


Illustration 15: Modified Settings within the Analysis Services Properties Dialog

15.  Click OK to enact the settings we have made above.

The "Executing" indicator appears in the bottom left corner of the dialog momentarily, and then the Analysis Services Properties dialog closes. We are returned to the SQL Server Management Studio.

We can confirm the creation of the new MSASQueryLog table within the AdventureWorksDW relational database easily from the SQL Server Management Studio, where we can access the relational world in combination with the OLAP world, as we shall see in the next steps.

16.  Click the Connect button atop the Object Explorer.

17.  Select Database Engine from the menu that appears, shown circled in Illustration 16.


Illustration 16: Connecting to the SQL Server 2005 Database Engine

The Connect to Server dialog appears, just as we saw earlier when connecting to Analysis Services.

18.  Enter the correct Server or Server / Instance combination into the Server name box.

19.  Select the appropriate authentication mode into the Authentication box that immediately follows (with related details in the Password box, as appropriate).

The Connect to Server dialog appears similar to that depicted in Illustration 17.


Illustration 17: The Connect to Server Dialog Appears

20.  Click Connect.

We return to the SQL Server Management Studio, where we see the instance of the Database Engine appear within the Object Explorer, underneath the Analysis Server with which we have been working, as shown in Illustration 18.


Illustration 18: The Database Engine Instance Appears within Object Explorer

21.  Expand the Database Engine Server by clicking the "+" sign to its immediate left, if required.

22.  Expand the Databases folder underneath the expanded server.

23.  Expand the AdventureWorksDW database.

24.  Expand the Tables folder within the AdventureWorksDW database.

The tables within the folder appear. Among them, we see the new MSASQueryLog table that we created in our earlier steps, as depicted in Illustration 19.


Illustration 19: The MSASQueryLog Table Appears in the AdventureWorksDW Database

The table is in place and query logging is enabled. We are positioned to conclude our preparations, by generating some sample queries, and to begin getting some hands-on practice with the Usage-Based Optimization Wizard.

Process the Database Clone and Generate Statistics to be Captured by the Query Log

We need some log entries upon which to base the work of the Usage-Based Optimization Wizard in our practice example, so we will populate the table with some rows in a "quick and dirty" manner – by doing quick manipulations of the data in the cube. First, we will process the new Analysis Services database copy we have created.

1.  Right-click the new Analysis Services database, ANSYS043 Adventure Works DW, which we created earlier. (The database can be found within the Databases folder of the Analysis Server instance, within Object Explorer.)

2.  Select Process from the context menu that appears, as shown in Illustration 20.

Click for larger image

Illustration 20: Processing the Clone Analysis Services Database ...

The Process Database dialog appears for Analysis Services database ANSYS043 Adventure Works DW, as depicted in Illustration 21.

Click for larger image

Illustration 21: The Process Database Dialog Appears

3.  Leaving all settings on the dialog at default, click OK to begin processing.

The Process Progress viewer appears, logging the events of database processing as they occur. Once all database objects are processed, we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as shown in Illustration 22.


Illustration 22: Successful Processing Completion is Indicated ...

4.  Click the Close button to dismiss the Process Progress viewer.

We are returned to the SQL Server Management Studio, where next we will perform a few actions to generate statistics in the Query Log table.

5.  Expand the Analysis Services database ANSYS043 Adventure Works DW, by clicking the "+" sign to its immediate left within Object Explorer.

6.  Expand the Cubes folder that appears within the ANSYS043 Adventure Works DW tree.

7.  Right-click the Adventure Works cube that appears within the Cubes folder.

8.  Select Browse from the context menu that appears, as depicted in Illustration 23.


Illustration 23: Select Browse from the Context Menu ...

The Adventure Works [Browse] tab appears within SQL Server Management Studio. Here we will do a few browses to generate sample query statistics within the Query Log.

9.  Expand Measures within the Metadata pane by clicking the "+" sign to its immediate left.

10.  Expand the Internet Sales measure folder that appears underneath the expanded Measures level.

The measures related to Internet Sales appear.

11.  Drag measure Internet Sales Amount into the Data pane, dropping it into the area marked Drop Totals or Detail Fields Here, as shown in Illustration 24.


Illustration 24: Drag and Drop the Measure into the Data Pane ...

12.  Drag measure Internet Order Quantity from the Metadata pane to the Data pane, dropping it to the right of the Internet Sales Amount.

13.  Expand the Date dimension within the Metadata pane.

14.  Expand the Calendar folder that appears underneath the expanded Date dimension.

15.  Expand the Date.Calendar hierarchy that appears within the expanded Calendar folder.

16.  Drag member Calendar Year into the Data pane, dropping it onto the area marked Drop Column Fields Here, as depicted in Illustration 25.


Illustration 25: Drag and Drop Calendar Year into the Column Fields of the Data Pane ...

17.  Expand the Product dimension within the Metadata pane.

18.  Expand the Product Categories hierarchy that appears underneath the expanded Product dimension.

19.  Drag the Category level, appearing underneath the Product Categories hierarchy, into the Data pane, dropping it onto the area marked Drop Row Fields Here, as shown in Illustration 26.


Illustration 26: Drag and Drop Product Categories into the Row Fields of the Data Pane ...

The Data pane appears, after our insertions, as partially depicted in Illustration 27.


Illustration 27: Our Initial Browse in the Data Pane (Partial View)

20.  Drill down on each of the Product Categories by clicking the "+" sign to its immediate left, as shown in Illustration 28.


Illustration 28: Drilling Down on Product Categories ...

21.  Drill down on the Calendar Year CY 2003, to display the bi-annual levels H1 CY 2003 and H2 CY 2003.

22.  Drill down on level H2 CY 2003 one level further, to expose the underlying quarterly levels Q3 CY 2003 and Q4 CY 2003, as depicted in Illustration 29.


Illustration 29: Drilling Down on Select Date Dimension Levels ...

23.  In a manner similar to the steps above, perform several browses involving different dimensions / dimension hierarchies and measures.

24.  Close the Adventure Works [Browse] tab, when finished.

Having provided for the collection of a representative set of query statistics, we can now call the Usage-Based Optimization Wizard into action and get some exposure to its operation.

Procedure

The Usage-Based Optimization Wizard in Action

We can call the Usage-Based Optimization Wizard for a specific partition within a cube, within the context of the Measure Group for which the partition has been created, as we shall see. We can work with the Usage-Based Optimization Wizard from within the SQL Server Management Studio, or from the Business Intelligence Development Studio. Because we are already in the Management Studio, and have worked here with both the Analysis Server (and cube) and the Database Engine (for the underlying relational data source – which also now contains the Query Log, as well), we will call the Usage-Based Optimization Wizard from within the SQL Server Management Studio. The Management Studio is best used as an administrative environment (and I would consider usage-based optimization as an administrative function, in most cases), whereas the BI Development Studio might be best leveraged as a development environment, but the lines often blur, and, because we can access the Usage-Based Optimization Wizard (and many other capabilities) from both environments, local policies and business needs might dictate that one or the other is more appropriate.

Let's practice performing usage-based optimization on a sample Measure Group within the Adventure Works cube, within our clone Analysis Services database, ANSYS043 Adventure Works DW, by taking the following steps.

1.  From the Object Explorer in SQL Server Management Studio, expand the Analysis Services database ANSYS043 Adventure Works DW, by clicking the "+" sign to its immediate left, as necessary.

2.  Expand the Cubes folder that appears within the ANSYS043 Adventure Works DW tree.

3.  Expand the Adventure Works cube that appears within the Cubes folder.

4.  Expand the Measure Groups folder that appears within the Adventure Works cube.

The various Measure Groups for the Adventure Works cube appear.

5.  Expand the Internet Sales Measure Group that heads up the Measure Groups list for the Adventure Works cube.

6.  Expand the Partitions folder that appears underneath Internet Sales.

The partitions that make up the Internet Sales Measure Group (one for each year of Internet Sales data contained in the cube) appear, as shown in Illustration 30.


Illustration 30: The Partitions for the Internet Sales Measure Group ...

NOTE: For more detailed information regarding the nature and use of partitions, and related topics, see other articles in my Introduction to MSSQL Server Analysis Services series.

7.  Right-click the Partitions folder.

8.  Select Usage Based Optimization ... from the context menu that appears, as depicted in Illustration 31.


Illustration 31: Calling the Usage-Based Optimization Wizard ...

The Usage-Based Optimization Wizard appears, beginning with the Welcome page, as shown in Illustration 32.


Illustration 32: Usage-Based Optimization Wizard – Welcome Page

9.  Click Next.

The Select Partitions to Modify page appears. It is here that we can direct which partitions we wish to modify. (The fact that we can handle multiple partitions in one dialog here might come as a relief to those who, finding that the Usage-Based Optimization Wizard could be called by right-clicking individual partitions, have concluded that we are required to handle the partitions one-by-one. The relative obscurity of the documentation on the Usage-Based Optimization Wizard has led to some confusion here, if forums, blogs, and my own e-mail receipts on the topic are any indication). We are reminded that we must choose at least one partition within this page.

10.  Select the All checkbox (on the column header, to the left of the Partition Name label), as depicted in Illustration 33.


Illustration 33: Usage-Based Optimization Wizard – Select Partitions to Modify ...

11.  Click Next.

The Specify Query Criteria page appears. This dialog affords us the opportunity to filter among the queries taken into consideration by the Wizard in proposing usage-based aggregation designs. We have, within this dialog, three general options (four actual checkbox choices) for query selection criteria; we can use one or more of these to narrow the selection of logged queries upon which we wish to base our design for optimization. The three general criteria, together with descriptions, are summarized in Table 1.

Query Criteria Option

Optimization Selection is Based Upon:

Queries within date ranges

Date range for queries. Date range type can be selected from:

Between – All queries between a desired Beginning date and Ending date, inclusive


Beginning Date – All queries on or after a selected Beginning date


Ending Date - All queries executed before a selected Ending date

Queries by specific users

Users and Groups of Users, as defined by User Roles

Query frequency

The most commonly executed queries based upon percentage represented of all queries.

Table 1: The Three General Query Selection Criteria – Usage-Based Optimization Wizard

12.  Leave the number selections blank, putting no filters in place, as shown in Illustration 34.


Illustration 34: Specify Criteria Page – No Filters Specified ...

13.  Click Next.

The Review the Queries that will be Optimized page appears next, as partially depicted in Illustration 35.


Illustration 35: Review the Queries that will be Optimized Page (Partial View)

Here we see every query in the log (since we have specified no filters), grouped by number of occurrences, for which average durations are displayed. We have the opportunity, at this point, to eliminate individual queries from consideration, for added flexibility. (We will leave them all selected for purposes of our practice session).

14.  Leaving all selections in place, click Next.

The Specify Storage and Caching Options page of the Wizard appears next. Here we can adjust the existing settings for storage mode and caching options. We will leave the settings at default (solely MOLAP), as shown in Illustration 36.


Illustration 36: Specify Storage and Caching Options Page – Default Settings

15.  Click Next.

The Specify Object Count page appears next.

16.  Click the Count button, to direct the Wizard to calculate object counts.

Counting commences, and we quickly see the values appear, as partially depicted in Illustration 37.


Illustration 37: The Specify Object Count Page (Partial View)

17.  Click Next.

The Set Aggregation Options page appears next. This page is familiar to those of us who have used the Aggregation Design Wizard – and varies only little from the equivalent page of the previous version of Analysis Services.

18.  Under Design aggregation until, click the checkbox labeled Performance gain reaches, to select this aggregation option.

19.  Input 75 % into the selector box to the immediate right of the Performance gain reaches label.

The Set Aggregation Options page appears as shown in Illustration 38.

Click for larger image

Illustration 38: The Set Aggregation Options Page

20.  Click Start.

Aggregation design begins, and continues until the desired optimization objective is reached. In the present case, we receive a message in the status bar at the bottom of the Set Aggregation Options page, indicating a count of the aggregations designed, along with the optimization level percentage reached and the estimated storage size of the cube, similar to that depicted in Illustration 39.

Click for larger image

Illustration 39: Indication of Successful Aggregation Design Appears ...

Keep in mind that aggregations are only designed by the Wizard; the affected partitions must be processed to physically create the aggregations that the Wizard has designed.

21.  Click Next.

The Completing the Wizard page appears, providing an opportunity to review the partitions that are about to be optimized, once processing begins. We can elect to begin processing as the final step of the Usage-Based Optimization Wizard, or to defer processing until a later time. We will elect the former option at this point.

22.  Click the checkbox labeled Process partitions immediately, in the bottom left corner of the Completing the Wizard page.

The Completing the Wizard page appears, as shown in Illustration 40.


Illustration 40: The Completing the Wizard Page

23.  Click Finish.

The Process Objects dialog opens. Here we can perform various batch settings and review, for a final time (and even remove some, or all of) the objects scheduled to be processed. The Process Objects dialog appears as depicted in Illustration 41.


Illustration 41: The Process Objects Dialog

24.  Click OK to begin processing the partitions.

The Process Progress viewer appears, logging the events of the processing of the partitions as they occur. Once all scheduled partitions are processed, we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as shown in Illustration 42.


Illustration 42: Successful Processing Completion is Indicated ...

25.  Click the Close button to dismiss the Process Progress viewer.

We are returned to the SQL Server Management Studio. Usage-based optimization is completed.

26.  Discard the clone Analysis Services database, ANSYS043 Adventure Works DW, as desired. (Right-click and Delete.)

27.  Reset the Log \ QueryLog \ CreateQueryLogTable entry, within the Analysis Server Properties dialog for your local server, to "false," if desired, to disable query logging.

28.  Select File ---> Exit, when ready to close SQL Server Management Studio.

We have completed our practice steps with an Analysis Services 2005 performance tuning tool, the Usage-Based Optimization Wizard. The Wizard is similar to the tool of this name within Analysis Services 2000, with performance and utility enhancements in evidence. The Usage-Based Optimization Wizard allows us to optimize the aggregations for our MSAS cubes partitions based upon queries that have previously been executed against the cube. Our ability to discriminate between common, frequently run queries and more ad hoc, less representative queries, when deciding the population to use as a basis for tuning the cube's performance with future queries, allows us to customize the action of the tool a bit beyond what we might expect of more typical wizardry. One of numerous characteristics of the process that remains unchanged since Analysis Services 2000 is the role of the administrator within this flexible, albeit guided tuning event: making the correct selections here can certainly inject the element of artistry into what might otherwise appear to be an objective evolution.

Conclusion

In this lesson, we revisited the Usage-Based Optimization Wizard, which remains a formidable tool in our Analysis Services 2005 administration toolset. We noted that the Usage-Based Optimization Wizard embellishes the effectiveness of the Usage Analysis (going significantly farther than the generation of simple reports) and Storage Design (allowing for up-to-date, usage-based optimization) Wizards. The Usage-Based Optimization 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 as information is collected, from which meaningful statistics can be derived.

In this article, we examined the operation of the Usage-Based Optimization Wizard within a context of aggregation design. Our practice exercise included preparation steps, within which we created a copy of a sample Analysis Services database for use in our practice exercise, enabled the Analysis Server Query Log to capture query statistics, processed the clone database, and then manipulated data within a cube therein to create Query log entries. We then performed a procedure whereby we set aggregations for our designated practice cube with the Usage-Based Optimization Wizard. Throughout the guided steps of the Wizard, we examined each of the possible settings that it makes available to us, and commented upon general optimization concepts as we proceeded through the practice example.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved