PowerBI Reporting

From Hornbill
Revision as of 09:16, 10 September 2021 by Samuels (talk | contribs) (→‎Dependencies)
Jump to navigation Jump to search

Using R Scripts and the Hornbill Reporting And Trend Engines as Data Sources in Power BI

Power BI Dashboard built using the Hornbill Reporting APIs as its data source
Power BI data view of the above dashboard, with data taken from the Hornbill Reporting APIs.
Setting up the Power BI Data Source, using R script

Overview

A number of example R 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:

  • readr
  • httr
  • readxl - Just for the Report and HistoricReport scripts, when useXLSX is set to TRUE
  • data.table - Just for the TrendingData script

Once downloaded and unzipped, the dependency folder must be placed (in its entirety) in the "library" folder of your Microsoft R Open installation. This is typically Program Files\Microsoft\R Open\R-3.5.3\library but may be different depending on your chosen installation path for Microsoft R Open 3.5.3.

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. Your instance name can be found at the end of the URL which you use to navigate to your Hornbill instance i.e. https: //live.hornbill.com/[instanceName]/
  • 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. The following page will outline how to create an API Key: API Keys

Each script can be configured to use a proxy for access to your Hornbill instance. Set all of the below to NULL to not use a proxy. If using a proxy, the proxyAddress and proxyPort are the minimum required to be provided.

  • proxyAddress - The hostname or IP address of the proxy
  • proxyPort - The proxy port
  • proxyUsername - The username to access the proxy, if required
  • proxyPassword - The password for the above account
  • proxyAuth - The type of HTTP authentication to use. Should be one of the following: basic, digest, digest_ie, gssnegotiate, ntlm, any.

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.
  • useXLSX: FALSE = the script will use the CSV output from your report; TRUE = the script will use the XLSX output from your report. NOTE: XLSX output will need to be enabled within the Output Formats > Additional Data Formats section of your report in Hornbill;
  • deleteLocalXLSX: FALSE = the downloaded XLSX file will remain on disk once the extract is complete; TRUE = the local XLSX file is deleted upon completion;
  • xlsxLocalFolder: The folder where to store the downloaded XLSX file. Can be left blank, or specify a local folder to store the downloaded XLSX file into. Requires the postfixed / or \ on the path, depending on your OS;
  • csvEncoding: The character set to be used when decoding the CSV report data, or when converting the XLSX data into a Power BI friendly codepage. This will usually be "UTF-8", but if you have issues returning data with certain characters (the Windows E2 80* characters are the usual culprits) then choose a different character set to use, ie: "ISO-8859-1". Look out for an error that looks like this for character set issues: "Details: "Unable to translate bytes [E2][80] at index 1077 from specified code page to Unicode"";
  • suspendSeconds: The number of seconds the script should wait between checks to see if the report is complete.
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.
  • useXLSX: FALSE = the script will use the CSV output from your report; TRUE = the script will use the XLSX output from your report. NOTE: XLSX output will need to be enabled within the Output Formats > Additional Data Formats section of your report in Hornbill;
  • deleteLocalXLSX: FALSE = the downloaded XLSX file will remain on disk once the extract is complete; TRUE = the local XLSX file is deleted upon completion;
  • xlsxLocalFolder: The folder where to store the downloaded XLSX file. Can be left blank, or specify a local folder to store the downloaded XLSX file into. Requires the postfixed / or \ on the path, depending on your OS;
  • csvEncoding: The character set to be used when decoding the CSV report data, or when converting the XLSX data into a Power BI friendly codepage. This will usually be "UTF-8", but if you have issues returning data with certain characters (the Windows E2 80* characters are the usual culprits) then choose a different character set to use, ie: "ISO-8859-1". Look out for an error that looks like this for character set issues: "Details: "Unable to translate bytes [E2][80] at index 1077 from specified code page to Unicode"".
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 with R 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!

Using Python 3 and the Hornbill Reporting And Trend Engines as Data Sources in Power BI

Overview

A number of example Python 3 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 Python 3, and were developed using the following:

The following packages are required dependencies, and can be installed using the Python Package Installer (pip):

Configuration used in all scripts

Each script requires the following variables to be set (all case-sensitive):

  • 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.
  • instanceId - This is the (case sensitive) name of the instance to connect to. Your instance name can be found at the end of the URL which you use to navigate to your Hornbill instance i.e. https: //live.hornbill.com/[instanceName]/

Scripts

PowerBIReport.py

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 a dataframe called df, which can then be consumed by PowerBI.

Script Variables:

  • reportId: The ID (Primary Key, INT) of the report to be run;
  • suspendSeconds: The number of seconds the script should wait between checks to see if the report is complete;
  • 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;

- useXLSX: False = the script will use the CSV output from your report; True = the script will use the XLSX output from your report. NOTE: XLSX output will need to be enabled within the Output Formats > Additional Data Formats section of your report in Hornbill.

PowerBIHistoricReport.py

This script will:

  • Retrieve a historic report CSV from your Hornbill instance;
  • Present the report data back as a dataframe called df, which can then be consumed by PowerBI.

Script Variables:

  • reportId: The ID (Primary Key, INT) of the report to be run;
  • reportRunId: The Run ID (INT) of a historic run of the above report ID;

- useXLSX: False = the script will use the CSV output from your report; True = the script will use the XLSX output from your report. NOTE: XLSX output will need to be enabled within the Output Formats > Additional Data Formats section of your report in Hornbill.

PowerBITrendingData.py

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 a dataframe called df, which can then be consumed by PowerBI.

Script Variable:

  • measureId: The ID (Primary Key, INT) of the measure to return trend data from.

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 with Python Notes

Please see the Power BI Documentation for more information about using Python with Power BI.

These scripts have been designed to be used as data sources only, and not as the source of Python visuals within Power BI. Which is not to say they couldn't be used in your Python visuals, with a little extra code and the matplotlib library!

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.

If you use a proxy for all of your internet traffic, the HTTP_PROXY and HTTPS_PROXY Environment variables need to be set. These environment variables hold 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

set HTTPS_PROXY=HOST:PORT
Where "HOST" is the IP address or host name of your Proxy Server and "PORT" is the specific port number. IF you require a username and password to go through the proxy, the format for the setting is as follows:
set HTTP_PROXY=username:password@HOST:PORT

set HTTPS_PROXY=username:password@HOST:PORT

URLs to White List

Occasionally on top of setting the HTTP_PROXY variable the following URLs need to be white listed to allow access out to our network

Retrieved from "https://wiki.hornbill.com/index.php?title=PowerBI_Reporting&oldid=27553"