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
.

1. 
Start Microsoft
SQL Server Management Studio
.

2. 
Select Analysis
Services
in the Server type selector of the Connect to Server
dialog that appears.

3. 
Type / select
the server name (server name / instance, if appropriate) in the Server name
selector.

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

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



Illustration 1:
Connecting to the Server …

5. 
Click the Connect
button to connect with the specified Analysis Server.

The Microsoft SQL
Server Management Studio
opens.

6. 
In the Object
Explorer
pane (it appears by default on the left side of the Studio),
expand the Databases folder (click the “+” sign to its immediate left),
appearing underneath the Analysis Server within which we are working.

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.

7. 
Right-click
the DBJ_Basic AS DB database, and select Rename from the context
menu that appears next, as shown in Illustration 2.



Illustration 2: Renaming
the Sample Database

8. 
Rename the Analysis
Services
database as follows:

ANSYS065_Basic AS DB

9. 
Exit SQL
Server Management Studio
when ready.

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.

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