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.

1.  Expand the Partitions folder within the Internet Sales measure group, by clicking the “+” sign to its immediate left.

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

The Four Partitions for the Internet Sales Measure Group
Illustration 13: The Four Partitions for the Internet Sales Measure Group

2.  Right-click the Internet_Sales_2003 partition.

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

The Context Menu for a Selected Partition
Illustration 14: The Context Menu for a Selected Partition

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

3.  Click Properties 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.

The Aggregation Design ID Setting in the Partition Properties Dialog
Illustration 15: The Aggregation Design ID Setting in the Partition Properties Dialog ...

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

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

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

The Internet Sales Aggregation Design Appears
Illustration 16: The Internet Sales Aggregation Design Appears ...

5.  Right-click the Internet Sales aggregation design.

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

6.  Click Assign Aggregation Design on the context menu, as shown in Illustration 17.

Select Assign Aggregation Design
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.

The Assign Aggregation Design Dialog
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:

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

7.  Click Cancel to dismiss the Assign Aggregation Design dialog without making permanent changes.

8.  Return to the Partition Properties dialog for the Internet_Sales_2003 partition, where we left it on the General page.

9.  Click the Selection page in the Select a page pane on the left side of the Partition Properties dialog.

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

The Selection Page of the Partition Properties Dialog
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.

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

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers