SharePoint(2016/2013) – Tableau Sync Manager

In the previous article SharePoint 2016/2013: OData Connector for Tableau Reports, I had explained you 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

1

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

  1. Provision staging database, though this is optional and can be done directly at database level
  2. Query SharePoint using CSOM/REST API End Points and sync it with staging database which is a SQL Server based database
  3. Generate logs with differential changes
  4. Send Email Notifications to Tableau Report Administrator/Owners

Analytics Staging Database: This database will store the data retrieved from SharePoint and act as 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 setup this demo, I have created a SharePoint List “MyLocations” that will hold locations data as shown below:

2

Staging database is provisioned with a Table “My_Locations” that is having corresponding columns to store data from SharePoint as shown below

3

Once database has been created we can write any required SQL query to fetch the data. In this case I have used a simple select statement to fetch all the data from the table

4

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

5

Step 2 involves connecting to staging database and deleting the existing content from the “My_Locations” Table

6

Step 3 involves reading data from SharePoint List and inserting it into staging table

7

Step 4 involves an exception handler that will send notifications to the process administrators in case if any error occurred during the Sync Process

8

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

9

Go to database run the select query again to see if data synched successfully

10

Once data source is ready we can start designing the Tableau Report using Tableau Desktop by taking “Microsoft SQL Server” as connection.

On connecting with SQL Server specify the query to fetch the data for the report as shown below:

11

Once data connection is successful we can see data surfacing to Tableau Designer

12

Now you can design the report of your choice based on this data, for this demo I am presenting information as Geo Map which is most suitable for the kind of data that we have in “MyLocations” Lists in SharePoint

13

Based on this report I have added a dashboard and publish this dashboard to Tableau Server

14

This will be the final look of Tableau Report executing in browser

15

Now lets’ consider that we need to add another location type and to do so follow the Steps below:

  1. Access to SharePoint Site
  2. Add List Item to “MyLocations” List as highlighted below

16

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

17

Once Sync Manager executes successfully, just refresh the report by using “Refresh” button on the report

18

And sure enough you will see the changes reflected on the report

19

We can extend this Sync Manger to cater even more advanced scenarios, which I may cover in some of the upcoming articles.

That is all for this demo.

 

 

Advertisements

SharePoint 2016/2013: OData Connector for Tableau Reports

With the evolving technologies we are getting better tools every day. In my recent assignment I got the chance to explore “Tableau” which is quite a famous Analytics Tool that allows high end reports development.

Tableau can be hooked up with SharePoint Sites to represent SharePoint Data into meaningful reports. This integration is possible using Odata Connector available for SharePoint OOB Services

In this article I will cover the steps to connect Tableau with SharePoint Data and gradually will prepare a report to turn the SharePoint List Data into meaningful representation.

In order to start with this demo, I have added a SharePoint List “NetworkResources” in which we have some arbitrary data related to the health indicators of the Servers available in the Network.

Following is the snapshot of the “NetworkResources” list:

1

Once the data source is available we can launch “Tableau Desktop”, which is the report designer from Tableau.

This software is free for 10 days as trial version and can be downloaded from http://www.tableau.com/trial/tableau-software

Now Launch Tableau Desktop

2

Under “More…” section you can see option to connect to SharePoint Sites using Odata Connector

Select “OData”

3

On the configuration Screen in the “Server” Textbox specify the “SharePoint List.svc Endpoint” with respect to the “NetworkResources” List as shown below:

https://<Host Name>/sites/<Site Name>/_vti_bin/ListData.svc/NetworkResources

Also provide required authentication credentials to the Web Service Endpoint

Once all information provided click on “Sign In” button to try connecting to the List

4

And if this is the first time you are trying to connect using Odata connector you might encounter the following error:

5

When I had encountered this error I had to read quite a number of support articles from Tableau and finally I found the reason for this one of the Tableau Community Pages.

In order to read about this issue in details you can refer to the following link: https://community.tableau.com/docs/DOC-6407

Following are the steps that you can take to fix this issue:

Step1: On the Windows Start menu, click Run, type regedit, and then click OK.

6

Step2: In the Registry Editor, navigate to HKEY_CURRENT_USER > Software > Tableau > Tableau (Version) > Settings.

78

Step3: Right-click Settings, and select New > String Value.

Step4: Name the new string value LegacyHttpClientEnabled

9

Step5: Right-click LegacyHttpClientEnabled and select Modify

10

Step6: In Value data, type true, and then click OK.

Step7: Close the Tableau Designer & Open it again.

After Tableau launch again try connecting the list again

11

And this time we can see it downloading list metadata into Tableau Designer

12

In this article I am not talking about best practices on Tableau Report Development, though you can learn it from the following link: http://www.tableau.com/learn/training

Once we get the list data into Tableau Designer we can develop the report of our choice.

13

In this demo I have developed two basic reports as shown below:

Resource Health Card:

14

Resource Indicator Summary:

15

And finally I have added a Dashboard showing the consolidated report view for above reports:

Dashboard: Resource Health Card

16

That is all for this demo.

Point of Caution

Based on my discussion with Tableau Team, OData connectors are not confidently supported by Tableau for SharePoint Lists. It is a Hit or Miss Proposition and this article present you an approach that is just a workaround and not an official solution from Tableau.

In order to fix this issue I have device out another well-defined approach to deal with SharePoint List Synchronization with Tableau Reports. In one of my upcoming articles I will be describing this approach.

Hope you find it helpful. 🙂

 

Create Windows Scheduler Task by PowerShell Automation

In this article we will see simple steps to configure windows scheduler tasks using PowerShell Automation.

This article can be useful in conjunction with some of the earlier articles that I have written on Process Automation using PowerShell

Before getting into the code, let see the Windows Task Manager to understand where we can find tasks which are getting created by the code

Type “Task Manager”

1

You can see the highlighted section where you can find newly created Tasks

2

The code is pretty simple and based on standard PowerShell Command-lets as described below:

3

In Step 1, we are defining Trigger for the Task by using “New-ScheduledTaskTrigger” Command-Let. It could be any permissible Time Unit as shown below

4

In Step 2 & 3 we are setting up User Name and Password that the Task will be used as Owner Account to run the defined action

In Step 4 we are defining the action by using “New-ScheduledTaskAction” Command-Let that needs to be executed by the task when trigger reached, in this demo this task will execute a PowerShell script

Finally in Step-5 we are registering the tasks by using “Register- ScheduledTask” Command-Let. This Command-Let takes Name, Trigger, UserName, Password and Action as input parameter (as defined in above steps)

Once the script gets executed a new task will be added to the Windows Task Scheduler

5

We can see the new task added in Task Scheduler Window as shown below:

6

If we double click the this task, it will open the task configuration screen that we can use verify the task properties as explained below-

General Tab: Here we can see the name of the Task & User Account that will be used to execute the defined action

7

Triggers Tab: Here we can see the Trigger defined for this task

8

Actions Tab: Here we can see the action defined for this task. In this demo we have specified a PowerShell Script to be executed whenever respective trigger achieved

9

In case if you make any changes to this task Windows will present you a password dialog to confirm the User Credentials, If asked enter the respective credentials

10

This is a simple implementation that allows you to configure Windows Tasks using PowerShell Automation.

That is all for this demo.

Hope you find it helpful.