Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips







Learn more about SQL Server tools








Learn more about SQL Server tools


Improve SQL Server Analysis Services Performance with the Usage Based Optimization Wizard

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (3)   |   Related Tips: > Analysis Services Performance
Problem

Sometimes the SQL Server Analysis Services database query performance is slow and we need to improve performance. As a first step, I am assuming you have already created a partition to improve performance. In this tip I will demonstrate how to improve the query performance using the Usage Based Optimization Wizard.  Check out this tip to learn more.

Solution

In this tip we are going to use the Usage Based Optimization Wizard (UBO) to help improve performance. The UBO is a modern technique to analyze the data and propose aggregation to improve the query performance.  As the image below shows, the query activity is saved to a SQL Server database, then analyzed in order to build aggregations to improve query performance.

Usage Based Optimization Wizard

For this tip, I am going to divide the solution into three primary sections:

  • The dbo.OlapQueryLog Table Creation
  • Collect data in the dbo.OlapQueryLog Table
  • The SQL Server Analysis Services UBO Wizard

The dbo.OlapQueryLog Table Creation

The first step to use SSAS UBO Wizard is to configure a file or table to store the query information. In this example, we are going to create a table in the SQL Server msdb database named dbo.olapquery.  It is recommended in a production environment to use a user defined database instead of a system database.

  1. To create the table, open SQL Server Management Studio and Connect to Analysis Server, right click on the server name and select 'Properties' as shown below:
    Olapquerylog table creation

  2. In the properties, make sure that the option 'Log \ QueryLog \ CreateQueryLogTable' is set to true. This option will create a table to log the data related to long queries. This information will be used to analyze and store long queries.
    make sure that the option Log \ QueryLog \ CreateQueryLogTable is set to true

  3. In the Log \ QueryLog \ QueryConnectionString option click on the ellipsis button and a new window will be displayed, i.e. Connection Manager.  In the Connection Manager window, enter the 'Server Name' for the SQL Server Database Engine (not the Analysis Services server name), security, database, etc. as shown below. Once this is done, restart the Analysis Services Windows service. Make sure that the Analysis Server Service Account has permissions to the database used to create the table to store the long running queries. 
    The Server Name is the SQL Server Database Engine

      Once finished, you will have a table in the database selected named 'dbo.OlapQueryLog' as shown below.
      Analysis Server Service Account

      The dbo.OlapQueryLog table contains 6 columns:

      The MSOLAP_Database is the multidimensional database name e.g. AdventureWorks.

      The MSOLAP_ObjectPath is the path of the object, such as a measure group. For example, if the SSAS server name is 'myserver', the database name is 'adventureworksDW', the cube name is 'Adventure Works' and the measure name is 'Currency Rate', then the object path would be: 'myserver.adventureworksDW.Adventure Works.Currency Rate'.

      The dataset column is more complex to explain, you do not really need to know what the number means because the wizard is really straightforward.  The dataset column explains the use of attributes in the query. For example, if the information displayed in the dataset is 00000,000,0000,00 it shows which attributes are used in a query. 0 means that the attribute was not used in the dimension and an integer values shows the opposite. For more detailed information see the SSAS performance guide in the 3.3.1.3 Measure Query Speeds section (see the references in the Next Steps section).  

      The MSOLAP_User is a column that shows the user that executed the query.

      The StartTime is the date that the query was executed

      Finally the duration column is the time in milliseconds needed by the query to execute.

Collect Data in the dbo.OlapQueryLog Table

Once you created the table monitor the table to review the data.  When customers or users query the SSAS database and the query takes long time, the information is stored in the dbo.olapquerylog table. Here is a simple query to review the data:

SELECT * FROM dbo.olapquerylog 
 

The more SSAS queries you have the more information the table will have to suggest aggregations to improve the query response.

The UBO wizard

Once you have a representative amount of data in the dbo.olapquerylog table, you can continue with the UBO wizard as outlined below:

  1. In the Business Intelligence Development Studio (BIDS) or the SQL Server Data Tools, go to 'File' > 'Open Project' and select a SSAS project which is big and has slow query response time. 
    Business Intelligence Development Studio

  2. In the SSAS project, navigate to the Solution Explorer, double click on a cube and then go to the Aggregation tab as shown below. 
    the Aggregation tab

  3. In the Aggregation tab, click the UBO Wizard icon. 
    the UBO Wizard icon

  4. The wizard will start with the welcome window. Press the next button to continue the wizard.
    The wizard will start with the welcome window

  5. Select the partitions used for analysis and press the next button to continue.
    Select the partitions used for analysis
  6. The next section will show the users, the total queries and you give you the ability to filter the queries per date, per user or per frequency. For example, if you already created statistics in the past you will want to filter to an specific date. That way you will not need to analyze all the queries. Another example is to filter by users. Sometimes there are specific queries that are run just once per user in the month and creating aggregation is not urgent. In that case we can filter specific users or frequency to exclude these queries.
    the total queries

  7. The next interface will let you select the queries based on the number of occurrences and the average duration. You can filter and select them if you do not want to reduce the number of aggregations. As a an example, you can filter and select the queries that had the longest Average Duration.
    select the queries

  8. You can press the count button or write the numbers of estimated rows and press the next button. This 'Count' button estimates the number of rows in the measure in order to aggregate it later.
    press the count button

  9. The aggregations can take a lot of space so you can limit the number of aggregations according to a specified number of megabytes. At the end of this steps you will find the space used per all aggregations. There are options to stop when the performance reaches 100 % or when you click the 'Stop' button. You can also select the option to create aggregations until it reaches 100 MB or a specific size.  Once you select these options, press the 'Start' button to begin the process. 
    limit the number of aggregations according to a specified number of MegaBytes

  10. You will see the number of aggregations created i.e. 20, the space used i.e. 254 MB and the optimization level which is100%.  If the optimization level is 0, it means that the aggregations will not help to improve the performance.
    You will see the number of aggregations created

  11. You can merge the new created aggregation with existing ones or create a new group of aggregations.
    merge the new created aggregation with existing ones

  12. Once done,  go to 'File' and select the 'Save All' option.  In the Solution Explorer right click on the cube and select the 'Process' option.  This will load the Process Cube interface where you want to select the 'Process Index' for your cube.
    save the project and process the indexes of the partitions

    Process the cube

 

Next Steps
  • With these steps the query performance can improve potentially by a very large percentage. In order to test the improvements, run key queries in Analysis Services before and after running the UBO wizard and record the improvements in the response time.
  • Also pay special attention to your storage while using this tool.
  • The SSAS performance guide is the bible to improve the performance. You will find great information to improve performance there. For more information read the following links:


Last Update: 2/22/2013


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Friday, February 22, 2013 - 1:33:30 PM - kwymore Read The Tip

This looks like a much better way of handling SSAS performance issues than by just creating partitions and aggregations and hoping it helps. I can't wait to try this approach. Thanks for the article!


Tuesday, June 18, 2013 - 11:35:31 AM - Ola Read The Tip

You sure made my day with this...Thanks alot


Tuesday, August 13, 2013 - 4:10:31 PM - mbourgon Read The Tip

AHA!  That's clever - I already use OlapQueryLog to verify usage, but was totally unaware of the UBO.  Thanks, sir!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.