Just a quick post regarding a strange problem I encountered while working on an SSAS Tabular model project. Built on the same Vertipaq technology as Power Pivot, it’s very easy to get started and produce quick, efficient data models. Unlike Power Pivot, which runs as an Excel add-in, SSAS Tabular models are developed in Visual Studio via the SQL Server Data Tools (SSDT) Business Intelligence add-on. However, once you load up the development environment, it’s almost exactly the same, the only difference really being that when you build a tabular model in VS, it is developed against a temporary cube on a pre-installed SSAS Tabular instance. Power Pivot just works entirely in memory, without requiring an SSAS instance.
Somewhere along the line, this different approach to the Tabular model meant that I started encountering an unusual error whenever I tried to edit any of my model’s tables. Once you create a table in your data model in either Power Pivot or SSAS Tabular, you can edit the properties of that table by changing the underlying query to bring more columns from the data source or change the data selected via filters. However, I found that whenever I tried to change the column definition in my tabular model, I was greeted with the following error:
OLE DB or ODBC error: Login failed for user 'xxxxxxxx'. 28000. A connection could not be made to the data source with the DataSourceID of '2741e576-ed3f-43dc-863b-17715374870b', Name of 'my_connection_name'. An error occurred while processing the partition 'Data_654aaced-1fd2-4dd3-9bbd-adb028448b83' in table 'Data_654aaced-1fd2-4dd3-9bbd-adb028448b83'. The current operation was cancelled because another operation in the transaction failed.
The weird thing is, that I can still process the table and load in new data from the data source, I just can’t change the table definition at all. This suggests that processing the table connects in a different manner to changing the table structure.
When you first open or create an SSAS Tabular project, it asks you to connect to an SSAS Tabular instance that can be used as a workspace. This is so that VS can generate a temporary tabular cube while you’re working away. If you connect to your SSAS Tabular instance using SQL Server Management Studio (SSMS), you’ll see the temporary cube on there. In the interest of whittling down the variables, I decided to make sure that I could process the tables okay from the temporary cube via SSMS. And guess what? Same error. That seemed to suggest that the connection between the temporary cube and the data source wasn’t right, and that processing my table in Visual Studio used a direct connection, while changing table definition via Table Properties goes through the temporary cube.
So, I checked the connections section of the temporary cube, and re-entered the password for my SQL login there, which worked!
It looks like Visual Studio hadn’t copied the password information from my connections in Visual Studio across to the temporary cube. Adding the password to the connection in the temporary cube solved the issue. I tried playing around with the “Save password” checkbox but neither option seemed to make any difference. As a quick test, I tried to process the tables again via SSMS, which also worked this time.
So, if you encounter the above error while trying to change your Table Properties in an SSAS Tabular project in Visual Studio, try the following steps:
Connectionstree item and right-click the offending connection, then
Connection Stringsetting, click the ellipsis and enter your password on the resulting dialog.
Test Connectionto confirm, and
OKout of there.
While this workaround gets past the issue, I’m not sure if there’s a setting somewhere to do this automatically, or why exactly it happens. If you ’ve run into this before, or you have some answers/better solution, please get in touch via the comments form below.