Export underlying data from a report in Power BI service takes long time while in direct query mode

[Anonymous]

Export underlying data from a report in Power BI service takes a long time while in direct query mode.

The summary data export works fine.

The report has three measures in the form of a donut chart. If I remove the measures and replace with aggregate columns, export works fine. Problem is - that approach will not solve the business problem.

Data source: Azure SQL DB

Mode of connectivity: Direct query

Please advise if and how this can be fixed. If you need any details please let me know.

[v-yulgu-msft]

Hi @Anonymous,

Per my understanding, in the DirectQuery or live connection case, viewing data sends queries in real-time to the underlying data source. Your report performance in these cases depend largely on the performance of the underlying data source. Large amount of data and complex measure formulas will affect the report performance.

Regards,

Yuliana Gu

[Anonymous]

Thank you. I thought so, as well.

Unfortunately, business users are accustomed to using this method as a validation technique. This worked fine with simple aggregates at the visualization layer. But the moment the logic started getting complex, we had to introduce measures. So, when they saw that they were no longer able to conveniently export underlying data as it took a long time, it led to unhappiness. 

I have suggested using data extracts from the database for data validations, but I am finding it difficult to get a concurrence. 

Have you ever faced a situation like this? Any suggestions for me?

For back-end data, it is Azure. If I have to talk to the engineers who maintain that environment (I do not have access), what should I tell them to look at?

[Anonymous]

I am just wondering if export underlying data option should even be enabled in direct query mode, if there is a question on performance every time we are using measures and complex calculations in visualizations. Just raises user expectations and creates frustration when users are unable to export the data.

Add to this the concern around data privacy and security since it's not summarized data anymore. Anyone with access to the report can see granular data with sensitive information.

I am hoping for some thoughts around these, please.