Darren Gosbell

Category: Power BI Report Server

Diagnosing Performance of Paginated Reports in Power BI

Sometimes when you have a slow paginated report it’s hard to know where to start in order to improve the performance.

There is a feature in Paginated Reports on the Power BI service that was released a number of months ago which will show you a breakdown of the performance of that report.

After you have run your report you can access this feature by going into the View menu and clicking on the Diagnostics button

The Paginated Report Diagnostics dialog

Power BI Report Server and SQL Server Reporting Services do not have this button on the report itself, but you can get the same timing information by querying the ExecutionLog3 view in the ReportServer database (see Report Server ExecutionLog and the ExecutionLog3 View – SQL Server Reporting Services (SSRS) | Microsoft Learn)

This dialog shows us a number of pieces of interesting information about the report execution.

  1. Note the item at the top here which says “Capacity throttled = no” this applies to premium capacities and tells you if the capacity is currently in a throttled state. This is important as throttling adds a delay to interactive operations like report rendering and so your report may have slow performance because previous operations on the capacity have put it into a throttled state.
  2. This second section of the report shows you a breakdown of the different category of operations in the report as well as a count of the total dataset rows that were processed.

If you want to understand what is happening during each step of the performance metrics, I found the following information in this archived blog post from a former member of the SSRS team which breaks down the operations which go into each of these 3 categories.

Data Retrieval Time

The number of milliseconds spent interacting with data sources and data extensions for all data sets in the main report and all of its subreports. This value includes:

  • Time spent opening connections to the data source
  • Time spent reading data rows from the data extension

Note: If a report has multiple data sources/data sets that can be executed in parallel, TimeDataRetrieval contains the duration of the longest DataSet, not the sum of all DataSets durations. If DataSets are executed sequentially, TimeDataRetrieval contains the sum of all DataSet durations.

Processing Time

The number of milliseconds spent in the processing engine for the request. This value includes:

  • Report processing bootstrap time
  • Tablix processing time (e.g. grouping, sorting, filtering, aggregations, subreport processing), but excludes on-demand expression evaluations (e.g. TextBox.Value, Style.*)
  • ProcessingScalabilityTime**

Rendering Time

The number of milliseconds spent after the Rendering Object Model is exposed to the rendering extension. This value includes:

  • Time spent in renderer
  • Time spent in pagination modules
  • Time spent in on-demand expression evaluations (e.g. TextBox.Value, Style.*). This is different from prior releases, where TimeProcessing included all expression evaluation.
  • PaginationScalabilityTime**
  • RenderingScalabilityTime**

** The “scalability” times are when the engine does extra operations to free up memory in response to memory pressure issues during processing, pagination or rendering

Optimizing Report Performance

If you are interested in ways to optimize the performance of a paginated report, then many of the techniques outlined in this old article are still perfectly valid even though it was written for SQL 2008R2 – you can just ignore some of the points that are specific to on-prem scenarios like point 2 using Shared Data Sources which are not available in the Power BI service.

Power BI Report Server – fixing “Can’t upload this report”

A little while ago at my previous job we had a new user report an issue where they could not upload a Power BI Report to our Report Server and I found a simple fix which I thought was worth sharing.

In this case they were getting the error that the report was created with a recent version of Power BI Desktop that is not yet supported by this server.

So, how did this happen…

When you are running Power BI Report Server it is really important that your report authors use the Report Server version of Power BI Desktop that matches the release of your Server.

If you are not already aware there are actually 3 different distributions of Power BI Desktop

  • The Windows Store version
  • The Standalone installer version
  • The Power BI Report Server version

If you are running an on-prem Power BI Report Server, it is vital that you use the Report Server version of Power BI Desktop. Failing do so can mean that you may spend hours building a report that will not run on the server.

If you are not sure where to get this the Report Server Portal has a download link in the top right corner with a link (provided you are running one of the supported releases of Report Server, once the server goes out of support the downloads for Desktop are removed)

On my machine I have both the Microsoft Store version of desktop and the Report Server version. You can tell the difference because the Report Server version has a little black “RS” on the logo and the application title always includes the release month.

What had happened with this user was that someone had sent them a pbix file to work on. Being new to the organization they just downloaded the Microsoft Store version of Power BI Desktop, did their updates to the file and then tried to upload it to Power BI Report Server and this is when they got this error.

The problem is that now the file will not open in the Report Server version of Power BI Desktop since the file format has been updated to work with the newer version of Desktop from the Store.

Fix 1 – Manually rebuild the report

If your report is small and you realize this early, maybe just re-creating the report is an option, but often you do not realise this mistake until you have invested a significant amount of time in your report.

Fix 2 – Copy/Paste

One way to “fix” this is to run both the “Store” version of Desktop and the “Report Server” version of Desktop. Then open the file in the “Store” version of Desktop and manually copy the Power Query text from the Advanced editor, then copy measures and any calculated columns and tables, then copy visuals from one report to the other. As long as you have not used any new functionality that does not exist in Report Server this can work fine and is less work than a full manual re-build.

Fix 3 – Create a template file

This is the simplest option if it works.

All I did was to open the report in the “Store” version, did a “save as” and changed the type to a Power BI Template file. Then the user was able to open the resulting pbit file in the “Report Server” version of Desktop. Then I could refresh the data and upload it to Report Server.

This probably only works if you have not used functionality that only exists in the cloud service, but if you are using the lastest release of Report Server this number of things in this category should be relatively small. If you can identify any items like this, you may be able to remove them from your report before creating the template file.

Power BI Report Server – an unexpected error occurred (Nov 2021)

Earlier this week we started getting reports of the following error on our production Power BI Report Server (PBIRS) instance for any reports that were using a live connection back to one of our Analysis Services models.

All it basically said was “an unexpected error occurred. Please try again later”. And if you looked at the details it showed the message above saying that “We could not connect to the Analysis Services server”.

Some other fun facts about this issue are:

  • It was not affecting all users (at least in our environment)
  • There had been no recent updates or configuration changes on PBIRS, SQL or SSAS
  • The same issue appeared to be affecting our test environment but not our development environment.
  • It was happening for both pbix and paginated reports
  • It was affecting reports that sourced data from SQL Server as well as SSAS
  • When running a profiler trace against SSAS listening for the session events I could see some sessions with usernames and others where the user was listed as “ANONYMOUS USER”

Putting all of the above evidence together point to some sort of Kerberos issue. In our environment our SQL and SSAS instances are on different servers to our PBIRS instance we had to configure Kerberos with constrained delegation to allow for the user credentials to flow from client > to PBIRS > to SSAS.

Kerberos can be a little fiddly to setup initially, but once it’s configured I’ve never had an issue with it until the one this week. When we initially configured Kerberos I had distilled the generic instructions from the Microsoft documentation down into a series of specific steps for our production environment listing the specific setspn commands that needed to be run and how the constrained delegation settings needed to be configured and saved these in a document. This made it very easy to cross check that all these configurations were still correct.

At this point we knew that the Kerberos configuration appeared to be unchanged, but we still had an issue where it worked for some users and not for others.

So the next thing I started doing was to get some of our users to run the klist command from a command prompt. This command lists all the Kerberos tickets that are cached on the current machine. This lead us to find a pattern where everyone that was getting the error were getting their kerberos tickets from a specific list of domain controllers while people that did not have the issue were getting theirs from a different list of domain controllers. Our IT admins then noticed that all the failing servers were in a group which had had security patches applied earlier that day.

The specific patch that had been applied was KB5007206 and after raising a support ticket with Microsoft they were able to point us to the following hotfix (KB5008602) for this update which fixed an issue when you were using Kerberos authentication with constrained delegation and protocol transition (which is exactly how Kerberos needs to be configured when using PBIRS or SSRS or SSRS in Sharepoint integrated mode for people with on-prem Sharepoint environments).

Hopefully this post will help anyone else that runs into this issue.

Power BI Report Server – fixing the “Server is not reachable” Error

If you’ve ever worked with Power BI Report server there is a handy button in the top menu that says “Edit in Power BI Desktop”.

And as you would expect this button will open the report you are currently viewing in Power BI Desktop where you can edit it. All in all a very handy button, particularly when you get sent a link to a report and get asked to help fix something or explain some data issue.

The Problem

However recently I started seeing the following error stating “Server is not reachable” any time I tried to use this button

Power BI, Power Platform, Data Platform: In Action: Technical Preview of Power  BI Reports in SSRS

This is a bit strange as I’ve used this button many times before. So I tried going another way and using to File > Open in the Report Server version of Power BI Desktop and tried to open the report from the Report Server that way.

However I was again met with the same “Sever is not reachable” error which is not very helpful as I was definitely able to reach that server and view reports on it.

Error_Message.png

When this first happened I just went back to the tile view in the Report Server portal and chose the download option from the “…” menu on the report, then I was able to open the local copy and sort out the issue I had been asked to look at.

But today it happened to me again and I decided to do some digging to see if I could get to the bottom of this.

An initial solution

Doing some searches turned up a number of people hitting similar issues.

There was this one where the person was trying to connect using the webservice url instead of the portal url. But I knew that was not the issue in my case.

Then I stumbled across this one which suggested adding your credentials to the Windows Credentials section of Credential Manager. This actually worked for me, but I was not overly happy with this approach as in a few month time when my password expires I’d have to remember to come in here again and update my password. The more likely scenario would be that I would forget to do this and a few weeks later I’d realise that the “Edit in Power BI Desktop” button had broken again.

However while I was in the Credential Manager I clicked over to the web credentials and noticed the following interesting entry that started with SsrsSessionResourceKeyPrefix…

I knew that Power BI Report server was built on top of the old SSRS portal and when I expanded the entry it showed that these credentials had been saved by “Power BI”.

The Final Solution

Removing this entry and also removing the entry I just added to the Windows Credentials section fixed the issue properly. Now I don’t have to update a stored password and both the Edit in Power BI Desktop button and the File – Open option are both working as they should.

Power BI Report Server – fixing Pending subscriptions

Last week I came across an issue where a number of our paginated report subscriptions at work failed to go out. When we checked in the Portal these subscriptions had a status of “Pending”. This happened on our production Power BI Report Server instance which is currently running the Sep 2019 release.

This was very strange as these subscriptions had been running for months and months without issue and just stopped all of a sudden. It was even more confusing as we had some reports with multiple subscriptions and some of the subscriptions were still working while others were stuck with a status of “Pending”.

With no other information to go on in the Portal I started looking through the log files. This was complicated in our case as we have over one and a half thousand users and hundreds of report subscriptions. We also have 2 instances of PBIRS (Power BI Report Server) behind a load balancer which means two sets of log files to search through.

So I started by going to one of the pending subscriptions and clicking on the edit option

When you do this you will see the URL change to something like the following where there is a guid at the end with the SubscriptionId:

By default PBIRS writes a number of log files out to a folder at C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles If you look in this folder it will look something like the following with a bunch of different files with date stamps on the end. In this case because the issue is with a paginated report we need to look in the ReportingServicesService*.log files

What I found in one of the log files when I searched for the subscription Id was the following error

Error processing data driven subscription a743db7f-bbbe-4c45-9da1-2e2e286992dd: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name '<domain>\<contractor user>' is not recognized.

Where the <domain> was our company’s AD domain and <contractor user> was the login that had been used by a short term contractor that had worked with us to build the report in question.

In our case the account in question had been disabled when the contractor left the organization. So obviously nothing was checking the enabled state of this account.

But as it turns out that our AD team had done one of their periodic clean-ups yesterday where they actually deleted a whole group of disabled accounts. So it appeared that this was related somehow to this account no long existing in AD.

We already knew from years of working with Reporting Services that when a subscription is executed that the Owner of the subscription is checked to make sure a valid account is specified. (I believe this is possibly a security measure to stop people setting up a schedule to send reports to somewhere after they have left a company). However we already had the contractor set the subscription owner to one of our service accounts when they created the subscription to try and prevent this very scenario from happening.

In fact searching through all the properties for the subscription in the portal showed no sign of the <contractor user> account anywhere.

At this point I decided to open up a PowerShell window and use one of the tools from the ReportingServicesTools PowerShell module to see if that could shed any more light on this issue.

When I ran the Get-RsSubscription cmdlet I noticed the following:

Sitting in the ModifiedBy property of the subscription object was a reference to our <contractor user> which we were seeing in the error in the log file.

When running Get-RsSubscription against a report where some subscriptions were working and others were stuck in a “Pending” state I could see that the working subscriptions had a ModifiedBy of an account belonging to someone who still had an active account in AD.

My guess as to what is happening here is that Report Server is attempting to populate some of the properties of a user object from Active Directory and is failing now that the users has been physically deleted and this is throwing an exception that is preventing the entire subscription from continuing with it’s execution.

So if you only have a handful of subscriptions stuck in a pending state like this you can just edit them in the portal and make some non-functional change like adding a full stop to the end of the subscription name. This will set the ModifiedBy to your user account and the subscription will start working again.

In our case we took a backup of the ReportServer database and then ran an update statement to set the guid of the ModifiedBy to the guid of our service account user. This is not a supported activity and something you would do at your own risk. But in our case it did allow us to quickly fix numerous “broken” subscriptions that would have taken hours to fix through the UI.

Power BI Report Server needs an Admin Portal

I think one area where Power BI Report Server could do with some more work is in the area of administrator tools. At the moment if a report fails to render because of an error you have to wait for a user to report it. And if a subscription fails to send there is no central place where you can see these issues and easily take steps to correct them.