ODBC Auto refresh with gateway

[fabiomanniti]

Hello, I have a datasource on PBI which takes data from an PostgreSQL database through ODBC.

Anytime I need to refresh it I must use the Gateway.

The problem is I would like to set a daily autorefresh but this requires that the gateway on my pc must be on always (or, at least, at the time the autorefresh is set), and I don't think this is sustainable. 

Is there a way that my ODBC datasource can be autorefreshed without depending on me so much?

(FYI: I really have zero knowledge of Gateways; I just followed the manual I found online)

[ibarrau]

Hi. I'm sorry but if the data is on premise, you can't avoid the gateway. The best way to implement it would be asking IT to set up a VM that will be turn on 24 hours a day with database visibility connection to get the data. That way you can install the gateway there and forget about turning on your PC. That way you can ask for Power Bi Dataset Schedule refresh anytime.

I hope that helps,

[fabiomanniti]

@ibarrau 

I see.
But, just to know: what does it mean the data is on premise?

I read this link which says that data on premise is data that isn't in the cloud.

I have my database on Google Cloud so why data are on premise?

Thank you and sorry for the dumb question 

[ibarrau · Accepted solution]

Ok, this is good. OnPremise means not in the cloud. That's ok. However there are some connectors that cannot work without an environment. For example ODBC, needs a driver and a windows environment to help Power Bi understand the connection.

Depending on the connector you can avoid this because not all of them need environment. In you case there is a Postgres connector in Power Bi that might not need the gateway. You can try connecting the source Power Bi Desktop (pbix) file to the database with that connector instead the ODBC. After connecting publish the report, go to under Refresh Schedule settings and check the option "Datasource Credentials" to just edit the credentials for the Postgres. I know for a fact that postgres in Azure won't need a gateway, let's see if it is the same for google. (There might need to allow connections to the postgres or change firewall settings for the db)

If you want to keep the ODBC connection, I would sugget creating a VM in Google Cloud with a Data Gateway to keep reading data in the same cloud before jumping to Power Bi. It would be faster.

I hope that helps,

[fabiomanniti]

Thank you very much