In a previous article I wrote about a method for performance tuning your cube called Analysis Services Aggregations. In that article I detailed what aggregations are, how they help performance and even gave you step by step instructions on how to design your own aggregation. You learned that when you design an aggregation that a file stores possible queries/aggregations that Analysis Services determines is likely to be used. So Analysis Services essentially knows the answer to your question before you even ask it.
In this article I’ll describe a special type of aggregation design called Usage-Based Optimization (UBO), which allows you to use actual user queries to build an aggregation design. Now you don’t have to rely just on Analysis Services to find aggregations because this feature will take real queries that your users run and optimize based on them.
The benefits don’t stop here though. Adding filters to UBO allows you to only performance tune on queries that meet criteria you define. For example, you can pick individual users to optimize around. So if you want the queries run by your corporate staff to run faster than everyone else you can specify the user accounts then only their queries will be used in building the aggregations. You also have the option to optimize queries run during a specific date range. For example, you want queries run at the end of the month to be optimized because most month end reports are run then. Lastly, you can optimize based on frequency of query occurrences. If one query represents the majority of queries run it will be optimized over others.
There are a few setups to do before UBO can be used in an aggregation design. After following these steps your queries will start being collected in a regular SQL Server database table.
· In SQL Server Management Studio you will connect to Analysis Services.
· Right-click on server in the Object Explorer and select Properties.
· The four QueryLog properties must be manipulated to start collecting queries that are run against Analysis Services.
· Change the CreateQueryLogTable property to true to create the table that will collect queries.
· Change the QueryLogConnectionString property to connect to the database that the query log table can be created and store queries. Use SQL Authentication so Analysis Services can find the table without your credentials.
· Change the QueryLogSampling property to tell Analysis Services how many queries you want to log. By default this is set to log every 10th query.
· Last change the QueryLogTableName is you would like the table to have a different name then the default OlapQueryLog.
If you navigate to the database engine and find the query log table you can see it tracks the following:
· Analysis Services database used
· Analysis Services object queried
· User who ran the query
· Dataset queried. Doesn’t make sense to you and me but perfect for Analysis Services
· When the query was run
· How long the query took to run
Now that you have the setup completed you can an aggregation design using UBO in Business Intelligence Development Studio (BIDS).
· Open the cube designer and select the Aggregations tab.
· Right-click on the measure group that you want to design the aggregation for and select Usage Base Optimization. This example will follow the sample example from the previous article Analysis Services Aggregations which used the Internet Sales measure group.
· Click Next when the wizards open. Then select the partitions that the aggregation design should be applied to and click Next. Partitions were discussed in the article Analysis Services Partitions.
· You may have to enter the SQL Authentication used for UBO connection string when then next screen opens. Choose any optional Filter Criteria that were discussed earlier in the article. For example, only have corporate staff’s queries used in optimization. Again these are optional so you can just click Next and UBO will use all queries without filters.
· Next you will find the list of distinct queries found in the query log with the number of occurrences. You can check or uncheck queries that you do not want to be included in the aggregation design. Click Next when you have all the queries you want included.
· The next screen requires a count of each distinct attribute which was detailed in the previous article. Hit Count and then Next.
· Choose storage and query performance options. Do you want to continue aggregating until performance gain is a certain percentage or until the aggregation file is a certain file size. Once you decide your option hit Start then Next when it completes. In the previous article I wrote about a point of diminishing return that you typically only see when creating a regular aggregation where Analysis Services finds aggregations for you.
· Last you will can give the aggregation design a name or merge it with an existing design. If you decide to merge it will add the UBO aggregations with the already existing design aggregations. I like the merge function so I can get the best of both worlds. What Analysis Services decides is best and what your users actually query. Make your choice then hit Finish.
Usage-Based Optimization is a great feature and I hope this helps in better understanding it.
I watched the SQLLunch today on this topic (great session by the way) and asked the follow up question regarding how to do UBO in SSIS. Your answer makes sense regarding taking the script from the aggregation and putting it into an AS DDL task. My question is with regard to the last screen shot above, and how that translates to an AS DDL task. I am interested in running a kind of generic UBO based entirely on actual usage without regard to user or the query they are using. I assume that checking queries in the "Review Queries that will be Optimized" screen will result in a script that is specific to optimizine those queries. How would I create a script for use in an AS DDL Task that would reflect all queries run, or at least all queries run more than x times in the period in question?
Again, thanks for the article and the SQLLunch.
Create a Sample Aggregation in BIDS then browse your AS database in Management Studio. Drill down to find the aggregation you designed then right-click on it and script it out.