Wednesday, 8 June 2016

Automatic refresh issue with embedding PowerBI reports in SharePoint Online

I could talk about benefits of embedding Power BI reports in SharePoint for days but as there is no shortage of good blogs out there covering that topic already so instead my focus here will be to talk about overcoming a challenge of using SharePoint lists as a data source given Power BI’s data refresh limitations.

I recently worked on a project which required me to use a SharePoint list as a data source, and to connect that list to PowerBI. Users could then analyse and create reports using the SharePoint data, before embedding those reports in a SharePoint site so as to visualise the live data for internal and external users. 

To clarify this process I’ve drawn up a simple diagram:


Connecting a SharePoint list to PowerBI is very straight forward and took me no time at all. Analysis and reporting can also be done fairly easily in PowerBI and I left this to the users to work on.

Next, I created a SharePoint Online App/Add-in Part to display Power BI Dashboard Tile. 

Here are the high level steps to you need to take to create the App/Add-in:

      1. First you need to register your Power BI web app in Azure Active Directory. This establishes an identity for your application and enables you to specify permissions to Power BI REST resources.
When you register a web app, you receive a Client ID and Client Secret. The Client ID is used by the application to identify itself to the users tharequesting permissions from. The client secret Key is used by the web app to securely identify itself to the Power BI service.

     You can register your web app either with the Power BI App Registration Tool or on the Azure with the Power BI App Registration Tool or on Azure Management Portal. The Power BI App Registration Tool is the easiest option, since there are just a few fields to fill in. Click here for links and more information
  
       2.  Next Create a Provider Hosted Add-in using Visual Studio. Make sure to use a developer site on O365. Select SharePoint Online for target SharePoint version, 'MVC Web application'  for project type and use Windows Azure access control service for authentication setting.
     
           3.  Create a solution using codes from here or here
    
       4.  Once the SharePoint Add-in is created, it can be added on to any SharePoint page

Having carried out these steps, we were understandably excited about having our pretty visualisations added to the SharePoint site. But then…

DISASTER! We realised that by editing our SharePoint list items, our visualisations weren't being updated, as the connection is not real-time and the automatic refresh doesn’t apply to SharePoint list as data source. 


After a short period of mourning, and some hasty research, I learned from this link that the Live/DirectQuery feature for SharePoint is not available at the moment. Was this the worst day of my life? Perhaps not. But it was certainly the worst day of my life to involve an issue with PowerBI.




After more research I soon found a way to overcome this issue by using Azure SQL database, here is how I did it:
  1. Create a database table that stores list items/content in an Azure SQL Database.
  2. Create a CRUD connection between Azure SQL Table and SharePoint Online using ‘Business Connectivity Services’ in conjunction with ‘Secure Store Service’.  Check the step-by-step details here & here.
  3. This would enable creating an External List in SharePoint and users can benefit from using a single platform for creating/editing items on the list and we avoid any custom service in between these services.
  4. Finally, Power BI can directly connect to Azure SQL Database with the auto refresh option and provide a real-time report of the data.
           To illustrate this entire process I’ve drawn up another diagram. 



No comments:

Post a Comment