Set the Compatibility Level of a Multidimensional Database (Analysis Services)
In Analysis Services, the database compatibility level property determines the functional level of a database. Compatibility levels are unique to each model type. For example, a compatibility level of
1100 has a different meaning depending on whether the database is multidimensional or tabular.
This topic describes compatibility level for multidimensional databases only. For more information about tabular solutions, see Compatibility Level (SSAS Tabular SP1).
Compatibility Levels for multidimensional databases
Currently, the only multidimensional database behavior that varies by functional level is string storage architecture. By raising the database compatibility level, you can override the 4 gigabyte maximum limit for string storage of measures and dimensions.
For a multidimensional database, valid values for the
CompatibilityLevel property include the following:
|This value is not visible in script or tools, but it corresponds to databases created in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. Any database that does not have |
|This is the default value for new databases that you create in SQL Server 2012 or SQL Server 2014. You can also specify it for databases created in earlier versions of Analysis Services to enable the use of features that are supported only at this compatibility level (namely, increased string storage for dimension attributes or distinct count measures that contain string data).|
Databases that have a
Database compatibility levels are introduced in SQL Server 2012. You must have SQL Server 2012Analysis Services or higher to view or set the database compatibility level.
The database cannot be a local cube. Local cubes do not support the
The database must have been created in a previous release (SQL Server 2008 R2 or earlier) and then attached or restored to a SQL Server 2012Analysis Services or higher server. Databases deployed to SQL Server 2012 are already at
1100 and cannot be downgraded to run at a lower level.
The only way to view or modify the database compatibility level is through XMLA. You can view or modify the XMLA script that specifies your database in SQL Server Management Studio.
If you search the XMLA definition of a database for the property
CompatibilityLevel and it does not exist, you most likely have a database at the
Instructions for viewing and modifying the XMLA script are provided in the next section.
Before raising the compatibility level, backup the database in case you want to reverse your changes later.
Using SQL Server Management Studio, connect to the SQL Server 2014Analysis Services server that hosts the database.
Right-click the database name, point to Script Database as, point to ALTER to, and then select New Query Editor Window. An XMLA representation of the database will open in a new window.
Copy the following XML element:
Paste it after the
</Annotations>closing element and before the
<Language>element. The XML should look similar to the following example:
</Annotations> <ddl200:CompatibilityLevel>1100</ddl200:CompatibilityLevel> <Language>1033</Language>
Save the file.
To run the script, click Execute on the Query menu or press F5.
The following operations require that the source databases share the same compatibility level.
Merging partitions from different databases is supported only if both databases share the same compatibility level.
Using linked dimensions from another database requires the same compatibility level. For example, if you want to use a linked dimension from a SQL Server 2008 R2 database in a SQL Server 2012 database, you must port the SQL Server 2008 R2 database to a SQL Server 2012 server and set the compatibility level to
Synchronizing servers is only supported for servers that share the same version and database compatibility level.
After you increase the database compatibility level, you can set the
StringStoresCompatibilityLevel property in SQL Server Data Tools. This increases string storage for measures and dimensions. For more information about this feature, see Configure String Storage for Dimensions and Partitions.