Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective - Page 4

December 11, 2009

Partition Planning: Based Upon Resource Assignments and Settings

Within Analysis Services, as we have learned, we can split individual measure groups into multiple partitions, which can each have different resource assignments and settings. When planning partition design, we need to consider each of these options to optimize our overall solution in our own environments. We will discuss some of these resource assignments / settings in the individual subsections that follow. In each case, we will take a look at existing settings in an example measure group, Internet Sales, within the sample Adventure Works cube. (We will actually create and work with partitions in articles dedicated to various aspects of doing so in other articles of this subseries.)

Aggregation Settings

Discussion: As we discussed earlier, we can create a partition or partitions for non-current / historical time frames that experience light querying, and enact minimal (or even no) aggregations for such a partition(s). In contrast, we can plan to add more aggregations into partitions that experience heavier use, to enhance query performance. The degree of aggregation, based upon the expected degree of querying, is one of the most common partition planning considerations.

Examine Aggregation Settings: To see an example set of aggregation settings, let’s take a look at the Internet Sales measure group within the SQL Server Management Studio. From the Partitions folder, we use the context menu available for each of the partitions associated with the measure group in the cube to access many settings and properties, as we shall see.

Four partitions for Internet Sales appear, as shown in Illustration 13.

A context menu appears, from which we can perform many partition-related activities and settings, as depicted in Illustration 14.

We will focus on the partition Properties throughout most of this article.

The multi-paged Partition Properties dialog for the Internet_Sales_2003 partition, defaulted to the General page, appears. Of specific interest to us within this subsection is the Aggregation Design ID setting that we see on the General page. The setting references the aggregation design established for the partition. For the third partition supporting the Internet Sales measure group, we see that that the Internet Sales 1 Aggregation Design ID is referenced, as shown in Illustration 15.

Let’s take a look at the aggregation design settings themselves, to gain further insight, by accessing the Aggregation Designs folder back in the Object Explorer of the SQL Server Management Studio. (We can leave the Partition Properties dialog open, and take the following steps from our current position.)

Internet Sales appears, as the only design listed, as depicted in Illustration 16.

A context menu appears, from which we can perform a handful of administrative activities surrounding the aggregation design under consideration.

The Assign Aggregation Design dialog appears, showing the assignment of the design across the four partitions of the Internet Sales Measure Group, as depicted in Illustration 18.

Here we can assign aggregation designs to one or more destination partitions. We see the following columnar settings on the dialog:

• Partition – displays the name of the partition.
• Source – displays the source table or query for the partition.
• Aggregation Design - displays the name of the existing aggregation design for the partition.

We can use the checkbox labeled Hide partitions with aggregation designs to select to show only the partitions that do not have aggregation designs assigned to them, should this means of filtering prove useful in large lists, etc.

The Selection page of the dialog appears, as shown in Illustration 19.

In addition to presenting the Partition Name and Source information that we saw earlier, the Selection page of the dialog presents information concerning the type (MOLAP) and count (54) of aggregations.

We have explored some of the aggregation settings that exist for some of the aggregation settings within the measure group that we have examined within the sample cube. Recall that every measure group is created with at least one partition from the outset, and, that if we wish to plan partitions before their creation, we need to keep in mind the settings that we can modify, once we have accomplished this, with regard to aggregations. We typically modify aggregations with the Aggregation Wizard, a hands-on introduction to which we give in another article of this series.

We will conclude our Part 1 practice session at this point, returning in Part 2 to restart our session with an examination of partition source and fact table assignment, and to continue with other partition planning criteria, the properties and settings surrounding which we will continue to peruse within SQL Server Management Studio.

Conclusion

In this, the first half of a two-part article, we continued the general exploration of cube storage that we began in Cube Storage: Introduction, again focusing upon partitions, which we initially introduced in Cube Storage: Introduction to Partitions. We noted that this article was one of several others in this subseries that explore various concepts surrounding partitions in Analysis Services. We discussed partition planning considerations and how they are important to partition design within our business intelligence solutions.

After preparing a copy of a sample Analysis Services 2008 database with which to perform our practice session, we began discussing partition planning in general in Analysis Services; we explored the first of several individual resource assignment and settings considerations, Aggregation Settings, in planning partition design within Analysis Services, in general. (We will continue this examination in Part 2 for additional resource assignment and settings considerations). Finally, for the resource assignment and setting consideration we discussed, we examined a respective example resource assignment / setting within the Adventure Works sample cube, from the perspective of the SQL Server Management Studio.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The monthly column is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube.

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