Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005

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, 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.

Introduction

One of
the first tasks that awaits many of us, as Analysis Services
administrators, architects, and / or consultants that work with client and
employer data in Analysis Services 2000 cubes, is to provide guidance
and support in the migration of the databases that house them to MSSQL
Server 2005 Analysis Services. While the steps of the process themselves
are straightforward, the new environment will seem bewildering to many –
particularly challenging in the numerous paradigm shifts and additional options
that have come along, and which combine as components of a new MSSQL Server
to help this new powerhouse to merit well its being proclaimed the "BI
Release."

Migrating
any but the most simple Analysis Services 2000 databases and components
will require consideration of the new features, as well as whether old features
upon which the 2000 paradigm rested have been absorbed into new
structures, done away with entirely, or which otherwise will simply not
upgrade. The Unified Dimensional Modeling paradigm of Analysis
Services 2005 offers a design environment where the OLAP and relational
reporting environments are centralized. Making optimal use of this environment
means managing fundamental changes in the structures of our cubes and other
Analysis Services structures, changes in the MDX language, and a host of other
new and enhanced attributes that arrive with the new release.

While
certainly not a substitute for some serious study of the Books Online
and other documentation that accompanies the release of MSSQL Server 2005,
a quick way to gain some exposure to exactly the changes that will impact our Analysis
Services databases, cubes, dimensions and so forth, might be to migrate
copies of our cubes. I began doing this in early beta as a means of
ascertaining the readiness of "early adopter" clients to advance with
the beta. In some cases, the process brought considerations to light that
allowed us to focus planning of the upcoming migrations, including an in-depth
study of the existing, underlying relational structures, for enhancements that
we might leverage via the new database engine considerations emerging within MSSQL
Server 2005, as well as numerous other considerations whereby we might
prepare to take advantage of the dramatically more integrated Microsoft
business intelligence solution as a whole, including the relational database
engine, Analysis Services, and Reporting Services.

In
several scenarios, we found that we were able to at least "parallel" Analysis
Services 2000 and Reporting Services 2000 capabilities in the
2005 environment, clearing the way for rapid migration of production and
affording the enterprises the opportunity to study and test new capabilities
early enough to incorporate design enhancements and other architectural
improvements into the mix before cutover. The rapid and easy migration of Analysis
Services databases offered a seamless way to look forward to working
features and capabilities, which, in conjunction with the appropriate review of
the documentation that is available, and the crafting of a plan to assimilate
changes that have occurred in the component 2005 applications into our
own Analysis Services objects, provides a path to rapid upgrade of existing Analysis
Services implementations and the solutions they support.

In this article, we will:

• Briefly
  discuss approaches available for migrating Analysis Services 2000
  databases to Analysis Services 2005;

• Introduce the Migration
  Wizard and discuss its general operation with surrounding considerations;

• Prepare to use
  the Migration Wizard within a hands-on practice exercise, from inside SQL
  Server Management Studio;

• Migrate an Analysis
  Services 2000 sample database to Analysis Services 2005;

• Verify the
  results of our practice migration through a cursory examination of the migrated
  database within SQL Server Management Studio;

• Verify the
  results of our practice migration through a cursory examination of the migrated
  database within SQL Server Business Intelligence Development Studio.

Migrating an Analysis Services 2000 Database

Overview and Discussion

From
the perspective of MSSQL Server 2000 Analysis Services, practitioners
have three general approaches to consider in the installation of Microsoft SQL Server 2005 Analysis
Services and the
migration of Analysis Services 2000 databases. The options, with brief
descriptions, appear in Table 1.

As will
be apparent to many of us immediately, the flexibility in the installation and
migration / upgrade processes means many possible combinations of MSSQL
Server 2000 / Analysis Services 2000 / Reporting Services 2000 and
their 2005 counterparts, providing a host of variable opportunities for
staging partial and full cutovers, simultaneous operations of different
versions within other strategies, and so forth. Once the Analysis Services
2005 databases are created, they must be processed (against the original
data source) to populate their structures with the underlying data, a
prerequisite to our being able to query them, as we might expect.

We can
easily verify the process for, and review the results of, our upgrade
procedures within the SQL Server Management Studio, as we shall see in
our practice exercise that follows. Once we have ascertained that our
databases are fully functional and that Analysis Services 2000 is no
longer needed, we can simply uninstall the application. Having removed Analysis
Services 2000, we can, moreover, rename our newly installed Analysis
Services 2005 named instance as the default instance, if that is
desirable.

Considerations and Comments

For purposes of the
practice exercises within this series, we will be working with samples that are
provided with MSSQL Server 2000. These samples consist of the FoodMart
2000 Analysis Services database, properly installed within Analysis
Services 2000, as it installs along with the application from the CD. The
samples are easily restored within Analysis Services from the .cab files, which
can be obtained from the CD, the Microsoft site, and perhaps other locations if
they have been removed from, or were never installed within, our existing
environments.

A side-by-side
installation of MSSQL Server 2000 Analysis Services and MSSQL Server
2005 Analysis Services is required to obtain the most benefit from this
article, even though, as we have already learned, upgrades and migrations can
certainly be accomplished in alternative scenarios. Suffice it to say that a
side-by-side installation will provide much opportunity for learning the new Analysis
Services 2005 features, as well as easy capability to compare and contrast
the two versions in general. The valuable hands-on study of the enhancements
and options offered by these "parallel scenarios" will provide a firm
grounding in the underlying concepts, and affords a productive development
environment whose rewards will be evident in the integrated Microsoft business
intelligence solutions that come to life rapidly in our respective organizations.