Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server Analysis Services 
Azure Analysis Services
Fabric/Power BI Premium
In SQL Server 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 for tabular models in Analysis Services.
Note
Tabular models have additional database compatibility levels that aren't applicable to multidimensional models. Compatibility level 1103 doesn't exist for multidimensional models.
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 values:
| Setting | Description |
|---|---|
| 1050 | This value isn't visible in script or tools, but it corresponds to databases created in SQL Server 2005 (9.x), SQL Server 2008, or SQL Server 2008 R2. Any database that doesn't have CompatibilityLevel explicitly set implicitly runs at the 1050 level. |
| 1100 | This value is the default for new databases that you create in SQL Server 2012 (11.x) or SQL Server 2017. You can also specify it for databases created in earlier versions of SQL Server 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 CompatibilityLevel set to 1100 get an additional property, StringStoresCompatibilityLevel, that lets you choose alternative string storage for partitions and dimensions. |
Important
Setting the database compatibility to a higher level is irreversible. After you increase the compatibility level to 1100, you must continue to run the database on newer servers. You can't roll back to 1050. You can't attach or restore an 1100 database on a server version that is earlier than SQL Server 2012 (11.x) or SQL Server 2017.
Prerequisites
Database compatibility levels are introduced in SQL Server 2012 (11.x). You must have SQL Server 2012 (11.x)SQL Server Analysis Services or higher to view or set the database compatibility level.
The database can't be a local cube. Local cubes don't support the CompatibilityLevel property.
The database must be created in a previous release (SQL Server 2008 R2 or earlier) and then attached or restored to a SQL Server 2012 (11.x)SQL Server Analysis Services or higher server. Databases deployed to SQL Server 2012 are already at 1100 and can't be downgraded to run at a lower level.
Determine the existing database compatibility level for a multidimensional database
You can view or modify the database compatibility level only 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 doesn't exist, the database is most likely at the 1050 level.
The next section provides instructions for viewing and modifying the XMLA script.
Set the database compatibility level in SQL Server Management Studio
Before raising the compatibility level, back up the database in case you want to reverse your changes later.
Using SQL Server Management Studio, connect to the SQL Server 2017SQL Server Analysis 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 opens in a new window.
Copy the following XML element:
<ddl200:CompatibilityLevel>1100</ddl200:CompatibilityLevel>Paste the element 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, select Execute on the Query menu or press F5.
Supported operations that require the same compatibility level
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 (11.x) database, you must port the SQL Server 2008 R2 database to a SQL Server 2012 (11.x) server and set the compatibility level to 1100.
Synchronizing servers is only supported for servers that share the same version and database compatibility level.
Next step
After you increase the database compatibility level, you can set the StringStoresCompatibilityLevel property in SQL Server Data Tools. This setting increases string storage for measures and dimensions. For more information about this feature, see Configure String Storage for Dimensions and Partitions.