A business intelligence (BI) system transforms raw data into information that supports analysis and decision-making. Typically it contains at least four elements, as discussed below. For data volumes above a few terabytes, the system architecture gets a lot more complicated.
1. Data Warehouse
A relational database containing data from transaction systems.
2. Analytical Layer
A database or functional equivalent in which data from the data warehouse is structured to facilitate analysis and report writing. Until recently this was often a multidimensional ("OLAP") database.
3. ETL Processes
Data needs to be extracted from source systems, transformed into a suitable format and loaded into the data warehouse. The multidimensional database or similar must also be refreshed to reflect what has been loaded into the data warehouse.
4. User Interface
User interfaces vary widely with requirements (and budgets). A comprehensive solution might include:
- Highly formatted reports with a fixed structure and a few filters. These are built by IT developers.
- Dashboards containing a range of content and allowing a greater degree of end-user interaction than standardised reports.
- A tool that allows business users to perform their own analyses and to share the results with others
Software vendors have provided a wide range of products to fulfil the requirements listed above; a brief example from Microsoft follows.
A Microsoft Example
Microsoft provides a comprehensive stack of integrated products that can be used to provide a BI solution. The core elements of such a solution are bundled together with a SQL Server licence. This includes a number of products in addition to the eponymous database:
SQL Server (1)
A relational database suitable for large-scale data warehouses.
SQL Server Analysis Services (2)
In its original form, SSAS is a multidimensional database containing data structured and aggregated for end-user analysis and reporting. The in-memory "tabular" version is now included in both Standard and Enterprise editions.
SQL Server Integration Services (3)
SSIS is used to extract data from source systems, and to populate SQL Server and Analysis Services.
SQL Server Reporting Services (4a)
SSRS provides highly formatted reports delivered on demand via a web interface and via email when scheduled. Until recently, it was often integrated within SharePoint.
Power BI Report Server (4b+c)
This hosts reports and dashboards developed by end-users with Power BI Desktop. It is the on-premises equivalent of the Azure service. It is included with SQL Server Enterprise Edition + Software Assurance; otherwise it may be licensed separately.
A SharePoint Server enterprise licence includes additional products that can be used with either version of Analysis Services, as well as a range of other data sources. For many years SharePoint was the main platform for Microsoft's BI interfaces, but this is changing rapidly (see the blog for more information).
PerformancePoint Services (4b)
PPS provides scorecards and dashboards. It still exists in SharePoint 2016, but looks unlikely to survive another release.
Power View (4b+c)
Power View allows business users to build and and share reports. Although both are done via SharePoint, Power View is a Reporting Services feature and requires a SQL Server enterprise licence. Its days look numbered outside Excel.
Power Pivot uses the same technologies as SSAS Tabular, but is included with some versions of Excel. Models created by business users within Excel an be shared with others in SharePoint.
The above is an extremely brief summary of what is available for an on-premises solution. There are additional options for a cloud-based solution. Call for more information.