PC users can install Power BI from https://powerbi.microsoft.com/en-us/desktop/.
After you have Power BI installed, you need to set up the necessary components to support the PostgreSQL connector. Follow these steps:
Find and install
npgsql, a .NET driver package for PostgreSQL that is the official way for PowerBI to connect.
Select v4.0.10 (newer versions currently result in an error).
Under “Npgsql GAC Installation” on the Custom Setup screen, select Will be installed on local hard drive. Not installing the GAC will cause Power BI to fail later.
Find the PowerBI Desktop evaluation version.
After performing those preparatory steps, you can connect Power BI to Query Service:
Open Power BI.
Click Get Data in the top menu ribbon.
Choose PostgreSQL database, then click Connect.
Enter values for the Server and Database. Server is the Host found under the connection details. For production, add port
:80 to the end of the Host string. Database can be either “all” or a dataset table name. (Try one of the CTAS-derived datasets.)
Click Advanced options, and then uncheck include relationship columns. Do not check Navigate using full hierarchy.
(Optional but recommended when “all” is declared for the database) Enter a SQL statement.
If a SQL statement is not provided, then Power BI will preview all of the tables in database. For hierarchical data, a custom SQL statement should be used. If the table schema is flat, it will work with or without a custom SQL statement. Compound types are yet not supported by Power BI - to get primitive types from compound types, you will need to write SQL statements to derive them.
SELECT web.webPageDetails.name AS Page_Name, SUM(web.webPageDetails.pageviews.value) AS Page_Views FROM _TABLE_ WHERE TIMESTAMP >= to_timestamp('2018-11-20') GROUP BY web.webPageDetails.name ORDER BY SUM(web.webPageDetails.pageviews.value) DESC LIMIT 10
Select either “DirectQuery” or “Import” mode. In DirectQuery mode, all the queries will be sent to Query Service for execution. In Import mode, data will be imported in Power BI.
Click OK. Now, Power BI connects to the Query Service and produces a preview if there are no errors. There is a known issue with the Preview rendering numeric columns. Proceed to the next step.
Click Load to bring the dataset into Power BI.