Partition Planning: Based Upon Resource Assignments and Settings
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.)
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
Aggregation Settings: To see an example set of aggregation settings, lets
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.
Expand the Partitions folder
within the Internet Sales measure group, by clicking the + sign to its
partitions for Internet Sales appear, as shown in Illustration 13.
Illustration 13: The Four Partitions for the Internet Sales Measure Group
the Internet_Sales_2003 partition.
context menu appears, from which we can perform many partition-related
activities and settings, as depicted in Illustration 14.
Illustration 14: The Context Menu for a Selected Partition
focus on the partition Properties throughout most of this article.
on the context menu.
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.
Illustration 15: The Aggregation Design ID Setting in the Partition Properties Dialog ...
Lets 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
Expand the Aggregation
Designs folder within the Internet Sales Measure Group, once again, in the Object
Explorer, by clicking the + sign to its immediate left.
Sales appears, as the only design listed, as depicted in Illustration 16.
Illustration 16: The Internet Sales Aggregation Design Appears ...
the Internet Sales aggregation design.
context menu appears, from which we can perform a handful of administrative activities
surrounding the aggregation design under consideration.
Aggregation Design on the context menu, as shown in Illustration 17.
Illustration 17: Select Assign Aggregation Design ...
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.
Illustration 18: The Assign Aggregation Design Dialog
Here we can assign aggregation designs to one or more
destination partitions. We see the following columnar settings on the dialog:
displays the name of the partition.
displays the source table or query for the partition.
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.
Click Cancel to
dismiss the Assign Aggregation Design dialog without making permanent changes.
Return to the Partition
Properties dialog for the Internet_Sales_2003 partition, where we left it on
the General page.
Click the Selection
page in the Select a page pane on the left side of the Partition Properties
The Selection page of the dialog
appears, as shown in Illustration 19.
Illustration 19: The Selection Page of the Partition Properties Dialog
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.
10. Click Cancel to dismiss the Partition
Properties dialog without making permanent changes, and to return to the SQL
Server Management Studio.
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.
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
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.
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 ...
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