SharePoint Online BI and Connecting to On-Premise Data

Refreshing Data with PowerPivot and Excel Services

I came across this really good whitepaper on how to refresh data in PowerPivot in Excel Services in SharePoint Online. The most important part is “in SharePoint Online”.

If you know much about SharePoint Online Business Intelligence, may know we provide Business Intelligences features in SharePoint Online with limitations. The most important being something like Excel Services cannot make a live connection to a database. If the data resides in the Excel spreadsheet itself no problem Excel Services works great with that data. The challenge has always been how to get the data refreshed into the spreadsheet if the data needs to be pulled from a line of business databases?

The solution described in the spreadsheet takes advantage of several new capabilities of SharePoint Online. It takes advantage of the new SharePoint Online PowerShell commands, the Sync capability of SkyDrive Pro and the new PowerPivot solution now supported in SharePoint Online. The script is very simple. All you do is schedule it, it will check out the Excel workbook, refresh the data, check it in, and then sync the file right back into SharePoint Online. Nice!

Before this new release of SharePoint Online, organizations would have to manually do this action. If an organization got crafty they could use web services to accomplish the same thing but it would be a cumbersome solution to manage and maintain.

This new solution is just so easy and straight forward.

Refreshing Excel workbooks with embedded PowerPivot models from SharePoint Online (white paper) - http://technet.microsoft.com/en-us/library/jj992650.aspx

SQL Reporting Services

If you have read the new SharePoint Online Service Description on Insights (http://technet.microsoft.com/en-us/library/jj819252.aspx) you will have noticed several new capabilities. One of these new capabilities is SQL Reporting Services reporting web parts are now supported. SSRS reports running out of a document library in the cloud can utilize data in a SharePoint lists. You could use a similar solution using the new SharePoint Online Client Side Object model to refresh data in a SharePoint list that the SSRS report would use. The only limitation to consider is the amount of data you are moving into the cloud. I am not suggesting that a SharePoint List cannot handle the data however we all know that SharePoint Lists are not meant to replace something like a SQL Server database. I would recommend that you do as much processing and aggregation of the data before sending up into SharePoint lists. It may even make sense to de-normalize the data a little.

Still you should be able to create some really nice reports that can be centrally managed and access through SharePoint Online.

Visio Services

Visio Services has always been available in SharePoint Online. I have in the past successfully used it to connect to data in SharePoint lists and then create both reports and dashboards. As usual the limitation was how to get the data on-premise.

The way I see it, you have two approaches for getting Visio Services in SharePoint Online to work with a database on-premise.

First, you can do what I just described above where you use the Client Side Object Model to get data into SharePoint Online lists.

Second is leverage BCS! This is new. If you have read what is new with Visio Services (http://msdn.microsoft.com/en-us/library/jj164027.aspx) you will see that SharePoint Online supports BCS. Additionally if you have SharePoint 2013 on-premise, it is now possible to implement a SharePoint Hybrid solution (http://technet.microsoft.com/en-us/library/jj838715.aspx). In this case you can have live on-premise data securely connected to SharePoint Online which Visio Services can build reports off of.

Conclusions

As you are seeing with the introduction of SharePoint Online PowerShell, Client Object Model, SharePoint Hybrid, SSRS, PowerView and SkyDrive Pro along with new improvements to BCS, Excel Services and Visio Services we now have some better options for BI in SharePoint Online. I see the gap starting to close and hope with time, more gaps to be closed.