Databases Reference
In-Depth Information
Create Partitions to Speed Up Processing
We expect Analysis Services to be installed on a multi-processor machine to take
advantage of multiple processors and have better performance. When a fact table
is partitioned into multiple partitions within a measure group, due to the inherent
parallelism in Analysis Services, the partitions are processed in parallel. You can
also specify the parallelism to be used by Analysis Services through the process-
ing option as discussed in
Chapter 12
. Hence having multiple partitions reduces
the processing time for the measure group as compared to a single partition.
As discussed in the design optimization section, creating partitions can
significantly improve processing performance during the typical daily,
weekly, or monthly updates. Most often the partition corresponding to
the most recent time period needs to be updated. Since a small subset
of the data (most recent partition) is processed you speed up the pro-
cessing time for the entire measure group.
We recommend you create multiple partitions for the measure groups whenever
you have large volumes of data. Consistent with the
Microsoft SQL Server 2000
Analysis Services Operations Guide
by Carl Rabeler and Dave Wickert (
ht-
you should consider having partitions of size 5GB, or 20 million rows. Based on
your business scenario, you might need to create partitions outside of that size pro-
file.
Creating too many partitions can potentially can potentially hurt processing per-
formance. Analysis Services processes objects in parallel based on your system
resources. If all the partitions are processed in parallel they can be competing for
resources. Furthermore the relational data source can also slow down processing
performance if Analysis Services sends requests to retrieve data for all the par-
titions. Analysis Services allows you to control the number of objects to be pro-
cessed in parallel (described later in this chapter). Make sure you design the right
number of partitions based on user queries and process with a certain degree of
parallelism.
Search WWH ::
Custom Search