Creating Analysis Services Cubes - SQL Server Analysis Services 2012 Cube Development - page 97

Partitioning strategies
Each Analysis Services measure group consists of one or more partitions. When you
first create a measure group, it has a single partition; however, for large scale imple-
mentations, creating additional partitions is essential. Having multiple partitions bene-
fits both processing as well as querying performance. Partitions can improve query
performance because Analysis Services can examine multiple partitions in parallel
and therefore return the requested results quicker than if it had to scan a single, very
large partition. Additionally, Analysis Services can exclude the partitions which do not
contain the relevant data for a particular query request, thereby shortening the total
query execution time. Analysis Services can also process multiple partitions in paral-
lel to minimize the total processing time of the measure group. The mentioned bene-
fits can only be achieved with necessary prerequisites in place, and we will discuss
those prerequisites in this section. Partitions also ease cube maintenance; most of
the cubes retain relevant data for a limited time span, perhaps for a week, a month, or
even for a few years. Once data for a specific day, month, or year is no longer needed,
you can simply delete the obsolete partitions.
In general you should limit each partition to less than 20 million fact table records. It
is also beneficial to split a partition if the corresponding datafile size exceeds 2 GB.
These are merely best practice recommendations based on experience; your mileage
may vary and you should monitor querying as well as processing performance to de-
termine the most appropriate partition size and row count for each project.
You have multiple options for creating partitions; given in the following bullet list:
1. Use the Partition wizard within the Partitions tab of SSDT or SSMS.
2. Use the XMLA command; you can submit the command to the server through
the SSMS or the ascmd.exe command-line utility. Note that if you're not fa-
miliar with ascmd.exe , it is an extremely valuable tool for executing XMLA,
MDX, and DMX commands from the Windows command prompt (download it
from www.codeplex.com ) .
3. Use the Analysis Management Objects ( AMO ) interface to create partitions
programmatically using .NET or a scripting language of your choice.