Tips And Tricks For Configuring The Excel Services ODBC Driver By CDATA

[intapiuser]

If you organization has Excel documents stored within Sharepoint that you would like to bring into the ElastiCube Manager, you may chose to use a CDATA driver to bring this data in using an ODBC connection.

Below are some lessons learned in setting this up that are not documented in CData ODBC Driver for Excel Services documentation. All screenshots are from the ODBC connection interface when configuring a connection

1. Make sure to create the DSN under System DSN (not User DSN)

2. Within the Authentication section, use the screenshot below to guide filling out these settings

Special Notes:
- URL: Do not include the name of the file (that will be set later), also make sure there is no trailing slash at the end of the URL
- Use SSO: Use this guide on whether to set this to True or False

3. When the Folder or Library field is not supplied, the driver will default in the Shared Documents folder in the file path just before the name of the file. If Folder or Library is supplied, Shared Documents will not be added to the URL

4. Verify that you have item-level permissions using the steps outlined in the second reply to the forum post here.

5. Set the verbosity to 4 and designate a file path to a blank.txt file for logging. 

Verbosity of 4 is useful for troubleshooting, once you get the connection working, make sure to change the Verbosity back to 1 once things are working

6. There are a few ways to obtain the URL to the file, one of the ways is to find the document on the home page of your site to get the URL as shown below:

Another is by going to your document list on the site 

Then click on the 3 dots next to the document name and obtain the URL

7. Use the logs you set up in Step 5 to see what the URL is being formatted as. There should be a GET request to your URL with /_vti_bin/ExcelRest.aspx/ sandwiched into the request.

Hope this helps in configuring your Excel Services ODBC driver!