Slow performance directquery to Power BI Dataset

[mee3uk]

Hi all,

i am importing data from a csv file into PBI Desktop and publishing it to our premium workspace. The report is blank, and we use it as a shared Power BI Dataset. This will have roughly 50m rows and refresh hourly. 

I then want other users to make their own reports using my dataset as their source. 

The issue is that when connecting to my dataset their reports are incredibly slow. A page would take 30 seconds to refresh every time you click on something. 

Alternatively - if i put the same report on the original file with the data import and publish it - refresh times are a couple of seconds. So the issue appears to be connecting to the dataset rather than any dax issues.

When i run performance analyser on the slow report the majority of the delay is labeled "Direct Query".

Iis there any way to make the performance better? many thanks.

[tanstar]

Hi @mee3uk ,

Have you decided the problem? I have the same one. If you know the clue could you please share it.

Thank you.

[mee3uk]

In this instance there are two pbix files being created. The first is an import from a csv and is published to premium workspace to be used later as a Power BI Dataset. If i made a report on this file it actually runs quite quick. 

The second pbix file connects to the above Power BI Dataset as its source and we create dashboards which are against published to the workspace. This connection is done over directquery and is far slower to refresh the page when we click around.

The idea is for lots of different people to be able to make their own dashboards from the same dataset. 

[v-chenwuz-msft]

Hi @mee3uk ,

According to your description, your model is import mode and data comes from csv. But as l know this "direct query" only appears in the direct query model.

You can check the gateway log to determine if there is a significant delay in connecting to the data source.

If the origianl data from .csv is very complicated and more steps to transform, this will consume more CPU and memory resources. And you can adjust performance based on cpu.

Adjust gateway performance based on server CPU | Microsoft Docs

If the method above does not work well, you can try to optimize performance by streaming data.

Monitor and optimize on-premises data gateway performance | Microsoft Docs

The article linked above also mentions that if you look at the gateway log and what some of the parameters mean, it can help you understand what your problem is.

The same, Optimization your data model maybe a good choice.

Best Regards

Community Support Team _ chenwu zhu

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