Business Intelligence in SharePoint

Over the years, many people have asked for a business intelligence platform, tied to their SharePoint platform. We have all come to realize that in reality SharePoint is a great presentation layer and central repository for content. As such it was natural for Microsoft to add Business Intelligence features into it.

Over the years Microsoft has added the following core features:

Of course native support for SQL Reporting Services has been useful and used a lot by many organizations.

Excel services allows you as the end user to consume spreadsheet content easily, allowing for dashboard creation and calculation of values. Excel Services is defined to work very much like the Office Product Office and its core architecture is designed to make it a very flexible solution. Excel Services core components combined with the four user endpoints: REST API, Web Services, Web Access and ECMA Scripting, make this a very simple choice. Of course the issue now is that Excel Services no longer resides within SharePoint 2016, but is part of Office Online Server.

PerformancePoint was added as a more strategic analysis and data tool. The tool, allows for core On-Premises data to be consumed, and analyzed easily while presenting it into interactive dashboards and components. Using the core design application, reports and data can be melded together then pushed into SharePoint, offering greater analysis and drill-down of the data. The core components allow for web parts to be added to SharePoint pages for presentation, along with the core rendering and authoring web services. To scale components can be added to the Web Front End, with the core backed services running and deployed onto the application servers.

Visio Services was added in SharePoint 2013, to offer a better design canvas idea for building dashboards and visualization of data. Visio as an Office Product provides great features, allowing for highly graphical diagrams, and data mapping to objects within the canvas. Adding this to the Business Intelligence stack of SharePoint made complete sense allowing for better visualizations, not just out of the box but completely customized to you as an organization. Visio Services was deployed into SharePoint as Service Application allowing it to be used not just for Business Intelligence but also an alternative Workflow canvas. Visio Services is split between the Web Servers and Application Servers allowing for high performance and usage.

The client access features allow for not only standard Web Parts. But also Web Access and an API for mashing content together in a fast and lightweight client way.

PowerPivot became supported in SharePoint 2013, and allows just as with the client application, the tool for large dataset work. PowerPivot is really just tied to Excel Data, allowing larger dataset, and control of charts and pivoting of data easily. This application was designed to be really a client side application or add-in tied to Excel. Looking at the architecture design shows us this.

Finally, SQL reporting services that has been around a long time, has been enabled through various iterations of SharePoint. It is the most logical approach to use for Business Intelligence and it can be used and consumed outside of SharePoint too, allowing for great flexibility than a pure SharePoint Solution. The only downside however is that it has not really kept up with the times, albeit the new version in SQL 2016, so does not offer as rich an interface and user experience as the other core features. By the very nature of Reporting Services being separate to SharePoint the complete design is very different.

SharePoint utilizes the Report Manager and Report Server components, by using the Web Services to handle the connections.

SharePoint as a whole out of the box does offer Business Intelligence though it does not offer some of the deeper and more advanced features that other products have to offer. Most of Microsoft core investments in Business Intelligence recently have focused heavily on their cloud offerings, but with the acquisition of Datazen and release of SQL Mobile Reporting this could change the tools used for Business Intelligence in SharePoint.