Tuesday, October 15, 2013

Cube Optimization in SSAS

In a BI solutions, SSAS cube may contains billions of records. In such large volume of data, query processing will be crucial. End user wants to get the output without much delay and to cater that you need to optimize the query processing. Otherwise it may take minutes to get the query results.

Anyway before optimizing the cube, DW must be designed properly to get the good query performance. In this post, only the cube optimization is considered.
For cube optimization, two main approaches can be used

  • Partitioning
  • Aggregation Designing


Partitioning

Since there are large volumes of data, we can split those data of a measure group, into different portions by considering different attribute values. Those portions of data set of a measure group are called as partitions.
As an example we can create partitions considering the month value. For each month there will be different partitions under one measure group. Those partitions are not visible to the end user and the user will only see one measure group.
Creating partition will help to process partitions separately and if only one partitions gets changed, then only that partition can be processed without affecting other partitions. Therefore partitioning is making the maintenance of the cube much easier.

Aggregation Designing

By using aggregation designing, Cube keeps pre-calculated set of data. It will increase the query performance, since those calculations are already done in the query execution.
We can design aggregation using the below two methods in SSAS cube.

  1. Design Aggregations Wizard 
  2. Usage Based Optimization Wizard


Both of above wizards can generated using the Aggregations tab of the Cube in BIDS.
In the Design Aggregation options, we can set the thresholds for the required aggregations and it will automatically design aggregations according to those thresholds.

Usage Based Optimization method uses the executed queries and design aggregations based on those. In that approach, aggregations are designed based on the frequently used queries, which takes more time.

For the long run, Usage Based Optimization will provide more query performance for the end user, since it will design aggregations based on the used queries.

Used the below reference to identify the steps used in BIDS for those aggregation design methods.
Design Aggregation Wizard
Usage Based Optimization Wizard