Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective) - Page 4
November 18, 2009
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.)
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, lets 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.
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.
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. Lets 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.
The Aggregations tab displays the Aggregations that exist within our cube. The top item in the list, Internet Sales, is our focus at present. Lets 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.
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.
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.
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.