How to enable SSAS Usage-Based Optimization in SSAS

Friends,

Usage-Based Optimization is a fantastic feature in SSAS to improve the most frequently used MDX queries of any cube database. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio. But when ever you try to design Usage-Based Optimization you might have seen all the options disabled. Why is it so and how to enable it ? For the answers to these questions, continue reading the post.

This uses the technique of creating aggregations on FREQUENTLY USED queries in the given time interval. Options in Usage-Based Optimization wizard will be disabled because the queries executed on the CUBE database will not be stored anywhere by default and you c an see the same in below given pic.

When there is no queries stored then this concept fails and hence all the options will be disabled. We can change the settings in order to enable the STORAGE of queries that are executing on cube and hence we can enable the options in Usage-Based Optimization wizard. PFB the steps involved in enabling the storage of queries.

• Connect to SSAS using SSMS (Sql Server Management Studio).
• Right click on the server and select properties as shown below.

• set CreateQueryLogTable to true.
• Set the value of QueryLogConnectionString to any database where you wish to store your queries.
• Set the value of QueryLogSampling to the appropriate value for your environment (the default value of 10 means every 10th query will be logged). The lower the value of this property, the higher the overhead of query logging will be on the system.
• Set the value of QueryLogTableName property to any TABLE NAME of your wish. 
• Click OK.

Everything set and now try to execute some queries on the cube and check the table(given in the above steps) in the database(mentioned in the above steps). It should contain the queries you executed on the cube database.

Once the queries started logging, we are all set to configure Usage-Based Optimization for the queries. In the below pic you can see all the options are enabled.

These are the pre requisites to configure Usage-Based Optimization and check the next post to see the steps involved in configuring the same.

Thanks,
Roopesh Babu V