This article refers to when you use Power BI and the Data Management Gateway. Sometimes I have experienced reports that works locally on my machine I get Power BI refresh errors when they are scheduled in Power BI/Office 365. There seem to be quite a difference on what works locally compared to what works in the cloud. Unfortunately far to often the error that you receive in the Power BI refresh status page is not very descriptive. The errors that I describe in this post has occurred when using Data Management Gateway version 1.2.
Please see my previous post: (Power BI Limitations and Considerations)
In general make sure that you have data sources that are stated as supported by Microsoft. Do not write any SQL statements in the sources when defining them in Excel and do not use any “fancy” datatypes but try to keep as standardized types as possible. A best practice is to cleanse your data as close to the source as possible. Also avoid views with complicated queries that might cause the refresh to time out. Currently the limit for the data management gateway is 30 minutes. See here for some more description on SQL statements and unsupported data types:
Errors in the high-level relational engine
Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: ‘Type=System.ArgumentException,Message=The value of prefixLength argument must be 1,2, or 4. Parameter name: prefixLength,Source=Microsoft.DataTransfer.Common,’;transfer service job status is invalid Response status code does not indicate success: 400 (Bad Request).. The current operation was cancelled because another operation in the transaction failed.
In my case this occurred when I used a view as data source in Teradata. The view used LEFT JOIN to merge multiple tables together.
CREATE VIEW Example AS SELECT MyTable1.Name, MyTable2.CodeDesc FROM MyTable1 LEFT JOIN MyTable2 ON MyTable1.Code = MyTable2.Code
This worked perfectly fine when I did the data refresh of my report locally however not when uploaded and scheduled for refresh within Power BI in Office 365. First i thought the issue was due to that the LEFT JOIN caused null values and changed it:
CREATE VIEW Example AS SELECT MyTable1.Name, COALESCE(MyTable2.CodeDesc, 'Unknown') AS CodeDesc FROM MyTable1 LEFT JOIN MyTable2 ON MyTable1.Code = MyTable2.Code
This did not work either however the error message became slightly different:
Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: Unexpected data read from blob.. The current operation was cancelled because another operation in the transaction failed.
The problem seems to be related to that Power BI will not be able to tell the datatype of the column from MyTable2.CodeDesc in the view. In my case the CodeDesc was a varchar(50) in the MyTable2 so not any special kind of datatype. And also i was confused since it worked properly when refresh locally on my computer. When i changed the view to cast the CodeDesc into varchar(50) in the view it worked and refreshed successfully:
CREATE VIEW Example AS SELECT MyTable1.Name, CAST(MyTable2.CodeDesc AS VARCHAR(50)) AS CodeDesc FROM MyTable1 LEFT JOIN MyTable2 ON MyTable1.Code = MyTable2.Code
ExcelServices operation ended with error
ExcelServices operation ended with error: Correlation ID: <GUID>
When this error occurred I had an Excel spreadsheet that had a standard Excel PivotTable that was linked to a PowerPivot model. From the standard PivotTable I created an Excel table that I added to Power Pivot (linked table). Then I added this table from Power Pivot into a new PowerView report. This worked fine on my local machine but not when trying to schedule report for Power BI refresh in Office 365 as seen in the screenshot above.