Usage Based Optimization is the process of defining aggregation design based on the actual queries executed by the users/applications accessing the cube, which is captured using a log.

SSAS offers a wizard called Usage Based Optimization Wizard to perform Usage Based Optimization by defining aggregation design. The Usage Based Optimization Wizard provides the following options as part of the Aggregation Design Process:

• Design Aggregations until estimated storage reaches “X” MB.
• Design Aggregations until performance gain reaches “X”  percentage.
• Design Aggregations until the person, designing the aggregations, clicks the “Stop” button.

As we can see, the above options offered by the Usage Based Optimization Wizard are similar to the ones offered by the Aggregation Wizard.

However, in the case of the Aggregation Wizard, all possible queries are given equal importance, whereas in the case of the Usage Based Optimization Wizard, the importance is based on the actual queries being fired.

To use the Usage Based Optimization Wizard, Query Log Properties need to be configured at the SSAS Server Level for the tool to be able to log the queries fired by users for accessing the cube.

Until next time,

                           keep learning, keep searching and keep succeeding…

Powered by CodeReview – Let’s make it Better!