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

About the Series …

article is a member of the series Introduction to MSSQL Server Analysis
. 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

Note: To follow along with the steps we undertake, the following components,
samples and tools are recommended, and should be installed according to the
respective documentation that accompanies MSSQL Server 2000 and 2005:

  • Microsoft SQL
    Server 2005 Database Engine (for SQL Server Management Studio and SQL Server Business
    Intelligence Development Studio);

  • Microsoft SQL
    Server 2000 Analysis Services;

  • Microsoft SQL Server
    2005 Analysis Services;

  • Microsoft SQL
    Server 2000 Analysis Services FoodMart Sample Database.

    successfully replicate the steps of the article, you also need to have:

  • Membership
    within one of the following:

    • the Administrators
      local group on the Analysis Services 2005 computer

    • the Server
      role in the instance of Analysis Services 2005.

Note: Current Service Pack updates are assumed for the operating system, MSSQL
Server 2000 Analysis Services
("Analysis Services"), and the related
Books Online and Samples. Images are from a Windows
2003 Server
environment, but the steps performed in the articles, together
with the views that result, will be quite similar within any environment that
supports MSSQL Server 2000 and 2005 and its component


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

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.

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
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
, Analysis Services, and Reporting Services.

several scenarios, we found that we were able to at least "parallel" Analysis
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
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
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
    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

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

Installation Approach

Brief Description

installation with the earlier version

the best way to compare and contrast the environments in a convenient
environment, the "side-by-side" approach is my favorite for many
reasons. Because SQL Server 2000 Analysis Services does not support named
, the instance of SQL Server 2000 Analysis Services must
always be the default instance on a computer where both versions are
installed. When we do a "side-by-side" installation, SQL Server
permits the installation of named instances of SQL Server
2005 Analysis Services
, meaning that SQL Server 2000 Analysis Services
can continue to run as the default instance, alongside named instances
of SQL Server 2005 Analysis Services.

The "side-by-side"
approach lends itself to planning, and enrichment of existing models through
development within an insulated environment. The development PC can thus
become a formidable tool in this configuration, advancing our migration
efforts while leaving the production environment in place until after we have
developed and tested our evolving solutions, which we can easily stage from
development with continually improving versions – versions that take
advantage of the new technologies of the Microsoft integrated business
intelligence solution.

"Straight upgrade"
of the earlier version

By "straight
upgrade" of SQL Server 2000 Analysis Services, I mean the
installation of SQL Server 2005 Analysis Services, and the subsequent
migration of existing Analysis Services databases to the SQL Server
2005 Analysis Services
database format. Migration can be done as a step
within an upgrade of SQL Server 2000 Analysis Services, where we
confirm that we wish to upgrade the existing instance of Analysis Services
to Analysis Services 2005, via a Components to Upgrade

SQL Server 2000 Analysis Services databases can be migrated after we
install SQL Server 2005 Analysis Services, thanks to tools that are
provided in addition to the SQL Server 2005 Setup mechanism. This is
done by using the SQL Server Installation Wizard, among other methods
(using the Command Prompt approach, etc.), to install a named instance
of Analysis Services 2005. Once the Analysis Services 2005
instance is in place, we can migrate Analysis Services 2000 databases
that we identify.

Migration of existing
Analysis Services databases from within the SQL Server 2005 Analysis

The Analysis
Services Migration Wizard
affords a means of migrating SQL Server 2000
Analysis Services
database metadata to SQL Server 2005 Analysis
. The original Analysis Services 2000 databases are left
intact, clone databases are created, and then the clones are recreated within
the specified instance of Analysis Services 2005, in the format
of the new version.

Table 1: Installation
and Migration Options for MSSQL Server Analysis Services 2005

NOTE: For more information on the approaches described above,
see the installation and setup instructions, together with the Readme
and other documentation, that are included on the MSSQL Server 2005
installation CD(s).

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

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

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.

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