Readers:

I have another guest post from another one of my co-workers, David Timm. David loves to learn new things and has really taken a tiger by the tail with Tableau. Using his expertise in SharePoint, David has our City business partners real excited by the reports he has been developing for them.

David Timm is a SharePoint/Web Analyst with the City of Glendale, Arizona. Besides being the City’s SME for all things SharePoint, David also has developed many Tableau reports for our City business partners using SharePoint Lists as the data source.

I think you will find David’s guest blog post extremely helpful if you use or plan to use SharePoint Lists as your data source.

Best regards,

Michael

 

David Timm: Using SharePoint Lists in your Tableau Dashboards (or Integrating SharePoint into your Tableau Dashboards)

SharePoint Online  – why?

One of the tools we have at our disposal in the City for collecting data is SharePoint 365 (online). Although not a perfect tool, it gives us the ability to spin up a LIST without having to create a full-on front end/back end app that lives in SQL Server (or another db). Programs like InfoPath (I know) or PowerApps let us create robust codeless forms that are simple for the user. Plus, SharePoint’s ability to version records track who created and modified records gives a pretty good safety net in terms of security and validation.

We use SharePoint to gather and house the data input by the users. Great. It works. But until recently, the only way we could report on that data was to export to Excel and then bring it into Tableau manually. Pretty ugly. Users didn’t go for it with much gusto but resigned themselves to just using Excel if they HAD to. This made it more difficult to sell users on SharePoint being a valid data collection tool. We needed some way to make reporting better.

Tableau to the rescue

Somewhere around Tableau 10.2, it was SUPPOSED to be possible to connect to SharePoint lists. I think the option was even in the menu for servers. I don’t think we had success in our world until about 10.5. Now, again, we’re in the cloud version and not on-premise PLUS, we also have a government tenant (licenses) which means we have some additional restrictions. So, no Tableau web part for us.

How do I connect to my SharePoint site?

A great article on the initial connection to SharePoint can be found here: https://onlinehelp.tableau.com/current/pro/desktop/en-us/examples_sharepoint_lists.html. We use Third party SSO which didn’t require any additional set up on the Office 365 environment. Talk to your network team if you have one of the other options. Luckily there is lots of info out there.

Who should I connect as?

Connecting to a SharePoint Online site requires a user account and an Office365 license. You need to connect as a user that has access to the site/list you want to use (I’m also the SharePoint admin for all sites so that makes it easy. I can just use my credentials, connect to the site, and get whatever I want.). You might be tempted to just embed your own credentials in the connection. This. Is. A. BAD. Idea! If you were to win the lottery, get abducted by aliens, get hit by a random beer truck in the street, the minute the System Admins shut off your credentials, All those connections would fail and all the happy users of their awesome Tableau dashboards would be sad. We don’t want that. A sad user is one who is not liking Tableau. We want happy users.

A GOOD IDEA is to get your System Administrators to create a quasi-system account in Active Directory, grant that user an Office 365 license, add that user to the SharePoint site with your list with at least READ rights (I grant CONTRIBUTE just to be safe). If you want to get to different sites, I just make sure to add that user to a MEMBERS or VISITORS group and that will expose all the lists in that site or sub-site.

Great. I connected to my site. Now what?

You’ve got a user account and password created. The user has permission to the site. What’s next? Well, we then need to create a new workbook, use SharePoint List as our data source and successfully connect using the “SiteUser credentials”. Like this.

Your connection cranks away. You anxiously await to see if it’s going to resolve. Then you let out a scream of joy as you see a list of “tables” in the left area. These “tables” are actually the individual lists under our site. These behave just like a SQL or Excel or any other data source EXCEPT you can’t use custom SQL. But you CAN join multiple tables together assuming there are common fields in each.

In the screenshot below, I do just that to get some GEO data into a worksheet from a second list under the same site.  But you CAN join different data sets together also, like a GIS Shapefile or a separate Excel sheet (or anything else. Provided there are common fields between them).

Is it live or is it Memorex?

Extra points if you even know what Memorex is. A SharePoint connection yields an EXTRACT. You don’t have a choice (at least as of now). You’ll want to embed the password when you publish your data source to a server and schedule a data refresh on whatever best suits your needs. We do most of ours once a day. I wouldn’t suggest doing to it frequently just because. SharePoint is still a Microsoft product and might get moody. That being said, I’ll admit I haven’t gone more than every 4 hours. If you’re feeling brave and want to try for close to real time, go nuts.

Anything odd about the list data?

Yes. You need to break it on the SharePoint side to get the best results (with larger datasets). SharePoint lists use VIEWS to present the data to a user. You can configure that view to show selected columns, filter based on “xyz” criteria. You can have many views within a single list. On top of that, each list is limited to 5000 items/records that it will show in a single view even though a list can hold MILLIONS of records (in theory). You also have only 1 “default” list that is presented to the users when they first open the list itself.

Only 5000 records???

Now before you freak out on that limit there is good news. Even though SharePoint will throw an error if a list tries to grab more than 5000 records, Tableau doesn’t care. Yay Tableau! If I have 20,000 records in a list, I can’t view them in native SharePoint’s default list, but Tableau will grab them all on each extract. This might mean you have to educate the list users on this. Ideally, though, you’ve got them using Tableau for reporting and they won’t need to go there anyway.

Almost there. Now how do I use this data?

It’s now just data in Tableau and behaves like anything else. Basically, just go all Viz on the data like you normally would on any other data source. Add parameters, actions, make dashboards, everything.

Here is a heat map with a dual axis showing our city boundaries and the amount of work done in parks over time. Senior management loved this. I included a viz in tooltip on the heat areas to further show the data.

Any reactions from SharePoint users in this approach?

Once we got everything connected and showed examples like the above, other departments had a light bulb moment and wanted to get on board. The above map was even presented in a state-wide conference for the Parks Department. This certainly helped spread the value that Tableau can bring to the City using some of the existing tools and a little training.

Can you simplify all that?

1. Have permission to see the list you want to access (users won’t need this as Tableau permission will take over once you publish to a tableau server)
2. Create a User as a connection account in Active Directory (set password to never expire if you can get away with it)
3. Add that user to the SharePoint site with the list you’re after
4. Grant that user permission to read that list (Read permission or higher)
5. Open Tableau
6. Connect | To Server | SharePoint Lists
7. Fill out the Connection Info to the appropriate list
8. Once connected drag the Table/List to the top and join any tables together you need (optional)
9. Make worksheets and dashboards to bedazzle and wow your customers
10. Publish to a server and embed the password in the connection
11. Set up a refresh schedule for the data source

And that’s about it. With any luck, this will set you all on the path of mining your data out of SharePoint and into Tableau. I have about 6 major departments using this technique with workbooks having dozens of sheets and dashboards in them.

David Timm