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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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. Lets 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.
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.
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, lets
take a look at the Internet Sales measure group within the Cube Designer of the
Business Intelligence Development Studio. From the Partitions tab, we use the Measure Groups pane to manage the partitions
associated with each measure group in the cube.
1.
On the Partitions
tab, ensure that the Internet Sales measure group (the top group in a list of
eleven groups), which indicates the presence of four (4) partitions, is open by
clicking the two upward pointing carets (if the measure group is not already
open by default), as shown in Illustration 17.
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.
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. Lets take a look at the aggregation design settings themselves,
by accessing the Aggregations tab from our current position.
2.
Click the Aggregations
tab, as shown in Illustration 19.
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. Lets open this measure group to see the assigned aggregation
design.
3.
Expand Internet
Sales by clicking the + sign to its immediate left, as depicted in Illustration
20.
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.
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.
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
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.
Illustration 24: Enabling the Ellipses (...) Button ...
4.
Click the ellipses (...) button.
The
Partition Source Internet_Sales_2001 dialog appears, as depicted in Illustration
25.
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, lets 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.
Illustration 26: Click the Storage Settings Link
The
Partition Storage Settings Internet_Sales_2003 dialog appears, as depicted in
Illustration 27.
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:
|
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.
Illustration 26: Click the Options Button ...
The
two-tabbed Storage Options dialog appears, defaulted to the General tab, as
depicted in Illustration 29.
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.
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.
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.
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.
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.
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.
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