Dimension and Partition Processing - Microsoft SQL Server 2008 Analysis Services - page 394

know when users will get improved query performance for your cube. We recommend
that you use the ProcessIndex processing option to build aggregations and indexes for
your partitions. However, there is one exception: When a user performs a ProcessUpdate
of a dimension that has flexible attribute relationships, Analysis Services deletes all flexible
aggregations for all the partitions where changed attribute is used. You have an option to
rebuild all aggregations and indexes as part of a dimension ProcessUpdate command by
using the ProcessAffectedObjects processing command option. In that case, an update
of the dimension might take a long time. You might decide to let Analysis Services update
your dimension and rebuild indexes in the lazy manner.

If you distribute partitions to different machine using the remote-partition capabilities of
Analysis Services, you can improve the performance of building indexes and aggregations.
Because each machine builds indexes and aggregation independently, it can use more
system resources.

To fine-tune the building of aggregations and indexes, you can divide this process into
two stages. Build indexes of the partition before defining an aggregation design for it. (See
Chapter 23 for more about aggregations and aggregation design.) Issue an Alter command
to add the aggregation design to your partitions and process the indexes and aggregations
again. This time Analysis Services builds only aggregations for your partitions and doesn't
rebuild indexes.

You can add additional aggregations to your AggregationDesign object and build indexes
and aggregations again. In that case, Analysis Services doesn't rebuild all existing aggrega-
tions; it builds only new aggregations for your partition.

NOTE
Analysis Services doesn't build aggregations and indexes for small partitions (parti-
tions smaller than 4,096 records). It also doesn't build indexes for small aggrega-
tions. You can change this setting by using the IndexBuildThreshold server
configuration property.

Incremental Updating Partitions
To update your partition incrementally, you can use the ProcessAdd processing option.
Analysis Services internally implements the ProcessAdd operation in two stages. In the
first stage, it creates a new partition and processes it with new data. In the second stage,
Analysis Services merges the new partition into an existing one using the merge operation.

If we were to look at the larger problem of updating a cube with new data, we would see
three main solutions:

. Incrementally update existing partitions in your cube

. Fully reprocess partitions pointing to a table that contains old and new data

. Create a new partition that contains new data