Error: Multidimensional databases support CompatibilityLevel 1050 , 1100 only?

Question

Hello, I have an SSAS cube that was created in SQL Server 2012 and has a compatibility level of 1100. The job to refresh data and the cube was running daily but the data grew significantly in the last year. I changed the compatibility level of the datawarehouse database that is the data source for the cube from 2012 to 2016 to 2019 as I wanted to take advantage of columnstore indexes. With 150 compatibility the ad-hoc queries/reports over fact tables sped up significantly but the processing of the cube slowed down - takes 2 days now - which defeats the purpose of timely reporting. (When they are both in 2012 compatibility it takes 25-26 hours to process the cube which is also more than day). I was (probably erroneously) thinking that if I could change the compatibility level of the multidimentional database to match the compatibility level of the data source database, it could shorten the cube processing time. I opened the SSAS project in Visual Studio 2019 and changed the Deployment Server Version to 15.0 on the Properties build tab, pointed to my 2019 Server, rebuilt the project and tried to redeploy. I received an error:

"Severity   Code    Description Project File    Line    Suppression State
Error       The ddl500:DerivedFromColumnId element at line 2092, column 44 (namespace http://schemas.microsoft.com/analysisservices/2013/engine/500) cannot appear under Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Dimensions/Dimension/Attributes/Attribute.         0 "

I did not find any relevant information about this error. I also tried right-clicking on the cube database in SSAS and chose to script it as ALTER - not much info there in the XMLA apart from seeing the ddl500 under the Database tag:

<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">

On the Analysis Server Properties Information tab - it lists Version 15.0.32.50, the Default Compatibility level of 1100 and Supported Compatibility Levels of 1050,1100 but I read somewhere in microsoft documentation that that was just a bug in display and it should have listed all supported compatibility levels up to SQL Server 2019.
My question: is it even possible to change the compatibility of a multidimetional database to the latest server compatibility level which is 2019?
Thank you

Answer 1

Hi @Latinsky, lana ,

Welcome to Microsoft Q&A!

My question: is it even possible to change the compatibility of a multidimetional database to the latest server compatibility level which is 2019?

As dgosbell said, you cannot do this. For a multidimensional database, valid values for the CompatibilityLevel property include 1050 and 1100. 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. Please refer to Compatibility Level of a Multidimensional Database (Analysis Services) to get more information.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

On the Analysis Server Properties Information tab - it lists Version 15.0.32.50, the Default Compatibility level of 1100 and Supported Compatibility Levels of 1050,1100 but I read somewhere in microsoft documentation that that was just a bug in display and it should have listed all supported compatibility levels up to SQL Server 2019.

Can you provide the link to this information? It's possible that whatever you read referred to the compatibility levels for Tabular models, not multi-dim as multi-dim only goes up to 1100.

My question: is it even possible to change the compatibility of a multidimetional database to the latest server compatibility level which is 2019?

No there is no such thing in multidimensional.

Raising the compatibility levels does not always make things faster. It usually just changes what features are available. In SQL there was a change at one point that enabled the new cardinality estimator which can make things faster, but there are also cases where it makes some queries slower. I do not think there is anything like this in the SSAS compatibility levels that significantly alters processing performance.

Answer 3

Thank you, very much to those who answered.

Answer 4

Why I was thinking compatibility matters is that the processing of the cube significantly slowed down when I added a columnstore index on the biggest fact table and only when in 2019 compatibility mode. Doesn't really make sense to me but this is what happened. Thank you to all