Two common enterprise reporting & BI tools in use today are Microsoft Power BI and Microsoft SSRS (SQL Server Reporting Services). While both services are a part of the Microsoft Business Intelligence package, each system is intended to be used in a different manner. Power BI is a graphical modern reporting tool that has been built upon common web standards. SSRS has been traditionally been responsible for the “Pixel” perfect reports which are often paginated and emailed to individuals. Each tool has strengths. A company can leverage each of these tools to provide a robust reporting experience for all users. Below is a summary of the key major differences between Power BI and SSRS.
Definition
SSRS
SQL Server Reporting Services sits on top of the SQL database engine. SSRS leverages SQL Server to supply data and created detailed paginated reports
Power BI
Power BI is a full featured suite that serves reporting through a Microsoft maintained PowerBI.com service. The Power BI solution connects to many types of data sources (https://docs.microsoft.com/en-us/power-bi/desktop-data-sources). Power BI has multiple connection methods that enables users to manage data acquisition, transformation, modeling, and visualization. All users can easily create meaningful graphics using a drag and drop method integrated in Power BI.
Release Dates
SSRS
First released in 2004
Power BI
First released in 2015
Licensing
SSRS
SSRS licenses multiple editions based on an organization’s performance needs. All production editions have some cost attached to them. https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing
Power BI
Power BI Desktop is free to any user and includes data cleaning and preparation features, custom visualizations, and the ability to publish reports to the Power BI service. Power BI Pro and Power BI Premium are available for a cost and contain many extra features not available in Power BI desktop. https://powerbi.microsoft.com/en-us/pricing/
System Updates
SSRS
Receives a full system update every few years.
Power BI
Receives monthly updates driven by the strong Power BI community. https://ideas.powerbi.com
Data Types
SSRS
Limited to semi-structured and structured data types
Power BI
Able to handle all types of data, including unstructured data
Data Sources
SSRS
Works exclusively with server-based data, including data held on SQL Server relational databases, SQL Data Warehouses, and data transformed in SQL Server Analysis Services. A full list of data sources can be viewed here. https://docs.microsoft.com/en-us/sql/reporting-services/report-data/data-sources-supported-by-reporting-services-ssrs?view=sql-server-2017
Power BI
Has the ability to connect more data sources than SSRS. All supported data sources are categorized in groups, which include traditional files such as excel, databases of many types, Power BI datasets, Azure products, and many online services including SharePoint and Dynamics 365. A full list of supported data sources can be found here. https://docs.microsoft.com/en-us/power-bi/desktop-data-sources
Designed Purpose
SSRS
IT departments within an organization have traditionally owned and operated SSRS for the organization. Many organizations are moving away from this strategy and towards a hybrid approach of Business led reporting with the support of an IT department
Power BI
Created for the business user in mind. Designed to be a complete Self-Service BI solution that allows all users to create and analyze reports to find useful insights.
Ideal Use Scenario
SSRS
Designed for specific workloads. Organizations that store data on-premises and have a lot of existing database development may prefer SSRS. Organizations that utilize paginated reports and need to easily print reports should use SSRS as of now. However, Power BI has released a paginated report feature that will aim to replace a majority of standard SSRS reporting. https://docs.microsoft.com/en-us/power-bi/paginated-reports-report-builder-power-bi
Power BI
Full service meant to handle data from collection to visualization. Organizations that wish to empower all users to create reports should consider Power BI. The ease of standardizing data and creating new reports is a strength of Power BI. Using Power BI results in a completely unified user experience that allows data to be properly governed and to be visualized efficiently.
Report Sharing and Accessibility
SSRS
Reports are accessed through web services and desktop versions. Reports can be shared automatically via email distribution or through shared file locations.
Power BI
Reports are accessed through web services, mobile apps, and desktop versions. Reports, dashboards, and data sources can be shared easily through workspaces and shared file locations.
Creation of Reports
SSRS
Reports must be created by individuals familiar with SSRS, as levels of coding are required to make detailed reports, typically in the form of paginated reports.
Power BI
Reports rely on a drag and drop method to create powerful visuals, allowing users to find useful insights on their own by creating new charts and graphs.
Report Structure
SSRS
SSRS predominately produces paginated reports filled with text and numeric data. Data is easily drilled down into to find causes and trends (Power BI also has drilldown methods to drill within a report and between reports, this isn’t a SSRS only thing)
Power BI
Power BI specializes reports consisting of visuals and some data tables. Slicers and other features allow whole report pages to be dynamic and update as filters are placed on reports
Microsoft’s Future Plans
SSRS
Microsoft is shifting their attention from SSRS as it is a legacy service and is quickly being replaced by the more capable and dynamic Power BI.
Power BI
Continues to receive updates every month. Power BI is much more than a report builder, it is a full reporting service. Each update continues to push Power BI to the forefront of Business Intelligence solutions.
Summary of Comparisions
While SSRS has certain use cases, Power BI is slowly becoming an all-in-one service that can handle any reporting need. Power BI provides a unified platform for all reporting needs within an organization and allows for all users to be empowered to work with data. The ease of use of Power BI combined with its monthly product updates allow organizations to foster a data driven culture that everyone can contribute in. It is important for organizations to utilize the data they collect, and Power BI gives organizations a vehicle to stay on top of their data.