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.