Analysis Services 2005 Migration

By Michael Young, Proclarity Corporation, and Dave Wickert, Microsoft Corporation

SQL Server Technical Article
Applies To: SQL Server 2005

Summary: This paper guides you through the process of migrating to Analysis Services in SQL Server 2005. The Unified Dimensional Model (UDM) provides new design opportunities for cube developers. The UDM has brought together the relational reporting and OLAP reporting worlds to provide a unified forum for both data request environments. Understanding the new design options and how they affect your organization will optimize migration. The Migration Wizard is a fast and effective tool for moving your existing cubes to Analysis Services 2005. This paper introduces the wizard and helps you determine whether you should use it. In some cases it is better to redesign from scratch to ensure that you are properly leveraging the new features of Analysis Services 2005.

On This Page

About Project REAL
Introduction
Cube Design Issues that Impact Migration
Migration Tools
Migration in Project REAL
Migration vs. Redesign
Conclusion

About Project REAL

Project REAL is an effort to discover best practices for creating business intelligence applications based on Microsoft® SQL Server™ 2005 by creating reference implementations that are based on actual customer scenarios. This means that customer data is brought in-house and is used to work through the same issues that the customers face during deployment. These issues include:

  • The design of schemas.

  • The implementation of a data extraction, transformation, and loading (ETL) process.

  • The sizing of systems for production.

  • The management and maintenance of the systems on an ongoing basis.

By working with real deployment scenarios, we gain a complete understanding of how to work with the tools. Our goal is to attempt to address the full gamut of concerns that a large company would face during their own real-world deployment. This paper describes the role of migration in Project REAL. Project REAL uses data from two Microsoft business intelligence customers. Phase 1 of the project was modeled on a large electronics retailer that keeps sales and inventory data in a Microsoft SQL Server 2000 data warehouse. SQL Server 2000 Data Transformation Services (DTS) is used to manage the flow of data into the relational database, and from there into SQL Server 2000 Analysis Services cubes for reporting and interactive querying. This customer maintains approximately 200 GB of data in their relational store. All of this data is subsequently processed into Analysis Services cubes. Phase 1 implementation focuses primarily on the concerns that an existing SQL Server 2000 customer might have when carrying out a migration to SQL Server 2005. Our results largely represent the migration of existing functionality, with a few new capabilities used where appropriate. In the area of Analysis Services migration, the cube data was moved to Analysis Services 2005 primarily by using the Migration Wizard. Additional features were added to cubes after they had been migrated to the new version.

Phase 2 of Project REAL is based on a larger data set from a different customer, and exercises more of the new capabilities of SQL Server 2005 than does Phase 1. This is because Phase 2 is primarily a new implementation of a SQL Server 2005 solution. The target of Phase 2 was to demonstrate many of the new features of Analysis Services. It was determined that a redesign of the cubes was necessary in Phase 2 and the Migration Wizard was not used. Look for more papers about Project REAL in the future.

Introduction

Analysis Services 2005 offers a refreshing approach to OLAP and allows you to fully integrate your OLAP and relational reporting requirements. By leveraging Analysis Services 2005, you can decrease your overall cube administration while enhancing the end-user experience. The Unified Dimension Modeling (UDM) approach to design offers a seamless bridge between the relational and OLAP reporting worlds. It has changed the way in which cubes are fundamentally structured.

New features in Analysis Services 2005 allow you to better approach common navigation patterns, meet and exceed both relational and OLAP reporting requirements, deal with unique types of data, and decrease the memory overhead related to providing analytics.

This white paper will help guide you through the migration process and help you determine the optimal migration path for your organization. You can either migrate by using the built-in migration tools or by redesigning existing cubes. This paper describes design changes in Analysis Services 2005 and how your current cube structure will be affected by these changes.

This paper covers the main Migration Wizard steps. It also presents some questions you should ask yourself to determine whether using the wizard is your best choice.

This paper helps you determine whether you should use the Migration Wizard as the starting point for your cubes or whether you should redesign your cubes from the ground up.

Cube Design Issues that Impact Migration

Analysis Services 2005 cubes are very different than those in earlier versions of the product. If you migrate using the Migration Wizard, Analysis Services attempts to duplicate your existing cubes in the new environment. While this gets you to the new platform quickly, it does not allow you to leverage all of the new design features in Analysis Services 2005.

Analysis Services 2005 introduces the Unified Dimensional Model (UDM). The role of the UDM is to seamlessly merge the OLAP and relational reporting worlds. Traditionally, OLAP environments excel in providing navigation paths to drill up and drill down in data. The data is stored in levels. Relational reporting excels in generating reports where a field can be placed anywhere on the report.

A difficulty with OLAP arises when users find fields interesting, but these fields don't fit within the hierarchies defined by the cube administrator. When these key fields don't fit into a natural hierarchy with other fields. how do they get included in the analysis? To address this problem, an organization might end up with two different approaches — both an OLAP and a relational tool, for example.

The UDM fixes the problem of analytics and relational reporting integration from the ground up by allowing all interesting fields to be treated as first class attributes. The UDM allows a flexibility that has previously not been available. The UDM consists of several high-level components, which allow for a flexible design. These features are as follows:

  • Attribute hierarchies and user-defined hierarchies

  • Related attributes

  • Data source views

  • Measure groups

  • Perspectives

The following sections describe how each of these impacts the design of your cubes in Analysis Services 2005. This will help you decide your best migration path.

Attribute Hierarchies

With Analysis Services 2005, you have the ability to create attributes from any field you would like to provide in the analysis. Attributes can then be leveraged in your analysis or in a relational report. Furthermore, you can organize the attributes into hierarchies to provide a navigational path. From a navigation perspective, attribute hierarchies and user-defined hierarchies replace Analysis Services 2000 dimension. As a result, it will be common for cubes to have many, many attribute hierarchies. In fact, large complex cubes could contain hundreds of attributes. In Analysis Services 2000, it was often a best practice to use a small set of dimensions. This was done for two reasons:

  • To help use memory effectively. All dimensions defaulted to MOLAP storage and each member was loaded into memory. A small number of shared dimensions allowed for fast processing and quick query response times.

  • To add context for the user. It is difficult for users to conceptualize more than six or eight dimensions and maintain context while slicing, dicing, and drilling up and down.

    The UDM changes all of this. Each field that you would like to see in the analysis can, and in most cases will, be added as an attribute in the cube. You can then take multiple attributes and place them in user-defined hierarchies. User-defined hierarchies can be traditional hierarchies or strong hierarchies, where many children roll up to a single parent. Or, they can be a navigation hierarchy, where attributes can be placed along different navigation paths regardless of the cardinality between parent and child. This method of design provides the following advantages:

  • Any field can be placed on any part of the report or view of your data. It is very easy to take a single field and place it on columns and rows independent of the other fields in the hierarchy.

  • Cubes can be more representative of the data warehouse or data store. The Analysis Services 2005 cube can contain many attributes, user-defined hierarchies, and measures (from multiple fact tables). This allows the cube to be a closer representation of the source data.

  • Cubes can contain data from multiple dimension tables and multiple fact tables. You are virtually unlimited in the types of design you can come up with.

  • Navigation paths can be defined for all types of data. You are no longer restricted in the types of hierarchies that are created.

  • Member properties and virtual dimensions are no longer needed for reporting purposes. Analysis Services 2000 cube administrators were often forced to add member properties and then virtual dimensions so that they could add columns to reports. This is no longer necessary, as attributes are the basis for analytics and reporting and an attribute is, in most cases, representative of a single column.

The concept of member properties is expanded in Analysis Services 2005. The traditional member property is now available as an attribute relationship. When querying for member properties, you will not only see the traditional member properties but all attribute relationships. Attribute relationships are used by the Aggregation Wizard to determine where aggregations can be performed. It will be necessary to have attribute relationships between attributes that are organized in levels. For example, a common customer hierarchy could include Country, Region, State, City, and Customer as the levels. To leverage aggregations, City would have an attribute relationship to State. The Formula Wizard also uses attribute relationships to determine the best mechanism for performing a calculation.

Member properties were often used to facilitate reporting in Analysis Services 2000. To display a column that was not part of the hierarchy, a member property could be used to display the column value. The member property could be displayed as a calculated member, a virtual dimension, or through a third-party tool that exposed it natively. With the UDM, the creation of member properties for reporting is no longer necessary, as all interesting columns can be easily added as attributes. The attributes can then be placed on columns and rows to facilitate the analysis requirements.

Note:  Analysis Services 2005 uses attribute relationships to determine the calculations that are necessary for rollup in user-defined hierarchies. Without attribute relationships in the hierarchies, aggregations will not be created. In strong hierarchies a child will have its parent level as an attribute relationship. The aggregation process for attribute hierarchies is dependant on this relationship.

Data Source Views

Analysis Services 2005 adds an extra layer of abstraction between the cube and the data source. The data source view allows you to logically separate the cube from its data sources. One or more data sources can be combined in a data source view to provide a logical representation of the data. The data source view can either be a selection of tables from the data sources or it can be a named query. The named query is a SQL statement that you write so that you can load the data the way that you want (not unlike a relational view but a named query is stored in the DSV). The cube is then built from the data source view.

Data source views:

  • Provide a layer of abstraction from the data sources

  • Can include multiple data sources. This can be multiple databases on the same server or databases from different servers.

  • Provide named queries for you to write the query that Analysis Services uses to load the data. This can be used to filter, limit, or optimize the data load from the source.

  • Can be used to rename the data to provide a separate naming layer from the actual data source.

  • Allow for the representation of logical keys to define the relationship between the fact and dimension tables.

  • Support named calculations. Each named calculation stores an expression, which is used to build the column definition.

  • Provide the basis for building the cube.

Measure Groups

One of the primary areas of concern with your new cubes should be the context in which the user is viewing the data. It is possible to build a cube from multiple fact tables. Each of these fact tables can have multiple measures and each of the fact tables can have a differing level of granularity. Thus, not only will the cube have up to hundreds of attributes, but it will also have measures from different fact tables and different granularity.

For example you have a cube that has an Actual Sales measure group at the Product, Store, Customer, Day grain (who bought what, where, when?). The cube also has a Forecast measure group at the Product Class, Store, Month grain (what will we sell and where will sell it in the coming months?). And, it has an Inventory measure group at the Product, Store, Week grain (how much did we have on-hand, where, and when?). By combining these three measure groups in the same cube, you can look at trend information. For example, did having products out of stock impact your sales and how should that drive your forecast projections? This can be done because all of the facts can be combined in the same cube. In Analysis Services 2000, you would create two cubes and then combine them in a virtual cube. In Analysis Services 2005, you have a single cube with multiple measure groups (one for each fact table).

While this is very flexible for the cube builder, it does not always provide the context necessary for the end user. Most end users are not intimate with the data warehouse or data store design. Measure groups can be created to separate different measures from each other to help provide context for the user. Measures with similar levels of granularity can be grouped and treated (from an administrative perspective) as a single unit. By default, measure groups are organized by the fact table they are retrieved from. Measures from a single fact table should have a similar level of granularity. All measures within a single fact table will belong to the same measure group. Measure groups provide the following set of advantages:

  • Measures can come from multiple fact tables.

  • Measures can be grouped by granularity.

  • Multiple levels of granularity can be included in a single base cube.

  • Security can be applied to specific measure groups.

  • Measure groups can be exposed through one or more perspectives and grouped with the dimensions that make sense to the end user. Measure groups help provide context for the end user.

Perspectives

In Analysis Services 2000, cubes were often defined with a smaller number of dimensions and a set number of measures from a single fact table. To add measures of multiple granularity or to view many dimensions from different cubes, you could create a virtual cube to combine multiple base cubes to give the appearance of something much larger. This allowed you to build up to the final result. This not only allowed us to deal with granularity issues, but it also helped with the memory consumption in Analysis Services 2000.

This has changed with the UDM. Now the cube can have hundreds of attribute hierarchies, user-defined hierarchies, and multiple measure groups (which come from different fact tables). Because of this, cube design is very flexible. The problem that remains is the user context. At some point you need to narrow the view of the data so that the end user can make sense out of it. The view of the data should easily allow the user to meet their project requirements.

Perspectives provide context for the end user. A perspective is a set of attributes, user-defined hierarchies, actions, and measure groups that you want to group as a logical collection. The perspective provides the basis for the analytics and provides context for the user. It will be very common to have a large cube with hundreds of attributes and many measures. Then many perspectives will be created for the users to interact with the collection of data that makes sense for their task. The UDM is for the cube administrators — perspectives are for the end user. Perspectives can not be used to implement security.

Tip:  Perspectives are exposed to front-end tools in a manner similar to the way cubes were presented in Analysis Services 2000. If your users are used to looking at a set of cubes that they connect to and navigate, you can create a similar experience for your users by implementing perspectives that include the same dimensions and measures that were included in your Analysis Services 2000 cubes. For example, you might have a Sales physical cube with different perspectives based on the different business usages of that data, such as a Brand Manager perspective; or a Store Manager perspective; or a Regional Manager perspective. Each perspective actually is exposed as a cube to the end user (as is the larger, more complex base cube), but the dimensions, calculations, measure groups, and other entities have been tailored for just that particular business use.

Summary of Design Issues

In Analysis Services 2005, cubes can have many attributes, user-defined hierarchies, and measures. The UDM combines the best of the OLAP world with the best of the relational reporting world to provide you unlimited opportunities in cube design. The end result of the cube design could be much more encompassing than what the user requires. You can use measure groups and perspectives to provide context to users. In most cases, the perspective will be the basis for the user experience.

Migration Tools

Analysis Services 2005 provides tools to help you through the migration process. When you install Analysis Services 2005 on a machine that already has Analysis Services 2000 installed and you choose to install to the default instance, you are prompted to run the Migration Wizard during the installation. If you choose not to use the wizard at installation, you can use it later as a stand-alone tool.

The Migration Wizard allows you to either migrate an Analysis Services 2000 cube to the new version or create an XMLA (XML for Analysis) script to perform the migration at a later time. The wizards are fast and effective. You will need to determine whether the end result meets your needs. In some cases, the Migration Wizard is a great starting place and in others it is better to redesign. Even if you plan to redesign your cubes, it is a good idea to run the Migration Wizard. At a minimum, it is a very useful and educational experience to see how the wizard converts your objects. You may ultimately decide not to use its recommendations and to redesign the schema by using the normal design wizards, but at least you can see how the Migration Wizard would accomplish a similar activity even if you don't agree with its final decisions.

Analysis Services 2005 includes two built-in migration tools. The first tool is the migration option during the installation of Analysis Services 2005.

The second is the Migration Wizard started as a stand-alone process as one of your primary tools in the SQL Server 2005 program group.

Migration Wizard

The Migration Wizard does its best to duplicate the cubes you had in Analysis Services 2000. The goal of the wizard is not to provide a best practice Analysis Services 2005 cube. With that in mind it is easy to see why it makes the choices it does. After you have completed the wizard, you need to determine the additional steps you need to take to better leverage all Analysis Services 2005 features.

The wizard prompts you to select the OLAP databases that you want to migrate. After you select the appropriate OLAP databases, you need to determine whether you want to move them straight to Analysis Services 2005 or whether you would like to generate an XMLA script. The script can be run at a later time from SQL Server Management Studio.

To run the Migration Wizard

  1. In the SQL Server 2005 program group, open the Migration Wizard. The Migration Wizard is one of the Analysis Services Tools. The wizard should appear as shown in Figure 1.

    Cc917610.asmigrtn01(en-us,TechNet.10).gif

    Figure 1: The Analysis Services 2005 Migration Wizard can migrate your Analysis Services 2000 cubes

  2. Click Next to display the Source and Destination Page section of the wizard.

  3. Supply the data source and destination as shown in Figure 2. The data source should be your Analysis Services 2000 instance. The destination should be your new Analysis Services 2005 instance. You can select a script file instead of a destination. This causes the wizard to generate an XMLA script that you can execute later to generate your cubes.

    Cc917610.asmigrtn02(en-us,TechNet.10).gif

    Figure 2: The data source and the destination need to be provided as the basis for the migration

  4. After you supply a source and a destination, click Next. The wizard reads the metadata from your data source and then displays the Select Databases to Migrate screen.

  5. Select the databases to migrate. By default, the destination database name is generated for you. The wizard tries to create a database of the same name. If a database with the same name already exists, the wizard generates a name that adds an incremental value at the end of the database name starting with 1. To supply a name, click in the Destination Database cell and provide a name as shown in Figure 3.

    Cc917610.asmigrtn03(en-us,TechNet.10).gif

    Figure 3: You can rename the destination database to the name you would like to use in Analysis Services 2005

  6. After you supply the names of the databases to migrate and the destination databases, click Next.

  7. The Validating Objects portion of the wizard starts. This takes some time to run (depending on the amount of metadata in the data sources). As objects are validated, the wizard displays warning and error messages for data that could have potential problems. You can view details about changes that the wizard has made to your data. For instance, if a dimension is renamed, you receive a warning message that tells you how it was renamed. Later in this paper we will outline some of the potential issues you could run into when using the Migration Wizard.

    You can use the View Log and Save Log features to view and filter the details and save them to a file for review later. When you are done reviewing the object details, click Next.

  8. The Migrating Database screen should appear. The wizard is performing the migration; the metadata is being generated. The data, however, is not transferred. You will need to process the new cubes to populate them with data.

  9. After you have migrated the databases, click Next to complete the wizard.

    Note:  When the OLAP databases are migrated, the cube metadata is migrated to Analysis Services 2005. After you migrate, you will need to process the cube. The data will then be loaded from the data source into the cube and you can browse the data.

The following items are not migrated with the wizard:

  • Remote partitions

  • Linked cubes

  • Drillthrough options

The wizard makes some choices during the migration to map the old features to the new features. You should be aware of the following items:

  • Member properties are migrated to attribute relationships. You will notice that the term attribute relationship is primarily used in Business Intelligence Development Studio. If you query the API for member properties, it will return all attribute relationships. In addition to your set of original member properties, each level in the hierarchy will have an additional attribute relationship for its parent level. The attribute relationship is used by the aggregation engine to provide the relationship between your data points. These relationships are necessary for rollup calculations. You should not remove these related attributes. If you have added other member properties to address specific reporting requirements, you can remove the reporting related attributes as the columns should also be defined as attribute dimensions.

  • Some named calculations may be created for you in the data source view. These named calculations store the expression that is used to build the column. These will be named as columnx starting with column1 in each table. This occurs when you have a member name or member ID for a level that is based on an expression in Analysis Services 2000. In Analysis Services 2000 the member name and member ID property boxes allowed for SQL type statements to be used to handle issues like concatenation. These are now stored as expressions. For example, the Foodmart cube has an example of this in the Customers dimension. Upon migration you will notice a new column in the Customers table (in the data source view and also added as an attribute) that is named Column1. If you review the properties, the source is a concatenation of the First Name and Last Name columns.

  • Almost all columns from your data source tables are added as attribute dimensions. Excluded columns are those that have data types that Analysis Services excludes as not interesting including: timestamp, uniqueidentifier, and table. All numeric, date, and character-based columns are added attributes.

  • Virtual cubes are migrated as cubes. The measure groups are not added to the cube. They are migrated as linked measure groups that point back to the base cube. For each base cube that is referenced by the original virtual cube, you will have a linked measure cube pointing back to the base.

  • Virtual dimensions are subject to the rules of merging dimensions. Virtual dimensions were based on member properties and could include one or more levels. If the virtual dimension had a single level it will be merged into the dimension upon which it was based. If the virtual dimension had multiple levels it will be converted to an actual dimension. For example, in the Foodmart database we have three virtual dimensions. To illustrate this, we will look at Position (which has multiple levels) and Store Size in Sq Ft (which has a single level). Upon migration, Position is created as a new dimension. Store Size in Sq ft is merged into the store dimension. Square Feet is added as an attribute to the dimension.

  • You will receive a message for every action stating that it will be migrated as a command. In the Analysis Services 2005 user interface, you will see that these are still referred to as actions.

  • Data source views are created automatically and will create named queries to represent a set of columns from a table. The data source view that is created includes all of the fact and dimension tables that were used in the Analysis Services 2000 OLAP database.

  • Partitions are migrated, although the scripts that are used to automatically create them will be migrated to XMLA scripts. Partitions use a query binding by using the same SQL statement that is issued by Analysis Service 2000 to process the partition.

  • Roles are migrated with cube and dimension security. Analysis Services 2005 allows more types of security implementations. If you want to implement any of the new security options, you will have to do so after migration.

  • Multiple hierarchy dimensions are migrated but not always named the way you would like. For instance, the Time.Fiscal and Time.Calendar dimensions in Analysis Services 2000 will probably migrate to Time and Time 1. If you already had a hierarchy named Time, it would migrate to Time TimeDim and TimeDim 1. This can be confusing. While this is not the result you may want, it is very easy to rename dimensions.

After the Migration Wizard finishes, you may want to go into Business Intelligence Development Studio to review the results and make modifications as necessary. Tasks to perform after migration include:

  • Renaming dimensions so that they conform to your naming convention.

  • Determining whether all the user-defined hierarchies are in the appropriate dimensions. In some cases, the Migration Wizard creates more dimensions than are necessary. You might be able to consolidate some of your user-defined hierarchies and delete the additional dimensions. This helps to ensure that the new dimension names do not affect your existing MDX queries and cause them to fail.

  • Naming the named calculations. Columnx is the name applied to each named calculation that is added to your data source view. You will want to name these to an appropriate name for the column.

  • Setting up your drillthrough settings as a drillthrough action. Drillthrough is now performed as an action and will have to be configured.

To rename a dimension in Business Intelligence Development Studio, use the following procedure.

To view your new cube and rename a dimension

  1. In the SQL Server 2005 program group, open Business Intelligence Development Studio.

  2. When you open the tool, you will probably begin with the Visual Studio start page.

  3. To open your cube, click Open on the File menu. Click Analysis Services Database as shown in Figure 4.

    Cc917610.asmigrtn04(en-us,TechNet.10).gif

    Figure 4: From Business Intelligence Development Studio, you can open an existing Analysis Services database

  1. You are prompted to either Create a Database or Open an Existing Database. Select Open an Existing Database and provide your instance and database information.

  2. In Solution Explorer, you should see your database as well as a list of cubes and dimensions that were generated.

  3. To rename a dimension, right-click the dimension and click Rename. You can then type a new name as shown in Figure 5.

    Migration Wizard Figure 1

    Figure 5: It is easy to rename objects in Analysis Services 2005

The Migration Wizard does not implement a number of new features since these have no equivalent capability in Analysis Services 2000. These features are accessed on tabs and property boxes in the Analysis Services administrative tools. High-level features that are not implemented include but are not limited to:

  • Key Performance Indicators (KPIs)

  • Translations

  • Many-to-many dimensions

  • Role-playing dimensions

  • Semi-additive measures

Migration in Project REAL

The OLAP databases and cubes that were used in Project REAL were built from a redesign. The wizards in Analysis Services 2005 were used as the basis for the cube design. As a side process, with the intention of comparing results, the Migration Wizard was used against a sample data set from Barnes and Noble Booksellers. The data leveraged shared dimensions extensively and many of the shared dimensions were virtual dimensions. After running the Migration Wizard the following observations were made:

  • Each virtual dimension was based on member properties from an item table. Upon migration, each was merged (or renamed) to the item dimension. Each virtual dimension was added as a user-defined hierarchy in the item dimension. They were each made visible by default and can be referenced in the same manner as they were before migration. This increases the chances that your previous queries will continue to work by simply using the new provider.

  • A single data source view was created from the single data source that was used in Analysis Services 2000. The data source view represented all tables that were used by the existing data source.

  • The only virtual cube was migrated to a cube in Analysis Services 2005. Each base cube was migrated as a separate measure group: one for Store Sales; one for Store Inventory; and one for Distribution Center Inventory.

  • All security information, including roles and data security, was migrated properly.

  • With the exception of the Time dimension, which was renamed Time.Fiscal and Time.Calendar (as previously described in the discussion of multiple hierarchies), the dimensions did not have naming conflicts. Therefore, they had the same name in Analysis Services 2005 as they did in Analysis Services 2000.

  • Overall, the process was fast, seamless, and successfully created the same cubes in Analysis Services 2005 that existed in Analysis Services 2000.

  • In the inventory data, we used the new semi-additive measures to set LastNonEmptyChild for all of the on-hand and on-order measures. Since this is new functionality in Analysis Services 2005, we removed the complex calculations which were used for the semi-additive rollups over time.

Migration vs. Redesign

The Migration Wizard provides you with a good first look at your data in Analysis Services 2005 cubes. It should be noted, however, that cubes that are created with the Migration Wizard are meant to duplicate the cubes that you built in Analysis Services 2000. This may not be the result that you want. In some cases, you will be able to simply use the wizard to migrate and then add the features that you want. In other cases, it is better to redesign the cubes in the new architecture. The following set of questions will help you determine whether the Migration Wizard is a good starting point for you or whether you should just redesign.

  • Do you have many small cubes that are limited in dimensions and measures? Cube administrators in Analysis Services 2000 commonly built many smaller cubes. Each of these cubes will migrate to individual cubes in Analysis Services 2005 if you use the wizard. Because you are not restricted by issues such as memory usage, single fact table measures, distinct counts, and member properties in Analysis Services 2005, you may not want many small cubes. Your data might make more sense in a small number of larger cubes with perspectives providing the user context. If you used many cubes to provide the user context, you may be better off redesigning the cubes in Analysis Services 2005.

  • Do you have lots of member properties added to your dimensions for reporting purposes? The columns can now be added as attribute dimensions to help with the layout of reports and navigation. It is not necessary to have all the member properties as related attributes. This could require a lot of manual cleanup after the migration and you may be better off redesigning your cubes.

  • Did you use private dimensions for many of your cubes? Private dimensions will migrate over as dimensions. If you used a lot of private dimensions and the same type of dimension for different cubes, you will notice that the system will duplicate these dimensions as dimensions in Analysis Services 2005. This will require cleaning up the dimension names and editing the cubes. In this case, you probably want to consider a redesign.

  • Do you have a lot of calculated members that are now allowed as measures? Measures now have more aggregate functions that they can leverage. The list of aggregate functions has expanded from the original list of Sum, Count, Distinct Count, Min, and Max to include Average of Children, None, First Child, Last Child, First Non Empty Child, and Last Non Empty Child. If you have created calculated members that could now be used as measures, you may want to consider either a redesign or at least evaluate how extensive your clean up would be after migration.

  • Did you significantly alter your ETL process or create views in your relational database to create a single data source for your cube? If you would like your data to naturally come from multiple data sources, you are probably better off redesigning your cubes and building the named queries in your data source views to load the data.

  • Did you have to work around distinct counts? In Analysis Services 2000, distinct counts required a lot of attention. You were limited to one per cube and it was suggested that the distinct count be in its own cube and combined with other measures in a virtual cube. This restriction no longer exists and you may not be happy with the workaround you have to perform.

  • Are you using a lot of virtual dimensions and virtual cubes? These will be migrated to actual dimensions and actual cubes. With virtual dimensions, this is probably not what you want because the column can easily be created as an attribute dimension. With virtual cubes, you will probably need to clean up either the virtual cube or the base cubes because the data has now been data duplicated. If cleanup is going to be extensive, you are probably better off redesigning the cube.

  • Do you like your cube design but would like to use the new features provided in Analysis Services 2005? If you are happy with your cubes and feel that they will work with the new design requirements of Analysis Services 2005, then you should use the Migration Wizard to move your data to the new version. You can then add the new features you want. For a comprehensive list of the new features, see the product documentation.

Conclusion

With the Unified Dimensional Model (UDM), the bridge between relational reporting and OLAP reporting is complete. The new design choices you have with Analysis Services 2005 open the door to limitless design opportunities and significantly enhance the end-user analytical and reporting experience. The Migration Wizard is an excellent tool that can get you started along that path. By using the Migration Wizard, OLAP databases can be ported to the new version very easily so you can start leveraging the new features. The Migration Wizard does its best to recreate your current cubes in Analysis Services 2005. In the end, you may decide that the design changes are so significant that you would prefer to start from scratch rather than use the Migration Wizard. The decision between redesign and the wizard is best made by assessing what you currently have and how much you want it to change in Analysis Services 2005.

For more information:
https://www.microsoft.com/sql/

Download

Cc917610.icon_word(en-us,TechNet.10).gifREAL AS-2005-Migration.doc
241 KB
Microsoft Word file

Get Office File Viewers