Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation

About the Series …

This
is the twenty-fourth article of the series, Introduction to MSSQL Server
2000 Analysis Services. As I stated in the first article, Creating Our First
Cube, the
primary focus of this series is an introduction to the practical creation and
manipulation of multidimensional OLAP cubes. The series is designed to provide
hands-on application of the fundamentals of MS SQL Server 2000 Analysis
Services, with each installment progressively adding features and techniques designed
to meet specific real-world needs. For more information on the series, as well
as the hardware / software requirements to prepare for the exercises we
will undertake, please see my initial article, Creating Our First
Cube.

Introduction

In our
last article, Introduction
to Local Cubes,
we ventured beyond earlier topics surrounding the retrieval and reporting of
data from a server-based MSAS cube, and transitioned into the realm of
remote, independent OLAP data source design and creation. We explored
approaches to creating local cubes within MS Office, discussing
many of the foundational concepts behind the architecture of multidimensional
data sources, and their creation from an integrated MS Office client
application, Excel. As a part of a hands-on practice exercise, we then
created a local cube from an existing Excel PivotTable report,
sourced initially from an MSAS server-based cube.

We
explored many practical aspects of putting the functionality to work
immediately, discussing ways that local (or "offline") cubes
can meet the business requirements of distributed information consumers, and
add value to the organization in general. Throughout the hands-on practice
exercise we performed, in creating a local cube from an existing server-based
cube, we commented upon the results we obtained, to reinforce our understanding
of the concepts involved.

In
this article, we will explore a second approach to the creation of a local
cube. While we will rely again upon the PivotTable report as our
design and development tool, this time we will focus more on the use of Microsoft
Query ("MS Query"), and begin with a relational
database instead of an MSAS server-based cube. We will discuss
advantages in taking this approach and situations for which it is especially
useful. As with the prior article, Introduction to Local Cubes, the intent of this article is to offer options for more
independence from the perspective of the information consumer, as well as to
make the fruits of MSAS OLAP available to enterprise team members
through the conduits of the applications that are pervasive in the desktop
population we find in business today.

In
this lesson, we will:

• Discuss the
  creation of a local cube from a relational data source;

• Discuss
  scenarios where starting with a relational source might be advantageous;

• Discuss how
  the creation of a local cube from a relational data source can be
  used to complement an MSAS implementation;

• Derive a subset
  of relational data as the basis of our local cube with the Query Wizard;

• Introduce the OLAP
  Cube Wizard, and complete design of our local cube;

• Discuss the
  results obtained through the various steps of the cube development process in
  our practice exercises.