This article explains that on-prem report server is the best option for paginated reports as of now as per my experience and I am also recommending you to use the hybrid approach wherever it is possible. You can maintain a hybrid structure like, Power BI Desktop reports in Power BI Service and Paginated reports in On-prem server. This is something that you can prefer where you will get the more stable version of product with more features.
I am sure, Power BI team will make the Power BI Paginated Report as a stable version with tons of feature in future.
I am highlighting few points in which Report Server is getting more score than the Power BI Service.
License – if your organization has Power BI Premium Capacity node then you can get the report server product key and use them for on-prem installation or you can get it if you have SQL Server Enterprise Edition with Software Assurance.
Report Render Time-Out issue – We often encounter this issue in one of my client. We are using SQL Server and the database and we are handling large tables. If we run the query, it will take at least 5 to 6 mins due to relationship with multiple tables and conditions.
We have done all level of performance optimization but still same performance. If we use the same script in Report Builder it is taking more than 10 mins and same in service taking more than 10 mins and after that it end up with an error. When we check with Microsoft support engineer, they were highlighted that the issue is because of Authentication token expiration but not sure and there is no fix as of now. We tried all the approaches to overcome the issue but finally we end up with Report Server as an alternate solution for these kind of time-out issues. In Report Server we do have control to setup the time-out settings.
Subscription – Our customer had requirement to get the report’s excel export into their email or network drive. Power BI Service has Email option but no network drive as an out of the box feature. We need to use Power Automate to achieve the result which will be difficult for business users. This network file share is one of the default option in on-prem Report Server. Also we do have data driven subscription.
Gateway – Gateway is one of the component in Power BI Service which helps to connect Power BI reports with on-prem data sources. We always need to map the report with gateway data sources. If you have Power Bi Desktop reports then you can map the dataset once with Gateway datasource and you can replace them every time whenever you do the changes on the report and you no need to map the data source again and again for the same report where as you need to map with data source again and again whenever you overwrite the paginated report (rdl).
Security – In terms of security, we have to leverage only the pre-defined access levels which are Admin, Member, Contributor & Viewer in Power BI Service. These pre-defined access levels are more than enough for most of the cases but on few cases we may need to limit certain access. We have same kind of access levels in report server but we do have control to create our own access with defined controls. It will give more control to manage the access levels.
If you have PII data and you dont want to move the data outside of your network then report server is one of the best option.
Data Source Support – This is one of the feature that I highlight Power BI Report server is best. Power BI Paginated report support only limited data sources where as we have few more additional data sources like ODBC, OLEDB and etc supported in Report server paginated report. ODBC and OLEDB can cover many data source connections possible with Report Server report. One of the highlight of Power BI Paginated report, it supports Power BI datasets as a source which is not supported in report server.
Management – Power BI Service has workspace concept to maintain the reports for different teams and groups and etc. If you have multiple sections under one main group then we need to maintain multiple workspaces. In Report Server, all are folders, we can create folders, sub folders and can control the access to root folder or sub folder or report.
Additional Features – Power BI Report Server supports below features which is not fully available in Power BI Paginated reports yet.
1. History Snapshot
2. Cache control
3. Central Data Sources and Datasets
5. Document Map
6. Drill-through reports
7. Linked Reports
Development Tools – Power BI Report Builder is 32 bit and light weight tool and we have same kind of report builder for report server. Both of the tools hanging when we handle report with huge number of rows as a result. If you choose report server then you can use SQL Server Data Tools with Visual Studio and you can create reports and deploy. The development experience will be good when you choose Visual Studio.
Share your thoughts here.