Company and organisational information is often locked up inside SharePoint and is not always easy to manipulate or visualise as Business Information. This is where Tableau comes in. SharePoint data can be manipulated or accumulated with other information in a Tableau worksheet and published; it can even be rendered in the same or a different SharePoint site. Tableau does not have a pre-defined SharePoint connector, other than for a PowerPivot connection (excel workbook), but a connection can still be achieved easily. This simple demonstration shows how to connect SharePoint Lists to Tableau.
Make a SharePoint List
A new data list is created in a SharePoint online portal. This list was created based on an example found on the web. The same technique will also work for SharePoint on premise. The list can be an existing custom or standard list, such as an auditing or analytics view. Import using the import app: In Site Content, select ’add an App,’ then select ’Import Spreadsheet’. In this case, Olympics.xlsx was used. Note: If you get an error - "The list cannot be imported because a Microsoft SharePoint Foundation-compatible spreadsheet application is not installed or is not compatible with your browser. SP2013" - then open the 32 bit version of Internet Explorer. A list will be created in the lists area: https://<servername>/<path>/Lists/Olympics/AllItems.aspx
Create the Connection
In Tableau (9) I defined an OData Data Source connection: "http://<servername>/<path>/<sitename>/_vti_bin/ListData.svc/Olympics" Note: If you get this error when defining the connection - "You do not have permissions from the OData source to access the data. Check with the data provider to obtain or update your permission" - then open the 32 bit version of Internet Explorer to resolve the issue. Tableau Desktop 8.0 uses WinINet to handle HTTP requests, whereas Tableau Desktop 8.1 onwards uses libcurl. Disabling libcurl and enable WinINet will allow you to connect. Follow these steps to connect:
- On the Windows Start menu, click Run, type regedit, and then click OK.
- In the Registry Editor, navigate to HKEY_CURRENT_USER > Software > Tableau > Tableau 8.1 > Settings.
- Right-click Settings, and select New > String Value.
- Name the new string value LegacyHttpClientEnabled.
- Right-click LegacyHttpClientEnabled and select Modify.
- In Value data, type true, and then click OK.
Retrying the OData connection gives: This gets the List into Tableau.... Now you can manipulate the data, for instance; create a dashboard (the image below presents an example of how to add a map to the dashboard). Note: When you connect to SharePoint documents from Tableau Desktop, it is possible to use either a URL or a UNC path. If you use a URL, Tableau Desktop will download a copy of the data source and place it in the temp directory. Tableau Desktop will not be able to update this file when the data in the original file changes.
Pulling data from SharePoint is relatively easy. This is a useful technique to make use of the excellent features and functionality of Tableau and re-present the data back to SharePoint as BI style amalgamations.