The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
When I try to connect to SSAS MD cubes live via Power BI Desktop (Version: 2.36.4434.362 64-bit (Juni 2016)) on a SSAS Server 2012 sp3 cu4 Enterprise, it seems to depend on the Cube structure whether I can connect.
I'll receive the following error when I connect to one of my bigger and more complex cube/ssas DB.
Error in PBI Desktop
Profiler trace
Profiler trace replay of error and power bi debug trace are here:
https://1drv.ms/f/s!AhC1qKKZerqI91WZrfbupfhBUdr7
The strange thing is when I connect to one of the less complex SSAS DBs/ Cubes on the same server the connections works.
The documentation says some feature like translations/formatted cells, display folders etc. are not supporter.
Does it mean you cannot connect if they are used or they cannot be leveraged?
Hope someone has a tip what’s wrong here.
Best regards
Simon
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.
When connecting SSAS in Power BI Desktop, even some features is not supported in current release, it just means those features/settings will not be enabled/loaded into Power BI, but you should still be able to connect SSAS database properly. In your provided trace log, it shows error on when executing "Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet" function.
Please connect this SSAS database in SSMS, and execute some MDX against it to see if there's any corruption on this "complex" cube.
Regards,
I think u mean I should run a DAX query not a DMX query in SSMS right?
This gives me indeed the very same error as with Power BI Desktop
The screenshot shows as well that the Cube from [ASTraceCube] works with DAX and [VerkaufsdatenControlling_SW] doesn't.
Further I would never the less not say that the cube from [VerkaufsdatenControlling_SW] is corrupt because all mdx/xmla access works.
Thanks for your support
Simon
Sorry for my mistake, I mean "MDX", not DMX query.
I can't see your screenshot and URL returns 404.
Is this [VerkaufsdatenControlling_SW] cube the "complex and bigger" one? You can query and execute XMLA against it properly?
Regards,
XMLA and MDX works DAX doesn't with the more complex cube [VerkaufsdatenControlling_SW]
On the same server with the Cube [ASTraceCube] MDX and DAX works.
Here is a screenshot from the Profiler with a simple DAX query an each Cube:
Best regards
Simon
Any Update? The Problem still exists and I am pretty sure that it has to be connect to a certain SSAS MD feature we are using. Because as described some cubes work with Power BI Desktop and some don't on the same SSAS Server.