Let’s start by discussing what the modern BI stack looks like in SharePoint 2016, it has the following components:
- PowerPivot - Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.
- Excel Online - With Excel Online you use your web browser to create, view, and edit workbooks you store on OneDrive or SharePoint
- SQL Reporting - SQL Server Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality.
- Analysis Services - Analysis Services is an analytical data engine used in decision support and business analytics. It provides enterprise-grade semantic data models for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
- Data Quality Services - The data-quality solution provided by Data Quality Services (DQS) enables a data steward or IT professional to maintain the quality of their data and ensure that the data is suited for its business usage.
- Master Data Services - Master Data Services enables you to manage a master set of your organization's data. You can organize the data into models, create rules for updating the data, and control who updates the data. With Excel, you can share the master data set with other people in your organization.
- PowerBI - Power BI is a business analytics solution that lets you visualize your data and share insights across your organization, or embed them in your app or website. Connect to hundreds of data sources and bring your data to life with live dashboards and reports.
- PerformancePoint - PerformancePoint Services in SharePoint Server is a performance management service that you can use to monitor and analyze your business. By providing flexible, easy-to-use tools for building dashboards, scorecards, and key performance indicators (KPIs), PerformancePoint Services can help individuals across an organization make informed business decisions that align with companywide objectives and strategy.
- Visio Services - Visio Services lets users share and view Visio diagrams. It also enables data-connected Visio diagrams to be refreshed and updated from various data sources.
All these features have different requirements, and I’m going to list off the ones that you can use without SQL 2016 (See here for requirements):
- Excel Online
- If you plan to use Excel Online with advanced data models, at least one SQL Server Analysis Services in SharePoint mode must be registered in the Office Online Server configuration.
- Configure each computer in your Office Online Server farm as an Analysis Services administrator.
- The Secure Store Service must be configured in the farm if you want to use a Secure Store target application for data refresh scenarios, or if you want to use an Office Data Connection (ODC) file that specifies a Secure Store target application.
- Visio Service
- Secure Store must be configured in the farm if you want to store encrypted credentials for data refresh scenarios
- Kerberos constrained delegation must be configured if you want to delegate user credentials to an external data source for data refresh scenarios.
- Requires a Power BI Report Server to be installed on-prem the license is included with Power BI Premium your free to move to the cloud on your own terms (If not looking for cloud you can license via SQL Server Enterprise with SA)
- .NET Framework 4.5.2 or later
- SQL Server Database Engine (2008 or later), to store the report server database
- SQL Server Analysis Services (2012 SP1 CU4 or later), for your Live Analysis Services data sources
As you can see you can still have BI features even if you can’t install SQL 2016, in many ways you can have an amazing BI story by just using PowerBI Desktop and Server along with Excel Online and Visio services.