MSAS - Introduction and Managing Partitions

The cube is a storage container that contains data and aggregations of data. Every cube must have at least one partition which must be stored in the Analysis server in which the cube is defined. Partitions are stored as separate files in the Partitions folder under the cube.

If a cube contains multiple partitions, some of them can be stored in different physical locations. Partitions of a cube can also have different data sources. The aggregations of the data in the partitions can also be stored in different locations. The end user sees the cube as a single unit and the partitions are not visible to him. The cube displays all the data in the various partitions as a single composite data structure.

This technology allows portions of the cube to be distributed across multiple locations and in managing cubes that grow in size. Cubes with multiple partitions can be created only if the Enterprise edition of the SQL Server 2000 Analysis Services is installed in the machine. Multiple partition cubes have become an easy and flexible method of managing large data sources or multiple data sources. These are sometimes known as Distributed partitioned cubes. Partitions which are stored in Analysis servers other than the ones in which the cube is created are known as remote partitions.

Partitions can be stored using combinations of options for location of source data, location of aggregation data, storage mode, and aggregation design. The user can create storage options appropriate to his needs.

A partition of a cube may have a different data source from the cube. Even where the same data source is used the cube and the partition need not have the same fact table. Where a different data source is used by a partition, the source must contain a set of tables that are the same as those contained in the cube’s schema. Only minor variations --such as a difference in the name of the fact table-- is tolerated.

The data of a cube is a composite of all the data of its partitions. When the data in a partition is changed, or a new partition is added or a partition is deleted from a cube, and the cube is processed, the data in the cube changes.

The aggregations of the partition are stored in the Analysis server in which the cube is defined by default. However the user can choose to store the aggregations elsewhere as a remote partition. The storage mode also determines whether a copy of the partitions source data is stored on the Analysis server computer. Each partition can have a separate aggregation design which determines the number and contents of the aggregations created for the partition. Constraints for storage utilization can be specified using the Storage Design wizard. This helps the user tailor the aggregation design and increase query performance. The Usage Based Optimization Wizard enables the user perform the above actions of optimizing aggregation design based on queries previously sent to the partition’s cube. These aggregations are then created when the cube is processed.

In the object hierarchy partitions are immediately subordinate to the cube. The partition’s data source and its aggregations are subordinate to the partition.