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

1 comment:

Unknown said...

Hi Indira, thanks. where do we paste this? how does it know which Power BI report we need usage statistics for?

How to download files from Microsoft Fabric Lakehouse ?

  Challenge : Yesterday I was trying to download a parquet file from the Microsoft Lakehouse on to my laptop.  So I was searching for the do...