Professional SQL Server™ Analysis Services 2005 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. You can bet a company like Wal-Mart has many terabytes of detailed data feeding into its business intelligence solutions. 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 2005 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 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 2005 server role. Being a member of the server role is analogous to being ...