cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jijoyav Frequent Visitor
Frequent Visitor

How to automate Sync on-premise database to Power BI

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,

5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: How to automate Sync on-premise database to Power BI

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jijoyav Frequent Visitor
Frequent Visitor

Re: How to automate Sync on-premise database to Power BI

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

Moderator v-yuezhe-msft
Moderator

Re: How to automate Sync on-premise database to Power BI

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jijoyav Frequent Visitor
Frequent Visitor

Re: How to automate Sync on-premise database to Power BI

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.

image.pngimage.pngimage.png

Moderator v-yuezhe-msft
Moderator

Re: How to automate Sync on-premise database to Power BI

@jijoyav,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.