How many report viewers can BI service support before performance degrades?

[wpf_]

So we have a report in premium capacity and we have 500 users we have given rights to view the report.  Although not all 500 will ever view or refresh at the same time, maybe worse case say 100 users decide to refresh the visual and view at the same time, will that slow down the report substantially?  This is a directquery report.  

[selimovd]

Hey @wpf_ ,

when it's direct query, then the database has to process all of the request at the same time.

But nobody will be able to give you a proper answer without any details. It depends very much on the report, how many visuals, how complex, how many clicks an average user is doing and also on the underlying database.

If it's on a 20 year old machine with 2 GB RAM and without any optimization, it will struggle earlier than on a Parallel Data Warehouse with 128 GB RAM and an optimized index or a column store index. Also check the server logs and performance to see how it's doing.

But usually you will get a feeling with a growing audience when the server reaches it's limits.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[wpf_]

@selimovd Thanks for your reply.

So in directquery it comes down to the datasource database and not BI service?  

We are connecting to a Microsoft dynamics sql server data source.  

The report only has 1 table visual, and a date picker, and two slicers.    Once the user selects a date, and slices the filters, the table will show the records.  

I guess it comes down to trial and error?

If it do slow down, what are my options or alternatives to remedy it?

[selimovd]

Hey @wpf_ ,

yes, directquery means there is no data stored in your file or in Power BI Service. Whenever the user is opening the report or clicking something like a slicer, or a visual, etc. the query is sent to the underlying data source, the SQL query is executed and the result will then be shown in the report.

Why are you using direct query? In general I would be very selective with DirectQuery, I would only use it when there is a reason to use it. Depending on your case you could import the data to the file, then you have all possibilities within Power BI and the users are querying Power BI Service and not your server.

Yes, take a look or if there is no reason for Direct Query change the report to Import.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[wpf_]

@selimovd ,

I am using directquery because there is new data in datasource all the time and a data refresh with import mode would take too long.  

Does import mode work faster with more user's accessing the report then with directquery?

[v-deddai1-msft · Accepted solution]

Hi @wpf_ ,

>>I am using directquery because there is new data in datasource all the time and a data refresh with import mode would take too long.  

In original post, you are using premium capacity. So you can try to use incremental refresh, you don't need to refresh the whole dataset when you refresh the dataset. Please refer to :https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

>>Does import mode work faster with more user's accessing the report then with directquery?

It's quiet hard to say. In import mode, when users intreacte with reports, the whole dataset will be load into memory, if your dataset is very big, even though it is compressed to stored in power bi service, it will take  a long time. All the users who use the report will be impacted by the speed of loading dataset into  power bi service memory.

In direct query,  as mentioned by selimovd,  clicking something like a slicer, or a visual, etc. the query is sent to the underlying data source, the SQL query is executed and the result will then be shown in the report. If  users use the report at the same time, it will generate lots of queries to underlying data source. The performance will be impacted by the performance of data source and the network.

In your situation, it's hard to say which one is faster, But I suggest you use import mode for first. Beacuse you have premium capcity, you can configure workloads in a Premium capacity:https://docs.microsoft.com/en-us/power-bi/admin/service-admin-premium-workloads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai