Multiple viewers of one Paginated report - database impact

[WheresWally]

Hi, 

I have a paginated report designed to update frequently (1 per minute) and run an SQL query via a gateway. 

My question is: If say 10 separate users are viewing the report at the same time, are they all hitting the database with that query every minute, or is one user enough that it then shares whatever they are getting with other viewers of the report?

I'm trying to track down documentation on how this works if any

Thanks for any help. 

[WheresWally · Accepted solution]

Hi All, I raised a ticket and got a clear answer that paginated reports in Power BI service do not cache or share datasets yet. Each user viewing will always cause unique queries to the database. 

Here it is word for word from the very helpful support team:

"

Hello WheresWally,

Greetings for the day..

Appreciate your patience, Regarding your concerns I checked with my internal team and we received some update.

Issue 1: Multiple viewers of one Paginated report - database impact

If multiple users are viewing same report at same time then multiple queries will hit datasource to get refreshed data, in power bi service everything will be specific to individual user or session this is how power bi service works.

Issue 2: Cache copies of this report and use them when available

Right now there is no option something like this in power bi service, the only thing we can do is to submit this idea to Microsoft, based on votes to this idea maybe Microsoft will consider this idea to implement."

[v-janeyg-msft]

Hi, @WheresWally 

How do you refresh the data every minute?

[WheresWally]

Hi @v-janey-msft, I use the report properties pane in Power BI Report Builder. In the 'Other' section of that pane there is an auto-refresh setting which you can set 

[v-janeyg-msft]

Hi, @WheresWally 

Yes, you are right. 

I haven’t refreshed the pagination report, so I took it for granted. I'm very sorry. The paginated reports don't have  dataset in pbi service, so there is no schedule refresh and manual refresh of the dataset.

So every time you open the report and auto refresh, there will be a query sent to the source data.

So inevitably, when there are many users, there will be many queries sent, which may burden the database.

This is what you need to pay attention to. It seems that there is no better way currently.

You can check the video:

How to Refresh Power BI Paginated Report (13-6) TAIK18 - YouTube

Best Regards,
Community Support Team _ Janey

[WheresWally]

Hi, 

I don't think that's the case. Paginated reports don't use scheduled refreshes but rather they send an SQL query direct to the data source (via a gateway or not) each time the browser page is refreshed. The auto refresh does refresh the data through the query in that way. 

Edit to include this:

If you upload a paginated report to the service, go to the Manage option on it to see only Gateway connection and Data source credentials settings:

No scheduled refresh setting  as it doesn't make sense to refresh a dataset which is not there until the report is loaded and which will refresh based on the AutoRefresh or whenever the user comes into and goes out of the report. What I'm after are similar settings to report caching that you see here in normal report types. Or what the default arrangement is. 

[mwegener]

Hi @WheresWally ,

I assume that each user request is handled separately (isolated), so as not to conflict with user rights etc.

[WheresWally]

Thanks @mwegener , appreciate you taking the time to consider the question. 

I'm hoping there is some kind of built in caching but as you mention, we can only assume at the moment as there doesn't seem to be any answer either way in online documentation. 

The closest I could find was the purple note in this page mentioning 'per session' caching in relation to filtering and parameters.: https://docs.microsoft.com/en-us/power-bi/guidance/report-paginated-data-retrieval#filter-vs-paramet...

What I'm really after is something like this option (2) which is present in the web portal:

https://docs.microsoft.com/en-us/sql/reporting-services/working-with-paginated-reports-web-portal?vi...

[WheresWally]

I should note, I'm hoping for a definitive answer either way from someone in the know at Microsoft before accepting a solution - or some doco somewhere to the same effect. Do appreciate anyone wanting to join the conversation though. Not sure how likely a definitive answer is. 

[bradsy]

One option would be to create a Power BI Dataset as your data source. Then point your paginated report at the dataset instead of directly at the SQL data source. On the PBI dataset, you could then set your scheduled refresh.

[WheresWally]

I should say while this might work for some, a 30 minute refresh schedule won't work for a paginated report that needs fresh data every minute. I created a ticket with Microsoft and this has since been selected as the solution where I'd argue that my question re per minute refreshing being cached for multiple viewers has not been solved. 

[WheresWally]

Hi @bradsy 

Thanks yes, I think that's modern version of a shared dataset and would work okay for data that only needed 30 minute updates, but for live data needing about a 1 minute refresh in a paginated report it's not going to work.

I would just use a normal report with direct query without requiring a paginated report at all if it weren't the case that Power BI's mapping options aren't very customisable. I have a live map which is very detailed in ways that even the icons map won't quite achieve. So to make use of an already completed live mapping solution, I really do need a frequently refreshing paginated report which doesn't overload the SQL server with hits from many simultaneous users.