Despite its many advantages Tabular Analysis Services represents a step backwards in developer experience, particularly in the feedback following an exception.

This is a short list of some of the more head-scratching error messages that occur when processing a Tabular model (Version 1500). These are messages shown in the designer dialogs but the same messages are returned when processing on a server.

“key didn’t match any rows in the table”

Full error message (always the same):

Failed to save modifications to the server. Error returned: ‘OLE DB or ODBC error: [Expression.Error] The key didn’t match any rows in the table..

Meaning The table or view does not exist in the source.

Perhaps the table or view was renamed or dropped or Analysis Services does not have select permission on it.

Discussion

The term “key”, which is prevalent in data warehousing, and the phrase “rows in the table” strongly suggest that the problem is some mismatched value in the data. Well, it isn’t: the “key” and “rows” are something internal to Analysis Services – and Tabular does not check referential integrity in any case. The source table or view itself cannot be found.

Which table or view, though? Although the processing dialog box lists each item on its own line, if one item fails then all items are marked as failures, which gives no hint as to which one is the root cause. Each item has an “Error description link”, but the message behind each link is exactly the same (see below).

It is left to the user to find which item is unavailable. This is a particularly poor experience. The only way to get the designer interface to reveal which table it is is to attempt to process each item individually until the miscreant is found.

“The xxx column does not exist in the rowset”

Failed to save modifications to the server. Error returned: ‘The ‘Amount’ column does not exist in the rowset.

Meaning: one of the referenced columns could not be found in the source table or view.

This at least states which column is missing but it doesn’t say which table which can still lead to a bit of hunting.

“Type mismatch”

Failed to save modifications to the server. Error returned: ‘OLE DB or ODBC error: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Meaning Some column of some table contains data of the wrong type

Discussion

As usual, there is no indication of which column or which table. Note that Tabular does not check source data types from column metadata, hence the emphasis above. It only checks the incoming data, if any (according to my experiments). If a column has the wrong data type but has no data (only nulls) there will be no error.

If the incoming data can be coerced into the destination type there will also be no error. The exception is only raised once Analysis Services finds some data that it cannot manipulate to fit the destination data type. In this example the processing failed when character values were supplied for a numeric destination column in the Tabular model. On the other hand, incoming date-time values will be coerced into a numeric column without error.

Blank values not allowed for…

The full message is

Failed to save modifications to the server. Error returned: ‘Column ‘Date’ in Table ‘Date’ contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

and yet the column mentioned is not involved in a relationship and is not a primary key (nor marked as unique).

There is another reason not mentioned in the error message: the table is marked as a date table and the column mentioned is the date column.

Conclusion

Analysis Services Tabular does its best to make enterprise development difficult. But enterprise techniques help. Good source control of both source database and the Analysis Services project helps to track what changed to cause the problems and allows reversion to last known good.

After a while you become conditioned to the way it works and I should say that development is not normally as frustrating as this article might suggest (although Tabular has plenty of other annoyances to offer). It is in those moments, normally after a busy day, when you develop a temporary blindness to something basic that these inadequate messages take their toll, and you end up longing for the more complete and verbose exception messages of a more mature product.