Dimensional Model Components: Dimensions Part I

About the Series …

This
article is a member of the series Introduction to MSSQL Server Analysis Services. The series 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. For the
software components,
samples and tools needed to complete the hands-on portions of this article, see
Usage-Based
Optimization in Analysis Services 2005, another article within this
series.

Introduction

The
general consensus within current data warehousing and business intelligence
communities is that the preferred structure for presenting quantitative and
other organizational data to information consumers lies within the dimensional
model. The dimensional model has consistently been shown to meet
the primary objectives of business intelligence: to present relevant and
accurate information representing business operations and events, and to return
query results rapidly and accurately. The dimensional model is
particularly optimal in its support of “slice and dice” query creation and
modification; it supports an environment wherein information consumers can pose
quick and easy questions, and achieve rapid results datasets, using various
perspectives called dimensions.

In this, Part
I of a two-part article, the first of several articles focusing upon dimensional
model component structures, we will gain an introduction, with hands-on
exposure, to dimensions within a sample cube. Our examination will
include:

• An
  introduction to the Dimensional Model and a discussion of its role in
  meeting the primary objectives of business intelligence.
• An
  introduction to Dimensions from a general perspective, including the two
  primary dimension types within Analysis Services.
• An examination
  of the Database Dimensions and the Cube Dimensions that exist
  within our sample UDM, together with a discussion surrounding the
  differences between the two dimension types.
• Coverage of
  the general concepts and properties (including what they define and support,
  and how we can manage them) underpinning Database Dimensions.
• A review of
  the Properties associated with a Database Dimension, based upon
  the examination of a representative dimension within our sample UDM.
• A look ahead
  to Part II of this article, where we explore the Properties
  associated with a representative Cube Dimension.

Dimensions in Analysis Services

Dimensions form the foundation of the dimensional
model. They represent the perspectives of a business or other
operation, and reflect the intuitive ways that information consumers need to
query and view data. To use an analogy, if we consider the facts or measures
contained within our business intelligence system as verbs (or actions /
transactions undertaken by the business), then the dimensions might be
considered as nouns that take part in, or are otherwise associated with,
those verbs. Common examples of dimensions include time (or
date), customer, product, geography, lab type, campus, patient, promotions,
gender (and other demographics), and so forth. Each dimension is
associated with the facts / measures to which it relates via the linkages /
joins between the table(s) housing the dimension (the dimension
table) and the fact table. As in illustration (found within the Adventure
Works sample environment), the link / join between the DimCustomer table
and the FactInternetSales table reflects that the Customer dimension
participates in sales, order quantities, discounts, freight, and various other
measures.

Because we
typically support each dimension with an underlying table(s) within a
database, we commonly see a complete, discrete list of dimension members
within each table. (Within each single member row are also included various attributes
that describe, classify, and otherwise act as adjectives to the dimension
noun. We will examine dimension attributes in subsequent articles of
this subseries and elsewhere within my Introduction to MSSQL Server Analysis Services, and other Database Journal,
series’.)

We can
identify dimensions as a part of business requirements gathering when we
discuss the perspectives by which information consumers within the
organization wish to be able to analyze, or report upon, the activities,
results, and other facets of business operation. As illustration, we might
determine early in our requirements gathering efforts that information consumers
need to see Internet Sales by geography, by month, by product, or by a
combination of these perspectives, among others. Such perspectives need to be
included within our design, within the appropriate dimension tables (or
snowflake tables linked to those tables, as appropriate).

We will get some hands-on exposure to dimensions in
the practice session below. Before we get started working within a sample cube clone,
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 Sample Primary Analysis
Services UDM for the Practice Exercise

Before getting started with our
practice session, we will need a basic sample Analysis Services UDM
(containing a cube and other objects) with which to work. To quickly create a
copy of a basic Analysis Services sample project, from which an Analysis
Services database can be quickly deployed, please perform the steps of the
following procedure, located in the References section of my articles
index:

Once you have created the new
sample project, you should ascertain connectivity to the underlying
relational data source (particularly if you are running
“side-by-side” installations of MSSQL Server 2000 and MSSQL
Server 2005, but it is important to check even if not). You can do this by performing
the steps of the following procedure, also located in the References
section of my articles index:

Once you have created the new
sample project, and ascertained connectivity, deploy it to the Analysis
Server by performing the steps of the
following procedure, located in the References section of my articles
index (substitute object names for those appropriate to your own environment,
when following the procedure):

Ascertain that the New Practice Database is in Place and Rename It

We are now ready to ascertain that
the new Analysis Services database is in place, as well as to rename it
to keep it safe from writeover in future exercises within the series. (You
can, of course, always delete the practice database if you do not choose to
keep it for future reference, etc.) To do this, we will work from within the Microsoft
SQL Server Management Studio.

The Connect to Server
dialog appears similar to that depicted in Illustration 1.

The Microsoft SQL
Server Management Studio opens.

The Databases folder
opens, exposing the detected Analysis Services database(s). Our new Analysis
Services database, DBJ_Basic AS DB, should appear among the other
databases.

ANSYS065_Basic AS DB

Having ensured that the
new database is in place, and having renamed it, we are ready to begin the
procedural part of our practice session.