Visualizing SharePoint Data with Tableau

My employer has been a Tableau partner of the year for 3 years now so it is a technology that is brought up frequently in the office. For the past year I have wanted to learn more about the product but just have not had the time to dedicate towards understanding how it works and if it could connect to SharePoint. I was also under the impression that you need a license to build a Tableau workbook, however after going to their site I noticed that a 14 day trial was available! After playing around with Tableau workbook, I managed to answer my own question – yes you can connect to SharePoint! However with any technology stack there are always those fun caveats and I would like to document them if not for anyone else but myself.

First off, I would like to talk about the two operating models for Tableau. You can build a Tableau workbook with your fancy visualizations and maintain it just as you would an Excel file. It can be e-mailed around to others, shared on your network drives, etc. It is a self contained document which includes both the data you are visualizing in addition to the actual visualization you have created. Depending on the data source you may or may not have the ability to refresh the data within your workbook. The second option is to stand up a Tableau Server and host your workbooks there. Obviously the server route allows you to schedule activities such as data refreshes and ensures that everyone looking at your visualization is referencing the same data.

So after downloading Tableau Workbook I decided to take a shot at figuring out a way to connect to SharePoint. Prior to digging into this,  I had a feeling that there would be an option to make an oData connection which is exactly what I found just under MySQL:

Tableau-Connect

Once you pick OData from the list, another dialog box pops up:

Tablea-OData

The server information might be a little bit misleading at first since you are not pointing the workbook at your SharePoint server but rather the data which you would like to include in your workbook. Since this is an oData connection you need to build a REST URL which will return list items which you would like to visualize.

For example: http://sp2013/sites/ctspug/_api/web/lists/getbytitle(‘Leads’)/items

This is going to return items from a list called Leads in my site collection called CTSPUG. A couple of quick tips that I learned the hard way.

1. You need to connect to your oData service in Internet Explorer prior to passing it to Tableau. This might not always be the case in Prod environments where your user name matches the SharePoint instance, I’ve found that the specifying the username & password usually didn’t work for me at all.

2. You need to choose the “No Authentication” after having accessed the resource as per step 1. Again, not really sure why but this is the way to do it. Passing a username & password resulted in my Active Directory account being locked out a few times.

Now, there’s also another way of connecting to data in SharePoint that’s sitting in an Excel workbook already in SharePoint. I do not see a lot of clients using this functionality in their environments but it is kind of cool. So there is a REST API for Excel Services in SharePoint which you could pass in order to access all the data in a Table in one of your workbooks.

Here’s an example of the URL that I used to pull in all the data from “Table1” in my Excel Workbook called Matfess SharePoint Events.xlsx

sharepoint:5240/_vti_bin/ExcelRest.aspx/Shared%20Documents/Matfess%20SharePoint%20Events.xlsx/odata/Table1

Here is a snapshot of what I see in Tableau after making the oData connection:
Tableau-Excel-oData

ProTip: Remember that you need to create an actual table in Excel, not just point at a Worksheet.

TechNet Article for Excel Services API
https://msdn.microsoft.com/en-us/library/office/jj163874.aspx

So basically these are two ways that you can connect to SharePoint data from a Tableau workbook. There are of course a few caveats as with any solution:

1. There does not appear to be a good way to refresh the data. From what I can see, Tableau is extracting the data from the source and does not provide a good way of allowing you to update it.

2. For the straight oData connection you will need to know the correct REST syntax for expanding things like Lookup, Choice, People columns, etc.

I think the fact that you can quickly visualize your SharePoint data is pretty cool if you’re a Tableau “Wizard” who can quickly pull together a visualization.

Leave a Reply

%d bloggers like this: