Add a Live Data Feed from your SharePoint List to Tableau

by | Mar 13, 2018 | Uncategorized | 3 comments

Since my last blog on How to connect Tableau to a SharePoint list using OData, a lot of folks have asked about using a live feed to a SharePoint list. This is possible, however the method uses an MS Access database so please check that this is allowed by your administrator.

Note: Access in SharePoint is deprecated, as an alternative you can use PowerApps which is a paid-for service from Microsoft. In this post we’ll use Access.

I started by using John Maki’s reply to this blog:

https://community.tableau.com/message/143056#143056

To do this, you will need:

  • MS Access
  • Connection to a SharePoint list on premise or online
  • Tableau Desktop

Connect MS Access to SharePoint

  1. Open MS Access (I used 2016).
  2. Choose ‘New database’.
  3. Click ‘External Data’ and select ‘SharePoint List’.
  4. Specify the correct SharePoint site. I used a SharePoint Online site.
  5. Click the ‘Link to the data source by creating a linked table’ (Note: do not select the ‘Import’ option, or this process will fail).

Linking access to a SharePoint List

Note: I used a custom list derived from this All Time Olympic Medals Table Wikipedia article. I imported this to a spreadsheet using the age old ‘cut and paste’ method, then I imported the spreadsheet to SharePoint. If you do not know how to do this please reference my original blog here.

  1. Click the Next button. Sign in (add the account to the list permissions if necessary).
  1. Check the Link checkboxes for the List you want to import.

Choosing the List in Access

  1. Click OK. The selected lists will appear in the Access navigation pane.

Imported List in Access

  1. Save the Access DB file (in the accdb file format).

Saving the Access database

  1. Publish (Copy) the MS Access file to a SharePoint library.

The Access database needs to be put into a library on the same site as your source list. In this way, if the list is updated, the Access database will also update. Drag and drop the Access database file you just created to the Site Assets area.

Uploading the Access database to a SharePoint library

Connect Tableau to the SharePoint-Hosted Access DB File

Ensure that the account you use to open the Tableau Desktop application can log into the SharePoint site containing your Access database file. Add the permissions if necessary.

  1. Open Tableau Desktop.
  2. In the ‘Connect’ menu, select ‘Access’ as the data source.

  1. Browse to the SharePoint library where the Access accdb file is hosted.
  2. Select the file.
  3. Select the List and then arrange the data however you like using Tableau’s interface.

Data imported to Tableau Desktop

For example, the image shows Gold, Silver and Bronze medals won by Countries at the Olympics.

Example of Data Formatted in Tableau

  1. Once you have completed your data views, save the project to the workbook area as a twbx file.
  2. Place it in a SharePoint library where the Access file resides.

Viewing the Data

There are several ways to view the data. The simplest way is to save the view as above and open it in the Tableau Desktop.

The simple way

On opening the twbx file from SharePoint, the latest SharePoint data appears in Tableau Desktop. Changes to the list data will be shown on a refresh.

For example, edit the List in SharePoint and change a value (e.g. set Number of Silver medals for a particular country to 1000).

Open the twbx file from SharePoint.

Now refresh the data and you will see a lot of silver medals for the country you amended!

Changed data in SharePoint List is reflected in Tableau.

Note however that this file will need to be closed and reopened every time you want to see updated information. It will also require all users to have the Tableau Desktop installed. For a more complex option that you can view straight from SharePoint read on.

Embedding a live feed in a SharePoint Site

With a little more work the same view can be embedded into SharePoint, which can be on-premise or online. From here, you could even add an iFrame to view the SharePoint WebPart in CRM, AX or wherever you need to view the results.

Here we will use SharePoint Online.

  1. First the chart needs to be published from Tableau Desktop to Tableau public website, or your own Tableau server site. The Tableau web site has details on how to do this here.

Here is the public site where the ‘Olympic Medals Won’ site is published to (I am not looking to win prizes with the format):

https://public.tableau.com/profile/ralph.dalton#!/vizhome/OlympicMedalsWon_1/Sheet1?publish=yes

  1. In the SharePoint Online Site Admin Center create a new site. NOTE: do not use the top level (root) site. If you do not have permissions then ask your site administrator to do this for you.

e.g. https://{YourTenant}-admin.sharepoint.com/sites/{datalytyx}/ where {YourTenant} is the name of your SharePoint tenant and {datalytyx} is the new site name.

The embedded code is disabled by default in SharePoint Online. Use the following procedure to enable it.

  1. Connect to SharePoint Online Powershell (download this from Microsoft if you do not have it). Type the following to connect to your SharePoint site. Your site admin may need to do this for you if you do not have the rights.
connect-sposervice -url https://YourTenant-admin.sharepoint.com
set-sposite https://YourTenant.sharepoint.com/sites/datalytyx   -DenyAddAndCustomizePages $false

Enabling embedded HtmlFieldSecurity for a site in SharePoint online.

  1. Then in a browser, navigate directly to the settings page for the group and you will see the ‘HTML Field Security’ option which you can modify:
https://YourTenant.sharepoint.com/sites/datalytyx/_layouts/15/HtmlFieldSecurity.aspx

Finding the Html Field Security in site settings

Setting HTML Field Security

Now get the embed code from the Share section in the site you have published to.

  1. On the Tableau shared site that you have set up, or the Tableau public site open the Share menu.

Just to remind you here is the public site where the Olympic Medals Won site is published to:

https://public.tableau.com/profile/ralph.dalton#!/vizhome/OlympicMedalsWon_1/Sheet1?publish=yes

mouse over the Share Icon:

Finding the Share Icon

Copy the Embed Code:

  1. Now create a new page in the SharePoint Site.
  2. Edit the page and paste the embed snippet:

  1. Set the title and chrome as you wish.
  2. Save the page.

You now have an interactive chart showing live data right inside SharePoint!

Happy Tableauing!

3 Comments

  1. Any advice on this error from tableau desktop:
    “Database error 0x80004005: The database has been placed in a state by user ‘Admin’ on machine ‘AZ##########’ that prevents it from being opened or locked.

    Reply
    • Hi Katie. What’s your data source? It’s possible that your DB admin is preventing Tableau from connecting for some reason.

    • Hi Matt,
      Data Source is Access DB as described in the solution above to achieve a live connection between SharePoint list and Tableau. When I publish to tableau server, even after embedding all passwords, the user is still prompted to log in.

      Thank you,
      Katie

Submit a Comment

Your email address will not be published. Required fields are marked *