Moving Cubes from Analysis Services 2000 to 2005

SQL Server 2005 Analysis Services (SSAS) provides many compelling reasons for upgrading from SSAS 2000. Among the many enhancements, SSAS 2005 provides more flexible analysis than SSAS 2000 by exposing most dimension attributes as analytical objects. But executing on the decision to upgrade comes with some challenges. Should you use SQL Server 2005's built-in tools to migrate your SSAS 2000 cubes? Or, with all the architectural changes in SSAS 2005, should you rebuild the cubes from scratch? Both methods have pros and cons, and your decision will likely depend on your environment. Let's take a look at some key decision points that will help you make your choice.

Note that this article assumes familiarity with basic concepts in SSAS 2005. SQL Server 2005 Books Online (BOL) provides excellent information and tutorials that cover these concepts. The sidebar "Upgrade Your Analysis Services Terminology" also gives a high-level guide to the SSAS 2000 terms that have changed in SSAS 2005. Also, to explore the pros and cons of the migration-versus-rebuild decision, we'll refer to the FoodMart 2000 sample database. Although FoodMart presents a simplistic cube implementation, many of the fundamental features of SSAS 2000 are represented in one or more of the cubes in this database. FoodMart also provides a universally understood data model, so the examples we use will be easy to follow.

Migration

You can migrate an SSAS 2000 database to SSAS 2005 in a few different ways, but all options use the Analysis Services Migration Wizard. For information about the various migration options, check out Darshan Singh's November 2005 article "Step Up to SQL Server 2005" (InstantDoc ID 47749). Darshan notes that before you start your upgrade, it's a best practice to run the Upgrade Advisor to determine possible migration problems specific to your database. Also, you can read about overall migration considerations in BOL (http://msdn2.microsoft.com/en-us/library/ms143235.aspx). Known Analysis Services upgrade issues include the fact that drillthrough options, linked objects, and remote partitions won't be migrated.

Note that the Migration Wizard will make no effort to redesign your cubes to leverage the features of SSAS 2005. The wizard's mission is to simply recreate the cube in SSAS 2005 as it was in SSAS 2000. This simplistic approach is by design; Microsoft intended to minimize potential problems with front-end applications. Knowing about this design will help you more easily understand the choices that the wizard makes.

You can migrate the FoodMart 2000 sample cube by opening an SSAS 2005 instance in SQL Server Management Studio (SSMS) and walking through the Migration Wizard (right-click the instance name and select Migrate Database). The wizard will ask for predictable information regarding the source SSAS 2000 server and the databases you want to migrate. After running the Migration Wizard, you can open Business Intelligence Development Studio (BIDS) and view the resulting SSAS cubes and shared dimensions, which Figure 1 shows.

There's some room for interpretation about how FoodMart should be implemented on SSAS 2005, but most people agree that the migrated database in Figure 1 isn't the best design. The following documents some of the high-level observations:

Another issue is the ability to easily add new attribute dimensions (which are a cornerstone of SSAS 2005) to any of the core dimensions. The data source view (DSV) that the Migration Wizard creates includes only columns that support the migrated objects. The Customer table in the source FoodMart 2000 relational database contains several columns that might make for rich analysis options, such as city, state, customer region, total children, houseowner, number of cars owned, and more. You can update the DSV to include all columns from the source tables by right-clicking the background in the DSV designer and selecting Refresh. But do this at your own risk; some properties of pre-existing attributes might change. You might find it easier to simply replace specific dimension tables in the DSV (right-click the table, select Replace Table, With Other Table, then select the same table). Once you've updated the DSV with all available columns, you'll need to edit each dimension to add desired attribute hierarchies from the newly available columns.

Despite the negatives, a lot of good stuff happened during this migration. All of the MDX calculations that we defined in calculated members migrated successfully (look under the Calculations tab for the Sales cube in BIDS). Our parent/child dimensions (Account and Employees) migrated successfully. And the partitions and aggregation designs, actions, unary operators, and member properties were retained and are operational. Finally, note that the Migration Wizard will migrate only metadata. Migrated cubes must be fully processed to populate the cubes with data.

Starting from Scratch

Building a cube from scratch isn't as extreme as it first sounds. The main thing you need is a well-designed star or snowflake schema. You start in BIDS by adding a new data source, then a new DSV. Create the new DSV in the BIDS Solution Explorer by right-clicking Data Source Views and selecting New Data Source View. The wizard will guide you through selecting the data source and adding the tables required for all cubes (which will be measure groups in SSAS 2005).

When you view the new DSV in the DSV designer, you'll note several missing primary key and foreign key relationships. Although adding these relationships isn't required, doing so can help SSAS form better queries against the data source when refreshing dimensions or cubes. Also, if you don't add the appropriate relationships in the DSV and you use the Auto Build option to invoke Intellicube to build the cube, Intellicube won't be able to differentiate fact tables from dimension tables. In the case of FoodMart 2000, you'll notice that the five tables (account, category, department, product_class, and product) show up as fact tables if you attempt to auto-build the cube without creating these relationships. They show up this way because they have no defined relationships to other tables in the DSV. If you uncheck the box for the table in the Fact column, you'll see the warning message that Figure 2 shows. So, it's important to create relationships between the fact and dimension tables.

Another good practice is to add logical primary keys to all dimension tables. You can create relationships based on your knowledge of the underlying data source. If the data source isn't available, you can easily view it by bringing up the cube editors in the SSAS 2000 databases and examining the underlying relationships. You can modify relationships in the DSV designer. Note that these added relationships are in the DSV only. The DSV is an abstraction of the source and doesn't result in changes to the source.

After creating the appropriate relationships, you can create a new cube in SSAS 2005 by right-clicking Cubes in the Solution Explorer in BIDS, then selecting New Cube. Make sure Auto Build is selected to invoke Intellicube. The wizard now lists only one dimension table incorrectly as a fact table, presumably because of its relationship to other tables (i.e., itself ). You can simply uncheck the box in the Fact column next to the Employee table to remove this designation.

The Cube Wizard produces 10 dimensions, versus the 19 dimensions that the Migration Wizard created. These 10 dimensions, which Figure 3 shows, are more "pure"—all related attributes, hierarchies, and properties are contained within a single dimension. And the Cube Wizard has created just one cube. (We could have eliminated the HR information by removing it in the DSV or not selecting it during the cube creation).

As you can see in Figure 3, the resulting dimensions and cubes reflect the desired architecture in SSAS 2005. The biggest downside to starting from scratch is that the "extras" that existed in the SSAS 2000 cube aren't reflected in our brand-new cube. Namely, we have to manually set up calculated members, parent/ child dimensions, actions, custom member rollups, and unary operators just as they were initially set up in SSAS 2000. If we intend to use cube partitioning, we'll have to re-implement this functionality in the new database.

We also need to determine aggregation design before processing the cubes. Before designing aggregations, be sure to evaluate the dimension attributes to be used and set AttributeHierarchyEnabled to False on those attributes that won't be used for analysis.

So, What's the Best Plan?

In upgrading SSAS cubes, both migrating and rebuilding cubes have pros and cons. You might want to initially perform a straight migration just to take advantage of new engine features such as more efficient memory management and multi-instance support in SSAS 2005. Even when rearchitecting the cubes, you might find it beneficial to ultimately leverage both methods. Table 1 gives a high-level overview of the benefits of both methods.

As you can see, the benefits of the two methods have very little overlap, which makes combining the two methods even more appealing. You can migrate the database by using the Migration Wizard, create a new database and build the cube from scratch, script objects in the migrated database, and run the scripts in the new database as a means of copying the desired objects from the migrated cubes. The sidebar "Scripting a Cube in XMLA," page 34, describes how you can create a script to run in SSMS to create calculated members from the source Sales cube in the newly generated database.

Although I've tried to address the most common considerations for migrating cubes from SSAS 2000 to SSAS 2005, I don't have enough space here to include every possibility. For example, neither of the methods I discussed optimizes aggregation usage and attribute relationships. But you can use the ideas in this article to help you search BOL for specific migration issues that you might encounter.