DEV Community: Wilbon

How to Connect PowerBI to PostgreSQL.

Wilbon — Wed, 11 Mar 2026 07:05:06 +0000

Introduction
Data does not live inside dashboards. Instead, it is stored inside databases, processed through queries, and then visualized using tools such as Microsoft Power BI.

Power BI is a business intelligence and data visualization tool developed by Microsoft that helps analysts turn data into interactive dashboards and reports.
Organizations use Power BI to:

Track business performance
Monitor sales and revenue
Analyze customer behavior
Build executive dashboards
Support data-driven decision making

Power BI connects to data sources such as SQL databases such as PostgreSQL.
PostgreSQL is a powerful open-source relational database that is widely used for:

Data warehousing
Transaction systems
Analytical workloads
Data engineering pipelines

Connecting Power BI to a local PostgreSQL Database
** Step 1: Open Power BI Desktop**
Start by opening Power BI on your computer, then select blank report.
Thereafter, navigate to the Home tab and click the Get Data button.

Power BI supports many data sources including:

Excel
Web APIs
SQL Server
PostgreSQL
Cloud Databases Since our data is stored in PostgreSQL, we will select the PostgreSQL connector.

Step 2: Select PostgreSQL Database

Inside the Get Data button:

Search for PostgreSQL Database.
Select the connector.
Click connnect. Powwer BI wil now request the database connection details.

Step 3: Enter Database Connection Details
To connect to a local PostgreSQL database, we enter the server information.
Example confguration:
| Field | Value |
| -------- | --------- |
| Server | localhost |
| Database | sales_db |

Authentication method:

Database Authentication.
Username
Password Example configuration: Server: localhost Database: sales_db Username: postgres Password: ********

After entering these details, Click Connect.
Power BI will now establish a connection with your PostgreSQL database.

Connecting Power BI to a Cloud PostgreSQL Database (Aiven)

In many modern data systems, databases are hosted in the cloud instead of local machines.
Cloud platforms allow organizations to scale databases and access them remotely.
One such platform is Aiven, which provides managed PostgreSQL services.
Inside the Aiven dashboard, you will find the following connection details:

Parameter	Example
Host	pg-12345.aivencloud.com
Port	12345
Database	defaultdb
Username	avnadmin
Password	your password

You will also download an SSL certificate.

Why SSL Certificates Are Required
When connecting to a cloud database, security is critical.
An SSL Certificate ensures that:

The connection between Power BI and the database is encrypted.
Database transmitted over the internet cannot be intercepted.
The database server identify is authenticated. Without SSL encryption, sensitive informations such as login credintials could be exposed. Therefore, most cloud databases require SSL-secured connections.

*Connecting Power BI to the Aiven Database
*
The steps are similar to connecting to a local database:

Open Power BI Desktop
Click Get Data
Choose PostgreSQL Database

Enter the connection details:

 Host: pg-xxxx.aivencloud.com
 Port: xxxx
 Database: defaultdb
 Username: avnadmin
 Password: ********

When prompted, Power BI will also require the SSL certificate file downloaded from Aiven.
Once the connection is authenticated, Power BI will successfully connect to the cloud PostgreSQL database.

*Loading Tables to Power BI

After connecting succcessfully, Power BI opens the Navigator Window.
This window displays all the tables inside the database.
Example datasets:

Customers
Products
Sales
Inventory Select the tables you want and click Load Power BI will upload the tables as an internal data model.

Creating Relationship between Tables

After loading the Tables, we need to establish relationships between them.
Relationships allow Power BI to understand how different tables are connected.
Examples keys:

Table	Key
customers	customer_id
products	product_id
sales	customer_id, product_id
inventory	product_id

Example of relationships:
customers.customer_id → sales.customer_id
products.product_id → sales.product_id
products.product_id → inventory.product_id

This structure forms a relational data model.
In analytics, this is commonly designed as a star schema, where:
1. Fact tables contain transactional data
2. Dimension tables contain descriptive data
3. This structure enables Power BI to perform accurate analytics.
For example, we can calculate:

Total sales per customer
Revenue by product category
Inventory levels per product
Monthly sales performance

Why SQL Skills Are Important for Power BI Analysts
Although Power BI provides graphical tools,SQL remainsa fundamental skills for analysts and data engineers.
SQL allows us to prepare and manipulate data beforevisualization.
Example queries:

Retrieving Data
SELECT *
FROM sales
WHERE sale_date >= '2025-01-01';

Aggregating Data
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Joining Tables
SELECT c.name, s.amount
FROM customers c
JOIN sales s
ON c.customer_id = s.customer_id;

Using SQL enables Analysts to:

Filter large datasets
Join multiple tables
Perform aggregations
Clean and prepare data for reporting

Conclusion
Learning how to connect visualization tools like Power BI to SQL databases is an important skill for data professionals.
Combining SQL, database systems, and visualization tools like Power BI is an essential part of becoming a capable data engineer.

The Two SQL Concepts That Made Me Finally Understand Real Data: Joins & Window Functions.

Wilbon — Wed, 04 Mar 2026 11:45:27 +0000

When I first started learning SQL, I thought I had it figured out.

I could write things like:

SELECT * FROM employees
WHERE salary > 50000;

But the moment I started working with real datasets, things got messy.

Data wasn’t in one table anymore. It was split across multiple tables — employees, departments, salaries, etc.

That’s when two SQL concepts started making real sense to me:

Joins
Window functions

Once I understood these, SQL felt way more powerful.

In this article, I’ll walk through both concepts using PostgreSQL examples, a small dataset, and the mental models that helped me understand them.

The Example Dataset

Let's imagine a small HR dataset.

employees
employee_id name department_id salary
1 Alice 1 90000
2 Bob 1 80000
3 Carol 2 70000
4 David 2 60000
5 Eve NULL 65000
departments
department_id department_name
1 Engineering
2 Finance
3 Marketing

You can create this dataset in PostgreSQL like this:

CREATE TABLE employees (
employee_id INT,
name TEXT,
department_id INT,
salary INT
);

INSERT INTO employees VALUES
(1,'Alice',1,90000),
(2,'Bob',1,80000),
(3,'Carol',2,70000),
(4,'David',2,60000),
(5,'Eve',NULL,65000);

CREATE TABLE departments (
department_id INT,
department_name TEXT
);

INSERT INTO departments VALUES
(1,'Engineering'),
(2,'Finance'),
(3,'Marketing');

Part 1: Understanding SQL Joins

Joins are how relational databases connect information across tables.

When I first learned joins, the simplest way to understand them was this:

A join links rows from two tables using a common column.

INNER JOIN — Matching Data Only

An INNER JOIN returns only rows where a match exists in both tables.

Example:
show employees and their department names.

SELECT
e.name,
d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

Result:

name department_name
Alice Engineering
Bob Engineering
Carol Finance
David Finance

Eve disappears because she doesn't have a department assigned.

INNER JOIN only returns matching rows.

LEFT JOIN — Keep All Employees

Sometimes we want all records from one table, even if a match doesn't exist.

That’s where LEFT JOIN becomes useful.

SELECT
e.name,
d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

Result:

name department_name
Alice Engineering
Bob Engineering
Carol Finance
David Finance
Eve NULL

Now Eve appears, but her department is NULL.

This type of query is extremely common when:

analyzing incomplete data
identifying missing relationships

FULL OUTER JOIN — Everything

_A FULL OUTER JOIN _returns everything from both tables.

SELECT
e.name,
d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

This is very useful for data reconciliation.

Part 2:
WINDOWS FUNCTION

When I first encountered window functions, they looked confusing.

But once I understood the idea, they became one of the most powerful tools in SQL.

The key difference from _GROUP BY _is this:

GROUP BY collapses rows.

Window functions do calculations without removing rows.

*Ranking Employees by Salary
*
Let's say we want to rank employees by salary.

SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

_Result:
_
name salary salary_rank
Alice 90000 1
Bob 80000 2
Carol 70000 3
Eve 65000 4
David 60000 5

The keyword OVER() tells SQL that we are doing a window calculation across rows.

Ranking Within Each Department

What if we want to rank employees within their department?

SELECT
name,
department_id,
salary,
RANK() OVER(
PARTITION BY department_id
ORDER BY salary DESC
) AS department_rank
FROM employees;

Now every department has its own ranking system.
This concept is widely used in analytics for things like:

best-selling products per category
highest-performing employees per department

Running Totals with Window Functions

Another powerful use case is cumulative calculations.

SELECT
name,
salary,
SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;

This creates a running total as the rows progress.

These are heavily used in:

financial reporting
revenue dashboards.

Key Lessons I took away

1 Real data almost always lives in multiple tables

2 Joins are the foundation of relational databases

3 Window functions unlock powerful analytical queries

Final Thoughts

Learning SQL felt straightforward at the beginning.

But when I started working with real datasets, I realized that the real power of SQL lies in:

connecting datasets
analyzing patterns within them Joins and window functions are two of the most important tools for doing exactly that.

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI.

Wilbon — Mon, 09 Feb 2026 12:00:23 +0000

How Analysts Turn Messy Data, DAX, and Dashboards into Action with Power BI.

Real-life data is rarely clean. It comes from Excel exports, Google Sheets, manual entries, and it usually has blanks, duplicates, wrong data types and inconsistent names

Power BI is popular because it helps you take that messy data and turn it into something people can use — clear KPIs, simple dashboards, and decisions you can act on.

The Workflow

When an analyst gets a dataset, the process is usually:

Understand the business question
Clean and shape the data (Power Query)
Build the data model (relationships)
Create measures (DAX)
Design the dashboard
Translate insights into action

Let’s go step-by-step.

1) Start with the Decision (Not the Visual)

Before building charts, ask:

What decision should this report help someone make?
What does “good performance” look like?
Which 3–5 KPIs matter most?

Examples:

Sales: Revenue, Orders, Conversion Rate
Operations: Delivery Time, Late Deliveries
Finance: Monthly Spend, Budget vs Actual

2) Import Data into Power BI

In Power BI Desktop:

Home → Get Data
Select your source (Excel, CSV, Folder, SQL, etc.)

If your data comes in multiple monthly files, Folder import is a huge win (Power BI can combine files automatically).

3) Clean Messy Data with Power Query (Beginner-Friendly Fixes)

Click:

Home → Transform data (opens Power Query Editor)

Here are the most common cleanup tasks you’ll do:

Fix #1: Set correct data types

Dates should be Date
Money should be Decimal Number
IDs are often best as Text (keeps leading zeros)

Fix #2: Remove empty rows and handle errors

Home → Remove Rows
Transform → Replace Errors

Fix #3: Trim and clean text

This removes extra spaces and weird hidden characters:

Transform → Format → Trim
Transform → Format → Clean

Fix #4: Standardize values

Example: mombasa, Mombasa, MOMBASA should become one value.

Transform → Replace Values
Or use text formatting options

Fix #5: Split columns

Example: “Full Name” → First Name + Last Name

Transform → Split Column

When done:

Click Close & Apply

4) Build a Simple Data Model

Go to Model view.

A beginner-friendly model often looks like a “star” :

Fact table = transactions (Sales, Orders, Payments)
Dimension tables = descriptions (Products, Customers, Region, Date)

Then create relationships:

Fact table connects to each dimension

Tip: Always add a Date table if you want monthly trends,etc.

5) Write DAX Measures

DAX sounds scary, but you can start with a few simple measures.

Measures vs Columns (quick difference)

Calculated column: computed per row (can make the model heavy)
Measure: computed dynamically (best for KPIs and visuals)

6.Turn Insights into Actions (The Part That Matters)

A dashboard is not valuable because it looks good.

Insights without action are just interesting facts.

Great Power BI reports guide decisions.

Examples of Insight → Action;

Region X sales dropped by 18%

→ Investigate possible stock-outs, pricing changes, or supply delays
Product A has high sales but low profit margin

→ Review production costs, supplier pricing, or discount strategy
Weekend orders consistently spike

→ Schedule more support and fulfillment staff on weekends

Each insight should naturally lead to a follow-up decision .

Power BI Features That Help Drive Action

Tooltips

Add extra context when users hover over visuals without cluttering the report
Drill-through pages

Allow users to click on a data point and explore deeper details behind it
Conditional formatting

Use color signals (for example, red for poor performance and green for strong performance) to highlight what needs attention immediately

When your dashboard helps users decide what to do next, it's becoming a decision-making tool.

7) Publish and Share Your Report

Once your report is complete and ready for others to use, the final step is publishing it.

In Power BI Desktop, go to:

Home → Publish

You’ll be prompted to sign in and select a workspace in the Power BI Service where the report will live.

What You Can Do in Power BI Service

After publishing, Power BI Service becomes the hub for collaboration and distribution. From there, you can:

Share reports

Give colleagues or stakeholders access to view or interact with your report
Set up data refresh schedules

Automatically keep your data up to date without manual reloading
Create dashboards or apps

Pin visuals to dashboards or bundle reports into apps for wider distribution

Publishing turns your work from a personal report into a shared where insights can actually drive decisions.

Introduction To MS Excel for Data Analytics.

Wilbon — Fri, 06 Feb 2026 12:26:38 +0000

What is MS Excel?

Microsoft Excel is a spreadsheet application used to store, organize, analyze, and visualize data. As a beginner, Excel is one of the easiest and most practical tools to start with when learning data analysis.

Excel allows you to work with data in rows and columns, making it simple to record information, perform calculations, and understand patterns in data. Because of its simplicity and wide use, Excel is commonly used in offices, schools, and businesses around the world.

In data analytics, MS Excel helps to:

Enter and clean data
Perform basic and advanced calculations
Summarize large datasets using tools like formulas and tables
Create simple charts and reports for better understanding

Excel is often the first tool most people learn before moving on to more advanced tools such as Power BI, SQL, or Python.

Understanding the Excel Workspace
When you open Microsoft Excel, you work inside a workbook. A workbook is simply an Excel file, and inside it, you’ll find one or more worksheets where your data lives.

The Excel workspace may look a bit busy at first, but it’s actually very organized.

An Excel workbook is made up of three main parts:

Rows

Rows run horizontally across the worksheet and are numbered starting from 1, 2, 3, and so on. Each row helps you keep related data together.

Columns

Columns run vertically from top to bottom and are labeled using letters such as A, B, C, and so on. Columns help you organize different types of data.

Cells

A cell is where a row and a column meet. For example, A1 is the cell where column A and row 1 intersect.
Cells are where you actually enter and work with data in Excel.

What can a cell store?

Each cell in Excel can store different types of data, such as:

1. Text – names, labels, or categories
2. Numbers

Entering and Organizing Data

In Excel, data is entered in a tabular format, which simply means it is arranged like a table. This is one of the most important habits to learn as a beginner because well-organized data makes analysis much easier.

A good rule to follow is:

Each column should represent one type of data
Each row should represent one record or entry

For example, if you are recording sales data, your worksheet might look like this:

Name Age Gender Sales
John 25 Male 300
Mary 30 Female 450

In this example:

The columns describe the data (Name, Age, Gender, Sales)

Each row represents one person’s record

Organizing data this way helps Excel understand your information better, making it easier to:

Sort and filter data
Perform calculations
Create charts and summaries
Always try to keep your data clean, consistent, and well-structured.

Sorting and Filtering Data

As your data grows in Excel, sorting and filtering help you quickly find and understand information. These are very important tools for beginners in data analysis.

Sorting

Sorting arranges data in a specific order, either ascending or descending.

Common examples include:

Sorting sales from highest to lowest
Sorting names alphabetically
Sorting helps you easily identify top performers, lowest values, or trends in your data.

Filtering

Filtering allows you to display only the data that meets certain conditions, while hiding the rest.

For example, you can filter to show:

Only sales greater than 300
Only female employees
Records that match a specific category

Filtering is useful when working with large datasets because it lets you focus only on the information you need at that moment.

Using Excel Tables

Excel Tables make working with data much easier, especially for beginners. When you convert your data into a table, Excel automatically adds helpful features that improve organization and analysis.

Excel Tables help by:

Automatically formatting data, making it easier to read
Allowing quick sorting and filtering using dropdown arrows
Making formulas dynamic, so they automatically apply to new rows

This means you spend less time fixing data and more time analyzing it.

How to Create an Excel Table

Creating a table in Excel is very simple:

Select all your data
- Press Ctrl + T on your keyboard
- Click OK when the confirmation box appears

Once your data is in a table, Excel treats it as a structured dataset, which is very useful for analysis, charts, and formulas.

Getting used to Excel Tables early will save you a lot of time as your datasets grow.

Creating Simple Charts

Charts help turn numbers into visuals, making data easier to understand at a glance. As a beginner, charts are one of the best ways to quickly see patterns, comparisons, and trends in your data.

Excel provides many chart types, but beginners usually start with these common ones:
Column Chart – Used to compare values across different categories

Line Chart – Used to show trends or changes over time
Pie Chart – Used to show proportions or percentages

Steps to Create a Simple Chart

Creating a chart in Excel is simple:
Select the data you want to visualize
Click on the Insert tab

Choose a chart type that fits your data

Excel will automatically generate the chart based on your selected data. You can then customize it by changing titles, colors, or labels to make it clearer.

Practicing with simple charts will help you understand your data better and communicate insights more effective.

Why Excel Is Important for Data Analytics Beginners

Excel is one of the most important tools for anyone starting out in data analytics. It provides a simple and practical way to understand how data works before moving on to more advanced tools.

Excel is important because:

It is easy to learn – beginners can start using Excel with little or no prior experience
It is widely used in workplaces across different industries
It requires no programming knowledge, making it accessible to everyone
It helps build strong data analysis foundations, such as data cleaning, sorting, and summarizing

By learning Excel, one develops essential skills like organizing data, identifying patterns, and creating visual insights. These skills are transferable to more advanced tools like Power BI, SQL, and Python.

Many professional data analysts still use Excel alongside advanced analytics tools because it is fast, flexible, and reliable for everyday analysis tasks.

Conclusion

Microsoft Excel is a powerful and beginner-friendly tool for data analytics. With features like formulas, sorting,and charts, users can perform meaningful data analysis even at an entry level. Learning Excel is a strong first step for anyone interested in data analysis or data science.

Schemas and Data Modelling in Power BI

Wilbon — Mon, 02 Feb 2026 15:29:26 +0000

Schemas and Data Modeling in Power BI

Raw data on its own rarely tells the full story. In most cases, data comes from multiple sources and sits in separate tables. Before building a clean, actionable report in Power BI, you need to organize your data properly. That’s where data modeling comes in.

A data model defines how your tables connect, how calculations work, and how your report performs. A well-designed model ensures faster visuals, more accurate results, and easier-to-understand reports.

Good data modeling in Power BI primarily involves using the right schema, defining correct relationships, and structuring your data into fact and dimension tables. This guide breaks down these key concepts and explains why a strong model is essential for reliable reporting.

Fact vs Dimension Tables

Power BI models are mostly built from two table types:
-Fact
-Dimension tables.

Fact Tables

Fact tables store measurable business events—the numbers that matter.

Characteristics:

Large (many rows)
Continuously growing
Full of keys linking to dimensions (e.g., CustomerID, ProductID)

Example:

SalesTransactions table with columns:

Dimension Tables

Dimension tables provide context for your facts—they answer the who, what, when, and where.

Characteristics:

Smaller than fact tables
Contain descriptive columns (names, categories, locations)
Used for filtering, grouping, and slicing

Examples:

Customer: CustomerName, City, Segment
Product: ProductName, Category, Brand
Date: Date, Month, Quarter, Year

Fact = What happened

Dimension = Details about what happened

Schemas: Organizing Relationships

A schema defines how tables are structured and connected—like a map showing how your data “talks” to each other. How you organize your schema affects:

Report speed
Accuracy of calculations
How filters and slicers behave

Common Schema Types

Star Schema (Recommended)

Central fact table with key metrics
Dimension tables directly connected to fact table

Advantages:

Easy to understand
Simple to build
Fast and optimized for Power BI

Snowflake Schema

One fact table
Dimension tables split into smaller related tables, forming chains of relationships

Drawback: More complex, can slightly slow performance

Relationships: The Glue of the Model

Relationships connect tables and let data work together. Think of them as bridges that allow information to flow between tables.

Key Concepts

1. Cardinality – Defines row relationships:

One-to-Many (1:*) – Most common (e.g., one customer → many sales)
Many-to-One (*:1) – Reverse of above
One-to-One (1:1) – Rare, used for splitting wide tables
Many-to-Many (: :) – Use carefully, can cause ambiguity

2. Cross-filter Direction – Determines how filters flow:

Single direction: Filter from dimension → fact (default, recommended)
Both directions: Filter flows both ways; use sparingly

3. Active vs Inactive Relationships – Only one active relationship exists between two tables at a time. Inactive relationships can be used in DAX when needed.

Why Good Modeling Matters

Performance & Speed

Filters go directly from dimension tables to fact tables
Denormalized tables compress better
DAX calculations run faster
Reports are more responsive

Accurate Reporting

Prevents double-counting
Reduces filter ambiguity
Simplifies DAX formulas
Ensures consistency across reports

Practical Modeling Steps in Power BI

Understand your data – Identify tables, events, and entities
Identify facts – Numeric metrics like SalesAmount or QuantitySold
Identify dimensions – Descriptive info for slicing, like ProductName or CustomerCity
Flatten hierarchies – Simplify related dimension tables if needed
Create relationships – Connect dimensions to the fact table using keys
Set table properties – Verify cardinality and filter direction
Hide technical fields – Hide foreign keys or unnecessary columns
Create measures – Build DAX metrics like Total Sales or Average Order Value

Common Pitfalls to Avoid

Circular relationships: Loops with bidirectional filters cause errors
Too many bidirectional filters: Can slow reports
Missing relationships: Break filters; ensure all facts connect to dimensions
Mixing facts and dimensions in one table: Keep descriptive columns separate
Using many-to-many without understanding: Leads to ambiguous results
Ignoring date dimension: Makes time-based calculations difficult
Too many DirectQuery tables: Impacts performance; prefer import mode

Conclusion

Data modeling is the invisible engine powering Power BI reports. A well-designed star schema with clear fact and dimension tables and proper relationships transforms messy data into actionable insights. Investing time in modeling ensures fast, accurate, and scalable reports that users can trust.