Understanding errors from Tabular Analysis Services

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).

Analysis Services Tabular. A table near the end of the list doesn’t exist in the source… but none of the copious error paraphernalia in these dialogs tells you that.

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.

A column is missing from one of these tables… but which? All tables repeat the same error message

“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.

HTTP Access to Analysis Services (multidimensional)

In retrospect this was a simple task: the steps set out in the invaluable video Configuring HTTP for SSAS  all work.  However, in the middle of a long process trying to get it to work the first time it seems anything but simple.  So here are some observations to help reassure you that it really is straightforward, even after a full day battling without success.

Environment

These observations are from configuring HTTP SSAS access in this environment:

  • Windows Server 2016 Datacentre (EC2 instance)
  • SQL Server Analysis Services 2017 Evaluation (developer) edition
  • IIS 10

both SSAS and IIS running on the same server.

Install Cumulative Update 9

The most important lesson is to install SQL Server CU9 or later.   The fault in msmdpump.dll is indicated in this MS article: FIX: Access to SSAS by using HTTP fails in SQL Server The article is dated 18 July 2018.

Here are some of the responses you will see if you are using the pre-CU9 version of the file – assuming the configuration is otherwise correct.

SSMS

The response is immediate.

Cannot connect to http://...url/.

File system error: The following error occurred while writing to the file 'MSOLAP ISAPI 
Extension: \\?\C:\inetpub\OLAP\msmdpump.dll': Access is denied. .
Internal error: An unexpected exception occurred. (Unknown)

Excel

Response is immediate.

XML parsing failed at line 1, column 1: Incorrect document syntax.
.
File system error: The following error occurred while reading from the file 'Compressed stream'.

Browser

The response to a browser http request is the same as from the correct DLL, so this cannot be used to determine whether the msmdpump.dll version is correct.

Successful Installation

Connecting to the HTTP address on the same server as IIS returns this:

This is the right response.  It’s a 501 error code, so this won’t be seen from browsers on remote hosts.

Other Errors

Excel will report an error message from the host quickly although some are replaced by a generic “check typing of username” friendly message from Excel.  SSMS might allow server-level nodes and some cube structure to be viewed in the explorer.  If a cube is visible, attempting to browse it can provide more error information (although hidden by default).

  • Either the user, ‘NT AUTHORITY\IUSR’, does not have access to the ‘<Cube Name>’ database, or the database does not exist.    –  The solution is to make IUSR a member of the Server Administrators of the SSAS instance.  IUSR does not (and should not, for security) need to be a member of any Role within the cube.

Other Observations

SSL Offloading

The HTTP endpoint handles SSL encoding on load balancer without problem.  No additional configuration is required: IIS is configured as an unsecured http site.

Resources