Erickson mugendi

Posted on Jul 14

Connecting Power BI to SQL Databases: From Local Servers to Cloud Platforms

Introduction

When doing doing armature database creation and data storage for in local machine the type of connection mostly required is quite simple.
Large organizations and large files storage require bigger and sometimes very large storage, this is where the term Cloud comes in.
Companies like Microsoft, oracle, Aws, Digital ocean etc provides this services.

local Database connection

Depending on the type of database a user is using, one can choose what Database Management System to use.
Several DBMS platforms exist but one Dbeaver is a universal and is what we will focus on.
To connect to your local database on dbeaver you will need:

1.PostgreSQL Installed

Ensure PostgreSQL is installed on your computer.

PostgreSQL Server

PostgreSQL service running

2.Database Details
You'll need these connection details:

Field Example
Host localhost or 127.0.0.1
Port 5432 (default)
Database postgres or your database name (e.g., sales_db)
Username postgres
Password The password you set during PostgreS installation

3.DBeaver

Download and install DBeaver Community Edition if you haven't already.

4.PostgreSQL JDBC Driver

The first time you create a PostgreSQL connection in DBeaver, it will ask to download the PostgreSQL JDBC driver.

Click Download. DBeaver installs it automatically.

How to conect

Step 1

Open DBeaver.

Step 2

Click:

Database
→ New Database Connection

or click the New Connection icon.

Step 3

Choose

PostgreSQL

Click Next.

Step 4

Enter your connection details.

Example:

Host:
localhost

Port:
5432

Database:
postgres

Username:
postgres

Password:

Step 5

Click

Test Connection

If successful, you'll see:

Connected

Click Finish.

Connecting to a cloud Database

Today organizations increasingly store their data in online cloud-hosted databases rather than on local servers. These databases provide secure access over the internet, making it easy for teams in different locations to work with the same data.

How the Connection Works
Online Database
(AWS, Azure, Aiven, GCP)
│
│ Internet
▼
Power BI Desktop
│
▼
Power Query
│
▼
Reports & Dashboards

lets connect power bi to Aiven SQl database

Step 1: Install the MySQL Connector

Power BI uses the MySQL Connector/NET driver to communicate with MySQL databases.

If it isn't already installed:

Download MySQL Connector/NET from Oracle.
Install it.
Restart Power BI.

Step 2: Connect from Power BI

Open Power BI Desktop.

Go to:

Home
→ Get Data
→ MySQL Database

Step 4: Enter the Server Information

In the Server field, enter:

mysql-demo.aivencloud.com:15432

Replace the hostname and port with the values from your Aiven dashboard.

Step 4: Authenticate

Choose Database Authentication.

Enter:

Username:
avnadmin

Password:

Click Connect.

Step 5: SSL Configuration

Aiven requires encrypted connections.

Aiven mysql doesnt really need SSl certificate as it uses
the database driver (MySQL Connector/NET or Npgsql).

Step 8: Select Tables

After authentication, the Navigator window appears.

Example:
products

sales

employees

Select the tables you need.

Choose:

Load – Import directly into Power BI.
Transform Data – Open Power Query to clean and shape the data before loading.

Postgre Database

connection to postgre service requires ssl certificate

i) click your Windows taskbar search tool, type "Manage computer certificates" (or press Win + R, type certlm.msc, and press Enter) to open the Certificate Manager.
ii) On the left side panel, locate and expand the folder named Trusted Root Certification Authorities.
iii) Right-click the underlying Certificates sub-folder -> Go to All Tasks -> Click Import....
iv) Step into the wizard and click Next. When the file browser opens, change the extension dropdown in the bottom-right corner from "X.509" to "All Files (.)" otherwise, your ca.pem file will stay invisible
v) Select your ca.pem file, click Next through the default locations, and click Finish. Click Yes on the dramatic Windows security warning pop-up.