BI: (FIX) Failed to save modifications to the server. Error returned - The key didn't match any rows in the table

I was working with Azure Analysis Services the other day, and was having an issue with the processing of a tabular data model. When I first tried to process, I was receiving an error that told me pretty clearly that the AS server couldn’t connect to the SQL Database. Fair enough. Wasn’t sure what had nuked those credentials but reset them.

Next, I started to get this error: Failed to save modifications to the server. Error returned: ‘The key didn’t match any rows in the table

This turned out to be another whole issue. I’ve seen this error before, and generally it relates to the deployed data model schema no longer matching the schema of the table or view that it’s loading from.

So I checked thoroughly for that, to no avail. The schema matched perfectly.

Eventually I realised that although I’d fixed the credentials, I’d used the credentials that I had ADF connecting to the database as, not the credentials that AS was using to connect to the database.

And that was it.

AS could connect to the database, but it wasn’t seeing the views that it was meant to be loading from. The user that AS was connecting with, had no permissions to see them.

I would have hoped this would lead to a message telling me that it couldn’t find the source table or view, but it doesn’t. It leads to: “The key didn’t match any rows in the table”.

Far from obvious to me, and I hope it helps someone else.

2020-03-10