Working with one of our customers, I discovered that the reports include outdated data. I started digging deep into this issue and discovered several reports that were not refreshed.
The problem
First of all, I figured out that the Power BI administrators don’t know when an automatic refresh fails using the gateway. This leads to a situation when the analyst provides a report based on old data without knowing the report was not updated. So, the developers and administrators are not able to troubleshoot the report before the business users recognize the failure.
Very often this may happen after the important meeting. In this case, the Power BI report update failure may lead to bad consequences. Generally speaking, you can’t take accurate and informed business decisions based on irrelevant data. However, you want to rely on a fully automated tool to always deliver valid reports as there is no place for the human factor.
Power BI developers can manually check the report refreshes on a regular basis, for example, every morning. However, in large tenants, when a Power BI dashboards include many heavy data models, the refresh operation will take hours.
How DB Best addresses Power BI report update failures
You can use the Get Refresh History in Group Power BI REST API to discover and then troubleshoot Power BI report update failures.
Here is an example of how I use this API to configure email alerts every 30 minutes if any of the reports fail to refresh. This email also includes the reason that stands behind this failure.
The following query helps discover the failed datasheet refreshes:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT a.GroupName , a.DatasetName , CONVERT(VARCHAR(10), a.LastExecuted, 1) + ' ' + CONVERT(VARCHAR(10) , a.LastExecuted, 108) AS 'LastExecuted', a.RefreshType , SUBSTRING(a.ExceptionJson,charindex('pbi.error', a.ExceptionJson, 0)+20 , charindex('"', SUBSTRING(a.ExceptionJson ,charindex('pbi.error', a.ExceptionJson, 0)+20,500), 0)-1) AS 'ErrorType' FROM ( SELECT GroupName , DatasetName , CAST(switchoffset (StartTime, datename (TZoffset , CONVERT (datetime2(0), StartTime, 126) at TIME zone 'Central Standard Time')) AS datetime) AS 'LastExecuted' , RefreshType AS 'RefreshType' , ServiceExceptionJson AS 'ExceptionJson' , STATUS , ROW_NUMBER() OVER (partition BY DatasetID ORDER BY StartTime DESC) AS 'rn' FROM PBI_History WHERE CAST(StartTime AS DATE) >= CAST(getdate () - 1 AS DATE))a WHERE a.rn = 1 AND STATUS = 'Failed' |
Please note that the script converts the StartTime to the given time zone. Then we use the following JSON file from the ExceptionJson column to send an email notification.
1 2 3 4 5 | {"errorCode":"ProcessingError" ,"errorDescription":"Column 'Client+ZIP' in Table 'TERRITORY_ZIPCODE' contains a duplicate value '123456' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table. Table: TERRITORY_ZIPCODE."} |
