Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

9.8. Working with Partitions

When building business intelligence solutions at the enterprise level, it is common to work with terabytes of source (also known as fact or detail) data. Even if you are working with just a few hundred gigabytes, you will find the use of partitions to be critical to your success.

By adding partitions to your overall cube design strategy, you can manage how and where cube data is physically stored, how a cube is processed as well as the time required for processing, and how efficiently Analysis Services 2008 can retrieve data in response to user queries. One key benefit of partitioning is the distribution of data over and across one or more hard disk drives and the ability of Analysis Services to process or query the partitions in parallel using multiple processors or cores that helps in processing and query performance. And in the case of remote partitions, the data can be spread over various machines. Partitions can even be processed in parallel on the remote machines. In this section, you first learn how to set up a local partition. Then, in the section that follows, you learn how to set up a remote partition configuration — which, by the way, is not the simplest procedure.

In order to work with partitions, you first need administrator privileges on both the local and remote instances of Analysis Services you intend to use. Administrator privileges are granted to member groups or users assigned to the Analysis Services server role. Being a member ...