Powerbi Netsuite Automation

[KK4321]

Hello,

Requesting someone to help with the below error,

Background

I have a webquery excel file connected to Netsuite ERP that refreshes every 5 minutes using a power shell script code. I also have a folder with historical sales data. I have used power query to transform and append the two data sources into one then visualize using powerbi. All the files are in a server machine that's never switched off. I have then published the report and set gateway to autorefresh the report every 2 hours but for unknown reason  I am getting the below error.

The objective is to ensure the sales report is online i.e achieve auto refresh and auto update without any human intervention. There is currently no way to connect powerbi and netsuite ERP directly. Would like to know what  I need to change currently to make everything work or a suitable alternative

Data source error{"error":{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","pbi.error":{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","parameters":{},"details":[],"exceptionCulprit":1}}} Table: BO errors.

[v-hashadapu · Accepted solution]

Hi @KK4321 , Thank you for reaching out to the Microsoft Community Forum.

This typically indicates file access issues, network delays, or gateway configuration problems. The most likely root cause is the PowerShell script locking the Excel file while Power BI attempts to refresh, causing the gateway to fail to open a timely connection. Additionally, using Excel files with frequent updates and large transformations adds fragility to the system.

In the short term, you should refactor your PowerShell script to write to a temporary file first, then overwrite the original only after the refresh is complete, this avoids locking issues. Use UNC paths, ensure proper gateway permissions and stagger PowerShell and Power BI refreshes to avoid overlaps. Consider switching to CSV instead of Excel, as it reduces locking and improves performance. For longer-term, either move to NetSuite’s SuiteAnalytics ODBC connection (if available) or adopt a cloud-based architecture using Microsoft Fabric and OneLake, which allows automated data ingestion via APIs or pipelines and eliminates on-premises gateway dependency.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[JamesLee94]

Hey,

That error usually means your gateway is timing out connecting to the data source. Since you’re using PowerShell to pull NetSuite data into Excel then Power BI, try increasing the gateway timeout settings and ensure your server and network are stable.

Since there’s no direct NetSuite-to-Power BI link, tools like Skyvia can sync NetSuite data into a database or cloud storage Power BI reads, avoiding timeout issues.

This setup usually makes auto-refresh smoother without scripts or manual steps.

[v-hashadapu]

Hi @KK4321 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @KK4321 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @KK4321 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu · Accepted solution]

Hi @KK4321 , Thank you for reaching out to the Microsoft Community Forum.

This typically indicates file access issues, network delays, or gateway configuration problems. The most likely root cause is the PowerShell script locking the Excel file while Power BI attempts to refresh, causing the gateway to fail to open a timely connection. Additionally, using Excel files with frequent updates and large transformations adds fragility to the system.

In the short term, you should refactor your PowerShell script to write to a temporary file first, then overwrite the original only after the refresh is complete, this avoids locking issues. Use UNC paths, ensure proper gateway permissions and stagger PowerShell and Power BI refreshes to avoid overlaps. Consider switching to CSV instead of Excel, as it reduces locking and improves performance. For longer-term, either move to NetSuite’s SuiteAnalytics ODBC connection (if available) or adopt a cloud-based architecture using Microsoft Fabric and OneLake, which allows automated data ingestion via APIs or pipelines and eliminates on-premises gateway dependency.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[KK4321]

@v-hashadapu  thanks for your prompt response. I will try the second solution using Fabric and one data lake as Netsuite analytics ODBC didnt work. Does this mean API will connect to either Fabric or One lake? Have never used this before.

[v-hashadapu · Accepted solution]

Hi @KK4321 , Thank you for reaching out to the Microsoft Community Forum.

No, the NetSuite API doesn’t connect directly to Fabric or OneLake, instead, you use something in the middle like a Python script, Power Automate, or another tool to pull the data from NetSuite. That tool is what connects to the API, gets the data and then saves it into OneLake.

Think of it like this:

NetSuite API -> your script/tool -> OneLake -> Power BI (via Fabric)

Once the data is in OneLake, Fabric can pick it up using a Dataflow, Notebook, or Pipeline, and from there, Power BI can connect and refresh automatically.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.