Evolvemeter Umvuzo Secondary School Desktop Power BI Report

Requirement Document for Evolvemeter Umvuzo Secondary School Desktop Power BI Report

Project Overview

This document outlines the requirements and development methodology for the Power BI report designed for Evolvemeter Umvuzo Secondary School Desktop. The report aims to analyze and visualize school needs, teacher needs, and student needs, allowing stakeholders to make informed decisions.

Report Objectives

Provide an overview of school needs, teacher needs, and kids’ needs.
Enable filtering based on year and quarter selection.
Display percentage distributions for investment and resource allocation.
Support interactive data exploration and decision-making.
Ensure seamless integration with underlying data sources.

Data Sources & Integration

SQL Database
- Stores structured data related to schools, teachers, and students.
- Contains historical and current investment records.
- Includes master data for institutions and stakeholders.
Power Query
- Extracts data from the SQL database.
- Cleans and transforms data for analysis.
- Performs necessary joins and aggregations.
DAX (Data Analysis Expressions)
- Creates calculated measures and columns.
- Enables percentage distribution calculations.
- Implements advanced filtering and time intelligence.

Report Pages & Functionalities

1. Main Dashboard

Overview of all school needs.
Percentage breakdown of investments and resources.
Filtering options for Year & Quarter.
Email and contact details for communication.

2. School Needs Report

Visual representation of school needs.
Dynamic KPI indicators.
Comparative analysis by time period.

3. Teachers’ Needs Report

Breakdown of resources required for teachers.
Investment analysis for teacher development programs.

4. Kids’ Needs Report

Visualization of students’ requirements.
Investment tracking for student programs.

5. Investment Analysis

Data-driven insights on investment allocation.
Comparative analysis of fund distribution.

Evolvemeter Umvuzo Secondary School Desktop Download

Project Completion Method

1. Data Extraction – SQL Queries

The data for this report is assumed to be stored in a relational database (SQL Server, MySQL, or PostgreSQL). The following SQL queries help extract relevant data.

1.1 Extracting School Needs Data

sqlCopyEditSELECT 
    SchoolID, 
    SchoolName, 
    NeedCategory, 
    NeedDescription, 
    NeedPercentage, 
    Year, 
    Quarter
FROM SchoolNeeds
WHERE Year = 2024;

1.2 Extracting Teacher Needs Data

sqlCopyEditSELECT 
    TeacherID, 
    TeacherName, 
    Subject, 
    NeedCategory, 
    NeedPercentage, 
    Year, 
    Quarter
FROM TeacherNeeds
WHERE Year = 2024;

1.3 Extracting Student (Kids) Needs Data

sqlCopyEditSELECT 
    StudentID, 
    StudentName, 
    Grade, 
    NeedCategory, 
    NeedPercentage, 
    Year, 
    Quarter
FROM StudentNeeds
WHERE Year = 2024;

1.4 Extracting Evolvemeter Investment Data

sqlCopyEditSELECT 
    InvestmentID, 
    InvestmentType, 
    AmountInvested, 
    AllocationCategory, 
    Year, 
    Quarter
FROM Investments
WHERE Year = 2024;

2. Data Transformation – Power Query (M Language)

Once the data is extracted via SQL, it is loaded into Power BI using Power Query for transformation. The key transformations include:

2.1 Data Cleaning and Formatting

Remove duplicates.
Standardize column names.
Convert text columns to proper case.
Replace null values with appropriate defaults.

mCopyEdit// Example: Removing Nulls and Formatting Columns
let
    Source = Sql.Database("ServerName", "DatabaseName", [Query="SELECT * FROM SchoolNeeds"]),
    RemoveNulls = Table.ReplaceValue(Source, null, "Not Available", Replacer.ReplaceText, {"NeedDescription"}),
    ChangeColumnTypes = Table.TransformColumnTypes(RemoveNulls,{{"NeedPercentage", Percentage.Type}})
in
    ChangeColumnTypes

2.2 Merging Data Tables

Merge SchoolNeeds, TeacherNeeds, and StudentNeeds into a single Needs Overview Table.
Merge Investments with Needs Overview to map allocated funds.

mCopyEdit// Merging TeacherNeeds with SchoolNeeds
let
    Teachers = Sql.Database("ServerName", "DatabaseName", [Query="SELECT * FROM TeacherNeeds"]),
    School = Sql.Database("ServerName", "DatabaseName", [Query="SELECT * FROM SchoolNeeds"]),
    MergedTable = Table.NestedJoin(Teachers, "SchoolID", School, "SchoolID", "MergedData", JoinKind.Inner)
in
    MergedTable

3. Data Modeling – DAX Calculations

Power BI’s Data Analysis Expressions (DAX) is used for aggregations, measures, and custom calculations.

3.1 Total Needs Fulfilled

DAXCopyEditTotal_Needs_Fulfilled = SUM(SchoolNeeds[NeedPercentage])

3.2 Percentage of Needs Met

DAXCopyEditNeeds_Met_Percentage = 
DIVIDE(
    SUM(SchoolNeeds[NeedPercentage]), 
    COUNTROWS(SchoolNeeds), 
    0
) * 100

3.3 Total Investment Allocated

DAXCopyEditTotal_Investment = SUM(Investments[AmountInvested])

3.4 Investment Allocation by Category

DAXCopyEditInvestment_Per_Category = 
CALCULATE(
    SUM(Investments[AmountInvested]), 
    ALLEXCEPT(Investments, Investments[AllocationCategory])
)

3.5 Yearly & Quarterly Filter Selection

DAXCopyEditSelectedYear = SELECTEDVALUE(SchoolNeeds[Year])
SelectedQuarter = SELECTEDVALUE(SchoolNeeds[Quarter])

4. Visualization & Report Development

4.1 Dashboard Design in Power BI

Main Dashboard:
- KPIs: Total Needs Fulfilled, Investment Allocation
- Filters: Year, Quarter
School Needs Report:
- Bar Chart: NeedCategory vs. Percentage Fulfilled
Teacher Needs Report:
- Table: TeacherName | Subject | Need Percentage
Kids Needs Report:
- Pie Chart: NeedCategory Distribution
Investment Analysis Report:
- Stacked Column Chart: Investment Allocation by Category

5. Deployment & Maintenance

Publish Power BI Report to Power BI Service.
Schedule Data Refresh (daily/weekly).
User Access Management: Restrict access to authorized users.
Performance Optimization:
- Optimize DAX measures.
- Use Aggregations for large datasets.
- Reduce the use of high-cardinality columns.

6. Conclusion

This methodology ensures the successful completion of the Evolvemeter – Umvuzo Secondary School Power BI Report by:

Deploying and maintaining the report for continuous insights.
Extracting structured data using SQL Queries.
Cleaning and merging data using Power Query (M Language).
Creating DAX measures for real-time calculations.
Designing an interactive Power BI dashboard.

sample data files for the Power BI report:

School Needs Data – Contains investment percentages for different needs like infrastructure, supplies, and technology.
Teachers Needs Data – Includes data on training, materials, and technology investments for teachers.
Kids Needs Data – Covers investments in books, food, and sports equipment for students.

You can download the CSV files

school_needs_data Download

teachers_needs_data Download

kids_needs_data Download