Performance Optimization - Professional SQL Server Analysis Services 2005 with MDX - page 640

changing and the data volume is huge, it is better to use MOLAP to increase query
performance. In addition, if you need real-time access to your data for analysis you
can create a small partition with the current month's data and set the storage mode
for that partition as ROLAP. Analysis Services retrieves the data from the appro-
priate partitions and provides you the aggregated real-time data for your business
analysis.
Yet another benefit of partitioning data is that you can create different
aggregations (pre-calculated data cache for improved query perform-
ance) for maximum benefit. Creating aggregations for your UDM is
covered in detail later in this chapter. You can design heavy aggrega-
tions for those partitions that are queried most (the current year data)
to maximize query performance, and create less aggregations for old
data that are queried less to save the server storage. Creating such
aggregations not only saves storage space but also reduces processing
times if the entire UDM is processed.
Finally, refreshing the data within a partition is much faster than refreshing the en-
tire UDM. You do not have to apply incremental processing on all the partitions.
You just have to do incremental processing on the partition whose data needs to be
updated. During incremental processing of the current partition, a temporary par-
tition is created and then merged into the existing partition, which could possibly
result in data fragmentation. By refreshing the partition, the server will re-sort the
data for optimized query performance.
Merging Partitions
If you have multiple partitions where data is used sparsely, merge the partitions so
that data from each partition does not get aggregated every time a query is issued.
Assume you have your measure group data partitioned by month for the year 2002.
If every user's query is asking for the entire year's data, Analysis Services needs
to retrieve the data from all the partitions and aggregate data within the server.
Having too many partitions could hurt query performance because the
server needs to scan those partitions and aggregate data from them.
Instead you can merge the data from all the partitions to form a single
partition. Queries referring to 2002 will henceforth touch a single parti-
tion. If you have aggregations created at the year level then your quer-
ies can be instantaneous.