Query Performance Tuning - Expert Cube Development with SSAS Multidimensional Models - page 207

Why partition?
Partitioning brings two important benefits: better manageability and better
performance. Partitions within the same measure group can have different storage
modes and different aggregation designs, although in practice they usually don't
differ in these respects; more importantly they can be processed independently,
so for example when new data is loaded into a fact table, you can process only the
partitions that should contain the new data. Similarly, if you need to remove old
or incorrect data from your cube, you can delete or reprocess a partition without
affecting the rest of the measure group. We'll explore these manageability benefits
in more detail in Chapter 11 , Monitoring Cube Performance and Usage .

Partitioning can also improve both processing performance and query performance
significantly. Analysis Services can process multiple partitions in parallel and this
can lead to much more efficient use of CPU and memory resources on your server
while processing is taking place. Analysis Services can also fetch and aggregate data
from multiple partitions in parallel when a query is run too, and again this can lead
to more efficient use of CPU and memory and result in faster query performance.
Lastly, Analysis Services will only scan the partitions that contain data necessary for
a query, and since this reduces the overall amount of IO needed this it can also make
queries much faster.

Building partitions
You can view, create, and delete partitions on the Partitions tab of the Cube Editor
in SSDT. When you run the 'New Partition' wizard or edit the Source property of an
existing partition, you'll see you have two options for controlling what data is used
in the partition:

• Table Binding : This means that the partition contains all of the data in

a table or view in your relational data source, or a named query defined
in your DSV. You can choose the table you wish to bind to on the Specify
Source Information step of the New Partition wizard, or in the Partition
Source dialog if you choose 'Table Binding' from the Binding Type
dropdown box.

• Query Binding : This allows you to specify a SQL SELECT statement to filter

the rows you want from a table; SSDT will automatically generate part of
the SELECT statement for you, and all you'll need to do is supply the WHERE
clause. If you're using the New Partition wizard, this is the option that will
be chosen if you check the 'Specify a query to restrict rows' checkbox on the
second step of the wizard; in the Partition Source dialog you can choose this
option from the Binding Type dropdown box.