Business Intelligence Architect Bill Pearson leads the
hands-on migration of an old friend, the Analysis Services Database FoodMart
2000, to MSSQL Server 2005. We then verify the action of the Migration Wizard
from two perspectives, the SQL Server Management Studio, and the SQL Server
Business Intelligence Development Studio.
About the Series ...
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
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:
Server 2005 Database Engine (for SQL Server Management Studio and SQL Server Business
Intelligence Development Studio);
Server 2000 Analysis Services;
Microsoft SQL Server
2005 Analysis Services;
Server 2000 Analysis Services FoodMart Sample Database.
successfully replicate the steps of the article, you also need to have:
within one of the following:
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
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
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.
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:
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;
results of our practice migration through a cursory examination of the migrated
database within SQL Server Management Studio;
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
Services and the
migration of Analysis Services 2000 databases. The options, with brief
descriptions, appear in Table 1.
Table 1: Installation
and Migration Options for MSSQL Server Analysis Services 2005
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
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.
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
of the earlier version
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
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 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.
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
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.
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
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.
Let's get some
hands-on practice migrating an Analysis Services 2000 database. We noted
earlier that the Migration Wizard is a convenient tool for this purpose,
particularly in light of the fact that the Wizard leaves the original Analysis
Services 2000 databases intact as Analysis Services 2000 databases.
The Migration Wizard actually works with a copy of the original
databases in recreating them on the desired instance of Analysis Services
Wizard allows us to select the Analysis Services 2000 databases that
we wish to migrate, and then provides us with a couple of options for getting
them to Analysis Services 2005: We can move the databases (actually
copies of the databases that are created for this purpose, as we mentioned
earlier) directly to Analysis Services 2005. A second option exists to
have the Wizard generate an XMLA script for execution at a later
time that will accomplish the same ultimate end.
We can start the Migration
Wizard in several ways. We will work with the Wizard within the SQL
Server Management Studio, taking the following steps:
Access SQL Server Management Studio
Click the Start button.
SQL Server 2005 within the Program group of the menu.
Server Management Studio, as shown in Illustration 1.
to Server dialog appears.
Select Analysis Services
in the Server type selector.
Type / select
the server name / instance, if appropriate) into the Server name
authentication information, as required in your own environment.
to Server dialog appears, with the appropriate input for our local
environments, similar to that depicted in Illustration 2.
Illustration 2: The
Connect to Server Dialog, with Representative Settings
Click the Connect
button to connect with the specified Analysis Services server.
Server Management Studio opens.
Within the Object
Explorer (the leftmost pane of the Studio, by default) right-click
the server in which we are working,
Database... from the context menu that appears, as shown in Illustration 3.
Illustration 3: Right-click
the Server Select Migrate Database ...
Services Migration Wizard Welcome page appears, as depicted in Illustration
Illustration 4: Analysis
Services Migration Wizard Welcome Page
The Specify Source and
Destination page appears.
Type the name
of the Analysis Services 2000 instance into the SQL Server Analysis
Services source server box.
radio button to the left of the word "Server," which appears
just underneath SQL Server Analysis Services destination server.
Type the name
of the desired Analysis Services 2005 instance into the Server
box, which is now activated.
The Specify Source and Destination page appears, with my local settings, as shown in Illustration 5.
Illustration 5: The
Specify Source and Destination Page with My Local Settings
The Select Databases to Migrate page appears, after a scan of the specified server, displaying all Analysis
Services databases on the server. The selection checkboxes for all servers
are selected by default.
checkbox on the column heading labeled Source Database,
unchecking it, and thereby de-selecting all databases.
checkbox to the immediate left of the FoodMart 2000 database, as
depicted in Illustration 6.
Illustration 6: Partial
View of the Select Databases to Migrate Page - FoodMart 2000 Selected
The Validating Databases page appears, as validation of the metadata begins. We see the cube
structure appear in a tree as this occurs. Messages indicating the merging of
some dimensions are likely, if our cube structures are similar to those I have
in my local FoodMart 2000 database. The resulting Validating
Databases page, once validation is complete, resembles that shown in Illustration
Illustration 7: The Validating
Databases Page after Metadata Validation for FoodMart 2000
too, the appearance of the following text in the scrollable message box at the
bottom of the page:
Validation is complete. Databases validated: 1 out of 1.The
structure of the migrated database may differ from that of the source database
because of changes in the data model of SQL Server 2005 Analysis Services.
Additionally, drillthrough settings, linked objects, and remote partitions will
not be migrated. For more information about migrating from SQL Server 2000
Analysis Services, reference the product documentation.
It is important to keep
in mind the Analysis Services 2000 features that are not migrated into 2005
when planning the migration of cubes. In most such cases, new features
compensate for those that disappear in Analysis Services 2005.
Moreover, there are many other new features that had no Analysis Services
2000 counterparts, too, that we might want to consider implementing after
migration of the core databases and their cubes, dimensions, measures and other
Migration begins, with
the appearance of the Migrating Databases page of the Migration
Wizard that appears next. We receive a confirmation message in short
order, alerting us to the fact that migration has occurred, as depicted in Illustration 8.
Illustration 8: We
Receive Confirmation that Migration is Complete ...
The Completing the
Wizard page appears. We see a representation of the metadata of the new
database in the tree that is presented, as shown in Illustration
Illustration 9: The
Completing the Wizard Page of the Migration Wizard
The Migration Wizard
closes, and we are returned to the SQL Server Management Studio, from
whence we began. We can now verify the migration of the Analysis Services
2000 database, by reviewing it within the Management Studio or
within the Business Intelligence Development Studio, as we shall see in
the section that follows.
Verification: Examine the Migrated Database in SQL Server Management Studio
The first place we will
review our migrated database will be from our current position within the SQL
Server Management Studio.
Expand the Databases
folder that appears in the Object Explorer pane, underneath the Analysis
Services server within which we are working, on the left side of the Management
The Analysis Services
databases in our local environment appear.
--> Refresh, as depicted in Illustration 10,
if the newly migrated FoodMart 2000 database does not appear
Illustration 10: Refresh
the Object Explorer as Necessary ...
The Analysis Services
databases in our local environment appear, including the new FoodMart 2000
database, similar to those shown in Illustration 11.
Illustration 11: FoodMart
2000 Appears Among the Analysis Services Databases
Expand the new
FoodMart 2000 database.
Expand the Cubes
We can easily review the
entire structure of the database here, simply by expanding the database, and
subsequently expanding the underlying folders, as depicted in Illustration 12.
Illustration 12: Objects
Making Up the Migrated FoodMart 2000 Database ...
the Sales cube in the FoodMart 2000 database Cubes folder.
from the context menu that appears, as shown in Illustration 13.
Illustration 13: Open
the Properties Page for the Sales Cube
The Properties page
for the Sales cube appears, as depicted in Illustration
Illustration 14: Example
Properties Page for a Database Object
We can examine
the underlying properties of each of the cube and other database objects
in this fashion. While we can make modifications, deletions and so forth
within the Management Studio, the Business Intelligence Development
Studio is often the better option for design and development within the
context of an integrated business intelligence solution, whether working with a
migrated Analysis Services database, or creating such a database from
Click OK to
close the Properties page.
to the Business Intelligence Development Studio next, and get an
introduction in opening our newly migrated database there.
--> Exit from the main menu, as shown in Illustration
15, to close the SQL Server Management Studio.
Illustration 15: Leaving
SQL Server Management Studio ...
Verification: Examine the Migrated Database in SQL Server Business Intelligence Development Studio
We will examine our migrated database within Business Intelligence Development Studio, where we could also make all manner of changes to the structure of the Analysis Services 2005 database and its component objects. From the Business Intelligence Development Studio, we could enhance our business intelligence solution to leverage the new features of Analysis Services 2005, from a central, unified development environment.
1. Click the Start button.
2. Select Microsoft SQL Server 2005 within the Program group of the menu.
3. Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 16.
The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 17.
Click for larger image
Illustration 17: The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)
4. Close the Start Page tab.
5. Select File --> Open on the Visual Studio main menu.
6. Select Analysis Services Database from the cascading menu, as depicted in Illustration 18.
The Connect to Database dialog appears.
7. Ensure that the radio button to the immediate left of Connect to existing database is selected.
8. Type the name of the server / server with instance into the Server box.
9. Select the FoodMart 2000 database in the Database selector underneath the Server box.
10. Input a convenient place to store the objects created in the New solution box at the bottom of the dialog.
The Connect to Database dialog appears, as shown in Illustration 19.
Illustration 19: The Connect to Database Dialog, with Our Input
Business Intelligence Development Studio indicates that it is reading the FoodMart 2000 Analysis Services database, and then the component objects appear. We see them manifested in Solution Explorer in short order, as depicted in Illustration 20.
Illustration 20: The Database and Constituent Components Appear in Solution Explorer
Here, within the Business Intelligence Development Studio, we can perform myriad enhancements and extensions to the existing model, including its introduction to source control, the addition of Role Playing dimensions, new security options, many-to-many dimensions, translations, Key Performance Indicators, and far more. We will be examining these new features in prospective articles, where we define business needs and then meet them with the appropriate functionality(ies).
Having examined our migrated database once again, we will leave the Business Intelligence Development Studio, and bring the article to a close.
11. Select File --> Exit, as shown in Illustration 21, from the main menu to close Business Intelligence Development Studio.
Illustration 21: Exiting Business Intelligence Development Studio
In this article, we examined the Analysis Services 2005 Migration Wizard. We began with a brief discussion surrounding approaches available for migrating Analysis Services 2000 databases to Analysis Services 2005. We then introduced the Migration Wizard, discussing its general operation and surrounding migration considerations. We then prepared to use the Migration Wizard within a hands-on practice exercise, from inside SQL Server Management Studio.
Within our practice exercise, we migrated an Analysis Services 2000 sample database, Foodmart 2000, to Analysis Services 2005. After using the Migration Wizard to accomplish this, we cursorily verified the results from two independent perspectives. We examined the results of the migration via the SQL Server Management Studio, and then performed a similar examination within SQL Server Business Intelligence Development Studio, discussing the appropriateness of the latter environment for further development within the highly enriched feature set of Analysis Services 2005, and looking forward to articles where we expand upon its manifold uses.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.