Differences between local cubes created with Analysis Services in SQL Server 2000 and SQL Server 2005

The following chart shows the primary differences between local cube files created with SQL Server 2000 and those created with SQL Server 2005/2008.

Feature	SQL Server 2000	SQL Server 2005/2008
New Analysis Services 2005/2008 features – Translations, Attributes, Measure Groups, KPI’s, MDX Script	Not available	Supported
Password-protected encryption for local cube files	No	Yes
Creation of local cube files with no data.	Not supported	Supported
Inclusion of members that have no data in a local cube	No	Yes
Multiple users allowed to use a local cube file at the same time.	Yes	No
Unary Operators	Do not work properly when used with parent-child dimensions	Supported
Custom Rollup Formulas	No	Supported

Password-protected encryption for local cube files

Analysis Services 2005 and 2008 both give the option of encrypting a local cube file. The same password used to encrypt the file must be used to decrypt it. This password is included in the connection string as the Encryption Password parameter both when creating the local cube file and when opening it.

New Analysis Services 2005 features – Translations, Attributes, Measure Groups, and KPI’s

One of the main advantages of the Analysis Services 2005 local cube files (as well as 2008)over Analysis Services 2000 local cube files is the ability to include the new Analysis Services 2005 features. Translations, attributes, measure groups, and KPI's are all fully supported in local cube files.

Creation of local cube files with no data

In SQL Server 2000, the creation of a local cube file fails when there is no data in the local cube. This can happen when multiple slicers are used and the combination of slicers eliminates all the data.

In SQL Server 2005/2008, a local cube file can be created with no data. When browsing a local cube file with no data, you can view the members of the levels, but no cells are displayed.

Inclusion of members that have no data in a local cube

In SQL Server 2000, individual members are not included in the local cube if they have no records in the fact table. This can cause problems, especially when using calculations with time dimensions.

In SQL Server 2005/2008, all members are included in the local cube, even if they do not have records in the fact table. This behavior can cause the AS2005/AS2008 local cube file to be much larger than a comparable AS2000 local cube. CubeSlice Relational ASSL allows the unused members to be excluded.

Multiple users allowed to use a local cube file at the same time

In SQL Server 2000 local cubes, many users can use one local cube file at the same time.

SQL Server 2005/2008 local cube files can only be used by a single user at a time. A single application can, however, open up to five connections simultaneously.

Unary Operators

In SQL Server 2000 local cube files, unary operators do not work properly with parent-child dimensions.

SQL Server 2005/2008 local cube files display unary operators properly.

Custom Rollup Formulas

SQL Server 2000 does not support the creation of local cube files with custom rollup formulas.

Custom rollup formulas are fully supported in SQL Server 2005/2008 local cube files.