Migrating from SQL Server 2000 Analysis Services to SQL Server 2005 Analysis Services

As should be apparent from reading the previous sections in this chapter, Analysis Services 2005 introduces a big paradigm shift from previous versions. There are two options for migrating to Analysis Services 2005: using the Migration Wizard and starting all over and manually creating cubes from scratch.


Analysis Services 2005 does not support migrating OLAP Services (the OLAP engine in SQL Server 7) databases.

Using the Migration Wizard

You can migrate Analysis Services 2000 databases to Analysis Services 2005 by using the Migration Wizard, which you can access by selecting Start | All Programs | Microsoft SQL Server 2005 | Analysis Services. You need to consider a number of issues when using this facility. The goal of the Migration Wizard is to migrate Analysis Services 2000 objects exactly. If you take this route, the new features of Analysis Services cannot be realized without some manual work. The following information should help you determine some of the compatibility issues with objects in previous versions and how to immediately leverage some of the new functionality in Analysis Services 2005.

An easy migration could be just that: You could migrate one or more databases and reprocess the objects. Applications should be validated to make sure that no functionality has been lost as a result of the migration. Drill-through settings, linked objects, and remote partitions are not migrated. Table 13.2 shows a list of object types in Analysis Services 2000 and how they appear in Analysis Services 2005.

Table 13.2. Analysis Services 2005 Migration Outcomes

Analysis Services 2000 Object

Analysis Services 2005 Object



Virtual cube

Cube with linked measure groups


Dimension with one hierarchy

Virtual dimension

Dimension attributes and hierarchies

Calc member

MDX script

Calc cell

MDX script

Named Set

MDX script

Mining model

Mining structure with one model


Not migrated

Linked cube

Not migrated

After you run the Migration Wizard, all objects need to be reprocessed. Any changes to incorporate any of the many new features in Analysis Services 2005 need to be performed manually.

Starting from Scratch

Rather than use the Migration Wizard and manually update the features that need it, it might be more desirable to manually create cubes from scratch. This requires less effort than it might seem it would due to the new Intellicube technology. If you know your underlying schema well and it is in a star or snowflake schema format, the Cube Wizard can make a very accurate determination about which of the tables should become dimensions and which should become fact tables to feed the cubes when it invokes Intellicube. Because all cubes need to be reprocessed with either the Migration Wizard or the Cube Wizard, the latter option is worth considering.

Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net