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.

This CKAN extension provides a basic OData 3.0 endpoint for CKAN. This allows it to talk to tools like the FREE Tableau Public for awesome visualizations using data from CKAN! The endpoint requires data to be held in the CKAN Datastore. The WDC is a collection of APIs that enables developers to connect Tableau to any data on the web. Use Cases: Create custom connections to data on the web and make that data available in Tableau. Benefits: Create your own WDC that reads data from virtually any site that publishes data in JSON, XML, or HTML.

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:

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).

Tableau Public Odata Server

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.
  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.

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):

  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.

Tableau Public Odata Free

  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.

Enabling embedded HtmlFieldSecurity for a site in SharePoint online.

Tableau Public Odata Key

  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:

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:

Tableau odata connector

mouse over the Share Icon:

Tableau Public Odata Key

Copy the Embed Code:

  1. Now create a new page in the SharePoint Site.
  2. Edit the page and paste the embed snippet:
Tableau public download
  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!

Tableau Public Download

Happy Tableauing!

The CData ODBC Driver for Microsoft Project enables you to access live Microsoft Project data in business intelligence tools like Tableau. In this article, you will integrate Microsoft Project data into a dashboard that reflects changes to Microsoft Project data in real time.

The CData ODBC drivers offer unmatched performance for interacting with live Microsoft Project data in Tableau due to optimized data processing built into the driver. When you issue complex SQL queries from Tableau to Microsoft Project, the driver pushes supported SQL operations, like filters and aggregations, directly to Microsoft Project and utilizes the embedded SQL engine to process unsupported operations (often SQL functions and JOIN operations) client-side. With built-in dynamic metadata querying, you can visualize and analyze Microsoft Project data using native Tableau data types.

Connect to Microsoft Project as an ODBC Data Source

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.

The User and Password properties, under the Authentication section, must be set to valid Microsoft Project user credentials. In addition, you will need to specify a URL to a valid Microsoft Project server organization root or Microsoft Project services file.

When you configure the DSN, you may also want to set the Max Rows connection property. This will limit the number of rows returned, which is especially helpful for improving performance when designing reports and visualizations.

Add Microsoft Project Data to a Dashboard

  1. Click Connect to Data -> More Servers -> Other Databases (ODBC).
    Select the CData Data Source Name (for example: CData MicrosoftProject Source).
  2. In the Database menu, select CData.
  3. In the Table box, enter a table name or click New Custom SQL to enter an SQL query. This article retrieves the Projects table.
  4. Drag the table onto the join area. At this point, you can include multiple tables, leveraging the built-in SQL engine to process complex data requests.
  5. In the Connection menu, select the Live option, so that you skip loading a copy of the data into Tableau and instead work on real-time data. The optimized data processing native to CData ODBC drivers enables unmatched performance in live connectivity.
  6. Click the tab for your worksheet. Columns are listed as Dimensions and Measures, depending on the data type. The CData driver discovers data types automatically, allowing you to leverage the powerful data processing and visualization features of Tableau.
  7. Drop the ProjectName column in the Dimensions pane onto the dashboard. When you select dimensions, Tableau builds a query to the driver. The results are grouped based on that dimension. In Tableau, the raw query is automatically modified as you select dimensions and measures.
  8. Drag the ProjectActualCost column in the Measures field onto the Detail and Color buttons. Tableau executes the following query:

    SELECT ProjectName, SUM(ProjectActualCost) FROM Projects GROUP BY ProjectName

    When you select a measure, Tableau executes a command to the driver to calculate a summary function, such as SUM, AVG, etc., on the grouped values. The SQL engine (embedded within the driver) is leveraged to process the aggregation of the data, where needed, providing a seamless experience in Tableau, regardless of the data source.

    To change the summary function, open the ProjectActualCost menu and select the summary you want in the Measure command.

  9. You can create other charts using dimensions and measures to build SQL queries visually:

Tableau

With the CData ODBC Driver for Microsoft Project, you get live connectivity to your Microsoft Project data, allowing you to build real-time charts, graphs, and more.

Coments are closed

Most Viewed Posts

  • Tor Browser Reddit
  • Combine Datasets
  • Tor Browser X32
  • Inner Join R
  • Google Drive File Stream Setup

Scroll to top