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

Friday Dec 11th 2009 by William Pearson
Share:

BI Architect Bill Pearson continues his discussion of Partition planning for Analysis Services, this time focusing on the settings and properties involved from a SQL Server Management Studio perspective.

This article continues our introduction, begun in Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective), to partition planning for Analysis Services. Here, we will continue to discuss considerations involved in, and to emphasize the importance of, planning in partition design begun in Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective), this time focusing on the settings and properties involved from a SQL Server Management Studio perspective.

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 Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective), 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 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.

This article, another of several others in this subseries that explore various concepts surrounding partitions in Analysis Services, largely replicates the subject matter we covered within Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective). The primary difference lies in the perspective (this time, it will be that of SQL Server Management Studio) from which we examine the properties and settings in the amplification of the planning considerations that we cover. We will again 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 SQL Server Management Studio, where applicable, of a respective example resource assignments / settings 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 that partitions 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.

Note: If you took the following preparatory steps in completing the practice session of the previous article, Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective), of this series, you can access the objects created there, from SQL Server Management Studio this time, to complete the hands-on portions of the practice session in this article. If you are joining the series with this article, you will need to complete the preparatory sections that follow to gain the most benefit from the procedures we take within the subsequent practice session.

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.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2008 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.

Opening SQL Server Management Studio
Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name / instance, if appropriate) into the Server name selector.

6.  Supply authentication information, as required in your own environment.

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

The Connect to Server Dialog, with Representative Settings
Illustration 2: The Connect to Server Dialog, with Representative Settings

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  Within the Object Explorer (the leftmost pane of the Studio, by default), expand the server in which we are working, if necessary, by clicking the “+” sign to its immediate left.

9.  Expand the Databases folder that appears underneath the expanded server.

10.  Right-click the Adventure Works DW 2008 database.

11.  Select Back Up... from the context menu that appears, as shown in Illustration 3.

Right-click the Adventure Works DW 2008 Database – Select Back Up
Illustration 3: Right-click the Adventure Works DW 2008 Database – Select Back Up ...

The Backup Database – Adventure Works DW 2008 dialog appears.

12.  Replace the default name that appears in the Backup file box with the following:

ANSYS087_AS.abf

13.  Uncheck the Apply compression setting in the Options section.

14.  Uncheck the Encrypt backup file setting that immediately follows.

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

The Backup Database – Adventure Works DW 2008 Dialog Appears
p>Illustration 4: The Backup Database – Adventure Works DW 2008 Dialog Appears

15.  Click OK to begin the backup.

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.

16.  Within the Object Explorer, right-click the Databases folder underneath the Analysis Server name.

17.  Select Restore... from the context menu that appears, as shown in Illustration 5.

Right-click the Databases Folder – Select Restore
Illustration 5: Right-click the Databases Folder – Select Restore ...

The Restore Database dialog appears.

18.  Click the Browse button to the right of the box (first from the top, in the Restore Source section) labeled Backup file.

The Locate Database Files dialog appears.

19.  Navigate to the following backup file (where we located it in our backup steps above):

ANSYS087_AS.abf

20.  Click the file within the Select the file window, to place the file name into the File name box, at the bottom of the dialog, as depicted in Illustration 6.

Locate Database Files Dialog with Our Input
Illustration 6: Locate Database Files Dialog with Our Input ...

21.  Click OK to accept the file path / name, and to close the Locate Database Files dialog.

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

22.  Type the following into the Restore database box immediately above the Storage location box in the Restore Target section:

ANSYS087_AS

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

The Completed Restore Database Dialog
Illustration 7: The Completed Restore Database Dialog

23.  Click OK to initiate the restoration.

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.

24.  Within the Object Explorer, right-click the Databases folder underneath the Analysis Server name, once again.

25.  Select Refresh... from the context menu that appears, as depicted in Illustration 8.

Right-click the Databases Folder – Select Refresh
Illustration 8: Right-click the Databases Folder – Select Refresh ...

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

The New Database Appears
Illustration 9: The New Database Appears ...

Having finished the preceding steps, we are now ready to access the new Analysis Services database we have created within the SQL Server Management 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.

Note: We examined similar options available for resource assignment and partition settings from a Business Intelligence Development Studio perspective in Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective), as mentioned above. To see similar settings to those we examine below in Business Intelligence Development Studio, please see that article.

Let’s focus, at this point, upon 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:

26.  In the Object Explorer of the Management Studio, once again, expand the ANSYS087_AS database that we created earlier by clicking the “+” sign to its immediate left.

27.  In similar manner, expand the Cubes folder that appears underneath the newly expanded database.

Folders for the two cubes in the sample database, Adventure Works and Mined Customers (a data mining cube) appear, as depicted in Illustration 10.

The Folders for the Cubes of Our New Analysis Services Database Appear
Illustration 10: The Folders for the Cubes of Our New Analysis Services Database Appear ...

28.  Expand the Adventure Works cube.

The Measure Groups folder for the Adventure Works cube next appears. We recall that partitions, at least within our present context, are associated with measure groups. Hence it is easy to see why partition settings and properties are located where they are.

29.  Expand the Measure Groups folder.

The individual Measure Groups for the cube next appear, as shown in Illustration 11.

The Measure Groups for the Adventure Works Cube Appear
Illustration 11: The Measure Groups for the Adventure Works Cube Appear ...

30.  Expand the Internet Sales Measure Group, by clicking the “+” sign to its immediate left.

The Partitions, Writeback, and Aggregation Design folders appear, as depicted in Illustration 12.

The Partition, Writeback, and Aggregation Design Folders Appear
Illustration 12: The Partition, Writeback, and Aggregation Design Folders Appear ...

For purposes of this article, we will be working within the folders that appear above (most predominantly within the Partitions folder), 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.

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

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved