Introduction
Connecting data allows users to establish links between Power BI and external data systems, enabling efficient data retrieval and manipulation for reporting purposes.
In this article, I would like to demonstrate how to connect to SQL database in Power BI Desktop. To be specific, I will be demonstrating how to connect to a locally hosted PostgreSQL database.
Step-by-step guide
Once you’ve launched your Power BI Desktop application.
Select Get Data from the Home tab.
Under Database choose your database – in this case, I will select PostgreSQL database.
On the pop-up box, I will specify the following.
Server as localhost (you can insert server URL)
Database as ‘Postgres’ – which is the name of database I am connecting.
Data connectivity mode as “DirectQuery”.
Learn More: Data connection modes in Power BI Desktop
Executing this takes you to the navigation window, where you can see all the tables in the database. Here, you can select the tables of interest and proceed to load them for analysis and visualization.
With data loaded, you can now begin analyzing and visualizing different business questions and objectives.
Connecting data this way loads all the data from the tables which may not be necessary for scenarios requiring only selected columns from select tables within the database. To configure tailored data retrieval connections – you need to write custom SQL queries to filter and aggregate data according to your business needs. Using Custom SQL queries ensures that only relevant data is imported, optimizing performance and usability.
Creating Custom SQL query in Power BI Desktop
Connect your database by following the same procedure above.
Once you’ve specified your server, database, and data connectivity mode.
Expand the Advanced options box, to write your SQL query statement as shown below.
(Note, the above SQL statement returns the county, ship_mode, and the sales values from the customers, orders, and order_details tables respectively)
Executing the above returns.
(Which returns only selected data from tables of interest – optimizing performance and usability)
Recommendation: Rather than using a custom SQL query connection inside the Power BI Desktop. The best recommendation is that you use an SQL statement to create a view inside your database, then connect the view to Power BI Desktop. By doing that pushes the workload back to the database and saves Power BI’s analytical processing power for loading and filtering workbooks.
Conclusion
By following the above set of instructions, you should be able to connect data from locally hosted PostgreSQL database. Same process applies when connecting to other databases using the respective database connectors available on the Power BI Desktop.
If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!
Thank you for reading!
