Challenges in replacing SSRS with Microsoft Power BI
There are several reporting tools and platforms available today, and enterprises usually choose the one that is best suited for their business needs. Two popular options for reporting platforms are SQL Server Reporting Services (SSRS) and Microsoft Power BI.
SSRS AND MICROSOFT POWER BI
SSRS is a server-based reporting platform that comes free with SQL Server 2012. It has been one of the most powerful tools for several years and includes a set of integrated components and features that can be used to create custom reports.
- SSRS allows better and more accurate decision-making mechanism for the users
- It allows users to interact with information without involving any IT professionals
- It is an intensified tool compared to other crystal reports
- It provides a host of security features
Microsoft Power BI is a business analytics tool, which is a collection of apps, connectors, and software services that work together to turn unrelated sources of data into coherent information. Power BI connects to data sources and analyzes what is important while allowing users to create and view reports and dashboards for a 360-degree view of the business from all the diverse sources.
- It is widely used for modeling and structuring of unshaped data.
- It can process a large amount of data
- It helps in transforming enterprise data into rich visuals
- It can be embedded into any custom apps
MIGRATING FROM SSRS TO POWER BI
For SSRS, the report server is configured in two ways.
- Native Mode
- SharePoint Integrated Mode
Based on the mode of the existing SSRS mode, we can migrate the reports from On-Premise SSRS to Power BI report server.
Here are the typical steps for migrating from SSRS to Power BI:
- Backup the database, application, and configuration files
- Back up the encryption key
- Clone your report server database hosting your reports
- Install Power BI Report Server. If you are using the same hardware, you can install Power BI Report Server on the same server as the SSRS instance.
- Configure the report server using the Report Server Configuration Manager and connect to the cloned database.
- Perform any cleanup needed for the SSRS (Native mode) instance
Migrating from an SSRS (SharePoint-integrated mode) to Power BI Report Server is not as straight forward as a native mode. There are definitely some challenges to be faced in this migration.
CHALLENGES IN REPLACING SSRS WITH POWER BI
There are some basic differences between Power BI and SSRS, and therein lies the challenge in replacing SSRS with Power BI. Power BI was not designed with SQL in mind, and hence replacing SSRS with it poses challenges.
ON-PREMISE VS CLOUD
- Power BI is cloud-based whereas SSRS is server-based. This causes a fundamental difference in the way data is stored or published
- Power BI can, therefore, use data from the cloud from several sources. SSRS, on the other hand, can only access data from on-premise servers
- In SSRS, you need to purchase a SQL Server license, define requirements and scope, customize/develop your software according to the requirements, deploy, develop reports in a coding interface, schedule data refreshes, etc. Power BI is free, however, you require purchasing Power BI Premium license for PBIRS.
- Replacing the server
- Migrating SSRS 2012/2014/2016 to Power BI is fine. However, migration from SSRS 2017 has some issues with the PBI RS server. The SSRS configuration manager forgot to run a script that updates the SSRS 2017 ReportServer database so that it can actually work with the Power BI objects. To solve this, after migration you need to open up SQL Compare, compare the schema of the SSRS 2017 database with a brand new ReportServer database created by PBI RS server, and push the missing objects into the SSRS database. Restart the service for it to work.
- Specific report server content from SharePoint needs to be migrated to the Power BI Report Server. For this, Power BI Report Server should be installed somewhere in the environment.
- To copy the report server content from the SharePoint environment to Power BI Report Server, tools such as rs.exe should be used.
- Reports in SSRS and Power BI
- SSRS is conventional and involves manual effort and time to create reports and analysis. Power BI, on the other hand, is more modern and graphical. Power BI has a graphical interface with the drag-and-drop capability to create reports. You can work with unstructured data, use modern rendering, publish, integrate and collaborate easily across platforms.
- For simple SSRS reports, it is not hard to move the queries over to Power BI and then reproduce the same visuals (tables, charts, etc). Power BI has a focus on interactivity and data exploration that SSRS does not. As you convert reports, you can update them to take advantage of features like slicers and cross-filtering and drill-downs.
- On the other hand, SSRS has a lot of fine-tuning options that simply do not exist in Power BI (including an entire expression language). There are a lot of things you can do with SSRS that you can’t easily recreate in Power BI (which is why a general conversion tool would be very difficult to write).
- Power BI Report Server (PBIRS) is the successor of SSRS and has more features than SSRS. For one, it can use and render both interactive PBIX and analytical XLSX reports which SSRS cannot.
- PBI now has Cortana integrated for AI-based natural language, Q&A about your data and reports in Power BI. SSRS does not have this feature.
- If you are using SSRS/Paginated Reports, you can still use the regular data connections. With Power BI reports, you can access only the Analysis Services.
- Credentials must be stored in the report in order for the object to refresh. The pinning process actually creates a SSRS subscription that requires stored credentials in order to execute. Furthermore, if you change the item name, the object will stop updating based on the subscription. In addition, if the pinned object is deleted, the related subscription is not deleted and must be removed manually.
- Lastly, if you try to pin an object and the SQL Server Agent is not running, an error results. The SQL Server Agent where the SSRS database resides must be running in order to pin objects.
Replacing SSRS with Power BI Report Server is not easy. If you are currently using SSRS and its suiting your needs just fine, migrating or replacing with Power BI may not be a wise choice.
Replacing with Power BI may be considered in cases where you are looking to build many new reports or you need reporting on a variety of data sources. If you are writing your reporting services from scratch, Power BI is the way of the future. It also offers a more visual and interactive UI for the reports.