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.

Note

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
Cube	Cube
Virtual cube	Cube with linked measure groups
Dimension	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
Drill-through	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.