PowerBI Reporting
Power BI R Script Data Sources for Hornbill Reporting And Trend Engine
Overview
A number of example scripts have been provided to enable Power BI administrators to use the Hornbill Reporting and Trending Engine APIs as Data Sources within Power BI reports and dashboards.
The scripts can be found on our public Github Repository.
Dependencies
The scripts have been written in R, and were developed using the following:
The following packages are required dependencies, and can be installed via the CRAN repositories:
Configuration used in all scripts
Each script requires the following variables to be set (all case-sensitive):
- instanceName - This is the name of the instance to connect to
- instanceZone - This is the zone where the instance resides
- eur - If your instance resides in the European zone
- nam - If your instance resides in the North American zone
- apiKey - This is an API key generated against a user account on the Hornbill Administration Console, where the user account has sufficient access to run reports and access trending data.
Scripts
PowerBIDataSource_Report.R
This script will:
- Run a pre-defined report on the Hornbill instance;
- Wait for the report to complete;
- Retrieve the report CSV data and present back as an R data frame called dataframe, which can then be retrieved and reported on by PowerBI.
Script Variables:
- reportID: The ID (Primary Key, INT) of the report to be run. This can be found in the browser URL when viewing the report in Hornbill Administration.
- reportComment: A comment to write against the report run job.
- deleteReportInstance: a boolean value to determine if, once the report is run on Hornbill and the data has been pulled in to PowerBI, whether the historic report run instance should be removed from your Hornbill report.
- suspendSeconds: The number of seconds the script should wait between checks to see if the report is complete. NOTE: there is a defect/incompatibility between Power BI and the RCurl library that we are using to make the HTTP requests to Hornbill, where if more than 4 or 5 HTTP requests are made with getURL within the same script then getURL hangs until Power BI times out. Increasing the number of seconds between checks reduces the required number of calls to your Hornbill instance, and will fix data source hanging issues
PowerBIDataSource_HistoricReport.R
This script will:
- Retrieve a historic report CSV from your Hornbill instance;
- Present the report data back as an R data frame called dataframe, which can then be retrieved and reported on by PowerBI.
Script Variables:
- reportID: The ID (Primary Key, INT) of the report to be run. This can be found in the browser URL when viewing the report in Hornbill Administration.
- runId: The Run ID (INT) of a historic run of the above report ID. This can be found in the "History" tab of the report you've specified in the reportID variable.
PowerBIDataSource_TrendingData.R
This script will:
- Run the reporting::measureGetInfo API against your Hornbill instance, with a given measure ID (Primary Key, INT);
- Build a table containing all Trend Value entries for the selected measure;
- Present the trend data back as an R data frame called dataframe, which can then be retrieved and reported on by PowerBI.
Script Variable:
- measureID: The ID (Primary Key, INT) of the measure to return trend data from. This can be found in the browser URL when viewing the measure in Hornbill Administration.
Outputs: As the response parameters from the Trending Engine is fixed (unlike the Reporting engine, which has user-specified column outputs), the output for this report will always consist of the following columns:
- value - the value of the trend sample;
- sampleId - the ID of the sample;
- sampleTime - the time & date that the sample was taken;
- dateRange.from - the start date of the sample snapshot;
- dateRange.to - the end date of the sample snapshot;
Power BI Notes
These scripts have been designed to be used as data sources only, and not as the source of R script visuals within Power BI. Which is not to say they couldn’t be used in your R script visuals, with extra code of your own!
HTTP Proxies
If you use a proxy for all of your internet traffic, the HTTP_PROXY Environment variable needs to be set on the local machine running Power BI Desktop. The https_proxy environment variable holds the hostname or IP address of your proxy server. It is a standard environment variable and like any such variable, the specific steps you use to set it depends on your operating system.
For windows machines, it can be set from the command line using the following:
set HTTP_PROXY=HOST:PORT
Where "HOST" is the IP address or host name of your Proxy Server and "PORT" is the specific port number.