Will Power BI replace the full BI stack?

Power BI is an excellent visualization tool, released by Microsoft in June 2015 and updated every month since then.

It’s relatively cheap compared to other similar tools on the market (desktop version is completely free and online version for business starts from $9.99 a month).

It’s easy and intuitive to use: visualization can be created in minutes by  dragging and dropping fields on the report canvas.

Power BI also includes a tool (Power Query) to modify and clean data, join and merge tables. In a way it’s a small ETL tool.

Having said all that, Power BI cannot, and it’s not meant to, replace tools the full Microsoft BI stack.

Microsoft offers several tools to organise and analyse data which range from SQL Server to Excel, going through 3 components which are widely used for BI:

SSIS, SSAS, SSRS.

SSIS, SQL Server Integration Services, is a rich and complex development tool to create ETL (Extract-Transform-Load) processes. It includes a wide variety of tasks to extract data from difference sources, apply changes and import it into a database.  It also offers the possibility of running queries and stores procedures saved directly on the server. Each task is linked in a sequence with the other and the connection can be customised with conditions and functions to create a dynamic flow. Email alerts tasks can be included into the flow. These are saved in packages that can then be nested and organised into projects. Finally, the project is deployed to SQL server and can be automated to run with SQL Agent jobs.

Performance, as well as step by step messages and possible failure, can be monitored using provided execution reports.

Once data is stored safely in a stable and well designed database or data warehouse, a further level can be added to organise and optimise reporting. This can be achieved using SSAS, SQL Server Analysis Service.

Since 2014, SSAS come in two version: Multidimensinal and Tabular. Bothh are based on the star schema with Fact Tables and dimensions, both create a cube of related dimensions and measures, to be queried with reporting tools. Multidimensinal models (OLAP) are more complex to create and are suitable for very big projects. SSAS tabular is a new, slicker version of the Analysis Services database. It uses state-of-the-art compression algorithms and multi-threaded query processor, the xVelocity in-memory analytics engine (VertiPaq), to provide an easy access, fast and powerful reporting base.

Both Power BI and Tabular model use DAX to enhance the model with advanced calculations and time intelligence.

Compared with Power BI, SSAS Tabular provides a more complete solution that can be shared on the server and can be connected to, using various reporting tools.

Finally, SSRS, SQL Server Reporting Service, is the more traditional reporting tool embedded in SQL server. The new version, SSRS 2016, offers the possibility of creating mobile reports and KPI visualizations directly on the server. This is the tool that overlaps more with Power BI and the one most “at risk” of being replaced.

Nevertheless there are still some advantages in using SSRS: more control on parameters, the use of subreports, the possibility of scheduling and sending reports directly to users, as well as a more granular security model.

SSRS can also be integrated with power BI: SSRS reports can be published to Power BI and vice-versa (with some limitation in the second case).

In a few words: Power BI is a great tool but not the only Microsoft BI tool!

For more information on Power BI training courses or Business Intelligence training courses, please contact us