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

By:   |   Comments (3)   |   Related: > Analysis Services Performance


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.


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 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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, August 13, 2013 - 4:10:31 PM - mbourgon Back To Top (26283)

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

Tuesday, June 18, 2013 - 11:35:31 AM - Ola Back To Top (25470)

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

Friday, February 22, 2013 - 1:33:30 PM - kwymore Back To Top (22364)

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!

get free sql tips
agree to terms