topic Re: How does PBI Report Server calculate usage in Report Server

How does PBI Report Server calculate usage

opejoseph — Mon, 12 Jun 2023 08:30:31 GMT

SO I HAVE THIS CODE SNIPPET TO GET USAGE METRICS FOR USERS THAT VIEW DASHBOARDS MORE THAN 1 MINUTES, I DON'T TRUST THE QUERY RESULTS. But my main question is, why does the execution log table shows that users view reports in 1, 3 seconds? Is that time not too short to even load a report, talkless filtering visualizations.Is there a better way to write this query or get usage metrics?

(the code snippet has been anonymized)

SELECT c.[name] AS dashboardName,
SUBSTRING(e.username, CHARINDEX('\', e.username) + 1, LEN(e.username)) AS reportUser,
SUM(COUNT(*)) OVER (PARTITION BY c.[name], SUBSTRING(e.username, CHARINDEX('\', e.username) + 1, LEN(e.username))) AS Views,
CAST(SUM(DATEDIFF(SECOND, e.TimeStart, e.TimeEnd)) AS NUMERIC(18, 0)) AS TotalSeconds,
CAST(AVG(DATEDIFF(SECOND, e.TimeStart, e.TimeEnd)) AS NUMERIC(18, 0)) AS AverageSeconds,
SUBSTRING(u.username, CHARINDEX('\', u.username) + 1, LEN(u.username)) AS dashboardBuilder
FROM [Server].[dbo].[Catalog] c
INNER JOIN [Server].[dbo].[executionlogstorage] e ON c.itemid = e.reportid
INNER JOIN [Server].[dbo].[users] u ON c.modifiedbyid = u.userid
WHERE e.TimeStart between 'XXXX-XX-XX' and 'XXXX-XX-XX'
AND e.UserName NOT IN ('PowerBIReportServer','T SERVICE\PowerBIReportServer')
AND DATEDIFF(ss, e.TimeStart, e.TimeEnd) >= 60
AND SUBSTRING(e.username, CHARINDEX('\', e.username) + 1, LEN(e.username)) != 'PowerBIReportServer'
GROUP BY c.[name], SUBSTRING(e.username, CHARINDEX('\', e.username) + 1, LEN(e.username)), SUBSTRING(u.username, CHARINDEX('\', u.username) + 1, LEN(u.username))

Re: How does PBI Report Server calculate usage

d_gosbell — Tue, 13 Jun 2023 05:28:01 GMT

opejoseph wrote:

But my main question is, why does the execution log table shows that users view reports in 1, 3 seconds? Is that time not too short to even load a report, talkless filtering visualizations.

This is because the server sends the report metadata for Power BI Reports to the user's browser and the rendering then happens in the browser and the server is unaware of how long the user has the report page open after it has sent the data to the browser.

There is no good solution for capturing the time viewing a report. You also cannot tell if the user is still looking at the report or if they have opened another browser tab...

Re: How does PBI Report Server calculate usage

opejoseph — Tue, 13 Jun 2023 06:18:18 GMT

safe to say we can only capture what the usage will be like but not exactly what it is right?

Re: How does PBI Report Server calculate usage

d_gosbell — Tue, 13 Jun 2023 06:29:20 GMT

You can capture how many people access a report and how often they open it. You just cannot get how long they had it sitting on their screen.