Reporting & Dashboards

This page documents the full reporting landscape across the Wasteology platform — from the client-facing Power BI reports that drive account-level billing visibility, to BINS invoice management, to internal ops dashboards.

---

Power BI — Client-Facing Reporting (Primary)

The Wasteology Data Platform Power BI report is the primary client-facing reporting tool. It is account-filtered and covers spend, weight, emissions, and service category breakdowns sourced from the dbt data warehouse — fct_billing for dollars, the four per-source weight facts (fct_weight_measured, fct_weight_scheduled, fct_weight_rebate, fct_weight_sensor) for tonnage/emissions, and dim_customer (the conformed location-grain customer dimension) for account/location filtering.

Weight has no single fact — it's four facts + a semantic rollup

There is no unioned weight surface in the warehouse. Tonnage is split across four per-source facts, and any cross-fact total (e.g. total_weight_tons) exists only at the MetricFlow semantic layer as a derived metric. Power BI DirectQuery issues SQL against relations, not MetricFlow metrics — so a cross-fact weight rollup forces a 4-way UNION of the weight facts per visual. Prefer Import mode (or the dbt Semantic Layer connector) for weight rollups; see Data Warehouse Architecture › Semantic layer.

Report structure — 8 pages:

Page	What It Shows
Home	Report landing / navigation
Spend Overview	Total spend + rebate by month; average spend per work day; spend by material (Trash / Recycling)
Spend Details	Service category breakdown — Front Load, Rolloff, Fuel Surcharge, Contamination, Overage, Damage fees; spend by month per category
Recycling	Landfill diversion rate; recycling tons by material; landfill vs. recycle weight trend by month; total recycle spend
Landfill	Landfill weight trend and totals
Carbon Overview	Total and monthly GHG emissions (CO₂e tons) — disposal + transportation
Carbon Details	Emissions breakdown by material, location, and category
Container Profile	Equipment and container-level service detail

Filter dimensions (cross-page):

• Parent Account → Account Name → Location Name
• Level 1 (account hierarchy)
• Date (Year + Month)

Key metrics surfaced:

• Total Spend, Total Rebate, Net Spend
• Spend per Work Day
• Landfill Diversion Rate (%)
• Total Weight (Tons) + GHG Emissions (CO₂e Tons)
• Service Category $$ breakdown

Data source

Power BI connects to the dbt warehouse (dbt_prod schema on Azure PostgreSQL). Contact the reporting team for workspace access or to add new accounts/locations to the report filters.

---

BINS Invoice Management App

BINS handles invoice ingestion and processing. It has admin surfaces for operational visibility but is not a reporting tool — there are no charts or aggregate views.

Feature	Where in BINS	What It Shows
Processing Management	ProcessingManagementPage.tsx	ReactFlow DAG: GCP pipeline stages with RUNNING / SUCCEEDED / FAILED per node
Invoice Grid	InvoiceGridPage.tsx	Filterable, sortable, paginated invoice table; XLSX export (up to 50K rows async via GCS)
Per-Invoice Status	ProcessingPage.tsx	4-stage audit trail: status_storage, status_doc_ai, status_database_insert, status_webservice_call

No chart library in BINS

BINS has no charting library. Invoice counts, dollar throughput, and status breakdowns require querying trashy.invoices directly or building against the BINS backend API.

BINS Reporting API Endpoints

Method	Endpoint	Description
POST	/invoices/query	Paginated filtered invoice list
POST	/invoices/export	XLSX export — sync (≤1K rows) or async via GCS (>1K rows)
GET	/admin/processing-status	All pipeline status records; filter with ?status=FAILED
GET	/admin/processing-status/{md5}	Single invoice pipeline status
POST	/admin/processing-status/{md5}/retry	Reset failed stage to PENDING

---

What Does Not Exist Yet

The following views are not currently built in any system:

• BINS invoice throughput in Power BI — invoice count / processing volume from BINS is not yet connected to the warehouse or Power BI
• Invoice status backlog chart — count by status (Unassigned / Needs Coordination / In Review / Complete) — requires BINS backend aggregate endpoint or direct DB query
• Complexity distribution — see Invoice Complexity Classification for implementation options
• Vendor-level invoice throughput — processing time or count grouped by hauler/vendor

What IS in Power BI

Dollar spend, weight, and emissions by account are already covered. The gap is specifically BINS operational throughput — invoice processing volume, queue depth, and status breakdown — which comes from trashy.invoices (Cloud SQL), not the dbt warehouse.

---

Quickest Paths to Close the Gap

Option A — BINS backend aggregate endpoint (live operational data)

Add GET /admin/invoice-stats to the BINS FastAPI backend (crud.py). SQLAlchemy + DB connection are already in place. Estimated effort: 1–2 hours.

-- Invoice count by status
SELECT s.name AS status, COUNT(*) AS count
FROM trashy.invoices i
JOIN trashy.invoice_status s ON i.status_id = s.id
GROUP BY s.name
ORDER BY count DESC;

-- Volume trend (last 30 days)
SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*) AS invoices
FROM trashy.invoices
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Option B — dbt reporting model (warehouse-integrated, Power BI ready)

Use the existing profitability surfaces

There is no mart_billing_summary model in the warehouse — that name was a proposal and was never built. The current warehouse-integrated billing surfaces are the two profitability facts in reporting/profitability/: fct_billing_service_month (grain: source_service_id × service_month) and fct_billing_service_sku_month (same dollars split by sku_type + market_designation). Point Power BI at these; only add a new reporting model if a required grain (e.g. status/backlog throughput) is genuinely missing. See Data Warehouse Architecture › Profitability mart.

If a billing throughput / status-backlog grain is needed (which the profitability facts do not carry), a new reporting/ model grouping fct_billing by month + status would make it queryable from Power BI alongside existing spend/weight metrics. Estimated effort: 2–4 hours (new dbt model + Power BI dataset refresh).

-- Proposed reporting model — NOT built today.
-- fct_billing columns: service_month, status, charge_amount, sales_amount.
SELECT
    service_month,
    status,
    COUNT(*)            AS billing_count,
    SUM(charge_amount)  AS total_charged,
    SUM(sales_amount)   AS total_sales
FROM {{ ref('fct_billing') }}
GROUP BY 1, 2

---

Data Sources Summary

View	Exists Today?	Best Data Source
Spend by account / month	✅ Power BI	fct_billing via dbt
Spend by service category	✅ Power BI	fct_billing via dbt
Weight + GHG emissions	✅ Power BI	Four per-source weight facts (fct_weight_measured / _scheduled / _rebate / _sensor); cross-fact totals via MetricFlow
Landfill diversion rate	✅ Power BI	Weight facts + dim_material via dbt
Profitability by service / month	✅ dbt	fct_billing_service_month, fct_billing_service_sku_month
Invoice status backlog (live)	❌ Not built	BINS trashy.invoices
Invoice volume over time	❌ Not built	BINS trashy.invoices.created_at
BINS throughput in Power BI	❌ Not built	New reporting/ dbt model + dataset refresh
Pipeline failure backlog	✅ Internal ops	Prefect via wdp-palantiri
Processing errors (per invoice)	✅ BINS admin	GET /admin/processing-status?status=FAILED