DEV Community: Young Odhiambo

Connecting Power BI to SQL Databases: A Practical Guide

Young Odhiambo — Sat, 04 Jul 2026 09:23:03 +0000

Introduction

In most modern organizations, data is one of the most valuable assets. Companies collect large amounts of information from sales systems, websites, customer platforms, and operational databases. To make sense of this information, businesses use tools that can transform this raw data into clear insights. One of the most widely used tools for this purpose is the Microsoft Power BI platform.

What is Power BI?

Power BI is a business intelligence and data visualization tool developed by Microsoft. It allows users to connect to multiple data sources, transform raw data into meaningful insights, and present results through interactive dashboards and reports.

Businesses use Power BI to monitor performance, identify trends, and support decision-making in areas such as sales, marketing and operations. Instead of relying on static spreadsheets, Power BI enables analysts to work with real-time data, ensuring reports remain accurate and up to date.

Most organizations store their operational and analytical data in SQL databases. SQL databases are designed to store large amounts of structured data in tables. They allow users to query, filter, update, and analyze data efficiently using Structured Query Language (SQL). SQL databases are reliable, secure, and scalable, hence they are widely used in business systems such as sales platforms, customer management systems, and inventory systems.

Connecting Power BI to a database allows analysts to access this stored data directly. Instead of manually exporting data into spreadsheets, Power BI can retrieve the data automatically, refresh it when the database changes, and build dashboards that always reflect the latest information.

This article discuss how to connect Power BI to SQL databases, connecting Power BI to local PostgreSQL database, how to connect to cloud database such as Aiven PostgreSQl, and how loaded data are used for analysis.

Understanding Power BI

Before connecting to a databases its important to understand the power BI interface. Power BI Desktop is the main application for building reports and dashboards.

Power BI Desktop contains a user-friendly interface designed to help build reports efficiently:

Ribbon: Contains tabs like Home, Insert, and Modeling with tools for data and visuals.
Report canvas: The main area where you build and arrange your visuals.
Fields pane: Displays the tables and columns loaded into Power BI.
Visualizations pane: Used to select and customize charts.

You can get the Power BI Desktop here Power BI Desktop

Connecting Power BI Desktop to a Local PostgreSQL

PostgreSQL is a free and open-source database management system used to store, organize and manage data efficiently. The steps below explain how to connect Power BI to a local PostgreSQL database.

Step 1 : Open Power BI Desktop
Start by opening Power BI Desktop on your computer.
When the application opens, a blank report canvas appears. This is where you will build your report after loading the data.

Step 2 : Get Data
On the Home tab of the ribbon, click Get Data.
This button opens a list of available data sources. Power BI supports many data sources including:

Excel
SQL Server
PostgreSQL
Web APIs The Get Data feature is the starting point for connecting Power BI to any data source.
Other data sources are as shown on the image.

Step 3 : Select PostgreSQL Database
From the list of available data connectors, click more to view more options. Scroll down, select PostgreSQL Database and click Connect.

Step 4 : Enter Database Connection Details
After selecting PostreSQL Database and clicking connect, Power BI opens a window requiring to enter the Database connection details.

Server : The location of the database server. If the database is on your computer, use localhost.

Database : The name of the database you want to connect to.

Step 5 : Provide Logins Details

After a connection is made Power BI will ask for authentication details.
You'll need to provide:

Username
Password

These credentials were set up during installation of the PostgreSQL and allows Power BI to securely access the database.

Once the credentials are entered, click Connect.

Step 6 : Select Tables To Import

After connecting successfully, Power BI opens the Navigator Window which displays all available tables in the database.
You can preview the contents of each table before loading them.
There are two options:

Load : Import the data directly.
Transform Data : Clean or modify the data before loading it.

Connecting Power BI To Cloud Database (Aiven PostgreSQL)

Cloud platforms like Aiven provide managed PostgreSQL databases that can be accessed remotely.

Connecting Power BI to PostgreSQL allows users to analyze real-time database data directly inside dashboards.

Step 1 : Get Database Connection Details from Aiven
Login to Aiven and inside your dashboard you'll find connection details fo your PostgreSQL service. These details are used by Power BI to locate and connect to your database.

Step 2 : Download and Install the SSL Certificate
In Cloud Databases customer data is often encrypted when stored in database tables, temporary files, and backups. External connections can be encrypted by using SSL.
SSL encryption ensures;

Data transferred between Power BI and the database is encrypted.
Unauthorized users cannot intercept the connection.
The database server identity is verified.

In Aiven download the CA Certificate from the connection.

Search for Manage User Certificates in your Desktop and choose Trusted Root Certification to import your Certificate connection.

Browse the files to select the certificate and click next until the importation is finished

Step 3 : Connect to Power BI
Open Power BI desktop as before, click Get Data, click more, scroll down and select PostgreSQL Database.
Copy the Server Name from the service URL(host_name:port_number) on Connection Information and paste on Power BI.
Input the name of your database and click OK.

Copy the username and password from Aiven, input them on the Power BI credentials window that opens and click connect.
Select the tables you want to work with and click on load or transform data depending on what you wish to do with the data.

Successfully loaded data displays on the data pane as shown on figure below.

Conclusion

Power BI is a powerful tool that helps organizations transform raw data into meaningful insights. By connecting directly to SQL databases, Power BI allows analysts to access structured data stored in business systems and convert it into interactive dashboards and reports.

Data Modeling, Joins, Relationships and Schemas

Young Odhiambo — Mon, 22 Jun 2026 20:46:52 +0000

Data Modeling

Data modeling is the process of creating a structured and visual representation of data, defining how data elements relate to one another within a system. It helps translate business requirements into organized data structures that support accurate analysis and effective decision-making.

What is a Data Model?

A data model is a visual and logical representation of an organization data elements and the relationships between them.

Importance of Data Modeling

Improved data organization.
Better data integrity.
Enhanced data security.
Increased scalability.

Types of Data Models.

There are three main types of data models:

Conceptual data models.

They are also referred to as domain models and offer a high-level view of what the system will contain, how it will be organized, and which business rules are involved. Conceptual models are created as part of the process of gathering initial project requirements.

Purpose: Organize and define business problems, rules and concepts.
Focus: High-level overview of data such as customer data, market data and purchase data.
Audience: Business stakeholders and analysts.
Example Use: Understanding the relationship between customers and their orders before designing the database structure.

Logical data models.

Shows how data entities are related and describes the data from a technical perspective.

Purpose: Define tables, columns, relationships and constraints that form the data structure.
Focus: Structure of the data without depending on any specific database management system (DBMS).
Audience: Data architects and analysts.
Example Use: Outlining the schema, relationships and rules for customer and order data, which later guides the physical database design.

Physical data models.

Guides the implementation of a database with a detailed representation of a database design.

Purpose: Define every element needed to construct a database, including tables, columns, keys and constraints (primary key, foreign key, NOT NULL, etc.).
Focus: Actual implementation of the database using queries and the chosen DBMS features.
Audience: Developers and database administrators (DBAs).
Example Use: Creating the database schema and ensuring that all constraints and relationships are enforced in the physical database.

Data Modeling Techniques

Here we describe the primary techniques used to develop data models.

Hierarchical data models: Represent one-to-many relationships in a treelike format. In this type of model, each record has a single root or parent, which maps to one or more child tables.

Entity-relationship (ER) models: Are a variation of relational models and they provide a graphical representation of entities, their attributes, and the relationships between them.

Relational Model: Relational Mode represent the links between tables by representing data as rows and columns in tables. It is frequently utilised in database design and is strongly related to relational database management systems (RDBMS).
Object-oriented data models: Models are similar to ER models in that they represent data as objects with attributes and methods, but object-oriented models abstract entities into objects.
Dimensional data models: Models are based on the concept of dimensions, which are used to categorize and organize data, and facts, which are the numeric measurements or values associated with the dimensions.

Ralationship in a Database

A relationship in a DBMS exists when a variable has a connection with the properties stored in different tables. Such relationships help the organization of entities intertwined with each other, ultimately enabling efficient data processing. They're exhibited usually via keys in a table, which is either columns or fields that specify a distinctive arrangement for each record.

Key features of database relationships.

Primary keys: Unique identifiers for each record in a table, ensuring that no two rows are identical.
Foreign keys: Links between tables, pointing to primary keys in another table to establish relationships.
Data integrity: Relationships ensure consistency and prevent errors, such as associating an order with a non-existent customer.

Types of Database Relationships.

Database relationships can be broadly categorized into four types, each serving a specific purpose in connecting data.

One-to-one (1:1) relationships

A one-to-one relationship connects one record in a table to exactly one record in another table. This is often used when splitting data into multiple tables for better organization or security.

One-to-many (1:N) or many-to-one (N:1) relationships

In a one-to-many relationship, a single record in one table can be associated with multiple records in another table. This is one of the most common relationship types in relational databases.

Many-to-many (N: N) relationships

A many-to-many relationship occurs when multiple records in one table are related to multiple records in another table. This is implemented using a junction table to manage the connections.

Self-referencing relationships

In a self-referencing relationship, records in a table are linked to other records within the same table.

Database Schemas

A database schema defines the structure and organization of data within a database. It outlines how data is logically stored, including the relationships between different tables and other database objects.

What is a Schema?

A schema is the blueprint or structure that defines how data is organized and stored in a database.

Types of Database Schemas

Conceptual Database Schema

A conceptual schema describes the overall structure of the entire database at a high level.
It focuses on the meaning of data and captures how different entities relate to each other—without worrying about how data is stored physically.

Physical Database Schema

The physical schema defines how the data is actually stored on disk.
Specifies files, indexes, partitions, storage blocks, access paths, etc.
It represents the lowest level of abstraction, focusing on performance, storage optimization, and data retrieval efficiency.

Logical Database Schema

The logical schema describes the logical structure of data as it appears to the database designers and developers. Defines tables, columns, primary keys, foreign keys, relationships, and integrity rules. This schema represents a higher level of abstraction and is independent of physical storage details.

Difference between Logical and Physical Database Schema

Database Schema Designs

Flat Model

A flat model schema is a 2-D array in which every column contains the same type of data/information and the elements with rows are related to each other. It is just like a table or a spreadsheet.

Star Schema

The star schema is a simple and commonly used schema in data warehousing. It consists of a central fact table surrounded by dimension tables, forming a star-like pattern.

Use Cases: Star schemas are ideal for straightforward reporting and querying. They are efficient for read-heavy operations, making them suitable for dashboards and summary reports.

The star schema does not necessarily have to be in the shape of a star. Below is also a star schema and is a preferred arrangement for many experts as in this arrangement is easier to visualize filter propagation from dimension to fact tables.

Snowflake Schema

The snowflake schema is a normalized version of the star schema. In this design, dimension tables are further divided into related tables, resulting in a more complex structure.

Use Cases: Snowflake schemas are used in scenarios requiring detailed data models and efficient storage. They are beneficial when dealing with large datasets where data redundancy needs to be minimized.

Joins in data modeling

Joins are used to combine data from multiple tables into a single view.

Types of Joins

Inner Join

An inner join returns only the records that have matching values in both tables being joined. It combines rows from both tables based on a common column known as the join key. The result of an inner join will only include rows where there is a match in both tables.

Left Outer Join

This type of join returns all the rows from the left table and matching rows from the right table. If there are no matching rows in the right table, it returns NULL values for the right table columns.

Right Outer Join

This type of join returns all the rows from the right table and matching rows from the left table. If there are no matching rows in the left table, it returns NULL values for the left table columns.

Full Outer Join

This type of join returns all the rows from both tables with NULL values in the columns where there is no match.

Summary

Here's the beginner-friendly recap:

Data Modeling is the process of organizing and connecting your data tables so Power BI knows how they relate to each other.

Schemas are the blueprint of your model. The Star Schema is your best friend in Power BI — one fact table surrounded by dimension tables. The Snowflake Schema exists but adds complexity without much benefit in Power BI.

Relationships are the lines between tables. They're built on Primary Keys and Foreign Keys, and they define how filters flow through your model. The most common type is One-to-Many (1:N).

Joins (like Inner Join and Left Join) are how Power BI physically combines rows from different tables when calculating your visuals.

How Excel is Used in Real-World Data Analysis

Young Odhiambo — Fri, 05 Jun 2026 10:54:36 +0000

Introduction

What is Excel?

Excel is a spreadsheet application developed by Microsoft that allows users to organize data into rows and columns, helping users analyze data, visualize data, and handle tasks efficiently.

Excel is Important because it's;

Simplifies data handling from basic lists to complex analysis.
Widely used for reports, project tracking and decision-making.
Provides formulas, functions, and tools that help identify trends, patterns, and relationships in data.
Saves time and increases efficiency when handling data-related tasks.

Real-World Uses of Excel in Data Analysis

Microsoft Excel is a widely used tool in the real world for managing, analyzing, and reporting data. It helps professionals organize information, perform calculations, and create reports using features like formulas, pivot tables, and charts.

Key Uses of Excel in Data Analysis

Data Analysis and Reporting
Excel is a powerful tool for analyzing data using formulas, pivot tables, and charts. it turns raw data into actionable insights, supporting data-driven decision-making
Financial Planning and Budgeting
Excel simplifies financial planning by allowing organizations to track expenses, create budgets, and calculate financial metrics. The use of built in functions ensures financial accuracy and precision.
Business Decision Making
Excel enables businesses to predict market trends, assess risks, and make data-driven decisions. By using historical data and forecasting models, businesses can plan for future growth and financial stability.

Excel Features and Formulas

SUM Function - Adds all numeric values in a selected range. Commonly used to calculate totals.
=SUM(number1,[number2],...)
Average Function - Calculates the mean range of values
=AVERAGE(number1,[number2],...)
MAX Function - Returns the highest value from a range.
=MAX(number1,[number2],...)
MIN Function - Finds the lowest value in a range
=MIN(number1,[number2],...)
COUNT Function - Counts how many cells in a range contain numeric values.
=COUNT(number1,[number2],...)
SUMIF Function - Adds values that meet a specific condition.
=SUMIF(range, criteria, [sum_range])
AVERAGEIF Function - Calculates the average of values that satisfy a condition.
=AVERAGEIF(range, criteria, [average_range])

Personal Reflection

Learning Excel has significantly changed the way I view data. Previously, I saw data as a collection of numbers and records. However, I now understand that data can reveal important patterns, trends, and insights when analyzed properly. Excel has taught me how to organize information efficiently, perform calculations accurately, and present findings in a clear and professional manner. As a result, I now see data as a powerful resource that can support decision-making, improve performance, and drive success in both academic and professional settings.