Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Partition Wizard

Create a Partition for the New Cube

We can begin partitioning of our new cube with a few basic steps.

12. Expand the new SSPerf_Budget cube.

13. Right-click the Partitions folder that appears within the Cube object.

The context menu appears.

14. Click New Partition, as shown in Figure 4.

Figure 4: Select New Partition

The Welcome dialog appears, as depicted in Figure 5.

Figure 5: Welcome Dialog – Partition Wizard

15. Click Next.

The Specify the data source and fact table dialog appears, as shown in Figure 6.

Figure 6: Specify the Data Source and Fact Table Dialog – Partition Wizard

We will leave this dialog at default, although we might have used it to specify a different data source to be used to define our partition. We will take advantage of this option in partitioning, and in an identical option for performing incremental updates, in future articles. For now, let’s continue with our exploration of the basic partitioning process.

16. Click Next.

The Select the data slice dialog appears. Here we can designate the portion of our fact table upon which our partition is based. This dialog makes specification of the slice easy, by offering a selection of the dimensional members (the slice can be based upon a member, from any level of any available dimension) that are available within our cube’s structure. A filter is created, based upon our selection in this dialog, which restricts the rows in the data source that MSAS retrieves during cube builds. We can also remove slices here, to modify an existing arrangement we have in place, as the need arises.

17. Click Time in the Dimensions box (left side of the dialog).

The members of the Time dimension appear in the Members box, to the right of the dialog.

18. Click 1997 to select it.

The Select the data slice dialog appears, with our selections, as shown in Figure 7.

Figure 7: Select the Data Slice Dialog – Partition Wizard

19. Click Next.

The Specify the partition type dialog appears. Here we can choose between a local (exists on the same server as the parent cube) or a remote partition type. This opportunity to specify the physical placement of the partition, to which we alluded in our introductory comments, can only be set at its creation. For purposes of our exercise, we will leave the dialog at the default selection of Local.

The Specify the partition type dialog appears as depicted in Figure 8.

Figure 8: Specify the Partition Type Dialog – Partition Wizard

20. Click Next.