Refresh from CSV files fails frequently - how to fix?

[Martin_D]

Hi,

I have a semantic model importing data from CSV files. This is not a large data model or complex transformations: A successful refresh takes less than 1 minute. The connector type is Folder.

The files are updated every 10 minutes and I run a refresh of the semantic model by API call every 10 minutes. Usually it is successful but it fails so often that usually you can see at least on failed refresh in the short refresh history showing the last 7 runs. It's the same with manually triggered refresh, it sometimes works or not, who knows.

The files are loaded through a gateway cluster of two servers with no availability issues from an on prem file share.

The only cause I can think about is an access conflict between the process updating the files and the gateway trying to read the file at the same time.

• Do you have any other ideas of the cause and how to fix this?
• Any ideas how to debug this to tell what is the cause?
• Is there a timout, and how long, to wait until other processes close a file that the gateway is trying to read?
• The files are updated by a Python script. Should I trigger the semantic model refresh from the Python script after the files are updated and closed?

It's all running on a P1 Premium capacity.

Kind regards,

Martin

[Anonymous · Accepted solution]

Hi,@Martin_D 

It's a pleasure to give you ideas.

Regarding the issue you raised, my solution is as follows:

I think you're right, there is a real chance that an access conflict between the process of updating the file and the gateway trying to read the file at the same time could cause you to make an error.

1.First, you can try to make sure that the Python script releases the file lock after it finishes writing. If the gateway tries to read a file while writing it, it can cause problems, and you can add the following to your code:

file.close()

2.Second, you may want to add a delay between when the Python script completes updating the file and when the semantic model refresh is triggered. This helps to avoid any potential conflicts between the two processes.

import time
import os

def watch_file(file_path, delay):
    # Get the initial timestamp of the file
    last_modified = os.stat(file_path).st_mtime

    while True:
        # Get the current timestamp of the file
        current_modified = os.stat(file_path).st_mtime

        # If the file has been modified
        if current_modified != last_modified:
            # Update the last_modified timestamp
            last_modified = current_modified

            # Delay before refresh
            time.sleep(delay)

            # Refresh (or any other action)
            print(f"File {file_path} has been modified. Refreshing...")

# Monitor a file with a delay of 5 seconds
watch_file("your_file_path", 5)

3.Then, we thought it might be a good idea to trigger a semantic model refresh from a Python script after the file was updated and closed. This will ensure that the refresh only happens after the file update process is complete.

4.Finally, you can check that the current gateway version is kept up to date, and then if there is an error, it is recommended to use the error log call within the gateway.

Power BI Gateway | Microsoft Power BI

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Martin_D]

Hi @Anonymous ,

I was just wondering whether someone would really spend this much effort on writing implementation suggestions based on the unverified assumption that my assumption of the cause is right if all I asked for was troubleshooting suggestions, except if it comes effortless from Gen AI. Usually people don't have this much time at Microsoft.

The idea of implementing a separate watchdog service to trigger the refresh is not wrong, although it might be completely overkill, since it would be enough to trigger the refresh after calling file.close() since there I know that the file is closed for sure. And if I would choose this architecture with the watchdog I wouldn't be restricted to Python, just because the file is created from Python. Since this is a separate process anyway, maybe even running on a different machine, I'd rather choose PowerShell for the watchdog where I can use your existing Power BI CmdLets.

The one correct suggestion referring to my question is just a side note: Look into the gateway log.

I'll give it a try. Thank you for your support.

Kind regards,

Martin

[Anonymous · Accepted solution]

Hi,@Martin_D 

It's a pleasure to give you ideas.

Regarding the issue you raised, my solution is as follows:

I think you're right, there is a real chance that an access conflict between the process of updating the file and the gateway trying to read the file at the same time could cause you to make an error.

1.First, you can try to make sure that the Python script releases the file lock after it finishes writing. If the gateway tries to read a file while writing it, it can cause problems, and you can add the following to your code:

file.close()

2.Second, you may want to add a delay between when the Python script completes updating the file and when the semantic model refresh is triggered. This helps to avoid any potential conflicts between the two processes.

import time
import os

def watch_file(file_path, delay):
    # Get the initial timestamp of the file
    last_modified = os.stat(file_path).st_mtime

    while True:
        # Get the current timestamp of the file
        current_modified = os.stat(file_path).st_mtime

        # If the file has been modified
        if current_modified != last_modified:
            # Update the last_modified timestamp
            last_modified = current_modified

            # Delay before refresh
            time.sleep(delay)

            # Refresh (or any other action)
            print(f"File {file_path} has been modified. Refreshing...")

# Monitor a file with a delay of 5 seconds
watch_file("your_file_path", 5)

3.Then, we thought it might be a good idea to trigger a semantic model refresh from a Python script after the file was updated and closed. This will ensure that the refresh only happens after the file update process is complete.

4.Finally, you can check that the current gateway version is kept up to date, and then if there is an error, it is recommended to use the error log call within the gateway.

Power BI Gateway | Microsoft Power BI

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Martin_D]

Are these answers from ChatGPT or Copilot?

[Anonymous · Accepted solution]

HI,@Martin_D 

Thank you for your quick response,

It's my pleasure to offer you ideas.I'm sorry to have caused you to doubt our capabilities, but that's all I can search for right now.

If you have any new questions, you can also choose to communicate with us.

Of course, you are welcome to communicate with us if you have any new discoveries.

Thank you for your patient response.

Best Regards,

Leroy Lu