PBI Pro - Couldn't load the data for this visual

[RHennings]

Hello together,

we are not using PBI premium (just pro users) and today only one report (out of some more in the same workspace) could not be loaded at all. Slicers were displayed correctly. For all visuals, which use measures we received an error message like the following:

The issue existed for a while (at least for more than 10 minutes). Now the report works fine again.

The underlying dataset (Import mode only) is not extremely huge. For some pages the number of (often simple) visualizations is high, which we feel might be one element of the issue. 

I have a few questions:

- What in general and what in this case could be the cause?

- Is there a way to comprehend, which report pages or visuals are causing the memory issue?

- Is there a way to find out how much of the available memory is being used by a still working report?

Thank you

Robert

[RHennings]

So the reason for the described issue seemed to be that users, who connect with the dataset via Excel and ran expensive queries like "show me all the materials and some aggregations of several measures without constraining many of the slicers", blocked the datasets resources/memory. It took some time until the dataset "recovers", so that normal users can use the usual reports again. Refreshing the dataset helped then. 
In the report itself there are not so complex queries, but we dont have the control, what the Excel users are building.

[aj1973]

Hi @RHennings 

For a pro licenses the workspace works with a shared capacity, but this wouldn't be the cause if you said that the dataset of the report is not "huge" (to be verified). The cause could be the refresh scheduled time for all reports in the workspace is running at the same time for all. could be also measures or calculated columns in the report that are not optimised. there are many videos on how to optimise DAX measures, Columns and many other things.

I would recomend  to start by analysing the Time scheduled.

[RHennings]

Hi @aj1973 ,

thank you for your answer.

regarding the size. It is 87 MB (Screenshot from the dataset settings in the service)

All the dataset refreshes happen in the morning and all are finished at ~ 6am. The issue existed around 12 -1 pm. No other report in the workspace was affected to that time. Just this report and all the pages of it. Thus I dont think, that it has something to do with the refreshes.  

Measures/Calculated columns: We are not using Calculated columns at all & most of the measures do not have complex logic (YTD, MTD, Deviations, etc.). However, there are still a few more complex measures using e.g. iterator functions or switches. In the past, when I used non-optimized measures, the report was just getting really slow. Then I was able to comprehend with e.g. performance analyzer, which measure causes this. But in this case none of the visuals even started loading. As all visuals were affected I had no chance to identify a potentially malicious measure. Therefore the questions for better comprehension.

I am still not sure, if it is an issue of the report/datamodel or if there was a temporary issue in the service, as we have seen this issue only twice. In general this report doesnt have long loading times (1-3 seconds)

Kind regards,

Robert

[aj1973]

Hi @RHennings 

Indeed, the size of the PIBX file is not big but dosen't mean the performance of the dataset is optimised.

It could be the Model/relationships, the Queries against the sources......I can't keep speculating on the matter without touching base of your PBIX, Sorry.