DEV Community

Benta Okoth
Benta Okoth

Posted on

Connecting Power BI to a SQL database

Power BI - Is a complete reporting solution that offers data preparation, data visualization, distribution and management through development tools and an online platform.

Power BI for Data Analysis and Business Intelligence

  • Helps track key performance indicators (KPIs).
  • Monitor business operations.
  • Identify trends.
  • Generate reports that help management make strategic decisions.

## Get Data In Power BI

  • The Get Data tab on the Power BI desktop helps use load data from various sources to our Power BI for the data visualization and report.
  • The various data sources are namely:
  • (i)Flat File
  • (ii) Relational data
  • (iii) NoSQL database
  • (iv) Online Service
  • (v) Azure Analysis Service

Connecting Power BI to SQL Database

  • We understand that the SQL database can be hosted on cloud or on premise.

On-Premise Database - is a database that is hosted on servers located inside a company’s physical infrastructure, such as their data center or office server room.
Example: a company installs Microsoft SQL Server on a server inside their office network.
Characteristics

  1. Stored on local company servers
  2. Managed by the internal IT team
  3. Requires physical hardware
  4. Accessible mainly through the company network

Cloud-Based Database - is hosted on remote servers managed by a cloud provider, and accessed via the internet.

Example:
Azure SQL Database
Amazon RDS
Google Cloud SQL
The infrastructure is maintained by the cloud provider.

Characteristics

  1. Hosted in the cloud provider’s data centers
  2. Accessible through the internet
  3. Scalable on demand
  4. Maintenance handled by the provider

Connecting Power BI to a Cloud SQL Database(Aiven PostgreSQL)

Steps :
1. Obtain Connection Details from Aiven

  • Ensure you have signed in to Aiven console using the link https://console.aiven.io/login Log in to the Aiven dashboard using your gmail.
  • Select your PostgreSQL service.
  • Navigate to the Overview or Connection Information section by clicking on the database to be connected.

2. Download the SSL Certificate

  • Cloud database services usually require encrypted connections for security. In Aiven, this encryption is enabled using SSL certificates.

  • To download the certificate:
    In the Aiven dashboard, go to the Connection Information or Security section.
    Locate the CA Certificate (Certificate Authority certificate).
    Download the certificate file to your computer by clicking on the download arrow.

  • This certificate will be used to verify the identity of the database server and encrypt the connection between Power BI and the database.

3. Connect Power BI to the PostgreSQL Database
After obtaining the connection details and SSL certificate, you can connect the database to Power BI.

Steps:

  1. Open Power BI Desktop.
  2. Click Get Data.
  3. Select PostgreSQL database.
  4. Enter the following:
  5. Server: host:port then click ok

  1. Database: your database name.
  2. Choose the connection mode (Import or DirectQuery).
  3. When prompted, enter: Username Password

## Connecting Power BI to a Local PostgreSQL Database
Step
1: Open Power BI Desktop

  • Launch Power BI Desktop on your computer.
  • Go to the Home tab.
  • Click Get Data then choose PostgreSQL db.

Step 2: Enter the Server Name
A connection window will appear asking for the server details.
In the Server field, enter:
localhost
or
localhost:5432
Explanation:
localhost means the database is running on your local machine.
5432 is the default port used by PostgreSQL.

Step 4: Enter the Database Name

  • In the Database field:
  • Type the name of the database you want to connect to.

  • If you leave this field blank, Power BI will display all available databases.

Step 5: Choose Data Connectivity Mode
Power BI will ask how you want to access the data.
You can choose:
Import Mode
Data is copied into Power BI.
Faster for reporting.
DirectQuery
Queries run directly on the PostgreSQL database.
Shows real-time data.
For most local analysis, Import Mode is commonly used.

Step 6: Provide Database Credentials

  • Power BI will then request authentication details.
  • Enter: Username – your PostgreSQL user Password – the password for that user Then click Connect.

Step 7: Select Tables to Load
Once the connection is successful, Power BI opens the Navigator window.
Here you will see:
All tables
Views
Database schemas
Steps:
Select the tables you want to analyze.
You can preview the data on the right side.
Click Load to import the tables into Power BI.
Step 8: Data is Loaded into Power BI
After clicking Load:
Power BI imports the selected tables.

The tables appear in the Fields panel on the right side.
You can now start creating:
charts
dashboards
reports
data models

Top comments (0)