Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Is it possible to create a retry loop for refresh failure raised by IDbCommand interface?

Using on-Prem PBI (not cloud/service), I get fairly frequent report refresh failures for multiple reasons such as:

1. DataSource.Error: The network name cannot be found <network_path>. The exception was raised by the IDbCommand interface.

2. The credentials provided for the Web source are invalid. (Source at <https://...etc). The exception was raised by the IDbCommand interface.

 

These errors are due to intermittent availabilty by these services and I'd like to implement a retry loop to attempt the connection again after some wait time. Otherwise it can be quite a long time before the scheduled refresh and results in a poor user experience.

 

I have tried to wrap the connection in a retry loop, but it does not seem to improve the fail rate.  I am wondering if there is any way to code in a retry for these kinds of errors which are raised by the IDbCommand interface?  Is this error being generated before my retry loop even runs?

example retry logic below:

 

        Retry = List.Generate( 
            () => [retry = 0, result=try MY_CONNECTION(),condition=true],  
            // Condition to continue looping
            each [condition],
            // Increment retry count
            each [
                retry = [retry] + 1,
                // Try connection again, wait some amount of time before retrying 
                result = try MY_CONNECTION_WITH_Wait(waitTimeInSeconds),
                condition = [retry] < retryCount and [result][HasError]
            ],
            // Get API call result. return both the value and number of attempts
            each {[result],[retry]} 
        )     
        ,
 // Get result or null if retries exhausted
        l = List.Last(Retry), // last array item from the loop
        val = List.First(l), // First = HasError Last = Value
        if val[HasError] then Error("Failed after " & retry & " attempts:" & val[Error][Message])

 

MY_CONNECTION function example (connect to some web API)

 

Source = Json.Document(Web.Contents("https://some_endpoint", [Headers=[#"api-key"="<some_key>]]))

 

MY_CONNECTION_WITH_wait function (run the above after some timeout):

 

result = Function.InvokeAfter(()=> MY_CONNECTION(), #duration(0,0,0,waitTimeInSeconds))

 

thank you very much,

 

Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    you are missing the "otherwise"  part of the "try"  statement.

     

    Check the refresh logs - it may already indicate that the scheduler is automatically retrying (it does so in the Power BI Service)

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      thanks for noticing that, however adding 'otherwise' does not appear to resolve.  I created a test function to force an error:

       

      Json.Document(Web.Contents("https://my_endpoint", [Headers=[#"api-key"="<fake_token>"]]))

       

      then used one-time retry loop as follows:

       

      try api_error() otherwise 1

       

      However the credentials error shows up and does not seem to move into the 'otherwise' code.

       

      Now, the api_error function is modified as follows, then the retry returns value of 1 as expected.  So it appears the type of error behaves different.  

      try error("test") otherwise 1

       I'd like to figure out how to catch the specific credential type of error.

  • saikrishnadugya's avatar
    saikrishnadugya
    Regular Visitor
    2 years ago

    hi 

    I have faced this problem earlier also so for this 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.

    code:
    let
    get_data = (counter as number) =>
    let
    output =
    try
    let
    //your source link
    Source = Excel.Workbook(Web.Contents("URL_TO_YOUR_FILE.xlsx"), null, true),
    Data =
    let
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
    in
    Sheet1_Sheet
    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", "File not found 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 Excel file or any 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 "URL_TO_YOUR_FILE.xlsx" with the actual URL of your source. You can then call this function with an initial counter value of 0.