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

Wednesday Nov 18th 2009 by William Pearson
Share:

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.

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 Database – Select Back Up
Illustration 3: Right-click the Adventure Works DW 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:

ANSYS086_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 Dialog Appears
Illustration 4: The Backup Database – Adventure Works DW 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):

ANSYS086_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:

ANSYS086_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 ANSYS086_AS database appear in the Object Browser, as shown in Illustration 9.

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

26.  Exit the SQL Server Management Studio when desired.

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:

1.  Click Start on the PC, once again.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

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

Launching SQL Server Business Intelligence Development Studio
Illustration 10: Launching SQL Server Business Intelligence Development Studio

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

3.  Close the Start page, if desired.

4.  Select File -> Open from the main menu.

5.  Click Analysis Services Database ... from the cascading menu, as shown in Illustration 11.

Opening an Analysis Services Database
Illustration 11: Opening an Analysis Services Database ...

The Connect to Database dialog appears.

6.  In the Server box atop the dialog, enter your Analysis Server name / instance.

7.  In the Database box just underneath the Server box, type or select the ANSYS086_AS database.

8.  In the bottom input box on the dialog, specify a location for the new solution we are creating (use the Browse button, if desired).

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

The Connect to Database Dialog, with Our Input
Illustration 12: The Connect to Database Dialog, with Our Input

9.  Click OK to connect to the specified Analysis Server.

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

The Database is Being Read from the Server
Illustration 13: The Database is Being Read from the Server ...

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).

10.  Click the Show All Files button, on the two-button toolbar atop the Solution Explorer, if necessary, to display all files / objects within the Solution Explorer, as partially depicted (Show All Files button circled) in Illustration 14.

The Cloned Analysis Services Database and Objects, within the Solution Explorer
Illustration 14: The Cloned Analysis Services Database and Objects, within the Solution Explorer (Partial View – “Show All Files” Button Circled)

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:

11.  Right-click the Adventure Works cube in the Solution Explorer (within the Cubes folder of the tree).

12.  Select Open from the context menu that appears, as shown in Illustration 15.

Open the Adventure Works Cube
Illustration 15: Open the Adventure Works Cube ...

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.

13.  Click the Partitions tab atop the Cube Designer, as depicted in Illustration 16.

Switching to the Partitions Tab
Illustration 16: Switching to the Partitions Tab ...

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.

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.

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.

1.  Click the Partitions tab atop the Cube Designer, as we did earlier.

The Partitions tab loads, once again.

2.  On the Partitions tab, ensure that the Internet Sales measure group (the top group in a list of eleven groups) is open, once again, by clicking the two upward pointing carets, as we did earlier, if necessary.

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

Source Column Setting, First Partition of the Internet Sales Measure Group
Illustration 23: Source Column Setting, First Partition of the Internet Sales Measure Group

3.  Click the top cell in the Source column, to give it the focus and enable the ellipses (“...”) button on its right, as shown in Illustration 24.

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

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

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

The Partition Source – Internet_Sales_2001 Dialog Appears
Illustration 25: The Partition Source – Internet_Sales_2001 Dialog Appears

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.

5.  Click OK to close the Partition Source – Internet_Sales_2001 dialog.

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.

1.  Click the Internet_Sales_2003 partition, within the Internet Sales measure group pane, to select it.

2.  Click the Storage Settings link, just underneath the Internet Sales measure group pane, as shown in Illustration 26.

Click the Storage Settings Link
Illustration 26: Click the Storage Settings Link

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

The Partition Storage Settings – Internet_Sales_2003 Dialog Appears
Illustration 27: The Partition Storage Settings – Internet_Sales_2003 Dialog Appears

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.

Setting

Description

Real-time ROLAP

Select to use the following storage and proactive caching settings:

  • ROLAP storage mode
  • Enables proactive caching
  • Drops outdated cache, with a latency period of 0 seconds
  • Brings object online immediately

Real-time HOLAP

Select to use the following storage and proactive caching settings:

  • HOLAP storage mode
  • Enables proactive caching
  • Drops outdated cache, with a latency period of 0 seconds
  • Updates cache when data changes, with a silence interval of 0 seconds and no silence override interval
  • Brings object online immediately

Low-latency MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode
  • Enables proactive caching
  • Drops outdated cache, with a latency period of 30 minutes
  • Updates cache when data changes, with a silence interval of 10 seconds and a silence override interval of 10 minutes
  • Brings object online immediately

Medium-latency MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode
  • Enables proactive caching
  • Drops outdated cache, with a latency period of 4 hours
  • Updates cache when data changes, with a silence interval of 10 seconds and a silence override interval of 10 minutes
  • Brings object online immediately

Automatic MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode
  • Enables proactive caching
  • Updates cache when data changes, with a silence interval of 0 seconds and no silence override interval

Scheduled MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode
  • Enable proactive caching
  • Updates cache periodically, with a rebuild interval of 1 day

MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode

Custom setting

Select to explicitly set storage mode, proactive caching, and notification options.

Table 1: Standard Storage Settings and Descriptions

3.  Click the radio button to the immediate left of the Custom setting label in the lower portion of the Partition Storage Settings – Internet_Sales_2003 dialog.

4.  Click the Options button to the right of the Custom setting radio button, as shown in Illustration 28.

Click the Options Button
Illustration 26: Click the Options Button ...

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

The Storage Options Dialog  - General Tab Appears
Illustration 29: The Storage Options Dialog - General Tab Appears ...

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.

5.  Click the Notifications tab on the Storage Options dialog box.

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

The Storage Options Dialog – Notifications Tab Appears
Illustration 30: The Storage Options Dialog – Notifications Tab Appears ...

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.

6.  Click OK to return to the Partition Storage Settings – Internet_Sales_2003 dialog.

7.  Click Cancel to close the Partition Storage Settings – Internet_Sales_2003 dialog without saving any changes, and to return to the Partitions tab of the Cube Designer.

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.

1.  Right-click the Storage Location setting for the Internet_Sales_2003 partition (currently indicating Default).

2.  Select Properties from the context menu that appears, as depicted in Illustration 31.

Selecting Properties for the Internet_Sales_2003 Partition
Illustration 31: Selecting Properties for the Internet_Sales_2003 Partition

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.

Properties Settings for the Internet_Sales_2003 Partition
Illustration 32: Properties Settings for the Internet_Sales_2003 Partition

3.  Scroll down in the Properties settings to the StorageLocation setting.

4.  Click the label of the StorageLocation setting, to enable the ellipses (“...”) button on its immediate right, as depicted in Illustration 33.

Enable the Ellipses (“...”) Button
Illustration 33: Enable the Ellipses (“...”) Button ...

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

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

Partition Type Selection Dialog for the Internet_Sales_2003 Partition
Illustration 34: Partition Type Selection Dialog for the Internet_Sales_2003 Partition

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.

Setting

Description

 

 

Processing Location Options

 

Current server instance

Makes the current Analysis Services instance responsible for processing the partition.

Remote Analysis Services data source

Makes a remote Analysis Services instance responsible for processing this partition.

(From the dropdown list, we can select the data source representing the remote Analysis Services instance that will be responsible for processing the partition.)

New

Creates a new data source representing the remote Analysis Services instance responsible for processing the partition.

Storage Location Options

Default server location

Makes the data folder of the current Analysis Services instance the storage location of the aggregation and indexing data for the partition.

Specified folder

Specifies the storage location of the aggregation and indexing data for the partition.

The ellipses button (“...”) to the right of this setting displays the Browse for Remote Folder dialog box in which we can select a folder for Specified folder.

Table 2: Processing and Storage Locations Settings and Descriptions

6.  Click Cancel to close the Partition Type Selection dialog without saving any changes, and to return, once again, to the Partitions tab of the Cube Designer.

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.

1.  Ensure that the Internet_Sales_2003 partition of the Internet Sales measure group is selected, once again, on the Partitions tab.

2.  Select Cube -> Storage Settings ... from the menu atop the Partitions tab, as depicted in Illustration 35.

Select Cube -> Storage Settings ... from the Menu atop the Partitions Tab
Illustration 35: Select Cube -> Storage Settings ... from the Menu atop the Partitions Tab

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.

3.  Click Cancel to dismiss the dialog.

4.  Exit Business Intelligence Development Studio, when desired.

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

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