Creating Partitions Using the OLAP Manager

Caution Incorrect use of partitions can result in inaccurate cube data. For more information, see Partitions and its subtopics.

Partitions are the storage containers for the data and aggregations of a cube. Every cube consists of one or more partitions. When a cube is created, a single default partition is automatically created for the cube. Multiple partitions are transparent to the user who sees only the total cube.

Note User-defined partitions are available only if you install Microsoft® SQL Server™ OLAP Services, Enterprise Edition.

To quickly and easily create a partition, use the Partition wizard. The wizard takes you through steps to specify the following definitions for the partition:

• Data source

  Select the data source that contains the fact table and dimensions you want in your partition. You can select the same data source used by the cube or a different one. If you select a different data source, it must contain fact and dimension tables with the same structure and columns as the cube’s data source.

• Fact table

  Select the fact table that contains the measures you want in your partition. You can select the same fact table used by the cube or a different one. If you select the same fact table, you must use a filter, as described below, to ensure that each partition in the cube includes mutually exclusive data. If you select a different fact table, it must have the same structure and columns as the cube’s fact table.

• Data slice (optional)

  Specify a data slice to create a partition that contains a subset of the fact table’s data. This option creates a WHERE clause expression that limits the partition’s data based on dimensions and member values you select. For example, if you select the Time dimension and the member values 1998 and Q3, the partition’s data is limited to the third quarter of 1998. The WHERE clause expression created by this option is used in combination with any filter (described below) you specify.

• Aggregations

  Select the aggregation design for the partition. You can design aggregations with the Storage Design wizard, defer aggregation design, or copy the aggregation design of an existing partition. If in the future you might merge the new partition with another, copy the aggregation design of the other partition. Merged partitions must have the same aggregation design.

• Filter

  Specify a WHERE clause expression to limit the data selected from the fact table and added to the partition. A filter is required if you select the same fact table used by the cube. For more information, see Filters.

To start the Partition wizard

1. In the OLAP Manager tree view, under a database, expand the Cubes folder, and then expand the cube in which you want to include the partition.
2. Right-click the Partitions folder, and then click New Partition.

After you create a partition, you must also process it to add its data to the cube. Depending on the size of the partition, processing may take considerable time.

The storage required for temporary files during processing can be substantially larger than the final size of the partition. If during processing you exhaust the free space of the disk containing your temporary file folder, you can specify a folder on another disk with more free space. For more information, see Properties Dialog Box - General Tab.

To process a partition

1. In the OLAP Manager tree view, under a database, expand the Cubes folder, expand the cube containing the partition, and then expand the Partitions folder.
2. Right-click the partition, and then click Process.
3. In the Process dialog box, wait for the partition to finish processing, or click Stop to halt and cancel processing.

After processing completes but before you close the Process dialog box, you can view the SQL statement used to process the partition. This statement is created in part from any data slice and/or filter specified in the Partition wizard.

To view an SQL statement

1. In the Process dialog box, click a line beginning with the SQL icon.
2. Click View Details.

Referential integrity of the data warehouse is not verified by OLAP Services. So, for example, if the partition’s fact table contains foreign key values that are not present in a joined dimension table’s primary key column, the rows containing those values are not processed. In this case, processing does not produce an error message, but the partition contains incomplete and therefore inaccurate data.