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

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.

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. Let’s take a look at the aggregation design settings themselves, by accessing the Aggregations tab from our current position.

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.

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.

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.

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.