Business Intelligence and Reporting in SharePoint (Excel Vs SSRS Vs PowerBI)

 Reporting options for SharePoint data

In a previous blog I wrote about collaboration across and within organizations using reports created via Power BI.


This article contrasts different options available to create and report on data inside SharePoint whether it is on premise or for SharePoint Online in Office 365.  As SharePoint as a technology evolves and most organizations adopt a hybrid model for hosting and utilizing SharePoint capabilities, there is no one size fits all for reporting needs.  Every tool available out there has some limitations and a tool has to be selected based on where the requirements fit best,Power BI Online Course


The options for creating dashboards, charts, KPIs or simple tabular data representation have been available via various applications that integrate tightly with SharePoint platform.  Listed below are some of them that gained adoption at various levels and have been in use for a while.


  • Performance Point
  • Visio Services
  • Excel Services
  • SQL Server Reporting Services (SSRS)


Some of the newer options available now are -


  • JavaScript
  • Datazen
  • Power BI


PerformancePoint for SharePoint never gained significant popularity or momentum in the SharePoint world.  While it continues to be supported and exist in the latest SharePoint 2016, it is an offering that is not seeing any further investment and there is a likelihood that it will be deprecated at sometime.  While the drill down graphs and KPIs in PerformancePoint were very powerful, they were suited best for SQL cubes and required developer level skills to configure and build these dashboards.


Reporting via Excel Services with Excel Web Access web parts gained significant popularity and adoption primarily because of the end user comfort with the Excel tool.  While dashboards can be built with Visio Services and published to SharePoint, the ease of use and adoption was not comparable to the other tools available in the spectrum.


Datazen is a new acquisition made by Microsoft in early 2015 and the advantage this tool offers over all the other tools is the ability to build responsive, interactive design that offer mobile support while the reports are NOT hosted on the cloud.  If the requirement is to make reports available on portable devices while the content is hosted on the on-premise servers then Datazen is an excellent fit.  However, the server configuration and settings are complex. I.e it does not natively integrate with SharePoint.  Additionally there is infrastructure set up and configuration needed to host the Datazen reports.  The dashboards are very compelling, rich and supports data coming from sources other than SharePoint.


The requirements for most organizations for reporting capabilities are evolving towards meeting self-service needs.  The newer tools are empowering end users to build, analyze and create their own reports or dashboards without having to rely on a skilled technical resource.  In this article I am going to compare SSRS, Excel Services and Power BI as the combination of tools that I believe will meet most of the requirements be it on-premise or on the cloud and are the technologies that Microsoft continues to invest heavily into.


SQL Server Reporting Services (SSRS)

Pros

  • Real-time data integration with SharePoint content - report refreshes upon load
  • Print, Export to PDF options for reports
  • Highly scalable for complex queries

Cons

  • Developer / IT Pro Tool
  • Partial to no mobile support
  • Limited visualizations and no interactive ability
  • Fixed query driven
  • Setup and configuration needed for integrated SharePoint setup
  • On-Premise data sources only

Reporting with Excel

Pros

  • Self service analysis and discovery tool
  • Familiar interface and ease of adoption for an average user
  • Ability to build interactive data driven reports with geo displays
  • Tight integration with SharePoint with Excel Web Access web part
  • Connection with multiple data sources with data modelling capabilities
  • Supports on premise and cloud data sources

 Cons

  • No mobile support
  • Limited support for automatic external data synchronization
  • Office release cycles and updates are slower than what is desirable for reporting capabilities

Reporting with Power BI

Pros

  • Mobile support with Power BI Apps
  • Built on top of the familiar Excel capabilities with interactive abilities targeting self servicing all users
  • Frequent enhancement and update cycle
  • Custom visualizers being added frequently
  • Supports Power BI developer tools for custom development
  • Integrate Excel with Power BI
  • Supports automatic data refresh

 Cons

  • Cannot publish or share reports on premise
  • Power BI Pro License needed for advanced capabilities which translates to increased $$ cost
  • Limited report formatting capabilities
  • Data Management Gateway configuration needed for on premise data connectivity



In summary, listed above are the most popular tools for reporting purposes in SharePoint.  As mentioned earlier, the question arises as to how would we decide which tool works best?


The table and matrix below lists a few FAQs and capability comparisons that can help determine which tool mostly fits the requirements.

Are you interested in taking up for Power BI Training Online? Enroll for Free Demo on

Power BI Course


How do I select a tool?



Capability Comparison Matrix





Comments

Post a Comment