Power BI + SQL School Data Analysis Project

This project involves using Power BI and SQL to analyze and visualize data from a school management database. Using the database structure and data you’ve provided, we can extract insights and create meaningful dashboards that showcase various aspects of the school’s operations.

Get the dataset here : https://github.com/slidescope/data/blob/master/school_management_system.sql

---

Project Goals

1. Analyze student demographics, fees collection, and class-wise statistics.
2. Create a dashboard in Power BI for:
   • Total and pending fees.
   • Student distribution by class, gender, city, and state.
   • Fee payment trends over time.
   • Identifying defaulters (students with unpaid fees).
3. Utilize SQL queries for data extraction and transformation before importing the data into Power BI.

---

Steps to Implement

1. Database Structure Overview

We will use the following tables from the database:

1. class:
   • Columns: class_id, class_name, class_fees
   • Purpose: Stores class details and fee amounts.
2. student:
   • Columns: student_id, student_name, gender, city, state, class_id
   • Purpose: Contains student demographic information.
3. fees_submit:
   • Columns: student_id, submit_date, amount_paid
   • Purpose: Tracks monthly fee payments for students.

---

2. Data Extraction with SQL

We will write SQL queries to extract and preprocess the data for Power BI.

a. Total Students and Gender Distribution

Query to count students by gender and class:

SELECT 
    c.class_name,
    s.gender,
    COUNT(s.student_id) AS student_count
FROM 
    student s
JOIN 
    class c ON s.class_id = c.class_id
GROUP BY 
    c.class_name, s.gender;

b. Fees Collection Status

Query to calculate total fees paid, total fees due, and defaulters:

-- Total fees paid and due by class
SELECT 
    c.class_name,
    SUM(c.class_fees) * 12 AS total_fees_due,  -- Annual fees for all students in the class
    SUM(f.amount_paid) AS total_fees_paid,
    SUM(c.class_fees) * 12 - SUM(f.amount_paid) AS total_fees_pending
FROM 
    class c
JOIN 
    student s ON s.class_id = c.class_id
LEFT JOIN 
    fees_submit f ON s.student_id = f.student_id
GROUP BY 
    c.class_name;

-- List of defaulters
SELECT 
    s.student_name,
    c.class_name,
    COUNT(DISTINCT f.submit_date) AS months_paid,
    12 - COUNT(DISTINCT f.submit_date) AS months_due
FROM 
    student s
JOIN 
    class c ON s.class_id = c.class_id
LEFT JOIN 
    fees_submit f ON s.student_id = f.student_id
GROUP BY 
    s.student_id, s.student_name, c.class_name
HAVING 
    months_due > 0;

c. Monthly Fee Payment Trends

Query to analyze monthly fee submissions:

SELECT 
    DATE_FORMAT(f.submit_date, '%Y-%m') AS month,
    SUM(f.amount_paid) AS total_fees_collected
FROM 
    fees_submit f
GROUP BY 
    DATE_FORMAT(f.submit_date, '%Y-%m')
ORDER BY 
    month;

---

3. Importing Data into Power BI

1. Use Power BI Desktop to connect to the SQL database:
   • Select Get Data > SQL Server.
   • Enter the server name and database name.
   • Load the class, student, and fees_submit tables.
2. Use Power Query for transformations:
   • Combine student data with class and fees data using relationships.
   • Add calculated columns for Pending Fees, Fully Paid, and Partially Paid.

---

4. Visualizations in Power BI

Dashboard Components:

1. Total Students by Class and Gender:
   • Bar chart showing the distribution of students by class and gender.
2. Fee Payment Status:
   • Pie chart: Percentage of fully paid vs pending fees.
   • Table: List of defaulters with months unpaid.
3. Monthly Fee Collection Trends:
   • Line chart showing total monthly fees collected.
4. Class-Wise Fees Overview:
   • Table: Total fees due, paid, and pending for each class.

---

Key Insights

• Student Demographics:
  • Identify which classes have more boys vs girls.
  • Understand student distribution across cities and states.
• Fee Collection:
  • Determine the percentage of students fully paying fees vs defaulters.
  • Monitor fee collection trends over the year.
• Operational Efficiency:
  • Highlight overdue fees and identify patterns in defaulters.

---

Tools & Technologies

• SQL: To query and preprocess data.
• Power BI: To build dashboards and visualizations.
• MySQL Database: To store and retrieve school data.

---

Outcome

This project will provide a clear and interactive view of:

• Student statistics.
• Financial health related to fee collection.
• Insights into improving operational efficiency for the school.