Transforming Environmental Data: The DWER Power BI Solution

date

Jan 1, 2025

slug

ecu-capstone-project

status

Published

Overview

The Transforming Environment Data Solutions project revolutionized data management for Western Australia's DWER by implementing an automated Power BI solution for the Pilbara Environmental Offsets Fund. By converting manual processes to streamlined workflows, this award-winning initiative enabled conservation staff to focus on environmental outcomes rather than spreadsheets, while dramatically improving efficiency and decision-making capabilities.

1. Business Challenge

Problem Statement

The Pilbara Environmental Offsets Fund (PEOF) manages critical conservation projects across Western Australia but faced significant operational challenges:

Manual Process Inefficiency: Staff spent 48+ hours monthly on Excel data entry and processing, diverting resources from conservation work

Data Quality Concerns: 28% of reports contained errors requiring correction, undermining confidence in financial decision-making

Reporting Delays: Conservation decisions delayed by an average of 12 days due to fragmented systems and manual validation requirements

Stakeholder Frustration: Program managers, field teams, and leadership all experienced distinct pain points throughout the reporting workflow

PEOF Data Workflow: Stakeholder Friction Points

2. Solution Overview

Transformed PEOF's environmental data management by implementing an integrated Microsoft Power Platform solution that reduced reporting time by 95%, improved data accuracy to 99.5%, and enabled real-time decision-making through automated refreshes.

The diagram below illustrates the transformation from manual to automated workflow.

3. Project Management

We managed the project using a straightforward waterfall approach with five key phases, making it easy for everyone to understand where we were in the process:

Requirements & Discovery: We sat down with stakeholders to really understand their pain points and what they needed from the solution

Design & Prototype: We sketched out the solution and created interactive mockups for the team to test and validate

Implementation: This is where we built the data pipelines, validation rules, and interactive dashboards

Testing & Validation: We thoroughly checked everything to make sure all reports were accurate across different scenarios

Training & Handover: We created user-friendly documentation and ran hands-on sessions so the team could maintain the system themselves

We tracked timeline and team responsibilities with Microsoft Project, hitting all our milestones on time while keeping everything within budget.

4. Project Stages

4.1. Requirements Analysis

Challenge: Complex environmental data workflows with high error rates causing delayed conservation decisions.

Solution:

Interviewed stakeholders to identify specific pain points

Analyzed 18 months of historical data revealing 28% error rate

Documented 48+ hours of monthly manual processing

Created measurable success criteria for the solution

4.2. Data Transformation

Challenge: Transform inconsistent financial data from multiple Excel files with duplicates and formatting issues into reliable, structured information while enabling non-technical staff to maintain the system.

Raw Data Issues:

Multiple Excel files with inconsistent formats

Duplicate entries across files

Negative values shown in red without proper formatting for calculations

Inconsistent company and project codes

Manual validation required for each entry

Solution:

Implemented Power Query transformation pipeline with 15+ steps

Created standardized templates for consistent data entry

Built automated validation with visual indicators for data quality

Developed reusable transformation logic for future files

4.3. Visualization

Challenge: Translate complex environmental and financial data into actionable insights for diverse users.

Solution:

Designed hierarchical data models supporting multi-level analysis

Created role-specific dashboard views for different stakeholder needs

Implemented DAX measures for environmental impact metrics

Developed mobile-optimized views for field staff

Key Visualizations:

Financial tracking with waterfall charts and conditional formatting

Geospatial maps showing project distribution and impact

Executive KPI dashboard for at-a-glance decision support

Operational tables with status indicators

4.4. Integration & Knowledge Transfer

Challenge: Creating a sustainable solution requiring minimal IT support.

Solution:

Connected Power BI to SharePoint for live data updates

Configured 8x daily scheduled refresh for near real-time monitoring

Implemented secure data gateway connections

Created notification system for validation issues

Developed user documentation and conducted hands-on training

Results:

Reduced reporting time from 3 days to 15 minutes (95% reduction)

Enabled real-time monitoring of conservation projects

Established self-service reporting capabilities

Solution adopted as template for future environmental projects

5. Impact & Recognition

Key Results:

Reduced processing time by 95% (48+ hours → minutes)

Improved data accuracy to 99.5% through automated validation

Enabled real-time environmental decision-making

Recognized as departmental case study for digital transformation

Solution adopted as template for future DWER environmental projects

Client Feedback:

"Solution transformed how we manage conservation data, allowing staff to focus on environmental outcomes rather than spreadsheets."

— Micheal, Environmental Program Manager

Award-winning solution recognized with "Best Project" and "Raving Client" honors at the MIS Capstone Integration Project (2024). Our team was voted #1 by peers, ECU staff, and received the highest client satisfaction rating for our PEOF data transformation work.