imageWith the imminent release of SharePoint 2016 in the coming months, now is an excellent time to reflect on the current landscape of BI.  Capabilities are changing and they vary depending on your version of SharePoint and what your options are whether you are using on-premise SharePoint 2010, SharePoint 2013, waiting for SharePoint 2016 or are in the cloud with SharePoint Online / Office 365.  It can be very confusing to keep up with what works where and with what.  This post will outline options and give guidance when you have SharePoint and are looking to enhance with graphs and charts to build dashboards.

If You’re Not Confused, You’re Not Paying Attention

Does this sound familiar?

“Hey Doug, how’s it going?”

“Pretty good Bob, I’m buried in requests for training folks how to use SharePoint, what can I do for you?”

“I need a cool dashboard for manager and other executives.  Can SharePoint do that?”

“Well, ughhhhh yea kinda.  I’m not entirely sure if our version of SharePoint can do that…”

So Doug sets off on a 3 hour googling adventure reading TechNet articles, forum posts and troll the Office 365 Network group in Yammer (because he is smart after all) but feels more confused than when he started. 

That’s because it is confusing!  The answer is typically “it depends” and “what do you mean by <finger quotes>dashboard</finger quotes>”.  I mentioned earlier that it depends on various factors like the SharePoint platform, the version of SQL Services, licensing, data sources, etc. 

What Are Our Options?

So what are our options?  Let’s first look at what all can be used, then I’ll show you what can be used where.  For our purposes, let’s assume that by dashboard,

  1. SharePoint 2010 Chart Web Part
    SharePoint 2010 Web PartIf you are using the Enterprise version of SharePoint 2010, you had the ability to use this out of the box web part.  It provided a good amount of configuration options and met a good many needs for charting. 

  2. Excel Services / Excel Web Part
    Excel web partSharePoint provides an Excel service that allows for different functions.  One of these functions is that you can embed an Excel workbook, sheet or chart (in our case) on a SharePoint page.  You can get your data together in Excel, create a standard chart or pivot chart, and show that chart on a SharePoint page.  It can certainly work in certain situations or some needs. 

  3. Excel BI / Power View / Power Map
    Excel Power ViewThis takes the above to a whole – other – level.  This goes past your dad’s pivot table.  Microsoft has bought in fully of making Excel an extremely powerful reporting tool for the business user with every newer version of Excel.  This really started getting data modeling in Excel 2010, then really got cool with Excel 2013, and now Excel 2016 is fully baked and is pretty awesome.  However, there are some limitations with loading in SharePoint, mostly with Power View. 
    Excel Power Map

  4. SQL Power Pivot / SharePoint Power Pivot Gallery
    SharePoint Power Pivot GalleryThis requires higher licensing for SQL Server (either the Business Intelligence or Enterprise SKUs).  This also requires an additional install on the SharePoint servers, but provides some special features and libraries in SharePoint to browse and load Excel files with Power Pivot data models. 

  5. 3rd-Party Add-On Chart Solutions
    SharePoint out of the box has limitations, we all know it.  That’s how they designed it!  Charting is no different.  If you have primarily SharePoint based data as a source and don’t want to do much hard work, these might fit the bill.  They are available from vendors like Bamboo, Nevron, Kwizcom, Amrein or Collabion (among others). 

  6. SQL Reporting Services (SSRS)
    SSRS is where we start getting into more what you would think of in a more traditional dashboard or reports.  SharePoint has had integration with SSRS for awhile, and each version it just gets better.  With it installed in integrated mode, there are web parts to load reports directly on a page.

    SSRS in SQL Server 2016

  7. PerformancePoint
    SharePoint PerformancePointThis used to be a 3rd-party product, then bought by Microsoft and built into SharePoint starting in 2010.  It really hasn’t changed that much in 2013 or 2016.  Available with the Enterprise features of SharePoint, you can create actual dashboards with KPIs, charts, reports, etc.  What’s special is that they can be dimensional data sources like cubes from SQL Analysis services (SSAS) and provide drill down, etc.
    PerformancePoint Decomposition tree

  8. Custom Solutions with jQuery Charting Plugins
    There are many charting solutions using jQuery and HTML5.  With some customization, you can leverage these in SharePoint to create some slick charts.  Some are free, some are not.  Check out options like Charts.js, Dynatable, Google charts, and many others.

  9. Power BI
    Power BIWe have arrived.  If you know the term Tableau, you should know or get to know Power BI.  This is Microsoft’s Business Intelligence as a service hosted in their cloud, with your on premise data or in the cloud with tons of available connectors.  It can be free, or a small fee for dashboard awesomeness (for either).  Let it dynamically generate visuals by asking it questions via the Q&A.  Take your reports and dashboards mobile!  The forums are very active, and they provide very frequent updates to the service, with monthly updates to the desktop report creation tool (Power BI Desktop).  The visuals API is available and developers can submit custom visuals free to the community.  The paid version can be licensed through Office 365 service offering.  Check out the Getting Started page to get rolling! 

    There used to be an offering called Power BI for Office 365, but this has been discontinued in lieu of the standalone Power BI offering described above that now integrates with Office 365.

One More Thing…

Depending on what systems you have, there might be additional options.  For example, if you have a CRM system like Dynamics CRM or Salesforce, you can use them for charting/dashboarding depending on your requirements.  If your source data is that system, it may make sense to keep charting and reporting in that system.

SharePoint BI Tool Usage Matrix

Now that we’ve seen WHAT is out there, I want to take a look at what can be used on the various platforms. 

SharePoint BI Tool Usage Matrix

Not everything with SharePoint 2016 and Office Online Server has been finalized yet as it relates to Excel services so this is subject to change.  And frankly so is SharePoint Online.  Microsoft at any day could change the way things are implemented. 

If you find any errors please contact us so I can make adjustments.  I’ll do my best to keep this updated as plans change.

If you are still confused, please reach out to us to discuss your SharePoint BI needs!