How to automate Sync on-premise database to Power BI

[jijoyav]

Hello,

I have an on-premise ERP database (Progress) which has been connected to Power BI using OA Adapter. I have an ODBC OA adapter server version (provided by ERP) installed on a Server and OA Adapter client version on my laptop on which Power BI is installed.

As of now, i do a manual sync of data as needed. Now with many dashboards created, I need to automate the sync. How do i go ahead?

I also have a SQL Reporting server on which OA Adapater and OA client is installed. This server is used for SQL reporting server. This has nothing to do with Power BI. Just trying to explain my setup

Regards,

[v-yuezhe-msft]

@jijoyav,

Do you firstly connect to the on-premises database in Power BI Desktop using ODBC data source? If so, you can add the ODBC data source within gateway following the guide in the similar thread below, then you are able to set schedule refresh for your dataset in Power BI Service.

https://community.powerbi.com/t5/Service/Enterprise-Gateway-with-ODBC/td-p/98959

Regards,
Lydia

[jijoyav]

Hello,

Thank you for your reply. I tried the link but was not successful to create a connection to my local database. I have installed the on premise gateway on my server. Do i need to do any additional settings on the gateway or just leave the settings default?

Here is what i have as of now:

• Installed OA Adapter Server
• Installed OA Adapter Client
• Created DSN and established connection to database
• Installed onpremise gateway.
• Do i need to further perform configuration on on premise gateway? Each time i open on premise gateway on server, it asks me for O365 login details.

Regards

Jijoy

[v-yuezhe-msft]

@jijoyav,

Are you able to connect to the ODBC data source in Power BI Desktop?

For the gateway part, you would need to firstly sign in using your Power BI Service account in the gateway configurator,configure gateway following the guide in this article, then go to Settings->Manage Gateway, and add the ODBC data source within the configured gateway.

Regards,
Lydia

[jijoyav]

Dear Lydia,

Yes, I am able to connect to ODBC from Power BI Desktop.

I have tried to configure ODBC via Manage gateway but have received errors.

Please find below error.

[v-yuezhe-msft]

@jijoyav,

Do you install Power BI Desktop and gateway on same machine?

Regards,
Lydia