Missing auto-retry on Scheduled Refresh (Azure SQL): The command has been canceled

[ibax]

Dear PowerBI Team

I have seen other posts, where the same error occured, due to limited source data availability.
But because the error occurred more than 3 times in the last 2 weeks I'd like to make a point to

1. improve the error/retry handling and
   
   
2. improve the availability within the cloud of the same provider/geo-location

Feature Request

It would be helpful to get more detailed, cause related error messages and last but not least, PowerBI scheduled refresh should automatically retry a refresh (i.e. 3 times after 5 min wait) in case of failure due to limited source data availability. It would be best, if the retry behaviour could be configured as part of the dataset refresh schedule settings.

Service Availability Request
In the context of using services from the same provider (Microsoft) from the same geo location (North Europe),
I would expect a more reliable source availability - assuming the error indicates an availability  issue...

Message:	The command has been canceled.
Cluster URI:	WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID:	c85882d9-b1ec-44bf-ba2b-09d70708ae5b
Request ID:	1d0d955a-b39c-4ada-8194-f6c8df47c3b3
Time:	2017-10-13 01:35:24Z

[v-haibl-msft]

@ibax

For feature request, please create an idea in https://ideas.powerbi.com.

For the second one, do you mean that sometimes the refresh for Azure SQL may failed with this error message, and the next refresh may succeed again without no change on your side? 

If you get data from the same data source in PBI Desktop, are you able to refresh it properly with several tries?

I also suggest to check the queries to see if it is possible to remove some unnecessary tables/columns to reduce the query time.

Best Regards,
Herbert

[ibax]

Dear Herbert

Thank you for hint to post the retry feature as new feature request under "ideas".

For the second
The model has not been changed for multiple weeks. The data is refreshed every night once by a scheduled refresh. All table/queries run 100% error-free at SQL. Most of the time (> 95%) the data refresh runs without any errors. There is no way to reduce the model / table volume, and I assume a data volume of 10-20 mio records shouldn't be an issue for PowerBI...

Usually we operate the Azure SQL source database at performance level S0 for cost reasons. Only during the ETL process with Azure Data Factory the database runs at a higher performance level (S3/S4). Because currently it is not possible to directly initiate a PowerBI refresh from within SQL or as ADF activity, we use a scheduled refresh for refreshing PowerBI outside the time of higher database performance levels.

To me it looks like a query timeout issue within PowerBI. If a query does not return a part of or the whole result set within a specific time, the query is canceled by PowerBI. I suggest the PowerBI team to increase the default timeout value or even better to make this setting available as property within the "data source credential" settings.

Cheers
Michael

[v-haibl-msft]

@ibax

Currently scheduled refresh for imported datasets timeout after two hours. This timeout is increased to five hours for datasets in Premium workspaces. If you are encountering this limit, you can consider reducing the size or complexity of your dataset, or consider breaking the dataset into smaller pieces.

Best Regards,
Herbert

[saikrishnadugya]

Dear Herbert,

I have implemented a Wait and Retry Mechanism for Data Refresh in Power BI:

To handle data refresh failures due to data source unavailability, I developed a function using M code in Power BI that incorporates a wait-and-retry mechanism. This solution ensures that the data refresh process can attempt multiple retries before finally failing, which increases the robustness of your data import process.

let
get_data = (counter as number) =>
let
output =
try
let
// Replace this block with your specific data source connection logic
Source = YourDataSourceFunction(),
Data =
let
// Replace this block with your specific data processing logic
ProcessedData = Source[YourData]
in
ProcessedData
in
Data
otherwise if counter < 4 then
Function.InvokeAfter(() => @get_data(counter + 1), #duration(0,0,0,20))
else Error.Record("Dataset refresh failure after multiple attempts", "Data source error", "Additional details need to check")
in
output
in
get_data

Explanation:

1. Function Definition: get_data is a recursive function that takes a counter as an argument.
2. Try Block: Attempts to load the data from the specified data source.
3. Catch Block:
   • If an error occurs and the counter is less than 4, the function waits for 20 seconds before retrying.
   • If the counter reaches 4, it records an error message indicating a refresh failure.

Usage:

To use this function, replace YourDataSourceFunction and YourData with the actual function and data processing logic for your specific data source. You can then call this function with an initial counter value of 0.