Thursday, May 7, 2015

Analytic & Sharepoint : Pros and Cons

I've had the pleasure (or torture) of integrating four enterprise class analytic packages with SharePoint.  I'm putting together this post to list out the pros and cons that I've experienced with each tool.  I know it's not an exhaustive list, but I hope you'll find it useful if you're exploring analytics in SharePoint. 

All of these tools use external tools to develop dashboards and reports. Also, they each have the ability to produce many different types of charts and grid reports.  All are interactive in one aspect or another, and are web-enabled (of course).

SQL Server Reporting Services (SSRS)

  • Included with SQL Server
  • Reports can be developed with Visual Studio or Report Builder (Free Download)
  • Works equally well with relational or dimensional data sources
  • Exports reports to PDF, Excel, and many other platforms
  • Produces "pixel perfect" reports for viewing online or printing
  • Includes geospatial analytical charting
  • Uses standard HTML capabilities to render reports on many browser platforms
  • Works well with "touch" devices (tables, phones)
  • Most flexible analytic tool listed 
  • Data can be "blended" from multiple sources
  • Data may be loaded from any ADO.Net data source (SQL Server, Oracle, Excel, Access, etc.)
  • Requires the SSRS server to produce reports for users
  • Does not fully support "touch" interfaces for mouse-hover tool tips
  • Developing reports requires thorough knowledge of the data sources
  • Development user interface is geared toward experienced developers

Performance Point Services (PPS)

  • Included with SharePoint Enterprise

  • Automatically detects the structure of the multidimensional data, allowing users to explore data via drill through.
  • Included in the SharePoint 2013 software distribution, no additional installation media required
  • When you drill-through items, only the affected components are refreshed, other KPIs and worksheets remain unaffected.
  • PPS dashboards are true dashboards, which consolidate wide data that can either be tied together or desperate.  In this sense it is the only "true" dashboard tool presented here.

  • Does not include geospatial analytical charting, but can be augmented with SSRS
  • Requires SQL Server Analysis Services dimensional data to operate
  • Requires two-button mouse support for full capabilities (drill-through and mouse hovers)
  • Dashboard development must be conducted on a system that is on the same domain as the SharePoint server.
  • Development user interface is geared toward experienced developers

Power View (a.k.a. Power BI)

  • Included with SharePoint Enterprise
  • Microsoft Excel or web-based tools are used to author dashboards
  • Supported in both Office 365 and on-premises SharePoint 2010 and 2013
  • Includes  geospatial analytical charting
  • Utilizes HTML5 for cross browser and platform support
  • Works well with "touch" devices (tables, phones)
  • Data can be embedded in spreadsheets and automatically hosted by SSAS.
  • Requires a Multidimensional data source or Tabular data source for data
  • Not fully "touch" enabled, popup tool tips require mouse hover
  • Although some editing on PowerView dashboards can be done on-line, the best experience comes using Excel 2013.


  • Per core or per user licensing (ref)
  • Tableau Desktop licensed per user for development activities
  • Tableau Server or Tableau Online (cloud offering) for publishing, and separately licensed.
  • A breakdown of costs has put together by Brad Fair at Interworks.
  • Beautiful worksheets and dashboards out of the box
  • Developing worksheets and dashboards is a drag & drop activity.
  • Data can be "blended" from multiple sources
  • Tableau "packaged dashboards" (.twbx files) are self-contained, with some or all data embedded.
  • "twbx" files can be distributed to users who can view them using the free Tableau Viewer.
  • Works best with single "flat" data sources or with  dimensional data such as SQL Server Analysis Services (SSAS)
  • Supports geospatial analysis and charting (i.e. plotting points on a map)
  • Software assurance (a.k.a. free upgrades) are included with maintenance costs 
  • Relatively expensive, if you already have an investment in SQL Server or some other BI suite with required yearly maintenance costs (see breakdown of costs)
  • Does not directly integrate into SharePoint, dashboards are shown via Page Viewer IFRAME HTML elements.
  • Not fully "touch" enabled, some features such as tool-tops require mouse-hover to show.
  • Tuning queries is difficult, since Tableau was designed for the embedded data model first
  • Tableau Online requires that reports be 100% "twbx" embedded data or have access to internet enabled data sources
  • When using dimensional data, textual reports (non numeric) are difficult, and the dimensional model must be tuned to support them, through "existence" facts.