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

BI Architect Bill Pearson continues his introduction to Partitions in Analysis Services 2008 with a discussion of Partition planning, providing hands-on exposure to settings related to each potential consideration.

This article introduces partition planning for Analysis Services. Here, we will follow upon our introduction to partitions in Analysis Services, and discuss the importance of planning in partition design.

Note: For more information about my MSSQL Server Analysis Services column in general, see the section entitled “About the MSSQL Server Analysis Services Series” that follows the conclusion of this article.

Introduction

In Dimensional Model Components: Dimensions Parts I and II, we undertook a general introduction to the dimensional model, noting its wide acceptance as the preferred structure for presenting quantitative and other organizational data to information consumers. As a part of our extended examination of dimensions, we discussed the primary objectives of business intelligence, including its capacity to support:

• the presentation of relevant and accurate information representing business operations and events;
• the rapid and accurate return of query results;
• “slice and dice” query creation and modification;
• an environment wherein information consumers can pose questions quickly and easily, and achieve rapid results datasets.

We noted in Cube Storage: Introduction that the second objective above, the capacity of business intelligence to support “the rapid and accurate return of query results”, translates to minimal querying time. We discussed that storage design plays a key role in enhancing query performance across our cubes, and learned, in Cube Storage: Introduction to Partitions, that partitions play a significant role in the way that Analysis Services manages and stores data and aggregations for a measure group in a cube.

In this article, we will continue the general exploration of cube storage that we began in Cube Storage: Introduction, this time focusing upon partitions, which we initially introduced in Cube Storage: Introduction to Partitions. This article is one of several others in this subseries that explore various concepts surrounding partitions in Analysis Services. Here we will discuss partition planning considerations and how they are important to partition design within our business intelligence solutions, touching upon points that include the following:

• A discussion of partition planning in general in Analysis Services;
• A discussion of each of several individual resource assignment and settings considerations in planning Partition design within Analysis Services;
• A hands-on examination, from within the Cube Designer of the Business Intelligence Development Studio, where applicable, of a respective example resource assignment / setting from the Adventure Works sample cube, for each of the resource assignment and settings considerations we discuss.

Planning Partitions in Analysis Services

As we noted in Cube Storage: Introduction to Partitions, as well as within other articles of our series, a partition is a physical file on a hard disk that contains a subset of the data included in an Analysis Services database. Analysis Services uses partitions to manage and store data and aggregations for a measure group in a cube. A measure group can have one or more partitions, and, when large data volumes are concerned, partitions afford us a means of scaling the Unified Dimension Model (UDM) we have designed to support our Analysis Services database.

When designing partitions, it is important to keep in mind that the whole objective is to support good query and processing performance for large Analysis Services measure groups. Partitioning can be vital when we anticipate large measure groups in our design environment, because the boosted query performance they support can make the difference in a mediocre solution and a solution that makes robust and satisfying analysis and reporting available to the information consumers of our clients and employers. The boost in performance is largely possible because the Analysis Server is capable of selecting and querying among the partitions that are available: that is, Analysis Services can select a single partition to meet the demands of a given query, and return results more rapidly because a (sometimes dramatically) smaller dataset is targeted and physically accessed.

We mentioned other ways partitioning can help us to manage large Analysis Services databases in other articles of this subseries, as well. Among these, we noted that small volumes of fact data (say that of a day or a week) can be added to a small portion of a cube (a partition) faster than we can incrementally add the same, small amount of data into a single, much larger partition that might contain years of history. Planning for the presence of a relatively small partition to contain current data can give us more options for supporting the delivery of real-time data, in many cases.

Recall that the primary reason for partitioning in the first place is to minimize the amount of time required to process a cube; partitioning makes this possible, as individual partitions can be processed independently of each other. Because we can process partitions independently, we can process our current data, which is subject to ongoing change, more frequently (say, to add small time frames incrementally), while we are afforded the option to process seldom changing historical data (say data belonging to years before the prior year) less frequently. Moreover, parallel processing of individual partitions can mean added efficiencies, for obvious reasons.

We will get some hands-on exposure to various partition settings in the practice session below. Before we get started discussing various partition planning criteria, and then taking a brief look at some sample resource assignments / settings involved with each, we will need to prepare the local environment for the practice session. We will take steps to accomplish this within the section that follows.

Preparation: Create a Sample Basic UDM within Which to Perform the Steps of Our Practice Session

Create a Clone Analysis Services Database in SQL Server Management Studio

We will begin our preparation within SQL Server Management Studio, where we will create a clone of the sample Adventure Works DW 2008 database, which can be installed by anyone installing 2008 Analysis Services.

The Connect to Server dialog appears.

The Connect to Server dialog appears, with the appropriate input for our local environments, similar to that depicted in Illustration 2.

The SQL Server Management Studio opens.

The Backup Database – Adventure Works DW 2008 dialog appears.

ANSYS086_AS.abf

The Backup Database – Adventure Works DW 2008 dialog appears, as depicted in Illustration 4.

The Backup Database – Adventure Works DW 2008 dialog grays, as the Executing symbol in the Progress pane (lower left corner of the dialog) becomes active. The process may run several minutes depending upon the resources available on the local PC. Once completed, the dialog closes, returning us to the Management Studio.

We will next restore the same backup under the new name we have given it, to create a copy of the existing sample database – a copy wherein we can make modifications without impairing the existing sample, which we may wish to use to complete tutorials included with MSSQL Server 2008 or elsewhere.

The Restore Database dialog appears.

The Locate Database Files dialog appears.

ANSYS086_AS.abf

We return to the Restore Database dialog, where we see the file we have selected appear in the Backup file box.

ANSYS086_AS

The Restore Database dialog, with our input, appears as shown in Illustration 7.

The Restore Database dialog grays, as the Executing symbol in the Progress pane, once again, becomes active. The process runs, and, once completed, the dialog closes, returning us to the Management Studio.

We see the new ANSYS086_AS database appear in the Object Browser, as shown in Illustration 9.

Having created the new ANSYS086_AS database, we can now proceed with the creation of a new Analysis Server project within Business Intelligence Development Studio, where we can get some hands-on exposure to partition resource assignment and settings options.

Create a New Analysis Services Project within Business Intelligence Development Studio

As most regular readers of my MSSQL Server Analysis Services column already know, the articles of this series depend heavily upon working within the Unified Dimensional Model (“UDM”), during hands-on practice sessions, as a means of employing the components of the integrated Microsoft business intelligence solution in a meaningful fashion – and in a manner such that what we learn can be extrapolated to the needs of our respective business environments. To provide an easy means of creating a relatively rich practice environment, we will “import” the design of our cloned Analysis Services database, ANSYS086_AS, into a project that we will create for that purpose within the Business Intelligence Development Studio. We will accomplish this by taking the following steps:

The equivalent on my PC appears as depicted in Illustration 10.

We briefly see a splash page that lists the components installed on the PC, and then Microsoft Visual Studio opens at the Start page.

The Connect to Database dialog appears.

The Connect to Database dialog appears, with our input, similar to that depicted in Illustration 12.

A “Reading database from the server ...” message briefly appears, as shown in Illustration 13.

Next, we see the ANSYS086_AS database, together with all its member folders and objects, appear within the Solution Explorer (which is positioned, by default, in the upper right corner of the design environment).

With a complete Analysis Services project in place, we have established development access to our sample database, containing a cube. This affords us an environment from which to perform the exercises of the following practice session, without having to build all the objects we might need from scratch.

We are now ready to access the new Analysis Services database we have created within the Business Intelligence Development Studio. Here, we are positioned to take a look at some of the options available for resource assignment and partition settings, which we will examine as a part of our discussion surrounding partition planning. Let’s open the Adventure Works cube, within the clone Analysis Services database we have created, and begin setting ourselves up for our review in the next section with the following steps:

The Cube Designer opens, defaulted to the Cube Structure tab. For purposes of this article, we will be working within the Partitions tab, where partitions can be conveniently constructed and maintained. Here we can perform settings and assignments related to storage, proactive caching, writeback, and more for each measure group within the cube, as we shall see.

The Partitions tab loads.

We are now positioned to begin our discussion of partition planning, and our examination of some of the resource assignments and settings upon which our partition planning efforts can be based.

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.

Partition Source and Fact Table Assignment

Individual partitions can be sourced from different physical locations, as we have already discussed, and as we shall see later in this practice session and elsewhere in this subseries. A planning effort, therefore, might include consideration of partition sources. We have already seen a couple of places where we can examine the partition source(s) for a pre-existing measure group.

Moreover, because any individual partition can be supported by a different fact table (within the same database / schema), fact table assignment can be a valid partition planning criterion.

The Partitions tab loads, once again.

We again see the grid containing the list of partitions that support the selected measure group. Our focus here is the Source column, which, for the first listed partition of the Internet Sales measure group, we see circled in red in Illustration 23

The Partition Source – Internet_Sales_2001 dialog appears, as depicted in Illustration 25.

Here we see the targeted Data source, the Adventure Works DW relational database, and the query (including fact table) that we apply against it to define the first of four partitions. We also note the warning here, that our query needs to exclude any data included in other partitions (to avoid double counting).

When we consider Partition Source as a criterion in partition planning, we should also consider the “filtering” of that same source for various elements. Time is a rather obvious dimensional basis for partitioning, an illustration of which we see in this example (within the WHERE clause of the Data source query), where data with an Order Date earlier or equal to December 31, 2001 is entrained into this partition.

Storage Type Settings

We discussed the three types (MOLAP, HOLAP, and ROLAP) of storage in Cube Storage: Introduction. Storage type represents another criterion upon which to plan partitions, as we can assign any individual partition the type of storage that we deem most appropriate. For example, we might consider assigning a ROLAP storage type to a partition containing rarely queried, historical data (and thus leave the storage burden largely to the underlying relational database); in most cases, by contrast, we would assign the MOLAP storage type to a partition upon which we expected heavy query demands, such as a partition containing current / recent periods’ data.

The Storage Settings link on the Partitions tab allows us to access the storage type settings for whichever partition we have highlighted. To illustrate the context-sensitive nature of the link, and to understand the settings it makes available to us, let’s take the following steps from the Partitions tab.

The Partition Storage Settings – Internet_Sales_2003 dialog appears, as depicted in Illustration 27.

We can use the Storage Settings dialog box in Business Intelligence Development Studio to set the proactive caching, storage, and notification settings for a dimension, cube, measure group, or partition. We can set storage type through the use of the setting slider that we see here. By clicking the radio button labeled Standard setting, we enable the slider with its predefined settings for storage mode and proactive caching features. The predefined settings, along with descriptions, appear in Table 1.

The two-tabbed Storage Options dialog appears, defaulted to the General tab, as depicted in Illustration 29.

We can use the General tab of the Storage Options dialog box in Business Intelligence Development Studio to set the storage mode and proactive caching settings for a dimension, cube, measure group, or partition. We discuss these settings in several independent articles of this series.

The Storage Options dialog appears, with the Notifications tab selected, as shown in Illustration 30.

We can use the Notifications tab of the Storage Options dialog box to set the notification method and related settings for a dimension, cube, measure group, or partition. Again, we discuss these settings in several independent articles of this series.

Storage Location Assignment

Whether we choose remote or local partitioning, location of the partition storage is yet another criterion upon which to plan our partitions. We might, for example, plan to create a partition with local storage for current data that we might expect to query often; in contrast, we might plan to create a remote partition for the historical data of previous years, upon which we might expect only minimal need to perform queries.

We noted earlier that we can design storage for a cube on the Partitions tab in Cube Designer. Moreover, we noted that we can configure storage and design aggregations separately for each partition in a measure group. We can also set default storage settings for any measure group. We can see sample Storage Location settings for a partition of the Internet Sales measure group by taking the following steps.

The Properties settings for the Internet_Sales_2003 partition appear underneath the Object Explorer, by default, in the lower right corner of the development environment, as shown in Illustration 32.

The Partition Type Selection dialog for the Internet_Sales_2003 partition appears, as shown in Illustration 34.

We typically specify the Microsoft SQL Server Analysis Services instance of the cube that owns the partition, as well as the Analysis Services instance that stores the data for the partition on the Processing and Storage Locations page of the Partition Wizard. (We get hands-on exposure to the Partition Wizard in independent articles of this subseries, where we see that we can define a partition as a remote partition by specifying either a remote Analysis Services instance or a storage location other than the default storage location, among other possibilities.)

The Processing and Storage Location settings on the Partition Type Selection dialog can serve as potential bases for partition planning, in general. The settings, along with a description of each, appear in Table 2.

Proactive Caching Settings

As we have noted in other articles within this series, when we use MOLAP or HOLAP storage, the data in the affected cube becomes stale the second the relational data source changes.  Proactive Caching is a feature in Analysis Services that allows us to specify when to process a measure group partition or a dimension as the data in the relational data source changes.  When Proactive Caching is implemented, Analysis Services handles maintaining updates on its own, based upon parameters we specify.  A common scenario for Proactive Caching would include a situation where we have a measure group partition where the latest data is added periodically, and then at some point the partition is merged with an existing partition.  The data for our partition is often determined based upon a transaction date.  For instance, we might want the partition updated hourly during the day with new transactions, and then at night we might merge the contents of the partition into a partition for the current week.  This approach would enable us to process the partition often, keeping it synchronized often with the relational data source, while also minimizing the amount of data that needs to be processed each cycle. 

Because Proactive Caching is established at the partition level, the need to provide this update capability is a partition planning criterion. We might, for example, choose to provide a partition for the data of the current year, wherein we might implement Proactive Caching, while we might create a partition, sans Proactive Caching, for historical data.

We can access Proactive Caching settings within the Business Intelligence Development Studio through any of the following means:

• Clicking the ellipsis button (...) for the ProactiveCaching property value of a dimension, cube, measure group, or partition in the respective Properties window of Business Intelligence Development Studio;
• Expanding a measure group in the Partitions tab of Cube Designer and clicking Storage Settings;
• Expanding a measure group and selecting a partition in the grid for that measure group in the Partitions tab of Cube Designer and clicking Storage Settings;
• Expanding a measure group and selecting a partition in the grid for that measure group in the Partitions tab of Cube Designer and clicking Storage settings on the Toolbar of the Partitions tab of Cube Designer.

We return to the Partition Storage Settings – Internet_Sales_2003 dialog that we encountered in an earlier section above. As we mentioned there, we can use Settings here to specify the proactive caching, storage, and notification settings for a dimension, cube, measure group, or partition. We can set storage type through the use of the setting slider that we see by clicking the radio button labeled Standard setting, and enabling the slider with its predefined settings for storage mode and proactive caching features. The predefined settings, along with descriptions, appear in Table 1 above.

Conclusion

In this article, we continued the general exploration of cube storage that we began in Cube Storage: Introduction, this time 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 discussing partition planning in general in Analysis Services, we explored each of several individual resource assignment and settings considerations in planning partition design within Analysis Services. Finally, for each of the resource assignment and settings considerations we discussed, we examined a respective example resource assignment / setting within the Adventure Works sample cube, from the perspective of the Cube Designer of the Business Intelligence Development Studio, where applicable.

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