SSRS vs Power BI

Problem

When contemplating a dashboard or reporting platform using the Microsoft Business Intelligence (BI) development stack, you have 2 main reporting tools to choose from – Power BI vs SSRS. But how do you select the most appropriate visualization tool for the needed solution? Which tool addresses the needs of the report consumers and produces the least amount of technical debt while also using the least amount of development and testing time?

Solution

In this tutorial, we hope to address the pros and cons of each BI tool in order to assist you in making an informed decision about which tool to use for your business users. We will also theorize just a bit about the future of SSRS and Microsoft Power BI. In either case, you must complete some installs to start working with either tool.

For SSRS, you can take a look at this tip for assistance centering on the installation and setup of SSRS (SSRS Install, Setup and Configuration). Additionally, either Visual Studio with the SSRS extensions must be installed (SQL Server Reporting Services SSRS 2017 Installation and Configuration Setup) or Report Builder (Report Builder 3.0 Tutorial). For Power BI, Power BI Desktop must be installed; these tips can assist with those install processes: Install and configure Power BI Report Server and Power BI Desktop and Getting Started with Power BI – Part 1.

Basic Differences

While working with both SSRS and Power BI, some basic differences permeate through all the different decision-making processes that will need to be made. First and foremost is the ultimate presentation for the report.

At the bottom of the tutorial, there is a table that outlines each section and where each product may be a better fit based on that feature for your own data analysis.

Below are different things to consider when picking Power BI vs SSRS.

Data Visualization Selection

Within the visualization category, Power BI definitely displays its advantage over SSRS by having approximately 30 visuals available in the visual gallery; SSRS contains roughly 10 base visuals, although one is the chart visual which allows for the selection of approximately 10 chart types. However, Power BI allows the power of community development to also have additional visuals, some free and some with a cost, that can be searched for and installed from the Power BI section of the App Store. For many visuals not available in the base set of Power BI visuals, the APP stored provides many needed visual options. Of course, care should be taken utilizing these visuals; some are Microsoft certified which provides a level of assurance that you are not sending your data to some rogue character.

Winner: Power BI

Printing

If the report needs to be printed especially with custom printing options, SSRS is the clear winner. A SSRS report includes formatting for exporting to a PDF file, a Word file, or even directly printed all with set page breaks (SQL Server Reporting Services Controlling Report Page Breaks) and with fixed page and group headers and footers (SQL Server Reporting Services SSRS 2017 Design Grid). Power BI dashboards can also be exported to PDF files or printed, but those exports currently are a what you see is what you get export, meaning if any scroll bars are present, nothing below the scroll line will be exported to the PDF or printed.

Winner: SSRS

Interactivity

Related to the printing question above, is the subject of interactivity. While SSRS does allow for modest “drill down” interactivity (SQL Server Reporting Services 2012 Drilldown Features and SQL Server Reporting Services 2012 Drilldown Features), Power BI is the winner in this category. While Power BI provides similar drill down functionality (Power BI Drill Through Example), it also provides a rich interactive experience via contextual filtering: Power BI Visual Interactions. Additionally, Power BI allows the use of filters and slicers, which are described below, to further enhance the interactive experience.

Winner: Power BI

Report and Dashboard Generation

For Power BI the conceptual idea for report consumer is that when the dashboard loads, it loads quickly with all the data rendered at that time. The data is stored in a compressed storage model on the Power BI service and requires a refresh schedule to keep the data up to date. This setup assumes that the data sources for the dashboard are using import mode as opposed to direct query, which executes the queries at the time of the report rendering. SSRS uses the later approach; it queries the requested data sets when a report is rendering. There is the option in SSRS to cache a report and store a temporary version of the report for later retrieval within a specified period of time. As you can see, there is a fair amount of overlap in this area, but the essence and purpose of the two tools is accented by the way and how fast they render the data to the report consumer.

Winner: Tie

Parameters

The next set of features actually cover several differences surrounding inputs, parameters, and report generation. Within this category, no specific winner is declared as it depends on the wants and needs of your report audience. First, although both SSRS and Power BI use parameters, their implementation is very different in the two tools. Parameters within SSRS are generally designed as a method to filter a query, dataset, report or to potentially control the display of a report header (SQL Server Reporting Services SSRS 2017 Parameters). These parameters are sourced from dataset queries or potentially manually input. Default parameters can be set in SSRS which allows for auto running of reports or even subscriptions. By comparison in Power BI parameters are used more like potentially a variable which can be set during a data set refresh (Using Parameters in Power BI). However, Power BI utilizes slicers and filters to act as criteria to control what is being displayed on a dashboard. The primary difference between slicers/filters and SSRS parameters is SSRS parameters are generally set before querying the main dataset or displaying the main SSRS visuals whereas in Power BI, the filter or slicers is filtering the data after the dataset has been rendered.

Thus, it truly depends on the needs of the report consumer on whether Power BI filters and slicers are the best choice or whether a SSRS report parameter is the best choice.

Winner: SSRS

Data Sources

The number of available data sources available in Power BI surpasses the number of available connections in SSRS, likely by a 5 to 1 ratio. The list of available sources is every expanding, out of the box (see Power BI Desktop Data Source Considerations and Using Power BI with JSON Data Sources and Files). However, by using external, non-related ODBC connections, a wide variety of data connections can be made in SSRS, but not near the direct connections available in Power BI (SQL Server Reporting Services SSRS 2017 Data Sources). For instance, Power BI allows directly connects to tables which are available on various websites. In both SSRS, by using a shared data source, and in Power BI, a data source can be shared across various data sets.

Winner: Power BI

Sub Reports vs Tabs

Similar to the parameter vs slicer dilemma, the SSRS sub-report vs Power BI tab issue is a toss-up. SSRS sub-reports, Multi-detail reports using sub reports in SQL Server Reporting Services, present the ability to include the contents of another developed report within the body of a master report. To the contrary, Power BI uses a tab concept including when drilling down from one report tab to another. The advantage to the tab approach is that it allows a multitude of tabs to be added to a single report, all from one or many data sources.

Winner: Tie

Tools to Develop Dashboard and Reports

Several options are available in order to develop reports, either Visual Studio or SSRS Report Builder. Both options allow for development of reports, but most BI developers find Visual Studio provides more flexibility although most functionality is available in Report Builder. Review this article, Visual Studio 2019 Configuration for the Production DBA, to get Visual Studio installed on your machine. Closely review the section about installing extensions like Microsoft Reporting Services Projects. This extension is needed to design, develop, and maintain SSRS reports in Visual Studio. The Visual Studio extension also lets you import existing SSRS reports into a project which lets us use source control. Finally, the SSRS Project extension includes a wizard to guide you through report project creation and report file design which is helpful for first time report developers.

Power BI utilizes a single tool called Power BI Desktop to develop all Dashboards; although an online version is also available and which has limited features (Compare Power BI Desktop vs. Power BI Online and Power BI: What I wish I knew when I started?). The desktop tool allows for both creating data sources in the Power Query Editor, which uses the M language, and also the design section that is used for the actual design of the dashboard itself, along with allowing columns and tables to be added via DAX (see below).

Winner: Tie

Languages Used

For most database-oriented folks, SQL is a commonly learned language that is used extensively in both SSRS and in Power BI data sources. In addition to SQL, SSRS may require the use of MDX if an Analysis Services data source is used. On the other hand, Power BI also potentially requires the use of M and DAX. Both M and DAX are not difficult to master, but they are both currently evolving as new functions are added regularly (please see: Dynamic Power BI Slicer Using DAX Logic and Power BI Histogram Example using DAX and Create Calendar Table Using Power Query M Language and Introduction to the Power BI Desktop and the M Language).

Winner: Tie

Report Size

Generally, the size of a SSRS Report RDL file is quite small as it does not contain all the data from the data sources. To the contrary, even with the compression that occurs in Power BI PBIX files, the file size can grow quite large as significantly sized data source imports occur.

Winner: SSRS

Security and Row Level Security

Security is ultimately a very important consideration for both tools. Through the use of Folder access in SSRS (SQL Server Reporting Services SSRS 2017 Website Security and Options) and through Workspace and APP permissions in Power BI (Power BI Workspace Permissions and Roles) , a report designer can limit who has access to see a report or dashboard. In both cases individual end users and network AD groups can be assigned to view or read access or build / publish related permissions. Furthermore, Power BI also includes the ability to use distribution lists. However, where Power BI shines is through the availability to quickly implement Row Level Security (Power BI Row Level Security

Winner: Power BI

Licensing

Licensing is truly about how much does it cost to get the product to the report consumers. For SQL Server, of which SSRS is part of, the licensing somewhat straight forward. To use SSRS in a product environment, you will need a SQL Server license (SQL Server Licensing Model and Costs). On the Power BI side, the licensing does get more confusing and complicated with different tiers of users, Pro vs Power BI Premium, along with decisions about paying per user or per capacity. Each tier offers different features and limitations around items such as model size, refreshes per day XMLA access points, and data flows. The best place to start your research is at: Power BI pricing . From a clarity standpoint, SSRS clearly wins in this area.

Winner: SSRS

Alerts and Subscriptions

Both SSRS and Power BI include subscription functionality, but the implementation of subscriptions is quite different between both tools. SSRS allows both simple and data driven subscriptions to be emailed or saved to a file location. The format in SSRS is any export format available in SSRS and includes file types such as PDF, Word, Excel, and Power Point, along with HTML based email. This tip provides an excellent basis for implementing data driven subscriptions: Reporting Services Subscriptions in SQL Server 2019. Likewise, this tip provides details on the basics of setting up a SSRS subscription: SQL Server Reporting Services 2016 Subscription Enhancements. Although somewhat similar, Power BI subscriptions take a similar form of being able to email a dashboard report tab, but what is emailed is only what would normally show on a single page (see Power BI Subscriptions). In addition to Power BI subscription, Power BI also includes Alerts which are based on data in only certain KPI type visuals such as Cards, KPI, or gauge visual. The alert is “sounded” when a predefined threshold is met (Power BI Features for Notifications). For customization of subscription emails, SSRS is the clear winner, but Power BI wins in the Alerting functionality.

Winner: SSRS – subscriptions and Power BI – alerts

Mobile

Of course, many folks will want to see their reports on the go via a mobile app, and thus will want access via their mobile device. Both SSRS and Power BI allow for mobile views of a report. However, Power BI definitely has a friendly user experience when designing and viewing dashboards on a mobile device (Power BI New Mobile Development Features). However, SSRS has recently provided a set of tools to assist in Mobile SSRS report development, Power BI New Mobile Development Features).

Winner: Power BI

Summary Table of Feature Winners

In the below table, each feature listed along with the preferred tool for that particular feature:

Future of Power BI and SSRS

This part of the tip is pure speculation on what is to come; with the introduction of the Power BI Report Server (Introduction to Power BI Report Server) and with the ability to pin SSRS reports to Power BI (Pinning a SQL Server Reporting Services Report to Power BI), I would suggest that eventually both products may merge into a single unified solution with the ability to develop, generate, send, and alert on both paginated and dashboard solutions.

So which tool is better? That is like asking a parent which child he or she likes better…IT DEPENDS on what solution you are aiming for from the business intelligence tool!

Next Steps

• Review the links in this article as well as these additional resources:
  • All SSRS Tips
  • All Power BI Tips
  • Create a SSRS from an SQL Server Analysis Services Tabular Database
  • Integrating SSRS with SharePoint