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 Nov 18, 2009

Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective) - Page 4

By William Pearson

Partition Planning: Based Upon Resource Assignments and Settings

With Analysis Services, 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 the sample UDM. (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. Planning partitions around the degree of aggregation, based upon the expected degree of querying, is one of the most common 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 Cube Designer of the Business Intelligence Development Studio. From the Partitions tab, we use the Measure Groups pane to manage the partitions associated with each measure group in the cube.

1.  On the Partitions tab, ensure that the Internet Sales measure group (the top group in a list of eleven groups), which indicates the presence of four (4) partitions, is open by clicking the two upward pointing carets (if the measure group is not already open by default), as shown in Illustration 17.

Open the Internet Sales Measure Group, if Necessary
Illustration 17: Open the Internet Sales Measure Group, if Necessary ...

The Measure Groups pane displays a grid containing the list of partitions that support the selected measure group. Partition settings for the Internet Sales measure group appear, as depicted in Illustration 18.

Settings for the Internet Sales Measure Group Appear
Illustration 18: Settings for the Internet Sales Measure Group Appear

Of specific interest to us within this subsection is the Aggregation column of the Measure Groups pane. The Aggregation Design column references the aggregation design established for the partition. For the first partition supporting the Internet Sales measure group, we see that that the Internet Sales aggregation design is referenced. Let’s take a look at the aggregation design settings themselves, by accessing the Aggregations tab from our current position.

2.  Click the Aggregations tab, as shown in Illustration 19.

Transiting to the Referenced Aggregation Design
Illustration 19: Transiting to the Referenced Aggregation Design ...

The Aggregations tab displays the Aggregations that exist within our cube. The top item in the list, Internet Sales, is our focus at present. Let’s open this measure group to see the assigned aggregation design.

3.  Expand Internet Sales by clicking the “+” sign to its immediate left, as depicted in Illustration 20.

Expand Internet Sales
Illustration 20: Expand Internet Sales ...

The expanded pane within the Aggregations tab displays settings and characteristics relevant to the Internet Sales measure group. We can see that there are 54 aggregations, and an estimated partition size of 32,265. We can see further information about the partitions from the rightmost Partitions column.

4.  Click the cell in the Partitions column, to give it the focus and enable the ellipses (“...”) button on its right, as shown in Illustration 21.

Enabling the Ellipses (“...”) Button
Illustration 21: Enabling the Ellipses (“...”) Button ...

5.  Click the ellipses (“...”) button.

6.  The Assign Aggregation Design dialog appears where we see the Internet Sales design selected at the top, as depicted in Illustration 22.

The Assign Aggregation Design Dialog
Illustration 20: The Assign Aggregation Design Dialog

Here can see the assignment of the aggregation designs to one or more destination partitions (four partitions, in the present case). Between the name of each partition and the name of the aggregation design assigned to it, we can see the source table or query for the partition, as we can elsewhere.

7.  Click OK to dismiss the Assign Aggregation Design dialog, and to return to the Aggregations tab in the Cube Designer.

And so we see the settings that exist 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.

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