Using Aggregations to Performance Optimize a Cube - Getting Started with SQL Server 2012 Cube Development - page 229

Chapter 8. Using Aggregations to

Performance Optimize a Cube

Performance is one of the crucial parts of any development project. If a user has to
wait too long in an application, they will be dissatisfied and not keen on using the ap-
plication. This is especially important for reporting applications. Many users can man-
age their daily job without reports; as an example, a sales representative can meet
customerswithout havinginformation aboutwhatthecustomerhasbought previously,
but the meeting will be more productive if they have this information. As a developer it
is your responsibility to build a solution that performs. But what is good performance?
If you ask a user, waiting more than 30 seconds is many times unacceptable for a re-
port. If you have to wait that long, then it is very hard to do the ad-hoc analysis in an
efficient way.

One of the main benefits of Analysis Services and other OLAP engines is the per-
formance that you will get from them. The reason that you will get good performance
is due to the fact that the OLAP engine has the ability to work with aggregations.

In this chapter, we will cover how you can optimize a cube solution using the following
methods:

• Adding aggregations using the aggregation wizard
• Creating efficient aggregations manually
• Using usage-based optimization to implement a self-tuning cube

Understanding cube aggregations

Analysis Services uses a multidimensional space that consists of cells containing the
data.

Since the data in Analysis Services is stored on the physical disk, running a query
that affects cells on the lowest level can be slow. If you consider the cube in the previ-
ous picture, running a SELECT statement that aggregates the Internet Sales amount
would access all the cells in the cube. However, you can pre-aggregate the data on
frequently used levels of the different attributes. In the previous picture, aggregating