So imagine you have, as a datasource, a Postgres database installed in a local server. You have a report that is published in Power BI Service using that dataset and you want to update the data.
One way to do it is opening the report in Power BI Desktop, hit refresh and publish the report again. But, what if you want to schedule that update? What if you don't have a machine with Power BI Desktop installed? Is there a way to do it?
Yes there is.
First step: install a data gateway on a machine that has access to the database. Access https://app.powerbi.com/, login, in the upper-right corner click in the arrow, "Downloads" (or just download it at https://powerbi.microsoft.com/pt-br/gateway/) Choose Download personal mode. Installation is pretty straightforward, all you need to do is enter your credentials.
Second step: in Power BI Service (https://app.powerbi.com/), find the dataset and access its "Settings". Then go to Gateway connection. It should show something like this:
Click the engine and "Add to gateway". Fill the name of the datasource, choose basic authentication, fill user and password and mark the checkbox to skip connection test (this is important). Then, you will probably get some error like this one: "Failed to test the connection to your data source. Please retry your credential"
There is nothing about it on Microsoft documentation, but it will only work if you connect to the database using an ODBC driver. You have to install it (https://www.postgresql.org/ftp/odbc/versions/msi/), and access the database through the driver. One way to do it, is creating a function, on Power BI Desktop, and just call it on each table (the advantage here is that, you can change it back, if needed, changing only the function):
(tableName as text) =>
let
Source = Odbc.DataSource("driver={PostgreSQL Unicode(x64)};server=XXXXX;port=XXXX;database=XXXX;dsn=PostgreSQL30", [HierarchicalNavigation=true]),
postgres_Database = Source{[Name="XXXXX",Kind="Database"]}[Data],
schema = postgres_Database{[Name="XXXXX",Kind="Schema"]}[Data],
table = schema {[Name=tableName,Kind="Table"]}[Data]
in
table
After changing all the tables to get data using the function above, publish the report again.
Go back to Power BI Service, dataset settings, gateway connection. Now, it should show something like this:
Select the gateway in the dropdown "Maps to" and you are good to go. You can refresh the data using Power BI Service or schedule it.
But, there is an extra catch: when you load data through the ODBC driver, booleans field will come as ones and zeros, not trues and falses. So you may have to replace values in your report or change conditions.
Comments