DEV Community

Owen Avedi
Owen Avedi

Posted on

How to connect Power BI to a SQL database

#database #datascience #sql

Power BI is a Microsoft tool that helps turn raw data into clear visuals like charts, dashboards, and graphs. It is mainly and widely used by companies for data analysis and business intelligence (BI). This basically means that companies use it to understand trends, track performance, and share effective business insights with teams.

Most companies connect Power BI to databases mainly because most business data is stored in databases rather than files. This connection, therefore, gives Power BI access to large amounts of reliable data.

SQL databases, on the other hand, are especially important because they store and manage analytical data in an organized and more secure way. They also handle a huge volume of information, support fast queries, ensure data is consistent, and allowing multiple users to access the same data safely.

Connecting Power BI to a Local PostgreSQL Database

Follow these steps for a local PostgreSQL database:

  • Download and open Power BI Desktop.
  • Click Get Data on the Home tab.
  • Choose Database > PostgreSQL database > Connect

  • Enter the server name and database name credentials to load data into Power BI.

  • Choose import mode.

  • Input your password and username when prompted, then connect.

Connecting Power BI to a cloud database like Aiven PostgreSQL

Aiven is a cloud service that hosts managed PostgreSQL databases.
Follow these steps to connect Power BI to Aiven database:

  • Log in to Aiven, then select your PostgreSQL service.

  • Go to the connection information section, where you will get:

    1. Host
    2. Port
    3. Username
    4. Password

  • Download the CA certificate. This is important because it encrypts the data between Power BI and the cloud database, so no one can read the data as it travels through the internet.

  • Back in Power BI Desktop, Click Get Data on the Home tab. Choose Database > PostgreSQL database > Connect.

  • Enter the server as host:port and the database name.

  • If Power BI asks for SSL settings, install it into your trusted root certificates on Windows for a smoother connection.

How to install an SSL certificate on Windows

  • Download the certificate from Aiven.

  • Press Windows + R to open the Run dialog.

  • Type mmc and press Enter to open the Microsoft Management Console.

  • Select File > Add/Remove Snap - in...

  • Find and select certificates in the left pane, click Add > my user account

  • Click Finish then Ok

  • In the left pane of MMC, expand Certificates > expand Trusted Root Certification Authorities

  • Right-click on the Certificates

  • Select All Tasks > Import

  • The Certificate Import wizard opens.

  • Click Next > Browse

  • In File Browser, change the dropdown at bottom-right from "Certificate Files (.cer;.crt)" to All Files (.) This is crucial so you can see your .pem file

  • Navigate to your downloaded Aiven .pem file, select it, and click Open

  • Click Next

  • It should auto-select Place all certificates in the following store: Trusted Root Certification Authorities

  • Click Next > Finish

  • Click Yes, it's safe for your own CA cert

  • You should see "The import was successful."

  • Provide username and password, then connect.

  • Choose the required tables.

Loading tables and creating relationships in Power BI

Once connected, in the navigator, click on the tables you want to connect. e.g., customers, sales, inventory, and products. Click Load to bring them into Power BI.

What Power BI does is load them as separate tables. To analyze them correctly, you need to model them or create relationships between them.

Basics of data modelling - link tables using common columns so Power BI knows how the data connects. This allows filter and calculations to flow properly.

Common relationships in our table:

  • Customers > Sales (Via Customer ID)
  • Products > Sales (Via Product ID)
  • Products > Inventory (Via Product ID)

These relationships help Power BI to correctly aggregate data for accurate reports and visuals.

Why SQL Skills Are Important for Power BI Analysts

Most real - world data lives in databases, not Excel files. This is where SQL becomes essential when dealing with databases for Power BI analysts.
Here's how SQL directly helps Power BI analysts:

  1. Retrieve data efficiently - Power BI connects to databases, but sometimes you don't want to pull everything. With SQL, you write precise queries to fetch only relevant data.

  2. Filter data sets - SQL lets you apply filters at the source before data reaches BI, making your imported tables cleaner and visuals more focused.

  3. Perform aggregations- you can load pre-aggregated results much more efficiently than doing it all in Power BI for quicker refreshes and simpler DAX.

  4. Prepare data before building dashboards - SQL is efficient in the ETL (Extract, Transform, Load) phase from joining tables, cleaning data, and creating derived columns.

Power BI is great for visualization and modeling, but SQL is the key to controlling and preparing the raw data underneath. Without SQL, analysts are limited to what Power BI's Power Query can do (which is powerful but slower for massive databases and lacks the precision of database-level operations). With SQL, you become more independent, optimize performance, and deliver faster, more reliable dashboards.

Top comments (0)