Every cube has at least one partition to contain the cube’s data; a single partition is automatically created when the cube is defined. When you create a new partition for a cube, the new partition is added to the set of partitions that already exist for the cube. The cube reflects the combined data contained in all of its partitions. A cube’s partitions are invisible to users of the cube.
Partitions represent a powerful and flexible tool for managing OLAP cubes, especially large cubes. For example, a cube containing sales information can contain one or more partitions for prior years’ data and partitions for each quarter of the current year. At the end of the year the four quarterly partitions can be merged into a single partition for the year. Partitions can be stored using various combinations of options for storage mode, source data location, and aggregation design. This flexibility enables you to design cube storage strategies appropriate to your needs.
Note User-defined partitions are available only if you install Microsoft® SQL Server™ OLAP Services, Enterprise Edition.
Partitions must be created and managed correctly to avoid inconsistent or inaccurate results. The integrity of a cube’s data relies on the data being distributed among the cube’s partitions such that no data is duplicated among the partitions. When data is summarized from the partitions, any data elements that are present in more than one partition will be summarized as if they were different data elements; this can produce incorrect summaries and erroneous data provided to the user. For example, if a sales transaction for Product X is duplicated in the fact tables for two partitions, summaries of Product X sales can include a double accounting of the duplicated transaction.
Partitions can be merged; you can use this feature in your overall storage and data update strategy. Partitions can be merged only if they have the same storage mode and aggregations. To create partitions that will be candidates for later merging, you can select the storage mode and copy aggregations from another partition when you create the partition. You can also edit a partition after it has been created and copy aggregations from another partition. Merging partitions must also be performed carefully to avoid duplication of data in the combined partition, which can cause cube data to be inaccurate.
When you are creating or merging partitions, you may need to perform manual operations or create appropriate filters to ensure that a cube’s partitions always contain the correct data. This section addresses issues and precautions to be aware of when you are: