Power BI Data Refresh to AS400

[Eddie_Price]

I have created a report using an ODBC connection to an AS400.  I have published the report to My Workspace.  Here I'm unable to refresh the data and I'm not sure how to setup the Gateway to automatically refresh the data before I share the report to my managers.  Any help would be greatfull!

[SmithDace]

To enable automatic data refresh for a Power BI report using an ODBC connection to AS400, install and configure the Mid Birthday Data Gateway on a machine with AS400 access. In Power BI Service, link the ODBC data source to the gateway and set up a refresh schedule under dataset settings. Ensure the gateway machine remains online and connected to the database for seamless updates.

[waskad0 · Accepted solution]

To enable automatic data refresh for your Power BI report using an ODBC connection to AS400, you'll need to set up an On-Premises Data Gateway. Here’s a step-by-step guide:

1. Install the On-Premises Data Gateway: Download and install the gateway on a machine that has access to your AS400 database.
2. Configure the Gateway: During installation, sign in with your Power BI account. Once installed, open the Gateway app and configure it to connect to your data source (AS400 via ODBC).
3. Data Source Settings in Power BI: Go to Power BI service, navigate to "Settings" > "Data sources" and link the ODBC connection to your Gateway.
4. Set Up Data Refresh: In the Power BI service, go to the report, select "Schedule Refresh" under the dataset settings, and enable the refresh schedule (daily, weekly, etc.).
5. Ensure Connectivity: Make sure the machine running the Gateway is always on and can reach the AS400 database.

Once the setup is complete, the data will automatically refresh based on your schedule, and you can share the updated report with your managers.

[v-jianpeng-msft]

Your solution is so great lbendlin

Hi, @Eddie_Price 

 After you install the on-premises enterprise gateway on your host, you can create a new gateway in Power BI service with the following link:

Follow the link below to fill in the corresponding information:

Power Query IBM Db2 database connector - Power Query | Microsoft Learn

Then configure this gateway connection for your semantic model:

Finally, set up a scheduled refresh:

Best Regards

Jianpeng Li

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

[lbendlin]

install the same ODBC driver on the gateway, create the same system DSN, and then create a connection in the gateway managment web page under that dsn.

[Eddie_Price]

Is this through the On-Premises data gateway?  If so, I'm missing where to add the ODBC driver to it.

[lbendlin]

usually you install the On-Premises data gateway on a dedicated VM  (Windows Server 2019 for example).  On the same VM you install all the required drivers. and then you use the ODBC-64 control panel to add the system DSN.