Tabular cube development is great. But like any other part of MSBI, you 'll run into issues from time to time. Recently I generated the following error while attempting to process a tabular cube....
OLE DB or
ODBC error: A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not accessible.
Check if instance name is correct and if SQL Server is configured to allow
remote connections. For more information see SQL Server Books Online.; 08001;
Client unable to establish connection; 08001; Encryption not supported on the
client.; 08001.
A
connection could not be made to the data source with the DataSourceID of
'8e03aac2-05c6-4712-87d4-af620a257aeb', Name of 'SqlServer <Server and/
Connection Name>.
An error
occurred while processing the partition
'dimXXXXX_74d208d3-9774-4094-83c1-a7e7615bfcc0' in table 'dimXXXXX_74d208d3-9774-4094-83c1-a7e7615bfcc0'.The current operation was cancelled because another operation in the transaction failed.
This occurred shortly after making subsequent updates to the cube across several weeks. My initial thought was that something was corrupted with the project files since it was all working just fine a week ago.
To resolve this issue, I had to first remove any Connections
that were not being used. This included two of the five connections I created in the project. You can
remove the connection string if there are no tables tied to the connection. To determine if there are tables connected to the connection,
you could simply try to delete it. To do this, try the following…
- In the Menu, Select Model
- Select Existing Connections…
- In the Select Existing Connection Dialog box, Attempt to delete a connection…The Tabular Model Designer will give you can error indicating “One or more tables are still associated with the data source that is being deleted. Remove the associated tables before deleting the data source: ….” It will list the associated tables.
- If the Connection is allowed to be deleted, it is safe because that connection is not tied to any tables imported
One that was done, I then changed all the connection strings
from using the SQL Server Native Client 11.0 (which is fine as
long as the SQL Server is on the same machine as the SSIS development / package
environment), but to the Microsoft OLE DB Provider for SQL Server which
is a bit more flexible (for local and remote servers).
Both of these together resolved my issue.
But the most important thing is how did I get to have some
many connections?
Well this was caused by improperly adding the new tables. When I would add new tables to the existing tabular cube, I would mistakenly start by
selecting “Import from Data Source” instead of correctly using “Existing Connections”. When you specify Import from Data Source you will
then be forced to add a new Connection which will increase the list of
Connections. If you do this enough, you have multiple connection strings that
are all the same other than the connection name. This gets really annoying when you process
the cube and are prompt for a password for EACH connection. This also
potentially introduces annoying issues this discussed earlier.
