Monitor Average Execution Time etc for live Power BI reports

[Steffe]

Hi,

Is there a smart way to get some kind of average execution time for a (live SSAS) report in Power BI Report Server.

I know about the dbo.ExecutionLogStorage table in the ReportServer-database, but my problem is that it seems that I get multiple rows in this table per render and I can not find a way to relate which rows that belongs to the same "report refresh" so to say. It would also be interesting to see how many times a report has been "refreshed" (for example filter changed) per day, but that is kind of the same problem.

I see that the first time you open a report you get a line with ReportAction=13, but if you then just change a filter in the report so that the report is refreshed you get multiple lines with ReportAction=14.

Maybe I can sum up the execution time (TimeEnd-TimeStart) to get some kind of total, but how to get a good average or execution count?

How do you monitor the performance of your reports?

[josef78 · Accepted solution]

Hi,

first, use ExecutionLog3 view instead physical table, there are human readable labes instead codes.

But the problem is still here. You must little change how look on usage of PBI reports. It is because PBI reports (no mater if are in import mode or live connection mode) are interactive, for these reports there nothing like report open or report render time (like paginated reports). During interactive work with PBI reports users making many DAX queries, depened number visualisation on page, and not only during open report, but also during any click in report (on pages, on filters, on refresh button, when highlight any value, when drilling, when use slicers, and so on).

There are some steps during opening report:

-Report must be in to memory (SSAS) - if not, must be loaded (only for import mode reports)

-Must be loaded report definition for first time of use of user

-and for each visual on report must be executed DAX queries

For some performance and utilization statistics you can measure more values, like:

-How long takes loading model in to memory and how often (RequestType=Interactive, ItemAction=ASModelStream)

-Number of open definition by user (ItemAction=ConceptualSchema)

-And most important, number and execution time of queries (ItemAction=QueryData). I mean execution time of QueryData is most important, and for example I'm monitoring Average, 50th, 75th and 95th Percentile of execution time (in miliseconds).