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. 



12 comments:

  1. Search engine marketing strategies that occur on a website http://websitedesignbyadam.com/microsites-for-seo/. As an example, enhancing webpage velocity, composing content, writing a blog, configuring site articles design and also other tasks are all on-site or on-site SEO techniques.

    ReplyDelete
  2. I know this is quality based blogs along with other stuff.
    http://listleveragereview.net/

    ReplyDelete
  3. The team’s development work has led to a rise in website agencies can expect a diligent and knowledgeable partner whose friendly style makes them easy to figure with.

    ReplyDelete
  4. I think this is a charming issue, I expect you would surely post on it again sometime near the future. Thanks guys!
    click now

    ReplyDelete
  5. It’s amazing in support of me to truly have a web site that is valuable meant for my knowledge.
    UI/UX designers

    ReplyDelete
  6. I'm also visiting this site regularly, this web site is really nice and the users are genuinely sharing good thoughts.
    https://www.youtube.com/watch?v=gtEYCdE0qZ8
    http://www.youtube.com/watch?v=gtEYCdE0qZ8
    Ministry Of Freedom

    ReplyDelete
  7. Thanks for sharing this post. Its very usefull for us.
    shopify store design


    ReplyDelete
  8. Excellent article. Very interesting to read. I really love to read such a nice article C_S4FCF_1809 Exam Questions. Thanks! keep rocking.

    ReplyDelete
  9. I heard about sharepoint and someone even mentioned that it would get very common in the future. Is that really so? I used Sharepoint in 2001 when I used to provide the best academic transcription writing service but I don’t know about the modern updates. Is it really recommendable to future generation?

    ReplyDelete
  10. Money is the most huge thing.Dating Call Girls in Manali You gain your money by troublesome work and tries,Hot and Sexy Call Girls in Manali so these escort young ladies are moreover working for money just,High Profile Call Girls in Agra whichever the money they take,Dehradun Housewife Escorts agency they will give a comparative proportion of joy to you, they fulfill each hankering of yours with veneration and care since they need you again.Dehradun Housewife Escorts agency Their major point is to work with dependability,Faridabad Russian Call Girls so they will give you more than your wants.

    ReplyDelete
  11. The blog is unique that’s providing the nice material. Please post more interesting articles here.
    app design companies

    ReplyDelete