I am new to Power BI and have been asked to look at he benefits of going with Power BI Server...
From my initial readings, it seems the advantages are that we can brand the dashboard as our own, as well as host both our Power BI Dashboards and reports alongide our existing SSRS reports in a single web portal, with Power BI Server essentially superceeding SSRS? I have a few queries regarding the SSRS Reports.
1, Will Power BI Report Server essentially replace the need for our exisiting SSRS Report Home? Is it just a case of deploying our .rld reports and data sources to the BI Report Server?
2, Can we still maintain folder security for the report?
3, Can I still schedule (subscribe) the reports to run and email to users as and when we choose?
4, Can the reports to continue to run, via stored procedure calls to our SQL server database? I think I read that they must connect to a SSAS instance?
Thanks in advnace for any thoughts.
@MarkSL, I will answer what I can from your questions. I am deploying Power BI Report Server (PBIRS) to my production environment tonight after several months in test.
Regarding your first paragraph, it is my understanding that PBIRS does not support true dashboards, only reports. You can brand SSRS and that branding carries over to PBIRS. However there are bugs with the branding implementation in PBIRS (see my posted issue on this).
1) PBIRS does in fact "replace" SSRS. There are visual differences, but they are minimal. You do not need to move or copy the ReportServer databases, but you do need to uninstall SSRS first to prevent conflicts over URL reservations. The recommendation is to back up your SSRS ReportServer databases prior to starting the PBIRS installation process so you can roll them back if needed as the database changes are supposed to be one-way.
Also, all existing SSRS reports, data sources, and subscriptions remain in place and work under PBIRS (based on my own testing/situation). Deploying SSRS reports and data sources continues to work as before (I use Visual Studio 2015 Enterprise, and have not had any issues supporting SSRS reports in my test PBIRS instance that are then deployed to the production SSRS 2016 instance).
2) Yes, the folder security is identical for SSRS and PBIRS, and the PBIRS reports can be saved to the same folders as SSRS reports. PBIRS reports appear under a heading of "POWER BI REPORTS", while the SSRS reports appear under a heading of "PAGINATED REPORTS" within the applicable folder.
3) Yes, for SSRS reports. I have configured scheduled data refreshes for Power BI reports but not report subscriptions. I'm not sure that subscriptions are currently supported for Power BI reports.
4) Almost all of my SSRS reports pull both dropdown criteria values and report data source values from stored procedures, and they all work fine under PBIRS. For Power BI reports, I have one direct-connected to an SSAS cube and two that use an imported data model which is refreshed each night. This requires the October 2017 GA release. The older June 2017 release was SSAS only.
My Power BI report data models import data from SQL Server views that I created specifically to surface data to outside (of the source system) report consumers. I haven't tried using a stored procedure to provide data to a Power BI report, but when you select SQL Server as the data source you can specify a SQL data command.
Notes about the scheduled refreshes, and also Power BI logging in general:
- The refreshes do fail occassionally, and the latest status is shown under "Manage > Scheduled refresh" for the specific report.
- The refreshes are configured as SQL Agent jobs just like SSRS report subscriptions. Reporting on SSRS report subscription history will include the Power BI data refreshes.
- Report execution history for Power BI reports is not the same as SSRS reports. Different numeric codes are used, and EVERY interaction with a report is logged (each filter, each slicer, each click on a visual, etc.). I wanted to include report execution stats for Power BI on my SSRS execution monitoring report and it killed my results. Future R&D effort 🙂
- There is one bug when first setting up data sources for a Power BI report that also needs a scheduled refresh. Both of my reports have 2 data sources, so that may be causing it. Basically, you set up the data sources before proceeding to create the scheduled refresh, but after entering the data source info the applicable menu items lock up. The workaround is to re-deploy the report to the server, re-enter the data source info, and then voila, you can create the scheduled refresh. A relatively minor annoyance, once per report.
I hope this info helps!
Many thanks for your detailed reply and good luck with your deployment tonight!
Regards to PBIRS replacing SSRS, this is good news, as we will be hosting it on a new server and it should save time not having to think about installing SSRS.
As for subscriptions, also good news on the SSRS (now Tabular reports) front. I had also read that subscriptions for Power BI reports was limited to alerting a user, by email, that a data refresh had occured with a link to the report on the dashboard.
With regards to the logging, all our reports run via stored procedures, which in turn write to our own reporting log file, recording for every submission, the user, report name, start time, end time, run time, rows returned etc, so hopefully this will continue to serve us well and not mean we need to look into the bogged down system logs.
Thanks again for your detailed reply, I'm sure I'll be back for more in the not distant future!
PBIRS is combination of Power BI report server and SSRS server which comes only with SQL Server Volumne license, or you need to buy PBIRS seperately for the use, it is not a replacement for the SSRS but, it is combination of SSRS and Power BI server allowing you to run you SSRS report as well as Power BI reports at a same server will all the permission and security features that are applied to SSRS report.
Hope this helps.
Does anyone know or have experience with Power BI Report Server and SSRS automation? We have SSRS at the moment and we automated the execution of reports using SOAP and ReportExecution Class (ReportExecution2005). Wondering if this service is part of Power BI Report Server?
Appreciate your detail explantion on SSRS.
I have recently migrated SSRS 2008R2 server to PBIRS 2017. We used backup restore to migrate databases, copied encryption keys and scripted jobs on old server to new server. we able to view reports in web portal without any issues. When we run scheduled jobs to email reports, jobs are executing successfully, but we dont recieve any reports.
am I missing something here?
SQL server and report servers are configured in two different servers. DBMail is working fine.
@samalat , hopefully this one posts - my first blew up. I would suggest modifying one of the subscriptions which is failing to just email to you, then run it directly in the Report Manager to see if it delivers. If it succeeds, you probably just need to edit each subscription and then save it to resest whatever got out of sync. If it fails, try creating a new subscription from scratch and running it immediately. If that fails, you have a bigger issue with server config. You mentioned Database Mail works, but I don't have a split server setup and I wonder if your report server IP address has to be setup in your mail server as an allowed SMTP email source as well as the SQL Server.
Thank you for the inputs.
modified one of the subscriptions which is failing to just email to you, then run it directly in the Report Manager to see if it delivers. - Failed.
If it fails, try creating a new subscription from scratch and running it immediately. - Failed.
If that fails, you have a bigger issue with server config. You mentioned Database Mail works, but I don't have a split server setup and I wonder if your report server IP address has to be setup in your mail server as an allowed SMTP email source as well as the SQL Server.- Added both Report and sql server in mail server.
I see some frrors in log files. can you help me on this please.
library!WindowsService_7!efc!03/21/2020-11:21:37:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.;
library!WindowsService_7!efc!03/21/2020-11:21:37:: w WARN: Transaction rollback was not executed connection is invalid
schedule!WindowsService_7!efc!03/21/2020-11:21:37:: i INFO: Error processing event with data: TimeEntered: 03/21/2020 11:21:37, Type: Event, EventType: TimedSubscription, SubscriptionID: c73df455-e214-4965-9c1b-2f83ec559feb. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.ExecutionCache'.
@samalat , great troubleshooting! You definitely have a more complex server config issue going on. I did a Google search for your complete error message and got the following promising links. Given my experience with in-place and side-by-side upgrades, I know that SIDs can get misaligned, SQL Agent job scripts complied against one server may not work correctly on another server, and PBIRS data connections can get corrupted. Everything has to be suspect until is has been proven to work. Check out the solutions in these posts, and/or Google search for the error message for more options (I ignored the ones earlier than 2017). Please post back any findings so we can all be in the loop.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!