The SQL BI Stack consists of a broad suite of tools to extend the value you get out of your SQL server database

SQL

The basic backbone of the SQL Server Stack for Business Intelligence is a SQL Server.  While all of the tools below are designed to run against Oracle and MySQL databases, or even JSON, XML and Excel flat files, the core database system for many Windows OS business software applications is SQL.

In addition to Entrance’s experience writing custom applications, we also have a great deal of great deal of experience interfacing with pre-existing applications, analyzing them and pulling the relevant data out without disturbing existing operations or creating additional load on the database.

All of the following tools are considered add-ons, part of the SQL Server Stack that adds value to a base SQL install; Even if you aren’t starting from a SQL database for your source data.

SSIS

When using the SQL Server Business Intelligence Stack, it’s often a requirement to move data out of its work environment and into an analysis database or data warehouse.  When moving data around, importing data from a CSV flat file, or merging data from several different application databases into a single data warehouse, SQL Server Integration Services is the de facto choice.  In the context of the SQL Server BI Stack, SSIS is used to load an Analysis Services Database, or hydrate a Power BI Data Source.

In addition to moving data, SSIS comes built in with a number of management tools that allow you to schedule runs, or communicate errors.  This ensures that the automated processes filling your Business Intelligence tools are filled with the latest data, and enables your IT staff to manage issues on an exception basis, before anyone gets a failed report or sees out of date data.

Entrance has extensive experience with building suites of SSIS packages, from supplying data to SQL BI Stack components like Power BI and SQL Reporting Services sources, to importing third party metrics into line of business systems.

SSAS

SQL Server Analysis Services is the workhorse of the SQL Server BI Stack. By constructing a multidimensional OLAP Storage engine, you preprocess your data and store it so that your analyses can be performed quickly, because the data is already stored in a highly computable fashion.

SQL Server Data Tools can be used to create custom BI Semantic Models involving custom analysis over the hundreds of functions already built into the SQL Server Stack.

SSAS provides an enormous amount of aggregation and drilldown support built in.  This means that your hierarchies are stored directly in the database, in a standard format that integrating tools understand.  The SQL Server BI Stack uses SSAS to store data sourced from other places in a fashion designed to be reported on and calculated across as quickly as possible.

SSRS

Since 2004, SSRS has been the server tool of choice for generating custom online or email tabular data reports.  It supports on-demand reports as well as emailing them out, in PDF, Word, or Excel formats.

With the SQL Server 2016 update, SQL Server Reporting Services has really come into its own as a business reporting tool.  The latest updates have added a huge variety of GUI functionality, and taken what was a simple tabular reporting tool into the future with a wide variety of built in report widgets for displaying extremely slick data visualizations alongside the already existing array of simple graphs.

SSRS also supports SharePoint and Power BI integrations, as well as mobile specific reports.

In short, SQL Server Reporting Services is the way you get flat data into the hands of your users from anywhere in the SQL BI Stack, be it from a pre-built Line of Business App, a dedicated reporting data warehouse, or an OLAP Data Analysis Cube.

POWER BI

Power BI is not strictly a part of the SQL Server BI Stack, however no discussion of SQL Server Business Intelligence functionality would be complete without Microsoft’s new data visualization front end.  Power BI is a direct competitor of applications such as Tableau and Spotfire.

Data Visualization tools provide flexible ways to explore your dataset in a visual way.  Power BI is specifically designed to consume data from the SQL Business Intelligence Stack, especially SQL Analysis Services, but supports the same wide variety of non SQL BI Stack interfaces such as Oracle, or even CSV files.