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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 11, 2009

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

By William Pearson

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date