Today my boss wanted a report regarding how often each PowerBI report was being hit.
Since we have an on-premise PowerBI server there is not an Admin Tools interface to pull a fancy already made report. Never fear, I can still get the info.
To find this I first checked to make sure logging was set up on our on-premise PowerBI server (60 days is the default) and then I can write a query to obtain the information.
After I launched SSMS (Sql Management Studio) in “run as administrator” mode, I connected to my PowerBI instance. With a RIGHT CLICK, I selected PROPERTIES and then chose the LOGGING page. Happily the default sixty (60) days was active.
Next, I wrote a quick query that would give an estimate of the number of times each report had been accessed over the past 60 days. This is NOT AN EXACT QUERY. I’ve grouped all report hits in the log that occur within the same minute as a single report so that sub-reports and other parts counted as one. I did this in a subquery (1) and then used an outside query (2) to create a total count for the sixty day period.
SELECT u.ReportPath, COUNT(*) AS TotalRepoortUse
FROM
(
Select ReportPath, UserName,
CONVERT(VARCHAR(10), TimeStart,112) AS ReportDate,
LEFT((REPLACE(CONVERT(VARCHAR(10), TimeStart,108),':','')),5) AS ReportTime,
Count(*) AS ReportCount
from [dbo].[ExecutionLog2]
where Format = 'PBIX'
GROUP BY ReportPath, UserName, CONVERT(VARCHAR(10), TimeStart,112) , LEFT((REPLACE(CONVERT(VARCHAR(10), TimeStart,108),':','')),5)
) AS u -- Group together all PARTS of the report that are HIT during the same minute so we only count the report ONCE.
GROUP BY u.ReportPath
ORDER BY u.ReportPath
I also created another variation that would give the report count by user.
SELECT u.ReportPath, u.UserName, u.ReportDate, COUNT(*) AS DailyReportCountForUser
FROM
(
Select ReportPath, UserName,
CONVERT(VARCHAR(10), TimeStart,112) AS ReportDate,
LEFT((REPLACE(CONVERT(VARCHAR(10), TimeStart,108),':','')),5) AS ReportTime,
Count(*) AS ReportCount
from [dbo].[ExecutionLog2]
where Format = 'PBIX'
GROUP BY ReportPath, UserName, CONVERT(VARCHAR(10), TimeStart,112) , LEFT((REPLACE(CONVERT(VARCHAR(10), TimeStart,108),':','')),5)
) AS u -- Group together all PARTS of the report that are HIT during the same minute so we only count the report ONCE.
GROUP BY u.ReportPath, u.UserName, u.ReportDate
ORDER BY u.ReportPath, u.UserName, u.ReportDate;The ExecutionLog2 and ExecutionLog3 tables in the PowerBI Report Database give other useful information as well.