Power BI on-premises data gateway May 2019 update released (version 3000.5.185).
Here are some of the things that we would like to highlight with this month release:
- Gateway Performance Monitoring (Public Preview)
- Amazon Redshift Refresh via the on-premises data gateway
- May version of the mashup engine
Gateway Performance Monitoring (Public Preview)
Traditionally, for monitoring performance, gateway admins have had to depend on manually monitoring performance counters through the Windows Performance Monitor tool. This feature is an out of the box feature which includes additional logging regarding queries and system counters along with a Gateway Performance PBI template file to visualize these. This would insights into gateway usage and allow troubleshooting slow performing queries.
This feature is currently available only for the On-premises data
gateway in the standard mode and not for the personal mode.
When you turn this feature on, 3 new log files would be created:
- Query Execution Report – Contains detailed query execution information.
- Query Execution Aggregation Report – Contains query information aggregated to a time interval. The default value is 5 minutes but can be adjusted as described below.
- System Counter Aggregation Report – Contains system counter values aggregated to a time interval. The default value is 5 minutes but can be adjusted as described below.
To enable this feature
Please make the following changes to the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file in the \Program Files\On-premises data gateway folder
- QueryExecutionReportOn – This attribute when updated to “True” enables additional logging for queries executed using the gateway and would create both the Query Execution and the Query Execution Aggregation Report files.
<setting name="QueryExecutionReportOn" serializeAs="String"> <value>True</value> </setting>
- Set SystemCounterReportOn – This attribute when updated to “True” enables additional logging for memory and CPU system counters and would create the System Counter Aggregation Report
<setting name="SystemCounterReportOn" serializeAs="String"> <value>True</value> </setting>
Other values in the config file related to this feature which may be of interest to you would be:
- ReportFilePath – Determines the path where the 3 new log files get stored using the attribute. This is by default the “\Users\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway\Report” or “Windows\ServiceProfiles\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway\Report” depending on the OS version. The PBIEgwService may need to be replaced with the service account running the data gateway.
- ReportFileCount – Determines the number of log files of each kind to be retained. The default value is 10.
- ReportFileSizeInBytes – Determines the size of the file to be maintained. The default value is 104857600.
- QuerExecutionAggregationTimeInMinutes – Determines the number of minutes for which the query execution information would be aggregated. The default value is 5.
- SystemCounterAggregationTimeInMinutes – Determines the number of minutes for which the system counters would be aggregated. The default value is 5.
Once you have made the above changes to the config file, do not forget to restart the gateway for these config values to take effect. You will now start seeing the report files getting generated in the ReportFilePath.
Note: Please wait for 5 minutes until the aggregate files get created in the ReportFilePath folder.
Amazon Redshift Refresh via the on-premises data gateway
In this month’s gateway release, we have enabled the Amazon Redshift to be refreshed via the on-premises data gateway. This would help with Redshift databases behind a firewall. Most customers have used the ODBC option to overcome this problem in the past but weren’t able to use features like DirectQuery.
May version of the mashup engine
This will ensure that the reports that you publish to the Power BI Service and refresh via the Gateway will go through the same query execution logic/runtime as in the latest Power BI Desktop version.