Error SSAS MD Connection on Server with Enterprise Edition depending on SSAS DB/ Cube

I solved my problem recently. Unfortunately, I did not find the root cause.

But here is what I did to solve the problem:

I took my non-working SSAS project and deployed the hole project into a test SSAS DB.

Then I processed only the structure.

Next I opened SSMS connect to the SSAS instance and opened a MDX query window:

Here I typed a simple DAXMD query like:

Evaluate 'Date'

If it works like it does in Adventure Works

It should look something like this:

If it doesn’t it should give you the error massage

"unexpected error"

If the error message is different either you need the following connection string property:

Or you are dealing with a different problem.

---------------------

From here on comes the part which involves some work depending on the size of your SSAS project (I assume from now on you received the "unexpected error" from advanced):

Open the non-working SSAS DB online which had only been processed with "process structure" in visual studio.

And then you need to start delete cube dimensions one by one.

After you deleted the first dimension do a process structure again and try your DAXMD query again in SSMS:

Evaluate 'YourDimension'

You need to repeat this until you find the dimension which causes the "unexpected error".

In my case it has been a role playing dimension.

Once I found the troubling dimension I went back to my source control version of the SSAS project removed the cube dimension in question and added it again. Be careful that you set all depending things like security, dimension usage, perspectives, action, aggregations etc. related to the deleted dimension back to way they had been.

Then I deployed the project again and now DAXMD and Power BI works.

I did a diff on the hole xmla of the SSAS DB and apart from the order of some codes parts I could not find any difference in the working and the non-working Cube.

Never the less no it works and I hope my instructions help to solve the problem for others as well.