SharePoint (2016/2013) – Tableau Sync Manager
In the previous article SharePoint 2016/2013: OData Connector for Tableau Reports, we explained the implementation details of connecting SharePoint with Tableau using Odata Connections.
Since this approach was not confident and was not working for me as expected I have developed another reusable Add-in to achieve data sync between SharePoint & Tableau.
I call this Add-in as “SharePoint –Tableau Sync Manager”. To understand this Add-in better you can refer to the below Technical Diagram section
Technical Diagram
https://howtodowithsharepoint.files.wordpress.com/2017/01/17.png?w=800
Following is the brief description of each of the components of this add-in-
**Task Scheduler: **This component is responsible to execute Sync Service at a defined frequency.
**Sync Service: **This component will perform following operations
- Provision staging database, though this is optional and can be done directly at the database level
- Query SharePoint using CSOM/REST API End Points and sync it with staging database which is a SQL Server-based database
- Generate logs with differential changes
- Send Email Notifications to Tableau Report Administrator/Owners
**Analytics Staging Database: **This database will store the data retrieved from SharePoint and act as a primary data source for Tableau Report.
**Tableau Report: **This could be any Tableau Report based on the query from staging database.
Demo
In order to set up this demo, we have created a SharePoint List “MyLocations” that will hold locations data as shown below:
https://howtodowithsharepoint.files.wordpress.com/2017/01/22.png?w=800
Staging database is provisioned with a Table “My_Locations” that is having corresponding columns to store data from SharePoint as shown below
https://howtodowithsharepoint.files.wordpress.com/2017/01/32.png?w=800
Once the database has been created we can write any required SQL query to fetch the data. In this case, we have used a simple select statement to fetch all the data from the table
https://howtodowithsharepoint.files.wordpress.com/2017/01/42.png?w=800
Now lets’ look into the Sync Service code that will talk to SharePoint using CSOM/ REST API End Points.
Step 1 involves connecting to SharePoint List by using usual PowerShell CSOM technique.
https://howtodowithsharepoint.files.wordpress.com/2017/01/52.png?w=800
Step 2 involves connecting to staging database and deleting the existing content from the “My_Locations” Table.
https://howtodowithsharepoint.files.wordpress.com/2017/01/62.png?w=800
Step 3 involves reading data from SharePoint List and inserting it into a staging table.
https://howtodowithsharepoint.files.wordpress.com/2017/01/72.png?w=800
Step 4 involves an exception handler that will send notifications to the process administrators in case if any error occurred during the Sync Process.
https://howtodowithsharepoint.files.wordpress.com/2017/01/82.png?w=800
Additionally, we can generate differential logs and success notification to the report owners or may extend this layer to connect with other sources as well.
So that is all for the code.
Once it gets executed successfully we can see data has been synced from SharePoint to Staging Database.
https://howtodowithsharepoint.files.wordpress.com/2017/01/92.png?w=800
Go to a database and run the select query again to see if data synced successfully.
https://howtodowithsharepoint.files.wordpress.com/2017/01/102.png?w=800
Once the data source is ready we can start designing the Tableau Report using Tableau Desktop by taking “Microsoft SQL Server” as a connection.
On connecting with SQL Server specify the query to fetch the data for the report as shown below:
https://howtodowithsharepoint.files.wordpress.com/2017/01/112.png?w=800
Once the data connection is successful we can see data surfacing to Tableau Designer.
https://howtodowithsharepoint.files.wordpress.com/2017/01/121.png?w=800
Now you can design the report of your choice based on this data, for this demo we are presenting information as Geo Map which is most suitable for the kind of data that we have in “MyLocations” Lists in SharePoint.
https://howtodowithsharepoint.files.wordpress.com/2017/01/131.png?w=800
Based on this report we have added a dashboard and publish this dashboard to Tableau Server.
https://howtodowithsharepoint.files.wordpress.com/2017/01/141.png?w=800
This will be the final look of Tableau Report executing in a browser
https://howtodowithsharepoint.files.wordpress.com/2017/01/151.png?w=800
Now lets’ consider that we need to add another location type and to do so follow the Steps below:
- Access to SharePoint Site.
- Add List Item to “MyLocations” List as highlighted below.
https://howtodowithsharepoint.files.wordpress.com/2017/01/161.png?w=800
Once data has been added/updated to SharePoint, Sync Manager will pick-out all the changes and sync back to Staging Database as per defined schedule.
https://howtodowithsharepoint.files.wordpress.com/2017/01/171.png?w=800
Once Sync Manager executes successfully, just refresh the report by using “Refresh” button on the report.
https://howtodowithsharepoint.files.wordpress.com/2017/01/18.png?w=800
And sure enough, you will see the changes reflected in the report.
https://howtodowithsharepoint.files.wordpress.com/2017/01/19.png?w=800
We can extend this Sync Manger to cater even more advanced scenarios, which we may cover in some of the upcoming articles.
That is all for this demo.