Cube Storage: Planning Partitions 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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles