TECHNICAL ARCHITECTURE

From Raw Data to Actionable Intelligence

A four-stage Business Intelligence pipeline. Every step documented, reproducible, and auditable.

Multi-Source Data Ingestion

Data Acquisition

Data was identified and ingested from four source categories: operational facility records documenting collection volumes and timelines; sorting facility logs capturing material categorization outcomes and accuracy rates; processing stage records tracking throughput, downtime events, and recovery yields; and administrative datasets covering workforce allocation and logistics. Ingestion was performed via API connections and structured database queries, with integrity validation applied at point of entry.

API Connections Database Queries Integrity Validation Multi-Source Reproducible

Data Cleaning & Feature Engineering

Power Query Transformation

All transformation work was performed in Microsoft Power Query using its declarative M language, ensuring every step is auditable and reproducible. Operations included: removal of duplicates, standardization of categorical labels, median imputation of missing values, and UTC normalization of timestamps. Derived features were engineered: collection efficiency ratios, sorting accuracy percentages, and downtime duration aggregates.

Deduplication Median Imputation UTC Normalization Feature Engineering M Language

Dimensional Data Architecture

Star Schema Data Model

The analytical model uses a star schema: a central fact table (ProcessingEvents) capturing transactional records linked to five dimension tables. This design prioritizes query performance and analytical flexibility — enabling aggregation across time, facility, material, operator, and stage dimensions simultaneously.

Interactive Data Schema Model Explorer

ProcessingEvents (Fact Table)

Central fact table storing individual operational transaction records: processed waste weights, sorting contamination outcomes, and processing downtime logs.

Fields: EventKey (PK), TimeKey (FK), FacilityKey (FK), MaterialKey (FK), OperatorKey (FK), VolumeTons, DowntimeHours, ContaminationPct

Star Schema 1 Fact Table 5 Dimensions Power BI Model DAX Ready

Interactive Dashboard Architecture

Power BI Visualization

The visualization layer follows a structured data storytelling methodology. Three dashboard pages cover executive overview, operational detail, and individual performance tracking. Cross-page filtering via synchronized slicers allows analysts to isolate specific facilities, time periods, and material types. Drill-through provides granular diagnostic capability.

Power BI Desktop 3 Dashboard Pages DAX Measures Cross-Page Filters Drill-Through

Advanced DAX Measures

A suite of DAX expressions was developed to compute the core KPIs and enable temporal comparisons.

Collection Efficiency (%)

Collection Efficiency = 
DIVIDE(
    SUM(ProcessingEvents[VolumeTons]), 
    CALCULATE(
        SUM(DimFacility[DailyCapacityTons]), 
        USERELATIONSHIP(ProcessingEvents[TimeKey], DimTime[TimeKey])
    )
)

Download the .pbix file to explore all measures