DEV Community: Mistry Khateyi

Connecting Power BI to SQL Databases: A Complete Guide to Local SQL Server and Aiven PostgreSQL with SSL

Mistry Khateyi — Sat, 04 Jul 2026 09:30:45 +0000

Introduction

Microsoft Power BI is among the strongest Business Intelligence (BI) solutions of the moment. It helps businesses turn data into actionable information with interactive reports and dashboards.
One of the best features of Power BI is that it can seamlessly integrate with a vast range of data sources, including SQL databases on premises or in the cloud. Cloud-hosted databases are more secure and additional security measures like SSL encryption are required for connecting to a local database, whereas it is relatively simple to connect to a local one.
In this tutorial, I will show you how to connect to Power BI:

A Local SQL Server Database
An Aiven PostgreSQL Cloud Database. This is a Secure SSL Configuration for Cloud Connectivity.

At the end of this guide, you will be able to securely connect Power BI to local and cloud databases and start leveraging Power BI to create rich analytics solutions.

Prerequisites

Before you start, you need to have the following:

Microsoft Power BI Desktop
SQL Server, for local connections to the database.
Aiven PostgreSQL database
DBeaver -optional, for testing database connection
Access to your machine for administrative purpose.
Internet connectivity.

Architecture Overview

Power BI Desktop
       │
       ▼
SQL Database
(Local SQL Server)

For cloud connectivity:

Power BI Desktop
       │
       ▼
 SSL Encryption
       │
       ▼
Aiven PostgreSQL
       │
       ▼
Cloud Database

in this section, you will be using a local SQL Server database to connect to Power BI.For this part, you will be using a local SQL Server database to connect to Power BI.

Part 1: Connecting Power BI to a Local SQL Server Database

Step 1: Launch Power BI Desktop.

Open Power BI Desktop, and click on.
A specific object on the local network. An entity on the local network, such as a SQL Server Database.
Power BI offers two types of connections:
Import Mode. Imports data into power BI.

Benefits:

Faster report performance
Offline analysis
Optimized visualizations

DirectQuery Mode.will retrieve information from the source database each time a report is refreshed.

Benefits:

Real-time data access
Works with large databases
Reduced storage requirements

Step 2:Enter Server Details

Provide:

SQL Server Name
Database Name (Optional)

Example:

Server: DESKTOP-SQL01
Database: SalesDB

Then CLICK OK.

Step 3: Authenticate

Choose the appropriate authentication method:

Windows Authentication
Database Authentication
Microsoft Account

Once authenticated, you will be able to see the tables and views that are available in Power BI.

Step 4: Select and Load Data.

Choose the appropriate tables.

Preview the data. Click Load.

Power BI takes the selected data and processes it for reporting and visualization.

In this part, we will connect Power BI to our Aiven PostgreSQL Database.In this part we will connect Power BI to our Aiven PostgreSQL Database.
When using cloud databases, you need to ensure that there is encrypted communication between the client and the database server.
PostgreSQL connections are secured with SSL certificates in Aiven.

Part 2: Connecting Power BI to an Aiven PostgreSQL Database

Unlike local SQL Server, cloud databases require encrypted communication between the client and the server.
Aiven secures all PostgreSQL connections using SSL encryption.

Step 1: Get the information of the database connection.

Database Name
Host
Port
Username
Password
SSL Mode
Service URI

These information will be utilized when setting up Power BI.

In this screenshot below, you can see a PostgreSQL connection string for Aiven. In this screenshot you can see an Aiven PostgreSQL connection string.

The settings above are the PostgreSQL connection settings that have been provided by Aiven. Such credentials are needed to create a secure connection from Power BI.

Step 2: Downloading CA Certificate.
Click into the Connection Information page and you will see the CA Certificate section below.
Download the certificate file of the application.

The CA certificate is used to identify the PostgreSQL server, and to create a trusted encrypted connection.
Now the SSL Certificate needs to be imported, for this.

Step 3.Open the certificate that is downloaded.

Open the certificate that is downloaded.

Start the Certificate Import Wizard.

Choose:

Current User or Local Machine
Trusted Root Certificate Authorities

Complete the installation process.

The Certificate Import Wizard is displayed. The Certificate Import Wizard appears.

The certificate installation guarantees that Windows will trust the Aiven PostgreSQL server and won't generate any SSL validation errors.

Step 4: Check the Connection with DBeaver

Check that the database is accessible prior to connection with Power BI.

Launch Open DBeaver and connect to the PostgreSQL instance.

Validate:

Successful connection
Database availability
Table accessibility
Data visibility

In this section, the user will verify the database using Dbeaver.Now, user will verify the database in Dbeaver.

Step 5: Connect Power BI

Open Power BI Desktop.

Select:

The first arrow is from Home to Get Data. The first arrow is from Home to SQL Server Database.

This is an example of a data source.This is an example of data source postgresql database.

Depending on your configuration.

Enter:

Server Name
Database Name

Choose:

Import
DirectQuery

Click OK.

Now, go to the Power BI Database Connection window by clicking this button. Click this button to open the Power BI Database Connection window.

Use a Power BI Database Connection to connect to a Power BI database. Connect to a Power BI database using a Power BI Database Connection.

This configuration screen is used by Power BI to create a connection to the Aiven PostgreSQL database.

Step 6: Authenticate

Provide:

Username
Password

If asked, enable SSL.

Power BI will create a safe and secure encrypted connection to the cloud database.

Step 7: Load Data into Power BI

After authentication:
First, pick the tables to which you want to add attributes.

Preview the data.
Click Load.

New data is now available on:

Dashboards
Reports
KPIs
Data Modeling
Interactive Visualizations.

Why SSL Matters

Data between Power BI and the database is encrypted with SSL.
Benefits include:

Data confidentiality
Protection against interception
Server authentication

The ability to adhere to security protocols. Security standard compliance.

Secure cloud communication

If not using SSL, sensitive data could be revealed in transit.

Aiven PostgreSQL vs Local SQL Server

Database type	Local SQL Server	Aiven PostgreSQL
Hosting	On-Premises	Cloud
Internet Required	No	Yes
Clear Text	Optional	Required
Scalability	Limited by Hardware	Highly Scalable
Plan	Construction	Operation
Data is available on local network	Data is available to global access
Manually configured	Manual	Built-in

Troubleshooting Tips

SSL Certificate Error
Ensure the CA certificate has been imported into the Trusted Root Certification Authorities store.
Authentication Failed

Verify:

Username
Password
Database name
Unable to Connect

Confirm:

Host name
Port number
Firewall settings
Internet connectivity
No Tables Visible

Check that your database user has permission to access the required schema and tables.

Best Practices
Use SSL for all cloud database connections.
Store credentials securely.
Keep Power BI Desktop updated.
Test database connectivity using DBeaver before connecting Power BI.
Use Import mode for better performance unless real-time data is required.
Regularly refresh datasets to keep reports up to date.

Conclusion

Connecting Power BI to SQL databases is a fundamental skill for data professionals. While local SQL Server databases offer a simple setup, cloud-hosted platforms such as Aiven provide scalability, high availability, and enhanced security through SSL encryption. By following the steps outlined in this guide, you can confidently establish secure connections, validate your database using DBeaver, and import data into Power BI for interactive reporting and analytics.

Whether you're working on personal projects, academic assignments, or enterprise business intelligence solutions, understanding both local and cloud database connectivity will strengthen your data analysis skills and prepare you for real-world scenarios.

Understands Relationships, Schemas and Joins in Data Analytics for Beginners.

Mistry Khateyi — Mon, 29 Jun 2026 12:14:05 +0000

Introduction.

One of the most difficult aspects for a data analyst looking to become successful is learning how data from various tables relates. SQL queries, SQL Dashboards in Power BI, or designing a database, three things are seen throughout:

*Relationships
*Schemas

Joins

The below concepts are the foundation of relational databases. Once you know them, you will find data easier to analyze.
In this article, we will discuss each concept, and provide some examples.

What is a database relationship?

A relationship is a linkage between two or more tables by the same column.
Imagine an online store with two tables:

Customers

CustomerID	Name
101	Alice
102	Brian
103	Carol

Orders

OrderID	CustomerID	Product
5001	101	Laptop
5002	101	Mouse
5003	103	Keyboard

Notice that the two tables have a column named CustomerID.
The column in that column is the person who placed the order.
If there were no relationships, each table would be considered a separate table.

Types of relationships

One-to-one relationship

One row in one table matches one and only one row in another.

One to many relationship

This is the most common relationship. This relationship is commonly leveraged in power BI models.

Many to many relationship

There are several records in one table that are associated with several records in another table.

What is database schema

Is the logical organization of a database is called a schema.

It defines:
*Tables

Columns
Relationships
Constraints

Database designers build schemas before data is stored, similar to how an architect designs a building before it is built. For example the blueprint of a building.

Common Schema Types

Star Schema

The best model to use in Power BI. It consists of :

One Fact table
Multiple Dimension tables For example;

            Products
                |
Customers ---- Sales ---- Dates
                |
            Employees

Advantages:

Fast performance
Easy to understand
Optimized for reporting
Recommended by Microsoft

Snowflake Schema

A snowflake schema forces dimension tables to be broken into other tables.
Example

Sales
   |
Products
   |
Category
   |
Department

Advantages

Reduces redundancy
Better data consistency

Disadvantages

More joins
More complex queries

Galaxy Schema

Also known as a Fact Constellation. It has several fact tables with dimension tables.
Example

Sales ------ Customers
   |
Inventory ---- Products

This is normally used in large organization.

What Is a SQL Join?

A join is a method used to create a result set from two or more tables based on the key column of one table that matches the key column of another.
Joins are essential for being able to query data from multiple tables.

INNER JOIN

Its Only the matching records will be returned.
Example

SELECT
    Customers.Name,
    Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result

Name	Product
Alice	Laptop
Alice	Mouse
Carol	Keyboard

Brian is not included as he has no orders.

LEFT JOIN

Selects all rows from left table.

If there are matching records in the right table, they are added.

SELECT
    Customers.Name,
    Orders.Product
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result

Name	Product
Alice	Laptop
Alice	Mouse
Brian	NULL
Carol	Keyboard

RIGHT JOIN

Returns all the rows from the right table.
If there is a mismatch on the left side then those matches are null.

FULL OUTER JOIN

Returns:

All matching records All records that do not match between the two tables. This can be helpful to detect missing data.

CROSS JOIN

Makes all possibilities.
If one table has 10 rows and another table has 20 rows then the result contain.
A table joins with itself. Useful for:

Employee-manager relationships
Organizational hierarchies
Family trees

Relationships vs Joins

Relationships and joins are different, yet appear as if they are.

Relationships	Joins
Permanent join of tables	Temporary operations
Defined in the data model	Written in SQL
Automatically used by Power BI	Used when you query the data
Enhance the navigation of the model	Add data to the model during execution

Best Practices

Use a Star Schema as much as you can

1.create relationships using unique keys.

If possible, do not use many-to-many relationships.

Use meaningful primary keys.

3.Minimize unnecessary joins.

Maintain dimension tables with no duplicate data.

Real-World Example

Suppose you are developing a Sales Dashboard in Power BI.
You might have:

Sales (Fact table)
Customers
Products
Date
Regions

The relationships link the tables together and Power BI uses the relationships to sum up sales by customer, by product, by month, or by region. In SQL you would have to join the tables beforehand and then work out your results.

Conclusion

The ability to understand relationships, schemas and joins are some of the most important skills for any Data Analyst. Relationships are the way that tables are related to each other, schemas provide a structure for the entire database, and joins allow to retrieve related data from other tables.
Understanding these concepts will help you design more effective SQL queries, create efficient Power BI data models, and design scalable databases.

How Excel is applied in real world data analysis.

Mistry Khateyi — Sun, 07 Jun 2026 19:52:56 +0000

My initial entry into data analysis was my belief that it would require me to learn a complex programming language or use third party software, before I could begin working with data. My idea was that data analysts would be coding their whole day, using highly specialized tools. Upon starting my Data Science & Analytics journey, however, there was one of the most powerful and widely used tools in data analysis that I knew: Microsoft Excel.
Microsoft Excel is a spreadsheet program designed to enable users to organize, calculate, analyze and visualize data. With the rise of sophisticated analytics tools like SQL, Python, and Power BI, you might wonder why Excel still plays a crucial role in many industries. While these new tools like SQL, Python, and Power BI have created a trend of more advanced data analytics, Excel is still an essential tool for many industries, largely because it is accessible, flexible, and effective for everyday data tasks.

Using Excel in the Real World

Business Decision-Making

There is a lot of data that businesses generate on a daily basis. Businesses can use Excel to keep track of sales, monitor stock levels, compare sales metrics across months and years, and make decisions based on those data.

For instance, a retail outlet can use Excel to compare sales data from one month to the next, and determine what products are selling best during certain seasons. These insights can then be used by the managers to make decisions for inventory and market. Excel can present raw data in meaningful information, which helps us in making decisions based on data.Excel can present the raw data in meaningful information, which helps us make decisions based on data.

Financial Reporting and Budgeting

Excel is the other programme used extensively in finance. It is essential to organizations for budget preparation, tracking of revenue, management of expenses, and financial forecasting.

SUM, SUMIF and SUMIFS are particularly helpful functions. The SUM function totals the values in a range; SUMIF and SUMIFS can total values based on conditions. A business, for instance, can easily determine the company's sales for a specified area or department. These functions help in financial performance analysis and in creating accurate reports.

Marketing and Operational Performance

Excel is a tool that is commonly used by marketing and operations teams for performance evaluations. Marketers can monitor clicks and conversions, advertising spend, and campaign results, and operations can monitor productivity and workflow.

For example, a marketing team could use Excel to compare how well a different social media campaign was going from a performance perspective. They can measure conversion rates and costs to determine which ad campaign provides the highest ROI. Excel can also be used to clean and arrange datasets, making trend analysis much easier.

Formulas and features I have learned to use in Excel.

Data cleaning and validation.

The first thing I learned was clean data. Excel offers tools to remove duplicate data, fix inconsistent data and set up data validation rules. Clean data enhances the accuracy and helps to make decisions based on accurate information.

Sorting and Filtering

Sorting and filtering can help to work with larger data sets. The characteristics enable the user to structure information, prioritize relevant information, and easily identify trends and patterns. Users can quickly narrow down what data fields are important and just look at thousands of rows, rather than reviewing them all individually.

Statistical Functions

I've also picked up some helpful statistical functions:

AVERAGE is a function that returns the average value of a data set.
The middle value of a set of data is determined by using MEDIAN.
The most common value is calculated by MODE.

These functions can provide you with various information regarding the same set of data. For instance, if examining the amount that customers spend, the median spending could give a better idea of the general spending habits of customers while the average might be skewed by some large purchases.
Personal Reflection.