SharePoint 2013 (and previous versions) shipped with Excel Services, allowing Excel workbooks to be viewed in the browser. SharePoint 2013 integrated with Office Web Apps (OWA), which also had the capability to show Excel workbooks in the browser. The complication in 2013 was that the two services had somewhat different functionality, resulting in some organizations having to use Excel Services to gain access to specific BI functionality. In SharePoint 2016, Excel on Office Online Server (OOS), formerly known as Office Web Apps (OWA), has been enhanced and now has the capability to work with Excel workbooks that utilize BI capabilities, such as data models.
Microsoft is now recommending that organizations move away from Excel Services and to use OOS instead. This has a couple impacts on clients:
- Smaller implementations, such as organizations using SharePoint as a platform for Project Server and that support a limited number of users, will now need a second server. Excel services was a service that ran on SharePoint, but OOS is a separate server product that cannot be installed on the same server as SharePoint.
- The list of requirements to support BI is still quite complicated, and will be challenging to communicate to clients. (perhaps a good infographic would help). For example, Excel files with Power View will require SSRS in SharePoint integrated mode to be viewable in the browser. Also, refreshing connections in Excel files with data models will require the PowerPivot add-in.
Microsoft Blog Post about this is here
(see the word doc that is linked in the first paragraph for details on deploying PowerPivot and Power View in 2016)
A good document that compares Excel Services and the Excel Web App from 2013 is here
The options for displaying data are vast and confusing. Following is a short(!) summary of some of the available options:
- SSRS is great for displaying static reports, such as when users want the report to look a certain way each and every time the report is viewed
- SSRS reports can be viewed in the browser or can be saved as PDF or Excel files and emailed to users
- Since SSRS reports can be viewed in a browser, users can view them with no training other than telling them what link to click on
- SSRS reports can be set to run the underlying queries on a set schedule so that slow queries can be run overnight
- SSRS works by sending queries to database, allowing it to send a query to a large database and receive only the records that meet some criteria. This allows SSRS to present live data with no wait times for users, even when the underlying datasets are large.
- SSRS can work in two modes:
- SharePoint Integrated
- Reports are stored in SharePoint libraries.
- Permissions for reports are in-part handled by SharePoint.
- Native Mode
- In Native mode, SSRS provides a site where users can view reports
- Permissions for reports are configured within the SSRS site
- Native mode supports the following for authentication: Negotiate, Kerberos, NTLM, Basic
- Reports can be surfaced in custom ASP.Net applications
- Excel can use data from databases as the source of pivot tables and charts
- Users who know how to use pivot tables can add/remove fields and otherwise rearrange the pivot table to meet their needs
- Pivot Tables support “slicers”, which allow users to easily filter reports
- Excel works by importing potentially large numbers of rows, and allowing users to filter/aggregate, etc. This is a nice capability for analysts, but this means that the data isn’t “live”. To get live data, the excel user must “refresh” the dataset, which can take a varying amount of time, depending on the amount of data.
- Excel “reports” can be uploaded to a SharePoint library, and can be displayed on a SharePoint page via a web part
- Excel “reports” that are uploaded to a SharePoint library can be refreshed with current data, but that feature requires the installation of additional components on SharePoint.
- Quite of bit of Excel Documentation will mention “Power View”. However, Power View is based on Silverlight and is being phased out.
- The phrase “Power BI” has meant a few different things of the last couple years:
- The set of BI tools that are available for Excel, including PowerPivot, Power Query, Power Maps, and Power View
- A feature within Office 365 that allowed PowerBI Excel workbooks to be displayed and datasets within to be refreshed. This particular capability has been phased out, and has been replaced by PowerBI.com
- A cloud-based web site (PowerBI.com) that allows users to create dashboards using data from cloud services as well as on-prem databases
- As mentioned above, this supports building dashboards based on data from cloud sources or on-prem databases. There are a couple main ways of supporting this scecario:
- Manual: a user creates an excel file with data from an on-prem source, uploads the file to powerbi.com, and then creates dashboards based on the data. Because there is no connection between PowerBi.com and the on-prem data source, the free version of PowerBI.com may be used.
- Automated: A user installs and configures a “gateway”, and then builds a dashboard using data from the on-prem database. Once published to PowerBI.com, the gateway allows PowerBI.com to refresh the dataset on a configured schedule.