Hands-On Procedure
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
2005.
The Migration
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:
Practice
Preparation:
Access SQL Server Management Studio
1.
Click the Start button.
2.
Select Microsoft
SQL Server 2005 within the Program group of the menu.
3.
Click SQL
Server Management Studio, as shown in Illustration 1.
The Connect
to Server dialog appears.
4.
Select Analysis Services
in the Server type selector.
5.
Type / select
the server name / instance, if appropriate) into the Server name
selector.
6.
Supply
authentication information, as required in your own environment.
The Connect
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
7.
Click the Connect
button to connect with the specified Analysis Services server.
The SQL
Server Management Studio opens.
8.
Within the Object
Explorer (the leftmost pane of the Studio, by default) right-click
the server in which we are working,
9.
Select Migrate
Database... from the context menu that appears, as shown in Illustration 3.
Illustration 3: Right-click
the Server – Select Migrate Database ...
The Analysis
Services Migration Wizard – Welcome page appears, as depicted in Illustration
4.
Illustration 4: Analysis
Services Migration Wizard – Welcome Page
The Specify Source and
Destination page appears.
11.
Type the name
of the Analysis Services 2000 instance into the SQL Server Analysis
Services source server box.
12.
Click the
radio button to the left of the word "Server," which appears
just underneath SQL Server Analysis Services destination server.
13.
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.
15.
Click the
checkbox on the column heading labeled Source Database,
unchecking it, and thereby de-selecting all databases.
16.
Click the
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
7.
Illustration 7: The Validating
Databases Page after Metadata Validation for FoodMart 2000
We note,
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
objects.
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
9.
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.
» 
Comment and Contribute
