How to Use Power BI Report Server to Create Inventory Reports & Data Refreshes

March 2, 2022

Unlike the Power BI service, Power BI Report Server does not come with tools to help analyze a system’s content. Here's a solution.

By: Mason Prewett

My team is in charge of a large Power BI Report Server installation at a current Key2 Consulting client. Unlike the Power BI service, Power BI Report Server does not come with tools to help analyze the system’s content.

After doing some research and not finding any solutions for this issue, we decided it would be best to develop our own solution to provide this data.

The Problem

We needed to be able to answer questions about our system’s content, such as:

How many Power BI reports are currently hosted in our system?
How many unique Power BI developers are publishing reports to our system?
How many data refresh schedules are currently set up for Power BI reports?
Are there data refreshes that are being executed too frequently?
Are there data refreshes that are consistently running too long?
Are there data refreshes that are continually failing?

Why This Is Important

These metrics provide valuable insight that help an admin team:

See trends in content growth and adoption
Use trends to help verify that system resources are adequate
Analyze data refreshes to ensure they are not causing performance issues. The report servers handle report executions as well as data refreshes, so this is a concern.

The Solution

We developed a series of PowerShell scripts that export this data from the Power BI Report Server into CSV files. We used the Reporting Services Tools PowerShell APIs to accomplish this.

Install Requirements

In order to use these APIs, you must execute the following PowerShell command once to get the necessary modules installed:

Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

1	Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

The Code

Export All Power BI Report Data

#Set Global Variables
$ReportServerURL = "https://pbiportal.com/Reports";
$ReportFolder = "/";
$PBIReports = @();
$FormattedInventory = @();
$ExportCSVFile = "D:\ExportedFiles\PBIRS_Reports.csv";

$PBIReports = Get-RsFolderContent -ReportServerUri $ReportServerURL -RsFolder $ReportFolder -Recurse | Where-Object {$_.TypeName -eq "PowerBIReport"}

foreach($report in $PBIReports)
{
    #Friendly formatting
    $fullURL = $ReportServerURL + "/powerbi" + $report.Path;
    $created = $report.CreationDate.ToShortDateString() + " " + $report.CreationDate.ToShortTimeString();
    $modified = $report.ModifiedDate.ToShortDateString() + " " + $report.ModifiedDate.ToShortTimeString();
    $hidden = "N";
    $description = $null;

    $newPBIReport = New-Object –TypeName PSObject;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name ReportID –Value $report.ID;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name ReportURL –Value $fullURL;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name ReportName –Value $report.Name;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name ReportDescription –Value $report.Description;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name ReportHidden –Value $report.Hidden;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name CreatedBy –Value $report.CreatedBy;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name Created –Value $created;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name ModifiedBy –Value $report.ModifiedBy;
    $newPBIReport | Add-Member –MemberType NoteProperty –Name Modified –Value $modified;

    $FormattedInventory += $newPBIReport;
}

$FormattedInventory | Export-Csv -LiteralPath $ExportCSVFile -NoTypeInformation

#Set Global Variables

$ReportServerURL = "https://pbiportal.com/Reports";

$ReportFolder = "/";

$PBIReports = @();

$FormattedInventory = @();

$ExportCSVFile = "D:\ExportedFiles\PBIRS_Reports.csv";

$PBIReports = Get-RsFolderContent -ReportServerUri $ReportServerURL -RsFolder $ReportFolder -Recurse | Where-Object {$_.TypeName -eq "PowerBIReport"}

foreach($report in $PBIReports)

{

#Friendly formatting

$fullURL = $ReportServerURL + "/powerbi" + $report.Path;

$created = $report.CreationDate.ToShortDateString() + " " + $report.CreationDate.ToShortTimeString();

$modified = $report.ModifiedDate.ToShortDateString() + " " + $report.ModifiedDate.ToShortTimeString();

$hidden = "N";

$description = $null;

$newPBIReport = New-Object –TypeName PSObject;

$newPBIReport | Add-Member –MemberType NoteProperty –Name ReportID –Value $report.ID;

$newPBIReport | Add-Member –MemberType NoteProperty –Name ReportURL –Value $fullURL;

$newPBIReport | Add-Member –MemberType NoteProperty –Name ReportName –Value $report.Name;

$newPBIReport | Add-Member –MemberType NoteProperty –Name ReportDescription –Value $report.Description;

$newPBIReport | Add-Member –MemberType NoteProperty –Name ReportHidden –Value $report.Hidden;

$newPBIReport | Add-Member –MemberType NoteProperty –Name CreatedBy –Value $report.CreatedBy;

$newPBIReport | Add-Member –MemberType NoteProperty –Name Created –Value $created;

$newPBIReport | Add-Member –MemberType NoteProperty –Name ModifiedBy –Value $report.ModifiedBy;

$newPBIReport | Add-Member –MemberType NoteProperty –Name Modified –Value $modified;

$FormattedInventory += $newPBIReport;

}

$FormattedInventory | Export-Csv -LiteralPath $ExportCSVFile -NoTypeInformation

Export All Power BI Data Refresh Plan Data

#Set Global Variables
$ReportServerURL = "https://vaww.test.pbi.cdw.va.gov/Reports";
$ReportFolder = "/";
$ReportRefreshPlans = @();
$ExportCSVFile = "D:\ExportedFiles\PBIRS_RefreshPlans.csv";

$PBIReports = Get-RsFolderContent -ReportServerUri $ReportServerURL -RsFolder $ReportFolder -Recurse | Where-Object {$_.TypeName -eq "PowerBIReport"}

foreach($report in $PBIReports)
{
    Try
    {
        #Get refresh plan for power bi reports
        $refreshPlans = Get-RsRestCacheRefreshPlan -ReportPortalUri $ReportServerURL -RsReport $report.Path;
    
        if($refreshPlans -ne $null)
        {
            foreach($refreshPlan in $refreshPlans)
            {
                #Format friendly output
                $start = [datetime]$refreshPlan.Schedule.Definition.StartDateTime
                $startTime = $start.ToShortDateString() + " " + $start.ToShortTimeString();
                $end = [datetime]$refreshPlan.Schedule.Definition.EndDate
                $endTime = $end.ToShortDateString() + " " + $end.ToShortTimeString();
                $modified = [datetime]$refreshPlan.ModifiedDate
                $modifiedTime = $modified.ToShortDateString() + " " + $modified.ToShortTimeString();
                $lastRun = [datetime]$refreshPlan.LastRunTime
                $lastRunTime = $lastRun.ToShortDateString() + " " + $lastRun.ToShortTimeString();

                $intervalType = "";
                $interval = 0;
                
                #Determine refresh interval and interval type
                if($refreshPlan.Schedule.Definition.Recurrence.MinuteRecurrence -ne $null)
                {
                    $intervalType = "Minutes";
                    $interval = $refreshPlan.Schedule.Definition.Recurrence.MinuteRecurrence.MinutesInterval;
                }
                elseif($refreshPlan.Schedule.Definition.Recurrence.DailyRecurrence -ne $null)
                {
                    $intervalType = "Daily";
                    $interval = $refreshPlan.Schedule.Definition.Recurrence.DailyRecurrence.DaysInterval;
                }
                elseif($refreshPlan.Schedule.Definition.Recurrence.WeeklyRecurrence -ne $null)
                {
                    $intervalType = "Weekly";
                    $interval = $refreshPlan.Schedule.Definition.Recurrence.WeeklyRecurrence.WeeksInterval;
                }
                elseif($refreshPlan.Schedule.Definition.Recurrence.MonthlyRecurrence -ne $null)
                {
                    $intervalType = "Monthly";
                    $interval = $refreshPlan.Schedule.Definition.Recurrence.MonthlyRecurrence.Day;
                }
                elseif($refreshPlan.Schedule.Definition.Recurrence.MonthlyDOWRecurrence -ne $null)
                {
                    $intervalType = "Monthly DOW";
                    $interval = $refreshPlan.Schedule.Definition.Recurrence.MonthlyDOWRecurrence.MonthFreq;
                }
                else
                {
                    $intervalType = "Once";
                    $interval = 0;
                }

                $newRPItem = New-Object –TypeName PSObject;
                $newRPItem | Add-Member –MemberType NoteProperty –Name ReportID –Value $report.ID;
                $newRPItem | Add-Member –MemberType NoteProperty –Name DataRefreshPlanID –Value $refreshPlan.Id;
                $newRPItem | Add-Member –MemberType NoteProperty –Name Owner –Value $refreshPlan.Owner;
                $newRPItem | Add-Member –MemberType NoteProperty –Name ScheduleDescription –Value $refreshPlan.ScheduleDescription;
                $newRPItem | Add-Member –MemberType NoteProperty –Name ModifiedBy –Value $refreshPlan.ModifiedBy;
                $newRPItem | Add-Member –MemberType NoteProperty –Name ModifiedDate –Value $modifiedTime;
                $newRPItem | Add-Member –MemberType NoteProperty –Name LastStatus –Value $refreshPlan.LastStatus;
                $newRPItem | Add-Member –MemberType NoteProperty –Name LastRunTime –Value $lastRunTime;
                $newRPItem | Add-Member –MemberType NoteProperty –Name RecurrenceType –Value $intervalType;
                $newRPItem | Add-Member –MemberType NoteProperty –Name RecurrenceInterval –Value $interval;
                $newRPItem | Add-Member –MemberType NoteProperty –Name StartDateTime –Value $startTime;
                $newRPItem | Add-Member –MemberType NoteProperty –Name EndDateTime –Value $endTime;

                $ReportRefreshPlans += $newRPItem;
            }           
        }
    }
    Catch
    {
    }
}
$ReportRefreshPlans | Export-Csv -LiteralPath $ExportCSVFile -NoTypeInformation

#Set Global Variables

$ReportServerURL = "https://vaww.test.pbi.cdw.va.gov/Reports";

$ReportFolder = "/";

$ReportRefreshPlans = @();

$ExportCSVFile = "D:\ExportedFiles\PBIRS_RefreshPlans.csv";

$PBIReports = Get-RsFolderContent -ReportServerUri $ReportServerURL -RsFolder $ReportFolder -Recurse | Where-Object {$_.TypeName -eq "PowerBIReport"}

foreach($report in $PBIReports)

{

Try

{

#Get refresh plan for power bi reports

$refreshPlans = Get-RsRestCacheRefreshPlan -ReportPortalUri $ReportServerURL -RsReport $report.Path;

if($refreshPlans -ne $null)

{

foreach($refreshPlan in $refreshPlans)

{

#Format friendly output

$start = [datetime]$refreshPlan.Schedule.Definition.StartDateTime

$startTime = $start.ToShortDateString() + " " + $start.ToShortTimeString();

$end = [datetime]$refreshPlan.Schedule.Definition.EndDate

$endTime = $end.ToShortDateString() + " " + $end.ToShortTimeString();

$modified = [datetime]$refreshPlan.ModifiedDate

$modifiedTime = $modified.ToShortDateString() + " " + $modified.ToShortTimeString();

$lastRun = [datetime]$refreshPlan.LastRunTime

$lastRunTime = $lastRun.ToShortDateString() + " " + $lastRun.ToShortTimeString();

$intervalType = "";

$interval = 0;

#Determine refresh interval and interval type

if($refreshPlan.Schedule.Definition.Recurrence.MinuteRecurrence -ne $null)

{

$intervalType = "Minutes";

$interval = $refreshPlan.Schedule.Definition.Recurrence.MinuteRecurrence.MinutesInterval;

}

elseif($refreshPlan.Schedule.Definition.Recurrence.DailyRecurrence -ne $null)

{

$intervalType = "Daily";

$interval = $refreshPlan.Schedule.Definition.Recurrence.DailyRecurrence.DaysInterval;

}

elseif($refreshPlan.Schedule.Definition.Recurrence.WeeklyRecurrence -ne $null)

{

$intervalType = "Weekly";

$interval = $refreshPlan.Schedule.Definition.Recurrence.WeeklyRecurrence.WeeksInterval;

}

elseif($refreshPlan.Schedule.Definition.Recurrence.MonthlyRecurrence -ne $null)

{

$intervalType = "Monthly";

$interval = $refreshPlan.Schedule.Definition.Recurrence.MonthlyRecurrence.Day;

}

elseif($refreshPlan.Schedule.Definition.Recurrence.MonthlyDOWRecurrence -ne $null)

{

$intervalType = "Monthly DOW";

$interval = $refreshPlan.Schedule.Definition.Recurrence.MonthlyDOWRecurrence.MonthFreq;

}

else

{

$intervalType = "Once";

$interval = 0;

}

$newRPItem = New-Object –TypeName PSObject;

$newRPItem | Add-Member –MemberType NoteProperty –Name ReportID –Value $report.ID;

$newRPItem | Add-Member –MemberType NoteProperty –Name DataRefreshPlanID –Value $refreshPlan.Id;

$newRPItem | Add-Member –MemberType NoteProperty –Name Owner –Value $refreshPlan.Owner;

$newRPItem | Add-Member –MemberType NoteProperty –Name ScheduleDescription –Value $refreshPlan.ScheduleDescription;

$newRPItem | Add-Member –MemberType NoteProperty –Name ModifiedBy –Value $refreshPlan.ModifiedBy;

$newRPItem | Add-Member –MemberType NoteProperty –Name ModifiedDate –Value $modifiedTime;

$newRPItem | Add-Member –MemberType NoteProperty –Name LastStatus –Value $refreshPlan.LastStatus;

$newRPItem | Add-Member –MemberType NoteProperty –Name LastRunTime –Value $lastRunTime;

$newRPItem | Add-Member –MemberType NoteProperty –Name RecurrenceType –Value $intervalType;

$newRPItem | Add-Member –MemberType NoteProperty –Name RecurrenceInterval –Value $interval;

$newRPItem | Add-Member –MemberType NoteProperty –Name StartDateTime –Value $startTime;

$newRPItem | Add-Member –MemberType NoteProperty –Name EndDateTime –Value $endTime;

$ReportRefreshPlans += $newRPItem;

}

Catch

{

}

$ReportRefreshPlans | Export-Csv -LiteralPath $ExportCSVFile -NoTypeInformation

Export All Power BI Data Refresh Plan History

#Set Global Variables
$ReportServerURL = "https://vaww.test.pbi.cdw.va.gov/Reports";
$ReportFolder = "/";
$ReportRefresh = @();
$ExportCSVFile = "D:\ExportedFiles\PBIRS_RefreshHistory.csv";

$PBIReports = Get-RsFolderContent -ReportServerUri $ReportServerURL -RsFolder $ReportFolder -Recurse | Where-Object {$_.TypeName -eq "PowerBIReport"}

foreach($report in $PBIReports)
{
    Try
    {
        #Get refresh history for Power BI report
        $refreshHistory = Get-RsRestCacheRefreshPlanHistory -ReportPortalUri $ReportServerURL -RsReport $report.Path

        if($refreshHistory.Count -gt 0)
        {               

            foreach($rh in $refreshHistory)
            {
                $start = [datetime]$rh.StartTime
                $startTime = $start.ToShortDateString() + " " + $start.ToShortTimeString();
                $end = [datetime]$rh.EndTime
                $endTime = $end.ToShortDateString() + " " + $end.ToShortTimeString();

                $detailsLength = 0;

                #Only get the first 1000 characters of the error message
                if($rh.Details.Length -gt 1000)
                {
                    $detailsLength = 1000;
                }
                else
                {
                    $detailsLength = $rh.Details.Length;
                }

                $Duration = $end - $start;                
                
                $newRHItem = New-Object –TypeName PSObject;
                $newRHItem | Add-Member –MemberType NoteProperty –Name ReportID –Value $report.ID;
                $newRHItem | Add-Member –MemberType NoteProperty –Name Status –Value $rh.Status;
                $newRHItem | Add-Member –MemberType NoteProperty –Name Type –Value $rh.Type;
                $newRHItem | Add-Member –MemberType NoteProperty –Name DataRefreshPlanID –Value $rh.SubscriptionID;
                $newRHItem | Add-Member –MemberType NoteProperty –Name StartDate –Value $startTime;
                $newRHItem | Add-Member –MemberType NoteProperty –Name EndDate –Value $endTime;
                $newRHItem | Add-Member –MemberType NoteProperty –Name TotalMinutesTaken –Value $Duration.TotalMinutes;
                $newRHItem | Add-Member –MemberType NoteProperty –Name Message –Value $rh.Message;
                $newRHItem | Add-Member –MemberType NoteProperty –Name Details –Value $rh.Details.Substring(0,$detailsLength);

                $ReportRefresh += $newRHItem;
            }
        }
    }
    Catch
    {
    }
}

$ReportRefresh | Export-Csv -LiteralPath $ExportCSVFile -NoTypeInformation

#Set Global Variables

$ReportServerURL = "https://vaww.test.pbi.cdw.va.gov/Reports";

$ReportFolder = "/";

$ReportRefresh = @();

$ExportCSVFile = "D:\ExportedFiles\PBIRS_RefreshHistory.csv";

$PBIReports = Get-RsFolderContent -ReportServerUri $ReportServerURL -RsFolder $ReportFolder -Recurse | Where-Object {$_.TypeName -eq "PowerBIReport"}

foreach($report in $PBIReports)

{

Try

{

#Get refresh history for Power BI report

$refreshHistory = Get-RsRestCacheRefreshPlanHistory -ReportPortalUri $ReportServerURL -RsReport $report.Path

if($refreshHistory.Count -gt 0)

{

foreach($rh in $refreshHistory)

{

$start = [datetime]$rh.StartTime

$startTime = $start.ToShortDateString() + " " + $start.ToShortTimeString();

$end = [datetime]$rh.EndTime

$endTime = $end.ToShortDateString() + " " + $end.ToShortTimeString();

$detailsLength = 0;

#Only get the first 1000 characters of the error message

if($rh.Details.Length -gt 1000)

{

$detailsLength = 1000;

}

else

{

$detailsLength = $rh.Details.Length;

}

$Duration = $end - $start;

$newRHItem = New-Object –TypeName PSObject;

$newRHItem | Add-Member –MemberType NoteProperty –Name ReportID –Value $report.ID;

$newRHItem | Add-Member –MemberType NoteProperty –Name Status –Value $rh.Status;

$newRHItem | Add-Member –MemberType NoteProperty –Name Type –Value $rh.Type;

$newRHItem | Add-Member –MemberType NoteProperty –Name DataRefreshPlanID –Value $rh.SubscriptionID;

$newRHItem | Add-Member –MemberType NoteProperty –Name StartDate –Value $startTime;

$newRHItem | Add-Member –MemberType NoteProperty –Name EndDate –Value $endTime;

$newRHItem | Add-Member –MemberType NoteProperty –Name TotalMinutesTaken –Value $Duration.TotalMinutes;

$newRHItem | Add-Member –MemberType NoteProperty –Name Message –Value $rh.Message;

$newRHItem | Add-Member –MemberType NoteProperty –Name Details –Value $rh.Details.Substring(0,$detailsLength);

$ReportRefresh += $newRHItem;

}

Catch

{

}

$ReportRefresh | Export-Csv -LiteralPath $ExportCSVFile -NoTypeInformation

Data Details

This will produce three CSV files:

1. PBIRS_Reports.csv – Contains the details of all Power BI reports deployed to the report server.

a. ReportID – The unique ID for the Power BI report
b. ReportURL – The full URL of the Power BI report. Can be used directly to render the report
c. ReportName – The name of the Power BI report file
d. ReportDescription – The description of the Power BI report file as entered though report manager.
e. ReportHidden – Whether or not the file has been marked as hidden.
f. CreatedBy – The user that initially deployed the Power BI report
g. CreatedDate – The date and time, and that the Power BI report was initially deployed
h. ModifiedBy – The user that last updated the Power BI report
i. ModifiedDate – The date and time, and that the Power BI report was last updated

2.PBIRS_RefreshPlans.csv – Contains the details of all Power BI data refresh plans that exist on the report server. A data refresh plan is the schedule that was created for a Power BI data model to be refreshed on.

a. ReportID – The unique ID for the Power BI report that contains the refresh plan
b. DataRefreshPlanID – The unique ID for the Power BI data refresh plan
c. ScheduleDescription – The description of the schedule. This is entered when creating or editing
d. RecurrenceType – The type of recurrence that the schedule is refreshed on. Example: Daily, Minutes, Once, Weekly
e. RecurrenceInterval – The interval at which the recurrence schedule is refreshed
f. LastStatus – The status of the last refresh
g. LastRunTime – The date and time of the last refresh
h. StartDate – When the schedule was first set to start. This is a start date for the entire schedule and does not represent any single refresh.
i. EndDate – When the schedule was set to end. This is an end date for the entire schedule and does not represent any single refresh.
j. Owner – The user that created the Power BI refresh schedule
k. ModifiedBy – The user that last updated the Power BI refresh schedule
l. ModifiedDate – The date and time, and that the Power BI refresh schedule was last updated

3. PBIRS_RefreshHistory.csv – Contains the history of all Power BI data refresh runs on the report server. A maximum of 10 data refresh histories are kept for a single report. A row from this view contains the details of a single refresh for a Power BI report data model.

a. ReportID – The unique ID for the Power BI report that was refreshed
b. DataRefreshPlanID – The unique ID for the Power BI data refresh plan that executed the refresh
c. Status – Shows the status of the refresh. Example: Completed, In Progress, Failed
d. Type – How the refresh was executed. Example: Scheduled, Manual
e. StartDate – The date and time that the refresh started
f. EndDate – The date and time that the refresh ended
g. TotalMinutesTaken – The total number of minutes that the refresh took to execute
h. Message – The completion message from the refresh
i. Details – The error message if there was a failure that occurred during the refresh

Automate

These scripts can be set up to run on a schedule through Windows Task Scheduler, and it is important to make sure that the identity of the scheduled job is local admin on the report server. This is the only way to ensure that it has access to all of the Report Server’s content.

Once all of the CSV files are exported, they can easily be imported into a database for easy querying. My production script for this has a last step of inserting the data directly into a SQL table. This completes all of the steps in one script.

Thanks for Reading! Questions?

Thanks for reading! We hope you found this blog post useful. Feel free to let us know if you have any questions about this article by simply leaving a comment below. We will reply as quickly as we can.

Keep Your Data Analytics Knowledge Sharp

Get fresh Key2 content and more delivered right to your inbox!

About Us

Key2 Consulting is a boutique data analytics consultancy that helps business leaders make better business decisions. We are a Microsoft Gold-Certified Partner and are located in Atlanta, Georgia. Learn more here.

Related Content

Understanding Power BI Filters, Slicers, & Highlighting

By: Mark Seaman Power BI has three powerful features that make understanding and analyzing your data and visualizations more impactful: filters slicers highlighting In this blog post, we explore the functionality of these three features. Power BI Filters...

How to Connect Power BI to Serverless Azure Synapse Analytics

By: Mason Prewett, Dean Jurecic, and Mark Seaman Introduction Azure Synapse Analytics (Synapse) is a powerful tool that makes connecting to data in Azure Data Lake Storage Gen2 (ADLS) as easy as traditional data sources like SQL Server. This article outlines the...

Microsoft Fabric – Starting a Trial

By: Mason Prewett, Dean Jurecic, and Mark Seaman Two Ways to Start Fabric Trial Fabric, the new AI-powered analytics platform by Microsoft, hosts resources in a workspace similar to Power BI. A new Fabric workspace can be created for this or an existing Power BI...

How to Use Power BI Report Server to Create Inventory Reports & Data Refreshes

The Problem

Why This Is Important

The Solution

Install Requirements

The Code

Data Details

Automate

Thanks for Reading! Questions?

Keep Your Data Analytics Knowledge Sharp

About Us

Understanding Power BI Filters, Slicers, & Highlighting

How to Connect Power BI to Serverless Azure Synapse Analytics

Microsoft Fabric – Starting a Trial

0 Comments

Key2 Consulting | [email protected] | (678) 835-8539