Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 11, 2004

Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction - Page 4

By William Pearson


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

1.  Expand the new DBJ_Budget cube.

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

The context menu appears.

3.  Click New Partition, as shown in Illustration 4.

Click for larger image

Illustration 4: Select New Partition

The Welcome dialog appears, as depicted in Illustration 5.

Click for larger image

Illustration 5: Welcome Dialog - Partition Wizard

4.  Click Next.

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

Illustration 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 subsequent articles. For now, let's continue with our exploration of the basic partitioning process.

5.  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 Analysis Services retrieves during cube builds. We can also remove slices here, to modify an existing arrangement we have in place, as the need arises.

6.  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.

7.  Click 1997 to select it.

NOTE: We see the Quarter levels if we expand 1997 with the "+" sign to the left, although we wish only to select 1997 at present.

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

Illustration 7: Select the Data Slice Dialog - Partition Wizard (Year 1997 Expanded)

8.  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 Illustration 8.

Illustration 8: Specify the Partition Type Dialog - Partition Wizard

9.  Click Next.

The Finish the Partition Wizard dialog appears. Here we can name the partition, and can make provision for its storage design. In our example, where we are creating partitions to segregate years, it is likely that, in the future (say, in 2000), our partitioning strategy might include the merging of years beyond the current and its immediate predecessor, which are by far the most frequently accessed for financial reporting purposes.

10.  Type the following into the Partition name box:


Merging 1997 and 1998 (the reciprocal partition) together requires that the storage design for both be identical. Another convenience that is provided in the Finish the Partition Wizard dialog is the opportunity to design storage for the new partition in the image of the existing partition (remember that all cubes have at least one), through the use of the Copy the aggregation design from an existing partition radio button.

11.  Select the radio button to the immediate left of Copy the aggregation design from an existing partition, in the What do you want to do? section of the dialog.

The Copy from selector that accompanies the Copy the aggregation design from an existing partition option becomes enabled.

12.  Ensure that DBJ_Budget appears in the Copy from selector.

13.  Click the Process the partition when finished check box.

Another option we have at this stage is accessed via the Advanced button, appearing in the lower right of the dialog. Here, we can use the WHERE clause of an SQL statement to further filter our partition. We will revisit this option in subsequent articles.

The Finish the Partition Wizard dialog appears as depicted in Illustration 9.

Illustration 9: The Finish the Partition Wizard Dialog (with Our Settings Circled)

14.  Click Finish.

Processing begins, and we are soon greeted with indication of successful completion via the Process log viewer. The log viewer appears as shown in Illustration 10.

Illustration 10: The Process Log Viewer Indicates Successful Completion

15.  Click Close to close the Process log viewer.

The new partition, together with the pre-existing one, appears in the cube tree, as depicted in Illustration 11.

Illustration 11: The New Partition Appears ...

16.  Discard the DBJ_Budget cube, as desired.

(Simply right-click, and select the Delete option from the context menu that appears, to delete the cube clone.)

17.  Select File -` Exit, when ready to close Analysis Manager.

We have thus practiced the basic steps of using another useful performance tuning tool, the Partition Wizard. As more data becomes available for analysis, partitions (for quarters or months, in our simple scenario) could potentially be created to meet the needs of the organization in an optimal way. Those requirements, and the frequency with which they would need to be met, would dictate the best strategy for storage and processing optimization through the use of partitions. Once the partitions were created, aggregations and other considerations could be designed for each, with the only limitation being mandated by the likelihood of future merges, as we suggested in our practice exercise.


In this lesson we introduced basic partitioning, another tool in our MSAS cube design and optimization toolset. As its name implies, the Partition Wizard provides us an easy means of creating and modifying partitions within our cubes. We first discussed partitioning as a concept, within the context of MSAS cubes, and then we began a hands-on exercise where we created a partition upon a copy of the Budget sample cube. As we proceeded through the guided steps of the Partition Wizard, we examined the options that are available to us, commenting upon general properties and optimization considerations along the way.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM