The Issue

Recently one of our team members faced 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 following cryptic message when we were trying to connect
  • “Unable to convert one or multiple M partitions into native source queries.”

Understanding

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

As I had no idea who changed what and there was bunch of tables in the model, I just tried to find right table by interval halving method. In my case I just removed half of the tables in tabular editor and see if issue disappear. And do again with the half which contains the issue. In each step you then eliminate 50% from the list of suspects. In several steps, instead of walking trough each table, I was able to identify 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 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 simple select from databse view. So I do not understand what is not supported.

Resolution

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

So there is no magic discovery in this issue. Just lesson learned – when you see this message it might simply mean you are missing one of the underlying table or view.