Dela via

  • Artikel

Migration Considerations (Analysis Services)

When you migrate from earlier versions of Microsoft SQL Server Analysis Services to SQL Server 2008 Analysis Services, you must be aware of changes in features, functionality, structure, and behavior. This overview topic provides information about common migration considerations that relate to data mining and the Analysis Services engine. For more information about specific areas of migration considerations, see the following topics:

Analysis Services Migration Considerations

Analysis Services Objects

The following table describes how SQL Server 2000 Analysis Services objects correspond to SQL Server 2008 Analysis Services objects.

SQL Server 2000 Analysis Services object

SQL Server 2008 Analysis Services object

More information

Aggregation

Aggregation

Only aggregations for hybrid OLAP (HOLAP) and multidimensional OLAP (MOLAP) partitions are migrated.

Analysis Services database

Analysis Services database

None.

Command

MDX script

A Multidimensional Expressions (MDX) script is written for the object that contains the command collection.

Cube

Cube

One data source view is also created for all dimensions, virtual dimensions, and cubes that share the same data source. A data source view is then created for each mining model and data mining dimension.

Cube and database role commands

Not migrated

None.

Cube role

Cube permission

Dimension and cell permissions are not migrated.

Data mining dimension

Data mining dimension

A data source view is also created for each data mining dimension.

Data source

Data source

None.

Database role

Database role and database permission

None.

Dimension

Dimension with a single hierarchy

None.

Dimensions that share the same name prefix (representing multiple hierarchies)

Separate dimensions, each with a single hierarchy.

MDX expressions that refer to the original hierarchy names no longer function.

Level in a hierarchy

Attribute in the dimension, and also a level in a user-defined hierarchy.

None.

Linked cubes

Not migrated

None.

Member property on a level

Member property on a level

None.

Mining model

Mining model

A data source view is created for each mining model in a database.

Mining model role

Migrated as two permissions: one for the mining structure and one for the mining model.

None.

Multiple parent-child hierarchies in a dimension

Single parent-child hierarchy, which is defined by settings on the parent attribute.

MDX expressions that refer to the original hierarchies no longer function. A SQL Server 2008 Analysis Services dimension can only have a single parent-child hierarchy.

Remote partitions

Not migrated.

Remote partitions are unavailable until the server on which they are located is upgraded or the database that contains the remote partition is migrated. Databases that contain remote partitions can be migrated individually. The definition of the remote partition must be updated to identify the name of the new server instance.

Repository

Object Definition Language (ODL) files in XML format

None.

Virtual cube

Linked measure groups

None.

Virtual dimension

A hierarchy of the source dimension

Virtual parent-child dimensions are migrated as separate dimensions, because parent-child dimensions cannot have multiple hierarchies.

Custom Level Formulas

In SQL Server 2005 Analysis Services (SSAS), assignments replaced calculated cells and custom level formulas. Calculated cells are supported in SQL Server 2008 Analysis Services and are migrated from SQL Server 2000 Analysis Services as defined, but custom level formulas are migrated as assignments.

In SQL Server 2008 Analysis Services, the calculation pass is automatically incremented with each pass. Therefore, if a cube contains custom level formulas, make sure that you verify that the results are the same as intended.

Data Source Views

Data source views were introduced in SQL Server 2005 Analysis Services (SSAS). A data source view differs from the cube schema in SQL Server 2000 Analysis Services in that the data source view is a logical representation of the source data from which multiple cubes can be defined, whereas the cube schema in SQL Server 2000 Analysis Services is a physical representation of relationships between tables for each cube.

Migrating Multiple-Hierarchy Dimensions

The model for multiple-hierarchy dimensions changed from SQL Server 2000 Analysis Services to SQL Server 2005 Analysis Services (SSAS). Wherever possible, such hierarchies are migrated to the same dimension, but there are circumstances when this is not possible. When this occurs, calculations that rely on the original dimension hierarchy might fail.

SQL Syntax

The means of stating SQL queries has changed in SQL Server 2005 Analysis Services (SSAS). The Backus-Naur Form (BNF) for the new syntax is used in Books Online.

Schema Rowset Changes

Many schema rowsets were augmented in SQL Server 2005 Analysis Services (SSAS) to provide additional information. Most of the time, the position and meaning of existing rowsets and columns remain exactly as in SQL Server 2000 Analysis Services. The following table describes the schema rowset changes that occurred in SQL Server 2005 Analysis Services (SSAS).

Schema

Column

Change

DBSCHEMA_TABLES_INFO

All

This schema is no longer supported.

MDSCHEMA_CELL_FORMULAS

All

This schema is no longer supported.

MDSCHEMA_DIMENSIONS

DIMENSION_CARDINALITY

This column returns the number of dimension key attribute members.

MDSCHEMA_MEMBERS

LEVELS_LIST

This schema column contains null.

MDSCHEMA_MEMBERS

MEMBER_ORDINAL

This column is deprecated. The value 0 is returned instead of the member ordinal.

Unique Member Names

In SQL Server 2005 Analysis Services (SSAS), unique member names were updated and they now differ from those in SQL Server 2000 Analysis Services. Because of restrictions on the unique member names, retrieving results from the members schema by using the original unique name might not return the same results as in SQL Server 2000 Analysis Services.

Data Mining Migration Considerations

The Migration Wizard transfers all the mining models from the SQL Server 2000 Analysis Services database to the destination SQL Server 2008 Analysis Services database. For each mining model in the source database, the Migration Wizard creates a new mining structure in the destination database. The mining structure contains exactly one model, and that model has the same name as the mining model in the source database.

To migrate individual mining models from SQL Server 2000 Analysis Services to an instance of SQL Server 2008 Analysis Services, you can use the Predictive Model Markup Language (PMML). The following example demonstrates how to retrieve the PMML representation, in the form of an XML script, of a SQL Server 2000 Analysis Services mining model:

SELECT * FROM [Model_Name].PMML

You can obtain the same information by retrieving the OLE DB schema, DMSCHEMA_MINING_MODEL_XML.

You can use the PMML script to create a new mining model in an instance of SQL Server 2008 Analysis Services by using a statement similar to the following:

CREATE MINING MODEL [Model_Name] FROM PMML '<PMML script>'

If you use the Analysis Services Migration Wizard to migrate your mining models, be aware that the wizard only supports the migration of metadata. Therefore, migrated mining models are unprocessed. However, the models still contain original data bindings, so that you can process them in SQL Server 2008 Analysis Services after migration. On the other hand, if you use the PMML to migrate the models, the original data bindings are not preserved. Therefore, you cannot reprocess the migrated models. Nevertheless, the models are ready to use, because the PMML includes the patterns and trends that were discovered by the source model.

Known data mining migration issues:

  • Mining models that have multiple case-level keys with multiple keys in a nested table cannot be migrated.

  • Mining models that were created by using aggregated data mining providers cannot be migrated.