Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 21, 2005

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

By William Pearson

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.

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.

    To 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 applications.


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.

Installation Approach

Brief Description

"Side-by-side" installation with the earlier version

Often 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 instances, 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 Setup 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 2000 to Analysis Services 2005, via a Components to Upgrade page.

Alternatively, 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 Services platform.

The Analysis Services Migration Wizard affords a means of migrating SQL Server 2000 Analysis Services database metadata to SQL Server 2005 Analysis Services. 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 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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM