Mason Prewett, Business Intelligence at Key2 Consulting
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:

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

 

Why This Is Important

These metrics provide valuable insight that help an admin team:

  1. See trends in content growth and adoption
  2. Use trends to help verify that system resources are adequate
  3. 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:

 

The Code

 
Export All Power BI Report Data

 
Export All Power BI Data Refresh Plan Data

 
Export All Power BI Data Refresh Plan History

 

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.

Our Most Recent Blog Content