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

Partition 1 Partition 2 Partition 3 Partition 4 Partition 5

2004 2005

2002 2003

CurrentMonth

Assume the fact data is in a single fact table called Yearly Sales, and
you have a measure group within your UDM that has been created from
this fact table. By default the Cube Wizard creates a single partition that
points to the relational table Yearly Sales. By dividing the data into mul-
tiple partitions, all the partitions can be processed in parallel, which will
utilize the processor and memory resources of your machine more ef-
ficiently. Unlike Analysis Services 2000, which processed each partition
serially, Analysis Services 2005 processes objects in parallel by default.
If the Server machine has more than one CPU and sufficient memory,
parallel processing will reduce total process time as compared to a ma-
chine with single CPU. During query time, if cubes contain several par-
titions, Analysis Services can scan those partitions in parallel, and quer-
ies can return results quickly because the server has multiple jobs run-
ning in parallel, each one scanning a partition. In addition to that, when
you divide the data into multiple partitions based on specific dimen-
sion member, Analysis Services 2005 by default retrieves data only from
relevant partitions needed for the query if the cube is of storage type
MOLAP. For example, if a query requests data for partition 2002, Analys-
is Services will only query the data from partition containing 2002 data.
In order to create multiple partitions, you can create multiple tables in
the relational database and then create the partitions. Alternatively, you
can create named queries within your DSV that correspond to your de-
sired partition strategy.

The second benefit of creating partitions in an environment where most of the data
does not change is that you only need to process the current partition, which has
new data added or modified. You need to only process the currentMonth partition
in the partition scheme to refresh the latest data changes within your UDM. The
data volume to process is reduced and the process time will be significantly de-
creased compared to processing the entire UDM.

The third benefit of partitioning data is that you can set up different storage modes
for each partition. In business scenarios where a certain amount of data is not