How I transformed a messy education dataset into an interactive Business Intelligence solution using PostgreSQL, Power Query, Data Modeling, DAX, and Power BI.
Introduction
Every dashboard tells a story.
But before the charts, KPIs, and colorful visuals come into existence, there's usually something much less exciting a spreadsheet full of inconsistent, duplicated, incomplete, and poorly structured data.
That was exactly the challenge my team received during our Power BI training.
Instead of being handed a perfectly structured dataset ready for visualization, we were given what many data professionals encounter in real organizations: a single flat dataset filled with inconsistencies that needed to be transformed into a reliable source of business intelligence.
Our assignment wasn't simply to create attractive charts.
Our assignment wasn't simply to create attractive charts.
We were expected to complete the entire Business Intelligence workflow, including:
- Extracting data from a PostgreSQL database
- Establishing a secure database connection using SSL certificates
- Cleaning messy data using Power Query
- Designing a dimensional data model
- Creating relationships between tables
- Writing DAX measures
- Building interactive dashboards
- Producing meaningful business insights
Although this was an educational project, the workflow closely mirrors what Business Intelligence developers and Data Analysts do every day.
In this article, I'll walk through that journey from start to finish.
Understanding the Business Problem
The dataset represented an education management system containing information about:
- Students
- Schools
- Teachers
- Subjects
- Academic performance
- Payments
- Locations
At first glance, everything appeared to exist inside one large table. While this may seem convenient, storing everything in a single table quickly creates problems.
Some of the issues included:
- Duplicate information
- Repeated school names and teacher details
- Inconsistent county names and spelling
- Mixed capitalization
- Missing values
- Incorrect data types and invalid numerical values
These are exactly the kinds of data quality issues that reduce trust in business reports. Our brief required us to clean the data, normalize it into logical tables, build relationships, and answer business questions through an interactive Power BI dashboard.
Why We Used PostgreSQL Instead of Importing a CSV
Many Power BI tutorials begin by importing an Excel or CSV file. While this works for demonstrations, most organizations do not store operational data inside spreadsheets.
Instead, business data is typically stored inside relational databases such as PostgreSQL, SQL Server, MySQL, or Oracle.
For this project, our dataset was hosted inside a PostgreSQL database, allowing us to simulate a real production environment where Power BI retrieves data directly from a database rather than from manually exported files.
This approach offered several advantages:
- A centralized source of truth
- Better data integrity
- Easier scalability
- Simplified data refreshes
- A more realistic enterprise workflow
Configuring PostgreSQL for Secure Connections
One of the most interesting parts of this project happened before opening Power BI.
To establish a secure connection to PostgreSQL, I first had to install and configure the required SSL certificates. Many cloud-hosted PostgreSQL instances enforce encrypted connections to protect data while it travels across the network. Without the correct certificates, Power BI cannot establish a trusted connection.
The process involved:
- Installing the PostgreSQL SSL certificate
- Configuring the database connection
- Verifying encrypted communication
- Testing the connection before importing data
Although this step often receives little attention in beginner tutorials, it reflects what happens in production systems where database security is a priority.
Key takeaway: Understanding secure connectivity is just as important as understanding visualization, because no dashboard can exist without reliable access to trusted data.
Connecting PostgreSQL to Power BI
With the secure connection established, I opened Power BI Desktop and selected Get Data > PostgreSQL Database.
After providing the server name, database name, and authentication credentials, Power BI connected directly to PostgreSQL and displayed the available tables through the Navigator window.
Rather than importing every available object, I selected only the tables required for analysis. This is a small but important optimization, because importing unnecessary tables increases model complexity and refresh times.
First Look at the Dataset
The initial inspection confirmed what the assignment description had warned about. The dataset contained numerous quality issues, including duplicate records, null values, incorrect spellings, mixed casing, inconsistent location names, invalid numerical values, and poorly formatted dates.
Important lesson: At this point, jumping directly into building charts would have produced misleading results. A dashboard is only as reliable as the data behind it.
For this reason, data preparation became the next priority.
Cleaning the Data with Power Query
Power Query is one of Power BI's most powerful features. Instead of modifying the source database, Power Query allows analysts to transform incoming data while keeping the original source untouched. This creates a repeatable Extract, Transform, and Load (ETL) process that can be refreshed whenever new data becomes available.
Our cleaning process involved several key transformations:
Standardizing Text
Some records used uppercase values, others used lowercase, and some contained abbreviations or spelling mistakes. For example, a county could appear in several different forms, causing Power BI to treat them as different locations. We standardized these values to ensure consistency across the entire dataset.
Correcting Data Types
Columns representing numbers had occasionally been interpreted as text, and date columns contained inconsistent formats. Power Query made it possible to convert each column into its correct data type before analysis.
Why this matters: Proper data types are essential because DAX calculations depend on them. A number stored as text cannot be summed or averaged.
Handling Missing Values
Several fields contained null values. Rather than blindly deleting records, we evaluated each case individually. Depending on the context, we either replaced missing values, kept them for transparency, or excluded unusable records where appropriate. Documenting these decisions is an important part of responsible data preparation because every transformation influences the final analysis.
Removing Duplicates
Repeated records inflate KPIs and distort business insights. Duplicate students, schools, or teachers could easily lead to incorrect counts. Power Query enabled us to identify and remove duplicate records while preserving unique entities.
Renaming Columns
Clear naming conventions improve model readability. Instead of cryptic or inconsistent column names, we renamed fields using descriptive, business-friendly names. This makes DAX formulas easier to understand and simplifies collaboration.
Creating a Repeatable Transformation Pipeline
One of Power Query's greatest strengths is that every transformation becomes an Applied Step. Instead of manually repeating the cleaning process each time new data arrives, Power BI simply replays the transformation steps automatically during refresh. This makes the cleaning process reproducible, auditable, and significantly less error-prone.
Why We Did Not Keep Everything in One Flat Table
Once the data was clean, an important question emerged: should everything remain inside one giant table?
Technically, it could. Practically, it should not.
Flat tables work for small datasets, but as projects grow they become difficult to maintain. Repeated information wastes storage, complicates updates, and slows analytical queries. Instead of keeping one massive table, we normalized the data into a dimensional model that better represents how business entities relate to one another.
This decision laid the foundation for the next and arguably most important phase of the project: building a Star Schema.
Building a Scalable Data Model with a Star Schema
At this point, our data had been cleaned, standardized, and prepared for analysis. However, one major challenge still remained: the dataset still represented an operational system rather than an analytical one.
Operational databases are designed to store information efficiently. Business Intelligence systems are designed to analyze information efficiently. Those are two very different objectives.
If we had continued using one massive flat table, we would have encountered several problems:
- The same school information repeated hundreds of times
- Teacher information duplicated for every student
- Location information stored repeatedly
- Larger file sizes and slower report performance
- More complex DAX calculations
- Higher maintenance costs whenever data changed
Instead, we redesigned the model using one of the most widely adopted dimensional modeling techniques in Business Intelligence: the Star Schema.
Why Star Schema?
Before building the model, we briefly considered the three most common approaches:
Flat Table: Stores everything in one place. While it appears simple at first, it becomes increasingly difficult to maintain because every entity is repeated multiple times. Imagine a school with 800 students. The school's name would appear 800 separate times in the table.
Snowflake Schema: Introduces additional normalization by breaking hierarchical data into separate tables (Country > Region > County > Sub-county). This reduces redundancy further but also increases relationship complexity. For our assignment, this introduced unnecessary overhead.
Star Schema: Strikes an excellent balance between simplicity, performance, and scalability. The design consists of one central Fact Table surrounded by multiple Dimension Tables. Microsoft also recommends Star Schema as the preferred modeling approach for Power BI because it simplifies filtering, improves query performance, and makes DAX calculations easier to write.
Designing the Fact Table
The fact table became the heart of our analytical model. Unlike dimension tables, which describe entities, the fact table stores measurable events such as:
- Student scores
- Fee payments
- Attendance records
- Performance metrics
Every row in a fact table represents an event that can be analyzed. For example:
Notice that the fact table references other entities rather than storing all their descriptive information repeatedly. Instead of storing the teacher's county, school address, and contact details every time, it simply stores a key pointing to the Teacher table. This dramatically reduces redundancy.
Building the Dimension Tables
Once the fact table was identified, we separated descriptive information into dedicated dimension tables:
Student Dimension contained: Student ID, Student Name, Gender, Age, School, County
Teacher Dimension contained: Teacher ID, Teacher Name, Assigned School, Subject
School Dimension contained: School ID, School Name, Category, Location
Subject Dimension contained: Subject ID, Subject Name
Location Dimension centralized county and region information into a reusable table. This allows both schools and teachers to reference the same geographical information while maintaining consistency.
Understanding Primary Keys and Foreign Keys
One concept that finally clicked for me during this project was the relationship between primary and foreign keys.
A Primary Key uniquely identifies each record within a table. For example:
| School ID | School Name |
|---|---|
| 101 | Green Hills Academy |
| 102 | Sunrise School |
The School ID is unique. No two schools share the same identifier.
A Foreign Key references the primary key of another table. For example:
| Student | School ID |
|---|---|
| Alice | 101 |
| Brian | 102 |
Rather than writing the school name repeatedly, each student simply stores the corresponding School ID. Power BI then uses relationships to retrieve the descriptive information whenever required. This is one of the principles that makes relational databases so efficient.
Creating Relationships in Power BI
Once the tables had been separated, we connected them using relationships. Power BI supports several relationship types:
One-to-One: Every record matches exactly one record in another table. Valid but relatively uncommon in analytical models.
One-to-Many: This became the dominant relationship in our project. For example, one school has many students; one teacher has many student records; one subject has many assessment records. This relationship type reflects how educational data naturally behaves.
Many-to-One: Power BI often displays the same relationship from the opposite perspective. Many students belong to one school. The underlying relationship remains identical.
Understanding Cardinality
Cardinality determines how Power BI filters information across related tables. If relationships are configured incorrectly, visuals may return duplicated values or incorrect totals. Because our model followed a proper Star Schema, defining cardinality became much more straightforward, with most relationships naturally following a One-to-Many structure.
Filter Direction
Power BI allows filters to flow between related tables. Whenever possible, we maintained single-direction filtering, allowing filters to flow from the dimension tables toward the fact table. For example, selecting a county automatically filtered schools, students, teachers, and performance records. This simplified report behavior while reducing ambiguity during DAX calculations.
Why Normalization Matters
Dividing one table into several smaller tables might appear to make the project more complicated. Ironically, the opposite is true. Normalization makes the model easier to maintain, easier to understand, faster to query, less repetitive, and more scalable. As datasets grow, these benefits become increasingly significant.
Turning Data into Insights with DAX
After building the data model, the next step was teaching Power BI how to answer business questions. This is where Data Analysis Expressions (DAX) became essential.
Think of DAX as the language that teaches Power BI how to think. While relationships organize data, DAX transforms that organized data into business metrics.
Calculated Columns vs. Measures
One key lesson from this project was understanding the difference between these two approaches:
- Calculated Columns compute values for every row and store the result in the model
- Measures calculate results dynamically based on the filters applied by the user
Since dashboards are interactive, measures are generally the better choice because they recalculate automatically as users interact with slicers and filters.
Measures We Created
- Total Students
- Total Schools
- Total Teachers
- Average Student Score
- Student Distribution
- School Distribution
Where the dataset lacked complete information, such as revenue or outstanding balances, we intentionally avoided creating misleading measures. Instead, we documented the limitation and focused our analysis on the data that was available.
Important principle: Good analysts never invent data to satisfy a report.
Applying Business Logic with DAX
Beyond simple aggregations, DAX can encode business rules. Functions such as IF(), AND(), and OR() allow multiple conditions to be evaluated, while SWITCH(TRUE()) provides a cleaner alternative to long nested IF statements.
For example, a revenue classification measure could be written as:
Revenue Category =
SWITCH(
TRUE(),
ISBLANK([Revenue]), "Not Provided",
[Revenue] > 500000, "High Revenue",
[Revenue] > 100000, "Medium Revenue",
[Revenue] > 0, "Low Revenue"
)
We also learned how CALCULATE() changes the filter context, enabling measures such as total students in a specific county or average scores for selected schools. This flexibility is one of the reasons DAX is such a powerful analytical language.
Designing the Interactive Dashboard
With the model and measures complete, the final step was presenting the insights.
Our dashboard combined several visualizations to answer the questions outlined in the project brief:
- KPI Cards for key metrics
- Bar and Column Charts for comparisons
- Donut Charts for distributions
- Slicers for filtering by school, county, teacher, subject, and gender
- Tables for detailed records
Rather than filling the report with as many visuals as possible, we focused on clarity. Each chart answered a specific business question, and together they formed a cohesive analytical story.
Key Insights from the Project
Building the dashboard highlighted how quickly insights become accessible once data is properly prepared. Users could identify:
- Schools with the highest student populations
- Student distribution across locations
- Gender distribution
- Teacher allocation
- Subject performance
- Academic trends across schools and regions
More importantly, the project demonstrated that the quality of these insights depended entirely on the quality of the underlying data model.
Beyond Power BI Desktop
One topic we covered during training was what happens after a report is built.
Publishing a report to the Power BI Service allows organizations to:
- Share reports securely with stakeholders
- Schedule automatic data refreshes
- Create email subscriptions that send report snapshots at scheduled intervals
- Embed reports into web applications and organizational portals
We also explored Microsoft Fabric, Microsoft's unified analytics platform that brings together data engineering, data warehousing, real-time analytics, and business intelligence into a single ecosystem. Although our project was developed in Power BI Desktop, understanding how it fits within the broader Microsoft analytics platform provided valuable context for enterprise-scale solutions.
Lessons I Took Away
This project changed the way I think about dashboards.
Before, I assumed the most important skill in Power BI was creating attractive visualizations. I now realize that visualization is only the final stage of a much larger process.
The real work happens long before the first chart is created:
- Connecting securely to data sources
- Cleaning inconsistent data
- Designing scalable data models
- Building meaningful relationships
- Writing reusable DAX measures
- Delivering insights that decision-makers can trust
A visually impressive dashboard built on poor-quality data is still a poor dashboard.
Final Thoughts
What began as a classroom assignment became an excellent introduction to the complete Business Intelligence workflow.
Over the course of the project, I learned how to connect Power BI to a PostgreSQL database using secure SSL connections, transform messy data with Power Query, redesign a flat dataset into a Star Schema, create relationships between fact and dimension tables, write DAX measures, and communicate insights through an interactive dashboard.
More importantly, I learned that Business Intelligence is not about creating charts. It is about transforming raw, imperfect data into information that supports better decisions.
As I continue exploring data engineering and analytics, this project serves as a reminder that every reliable dashboard begins with a well-designed data pipeline, a thoughtful data model, and a commitment to data quality.
Resources
If you are learning Power BI, I would recommend exploring:
- Microsoft Learn - Power BI Learning Paths
- Microsoft Learn - DAX Fundamentals
- Microsoft Learn - Power Query Documentation
- Microsoft Learn - Star Schema Guidance
- Microsoft Fabric Documentation
- PostgreSQL Official Documentation
- GitHub Repository
Connect With Me
If you are working on Power BI, data engineering, or analytics projects, I would love to connect and exchange ideas.
Top comments (5)
Very informative article! One question: when designing the Star Schema, what challenges did you face while deciding between fact and dimension tables, and how did you resolve them?
Well suprisingly ,deciding between the fact and dimension tables wasn't the biggest challenge because I had already spent time understanding dimensional modeling before starting the project. My approach was to first study the entire dataset and clean it thoroughly in Power Query. Once I was confident the data was consistent, I created duplicates of the original table one for each dimension table I planned to build, one for the fact table and an extra clean copy as a backup in case I needed to start over. For each dimension table, I kept only the columns that described that entity and identified its primary key. For example, the Student dimension contains the Student ID (primary key) along with descriptive attributes like the student's name, gender and other relatively stable details. Repetitive or transactional information was left for the fact table. Then, in the fact table, I removed those descriptive columns but retained the primary keys from each dimension. Those keys became foreign keys that link the fact table back to the dimensions, giving me a clean Star Schema without redundant data.
Repeating that process for each dimension made the model come together naturally. So rather than running into major challenges, having a clear workflow understand the data, clean it first, then break it into dimensions and facts made the modeling process much smoother. Hope i answered your question Hari
Yes, absolutely! That explained your approach very clearly. I especially liked how you duplicated the cleaned table before separating it into fact and dimension tables—it made the workflow much easier to visualize. Thanks for taking the time to explain!
I am glad I was of help 😊
keep posting ...