Breaking Changes to Analysis Services Features in SQL Server 2008 R2

Notes

For SQL Server 2008 R2, there have been no changes to the content that is listed in this topic.

This topic describes breaking changes in Analysis Services. These changes might break applications, scripts, or functionalities that are based on earlier versions of SQL Server. You might encounter these issues when you upgrade. For more information, see Using Upgrade Advisor to Prepare for Upgrades.

Breaking changes to Analysis Services in SQL Server 2008

The following table lists the issues that you might encounter when you upgrade a SQL Server 2005 Analysis Services (SSAS) Analysis Services database to SQL Server 2008.

Issue Type

Issue Description

The shallow exists function now works differently with named sets that contain enumerated members or crossjoins of enumsets.

In SQL Server 2005 Analysis Services (SSAS), the shallow exists function did not work with named sets that contained enumerated members or crossjoins of enumsets. For backward compatibility with the original release version and SP1 of SQL Server 2005 Analysis Services (SSAS), set the configuration property "ConfigurationSettings\OLAP\Query\NamedSetShallowExistsMode" to 1, or for backward compatibility with SQL Server 2005 Analysis Services (SSAS) SP2, set it to 2.

VBA functions handle null values and empty values differently than they were handled in SQL Server 2005 Analysis Services (SSAS) 

In SQL Server 2005 Analysis Services (SSAS), VBA functions returned 0 or an empty string when either null values or empty values were used as arguments. In SQL Server 2008, they will return null.

The Migration Wizard will fail because DSO is not installed by Default.

By default, SQL Server 2008 does not install the DSO (Decision Support Objects) backward compatibility component. The backward compatibility package is installed by default but the DSO component of the package will be disabled. Since the SQL Server Analysis Services Migration Wizard relies on this component, it will fail unless the component is installed. To install the DSO component, do the following:

  1. Open Control Panel.

  2. In Windows XP or Windows Server 2003, select Add or Remove Programs. In Windows Vista and Windows Server 2008, select Programs and Features.

  3. Right-click Microsoft SQL Server 2005 Backward Compatibility, and select Change.

  4. In the Backward Compatibility Setup wizard, click Next.

  5. On the Program Maintenance page, select Modify, and then click Next.

  6. On the Feature Selection page, if Decision Support Objects (DSO) is not available, click the down arrow and select This feature will be installed on local hard drive. Click Next.

  7. On the Ready to Modify the Program page, click Install.

  8. When installation is finished, click Finish.

You can remove DSO after migration is complete by following the previous steps, changing the option for DSO to “This feature will not be available.”

If the backward compatibility package is not installed, you can install it from the SQL Server 2008 distribution media. Note that there are versions for each target architecture (x86, x64, ia64). These versions can be found at the following locations:

x86\Setup\x86\SQLServer2005_BC.msi

x64\Setup\x64\SQLServer2005_BC.msi

ia64\Setup\ia64\SQLServer2005_BC.msi

It is not recommended to put the partition location in the Data folder.

The server manages the Data folder and creates or drops folders as objects are created, deleted, and altered. Therefore, specifying a partition storage location inside the Data folder is strongly discouraged, especially in the subfolders for databases, cubes, and dimensions. Although the server allows you to do this with Create or Alter, it will display a warning. When you upgrade databases from SQL Server 2005 Analysis Services to SQL Server 2008 Analysis Services that have partition storage locations in the Data folder, it will work. Restore or Sync will require that you move partition storage locations outside the Data folder.

Breaking changes to Analysis Services in SQL Server 2005

The following table lists the issues that you might encounter when you upgrade a SQL Server 2000 Analysis Services database to SQL Server 2008.

Issue Type

Issue Description

An object depending on a linked object is not migrated

Linked cubes and linked dimensions are not migrated by Upgrade Advisor in Microsoft SQL Server 2008 Analysis Services. Therefore, objects that refer to a linked cube or a linked dimension cannot be migrated because the linked object on which the object is based cannot be migrated. For example, an OLAP mining model based on a linked cube cannot be migrated because the linked cube on which the mining model is based cannot be migrated.

Autoexist can produce different query results when multiple hierarchies are migrated into the same dimension

When multiple hierarchies or virtual dimensions are migrated into the same Microsoft SQL Server 2008 Analysis Services dimension, querying the migrated hierarchies contained in the dimension may produce different results than querying the same hierarchies when they were in SQL Server 2000 Analysis Services, because autoexist functionality automatically removes tuples that do not exist in the dimension from any cross-join of sets containing members from the migrated hierarchies. To resolve this issue, you should review calculations that involve multiple hierarchies in the same dimension.

Browsing experience is different when disabled levels are used

Since SQL Server 2005 Analysis Services, hidden or disabled levels in hierarchies are no longer supported. Hidden or disabled levels are migrated as visible and enabled levels. Calculations that involve hierarchies containing such levels might return unexpected results. After upgrading, review and verify calculations that involve hierarchies that previously contained hidden or disabled levels.

Bucketing may be different for grouping levels

Since Microsoft SQL Server 2005 Analysis Services (SSAS), automatic grouping in might return a different set of member groups. Calculations that rely on these member groups might return unexpected results. After upgrading, review and verify calculations that rely on member groups.

Conversion from neutral language to specific language may produce unexpected results

In SQL Server 2000 Analysis Services and earlier versions, Analysis Services used only neutral language identifiers, also known as primary language identifiers, for example LANG_ENGLISH (0x09) for English and LANG_CHINESE (0x04) for Chinese.

To support translation and collation options, Analysis Services now uses specific language identifiers, which are a combination of a primary language identifier and a sublanguage identifier used for a specific culture. For example, the combination of the primary language identifier LANG_ENGLISH (0x09) and the sublanguage identifier SUBLANG_ENGLISH_AUS (0x03) describes Australian English.

Migrating from neutral to specific language identifiers can change the expected translation and collation behavior, producing unexpected results. After upgrading, review and validate objects such as dimensions, hierarchies, and members for which the language identifier has changed.

Cube role commands are not supported

Microsoft SQL Server 2008 Analysis Services does not support command objects on cube roles and will not migrate commands from previous versions.

Custom level formulas aggregate differently

If a cube contains a dimension with custom level formulas, and also contains dimensions both before and after it with custom member formulas and/or unary operators, then the cube may return different results than previous versions of Analysis Services. This occurs because calculation precedence rules have changed.

Custom member formulas and custom rollup formulas are migrated into MDX script

In previous versions of Analysis Services, the following properties are supported on dimensions and levels: custom rollup formulas, custom member formulas, All member formulas, and custom level formulas. In SQL Server 2005 Analysis Services, these properties were replaced by functionality supported in Multidimensional Expressions (MDX) scripts, and are upgraded to MDX scripts during migration.

Custom aggregations are not migrated

Aggregations that were manually generated in previous versions of Analysis Services are not migrated in Microsoft SQL Server 2008 Analysis Services. Only aggregations generated by the Storage Design Wizard are migrated. To resolve this issue, manually create the aggregations by using XMLA scripts.

Data members always exist in parent-child dimensions

Previous versions of Analysis Services gave you the option of not including data members in parent-child dimensions. The DataMembers property of a dimension in previous versions of Analysis Services supported three options: None, Hidden, or Visible. The None option is not available in Microsoft SQL Server 2008 Analysis Services. Data members are always included in parent attributes. To hide the data members in parent-child dimensions, you can set the DataMembers property of a dimension to Hidden. The MembersWithData property for the parent attribute supports only two options: NonLeafDataHidden or NonLeafDataVisible.

Database role commands are not supported

Microsoft SQL Server 2008 Analysis Services does not support command objects on database roles and will not migrate commands from previous versions of Analysis Services.

DefaultMember is migrated into MDX script

In previous versions of Analysis Services, the default member of a dimension is specified by a Multidimensional Expressions (MDX) expression, contained in the DefaultMember property of the dimension. In Microsoft SQL Server 2005 Analysis Services (SSAS), this property was replaced by functionality supported in MDX scripts, and the property is upgraded to a MDX script during migration.

Dimension and hierarchy renaming by migration may cause different query results

Dimension hierarchies in SQL Server 2000 Analysis Services are internally represented as separate dimensions, and a naming convention is used to identify them. Migration to Microsoft SQL Server 2008 might create a separate dimension, with a new name, for each dimension hierarchy instead of combining the dimension hierarchies together under the parent dimension because auto-exist results in different security rules than would apply in earlier versions of Analysis Services.

Drillthrough settings are not migrated

While drillthrough exists in Microsoft SQL Server 2008 Analysis Services, drillthrough settings are not migrated from previous versions of Analysis Services.

Hidden levels are visible after migration

Analysis Services no longer supports hidden or disabled levels in hierarchies. Hidden or disabled levels are migrated as visible and enabled levels. Calculations that involve hierarchies containing such levels might return unexpected results.

Invalid objects cause migration to fail

The database contains invalid objects. Migration can not be completed when the database contains invalid objects.

Linked cubes are not migrated

Previous versions of Analysis Services supported linked cubes. In Microsoft SQL Server 2005 Analysis Services (SSAS), this feature has been replaced by linked dimensions and linked measure groups.

Member unique names may change during migration

Analysis Services attempts to preserve the unique names of members during migration, but there are certain circumstances in which the unique name for a member is changed. If member unique names change, client applications, Multidimensional Expressions (MDX) expressions, and other properties that depend on member unique names may produce unexpected results.

ODBC data sources are not supported

While previous versions of Analysis Services allowed you to use ODBC data sources, this functionality is no longer supported.

Remote partitions are not migrated

Remote partitions are not migrated from Microsoft SQL Server 2000 Analysis Services to Microsoft SQL Server 2008 Analysis Services. Upgrade the server to SQL Server 2008 Analysis Services and then manually create the remote partitions.

Some mining model algorithm parameters are not supported

Previous versions of Analysis Services support using the MINIMUM_LEAF_CASES parameter with the Microsoft Decision Trees algorithm, and the MINIMUM_CLUSTER_CASES parameter with the Microsoft Clustering algorithm. Since SQL Server 2005 Analysis Services, both of these parameters have been renamed to MINIMUM_SUPPORT. If these parameters were used in mining models created using the previous version of Analysis Services , the parameters are not migrated.

The behavior of some MDX functions is changed

Due to enhancements and changes in the Multidimensional Expressions (MDX) language for Microsoft SQL Server 2005 Analysis Services (SSAS), certain MDX functions are either no longer supported, or behave differently than in previous versions of Analysis Services.

The CREATE KPI Command Introduces a New Keyword

A new keyword, KPI, has been introduced to the CREATE KPI command. If existing objects have the name KPI, then the new keyword will conflict with them.

Top Level for dimension security is not supported

In previous versions of Analysis Services, you could specify dimension security so that a user saw a top level different than the top level of the hierarchy. Members that are secured using the Top Level setting will be visible after migration.

UDF source .DLLs are not migrated and registered

User Defined Functions (UDFs) registered on previous versions of Analysis Services are not migrated.

Unary operators on non-parent-child hierarchies have limited support

Since Microsoft SQL Server 2005 Analysis Services (SSAS), unary operators on hierarchies other than parent-child hierarchies are not supported unless there is an attribute relationship between the attributes sourcing the levels in the hierarchy.

Virtual cubes are not supported

Previous versions of Analysis Services supported virtual cubes, a combined view of a subset of measures and dimensions from one or more regular or linked cubes. In Microsoft SQL Server 2005 Analysis Services (SSAS), this feature was replaced by linked measure groups. During upgrading, virtual cubes are migrated as linked measure groups.

Writeback table content is not migrated

The structure for writeback tables constructed by previous versions of Analysis Services was updated in Microsoft SQL Server 2005 Analysis Services (SSAS). Data from writeback tables constructed by previous versions of Analysis Services cannot be migrated into the new writeback table structure.