dcsimg

Usage-Based Optimization in Analysis Services 2005 - Page 7

January 9, 2006

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.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers