Connecting SharePoint Excel Services To Teradata
Excel services is a powerful component of SharePoint when considering data display and manipulation. Teradata is a powerful sharding data source system that has the capability to help store insane amounts of data. Combining the two is pretty cool, but following the default mechanisms of Excel services often times ends up bubbling up a series of errors. Even if you go through everything related to the connection; trusted data connections library, trusted excel files library, trusted data providers etc. The most common being an unrecognized provider being used to access the external data. The exact error I have run into the most will take on the form:
The workbook ‘<site URL>/blah.xlsx’ attempted to access external data using the unsupported provider ‘DSN=Teradata;UID=username;DATABASE=database name’.
You won’t see this if you are hoping Teradata ala SSAS.
To get this going, there are a few things that you have to ensure at setup correctly. Firstly, you must verify the DSN’s on the SharePoint WFE(s). Determine whether there are two DSN’s with the same value, for example in the user and system DSN entry. If there are, delete the user DSN, Excel Services will leverage the system DSN. Secondly, open up the connection file for Teradata. Find the following entry:
All logic will tell you that this should be set to None. But, just remove the whole line. Should be good to go after that.