Creating Analysis Services Cubes - SQL Server Analysis Services 2012 Cube Development - page 113

Defining aggregation designs
Aggregations are precalculated summary tables that provide query results much
faster than if you were to query the full fact table. For example, the sample
FactResellerSales table contains over 60000 rows. Instead of reading through
60000 rows to find the sum of total product cost, SSAS can read a single row in the
aggregation table. In real-world applications with billion-row fact tables, the differen-
ce is much more pronounced, especially when summarized values are grouped by
dozens of attributes. Unfortunately aggregations don't come free of charge; they are
calculated as part of partition processing and therefore increase the total process-
ing duration. Aggregations could also use significant amount of disk space. There-
fore, designing aggregations is an art of balancing performance improvement with the
overhead of processing time and additional storage.
SSDT and SSMS offer two wizards for designing aggregations: Aggregation Design
Wizard and Usage Based Optimization Wizard. To activate either of these wizards
from SSMS, right-click on any partition (or the Partitions folder) and select either the
Design Aggregations or Usage Based Optimization option, respectively. Both wiz-
ards are aptly covered in product documentation, so we will not include any screen-
shots here.
The aggregation design wizard allows you to specify the relative significance of each
dimension attribute you wish to include in aggregations. It can also count the number
of rows in the fact table and compare it to the count of rows found in each attribute to
determine approximate performance improvement offered by each potential aggreg-
ation. If the wizard determines that the aggregation file size will be at least one third
(1/3) of the full data file size, the aggregation will not be considered. This is limiting
because you may well have the most important query that is executed hundreds of
times per day that would benefit from a very large aggregation. Another limitation is
that counting rows in a fact table with billions of rows or in very large dimensions could
take unacceptably long and hence isn't always a practical option. Nevertheless, many
developers use the aggregation design wizard as the starting place when they don't
have a lot of time to dedicate to performance tuning and the initial data set is relatively
small.
Usage-based Optimization ( UBO ) wizard allows building aggregations depending
on query history. To use this wizard you must first enable query logging by editing
Analysis Services configuration properties (you can right-click on the SSAS instance