Tuesday, April 03, 2018

Extracting Power BI Usage Statistics from Power BI Report Server

Today I had a thought of monitoring the usage of Power BI Reports.

So I started exploring the PowerBIReportServer database and see whether there are any statistics that I could utilise.  The database had views of executionlogs that are similar to the SSRS ReportServer database.  After studying all the available views, I came up with this stored procedure to monitor the usage of the Power BI Reports.


USE PBIReportServer
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[PBI_Usage_Stats] 

AS

BEGIN

SELECT [InstanceName]
      ,[ReportPath]
      ,[UserName]
      ,[TimeStart]
      ,[TimeEnd]
      ,[TimeDataRetrieval]
      ,[TimeProcessing]
      ,[TimeRendering]
      ,[Source]
      ,[Status]
      ,[ByteCount]
   FROM [PBIReportServer].[dbo].[ExecutionLog2]
  where Status = 'rsSuccess' and ReportPath != 'Unknown' and Bytecount > 0
  and username not like 'NT SERVICE%' 
End

No comments:

Free Databases and Data Visualisations course for kids Week 6

Week 6 update: Yesterday the class was a bit different because it started off with a Database Fundamentals test.  There were 20 questions ...