Forum Discussion
Power BI Data Refresh to AS400
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:
- Install the On-Premises Data Gateway: Download and install the gateway on a machine that has access to your AS400 database.
- 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).
- Data Source Settings in Power BI: Go to Power BI service, navigate to "Settings" > "Data sources" and link the ODBC connection to your Gateway.
- 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.).
- 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.
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.
Is this through the On-Premises data gateway? If so, I'm missing where to add the ODBC driver to it.
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.