Migration Considerations (Analysis Services)

Migration Considerations (Analysis ...

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online

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.