The Issue

Recently one of our team members faced the following issue:

• We have SSAS Tabular model deployed in DirectQuery mode taking data from MSSQL server database.
• Suddenly SSAS Tabular model started to give us the following cryptic message when we were trying to connect
  • “Unable to convert one or multiple M partitions into native source queries.”

Understanding

Unfortunately, this message does not help too much. You do not know which partition or what the problem really is. I’m sure this can have multiple causes that’s why the message is so generic, but a list of wrong partitions would be helpful.

As I had no idea who changed what and there were many tables in the model, I just tried to find the right table by interval halving method. In my case I just removed half of the tables in tabular editor and checked to see if the issue disappeared. And then did it again with the half which contained the issue. In each step you then eliminate 50% from the list of suspects. In several steps, instead of walking through each table, I was able to identify the right one, which was causing the issue.

On the first look there was nothing wrong with the underlying M query. However, when opening the design of the query I could see the following message which did not help much either.

“This step results in a query that is not supported in DirectQuery mode.”

But the query is a simple select from a database view. So, I do not understand what is not supported.

Resolution

I was about to check the DB view … and I did not find it! Someone from other developers removed it for some reason.

There is no magic discovery in this issue. Just a lesson learned – when you see this message it might simply mean you are missing one of the underlying tables or views.