fsds/bbu-analytics-dashboards.md

BBU Analytics Dashboards via Superset API

Executive Summary

Create comprehensive Superset dashboards via REST API showcasing:

Data Platform Overview - Volume metrics across all systems
Shipment Lifecycle Tracking - Camera/RFID/basket correlation (the exciting new feature!)
Operations Dashboard - Daily throughput and processing stats
Data Quality Metrics - Processing success rates and dead letter analysis

Current State

Existing Assets

Database ID: 1 (Acsis PostgreSQL)
Datasets: 8 BBU views (created 30 days ago)
Charts: 4 (not assigned to any dashboard)
Dashboards: 0

Data Volumes (Live)

Entity	Count
Passports	1,506,998
Identifiers	3,644,510
Catalog Items	862,591
Item Types	554
Stacks (from Oracle)	151,679
Bakery Baskets	25,853
Shipments	2,848
Deliveries	40,757
Shipment Allocations	973,362
RFID Tag Reads	70,688
Camera Reads	36,693
Basket Correlations	1,997

Shipment Lifecycle Metrics (New Feature!)

Metric	Value
Stacks with basket correlation	127
Unique baskets linked	1,069
Total correlations	1,997
Avg baskets per stack	12.0
Match rate (TXT format)	~29%

API Configuration

Base URL: http://localhost:<port>  (check Aspire Dashboard for current port)
Auth: Bearer JWT token
Headers required:
  - Authorization: Bearer <token>
  - X-CSRFToken: <csrf_token>
  - Content-Type: application/json

Note: The Superset port is dynamically assigned by Aspire at startup. To find the current port:

Open the Aspire Dashboard
Find the "superset" service
Copy the endpoint URL

Dashboard 1: Data Platform Overview

Purpose: Executive view of total data volumes - impressive numbers!

New Datasets (SQL-based)

Dataset: platform_metrics_summary

SELECT
    'Passports' as entity, COUNT(*)::bigint as count, 'prism' as component FROM prism.passports
UNION ALL SELECT 'Identifiers', COUNT(*), 'prism' FROM prism.identifiers
UNION ALL SELECT 'Catalog Items', COUNT(*), 'catalog' FROM catalog.items
UNION ALL SELECT 'Item Types', COUNT(*), 'catalog' FROM catalog.item_types
UNION ALL SELECT 'Shipments', COUNT(*), 'transport' FROM transport.shipments
UNION ALL SELECT 'Deliveries', COUNT(*), 'transport' FROM transport.deliveries
UNION ALL SELECT 'Allocations', COUNT(*), 'transport' FROM transport.shipment_item_allocations
UNION ALL SELECT 'RFID Reads', COUNT(*), 'iot' FROM iot.zb_tag_reads
UNION ALL SELECT 'Camera Reads', COUNT(*), 'iot' FROM iot.dl_camera_reads
UNION ALL SELECT 'IoT Devices', COUNT(*), 'iot' FROM iot.devices

Dataset: item_type_distribution

SELECT
    it.name as item_type,
    COUNT(i.id) as item_count
FROM catalog.items i
JOIN catalog.item_types it ON it.id = i.item_type_id
GROUP BY it.name
ORDER BY item_count DESC

Charts for Dashboard 1

Chart Name	Type	Dataset	Metric
Total Passports	big_number_total	platform_metrics	count WHERE entity='Passports'
Total Identifiers	big_number_total	platform_metrics	count WHERE entity='Identifiers'
Total Items	big_number_total	platform_metrics	count WHERE entity='Catalog Items'
Data by Component	pie	platform_metrics	SUM(count) GROUP BY component
Item Types Top 20	bar	item_type_distribution	item_count

Dashboard 2: Shipment Lifecycle Tracking (STAR DASHBOARD)

Purpose: Showcase the new camera→RFID→basket correlation feature

New Datasets

Dataset: lifecycle_metrics_summary

SELECT
    'Stacks Tracked' as metric,
    COUNT(DISTINCT matched_stack_id)::bigint as value
FROM bbu.camera_read_processing
WHERE processing_status = 'Matched'
UNION ALL
SELECT 'Shipments Tracked', COUNT(DISTINCT matched_shipment_id)
FROM bbu.camera_read_processing
WHERE matched_shipment_id IS NOT NULL
UNION ALL
SELECT 'Baskets Correlated', COUNT(*)
FROM bbu.camera_read_baskets
UNION ALL
SELECT 'Camera Reads Processed', COUNT(*)
FROM iot.dl_camera_reads
UNION ALL
SELECT 'RFID Reads Captured', COUNT(*)
FROM iot.zb_tag_reads

Dataset: camera_processing_status

SELECT
    processing_status,
    COUNT(*) as count,
    COUNT(DISTINCT matched_stack_id) as unique_stacks,
    COUNT(DISTINCT matched_shipment_id) as unique_shipments
FROM bbu.camera_read_processing
GROUP BY processing_status

Dataset: hourly_processing_trend

SELECT
    DATE_TRUNC('hour', dcr.event_timestamp) as hour,
    dcr.file_format,
    COUNT(*) as camera_reads,
    COUNT(CASE WHEN crp.processing_status = 'Matched' THEN 1 END) as matched,
    COUNT(CASE WHEN crp.processing_status = 'DeadLettered' THEN 1 END) as dead_lettered,
    ROUND(100.0 * COUNT(CASE WHEN crp.processing_status = 'Matched' THEN 1 END) / NULLIF(COUNT(*), 0), 1) as match_rate
FROM iot.dl_camera_reads dcr
LEFT JOIN bbu.camera_read_processing crp ON crp.dl_camera_read_id = dcr.id
WHERE dcr.event_timestamp >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE_TRUNC('hour', dcr.event_timestamp), dcr.file_format
ORDER BY hour

Dataset: basket_correlation_detail

SELECT
    crp.id,
    dcr.decoded_value as lpn,
    dcr.event_timestamp as camera_time,
    crp.processing_status,
    crp.matched_stack_id,
    crp.matched_shipment_id,
    COUNT(crb.id) as basket_count
FROM bbu.camera_read_processing crp
JOIN iot.dl_camera_reads dcr ON dcr.id = crp.dl_camera_read_id
LEFT JOIN bbu.camera_read_baskets crb ON crb.camera_read_id = crp.dl_camera_read_id
WHERE crp.processing_status = 'Matched'
GROUP BY crp.id, dcr.decoded_value, dcr.event_timestamp, crp.processing_status, crp.matched_stack_id, crp.matched_shipment_id
ORDER BY dcr.event_timestamp DESC

Charts for Dashboard 2

Chart Name	Type	Dataset	Description
Stacks Tracked	big_number_total	lifecycle_metrics	The headline number
Baskets Correlated	big_number_total	lifecycle_metrics	Shows IoT value
Match Rate	big_number_total	hourly_processing	Today's rate
Processing Status	pie	camera_processing_status	Matched vs DeadLettered
Match Rate Trend	echarts_timeseries_line	hourly_processing_trend	Hour by hour
Format Comparison	echarts_timeseries_bar	hourly_processing_trend	TXT vs XML success
Basket Distribution	histogram	basket_correlation_detail	Baskets per stack
Recent Correlations	table	basket_correlation_detail	Live feed view

Dashboard 3: Operations Dashboard

Purpose: Daily operational metrics for monitoring

Datasets (Use existing views)

bbu_oblpn_status_daily (existing, ID: 5)
bbu_oblpn_load_progress (existing, ID: 7)
bbu_oblpn_location_backlog (existing, ID: 8)

New Dataset: rfid_daily_trend

SELECT
    DATE(event_timestamp) as read_date,
    COUNT(*) as total_reads,
    COUNT(DISTINCT epc) as unique_tags,
    COUNT(DISTINCT device_id) as active_readers
FROM iot.zb_tag_reads
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(event_timestamp)
ORDER BY read_date

Charts for Dashboard 3

Chart Name	Type	Dataset	Description
Daily Throughput	echarts_area	status_daily	Existing chart (ID: 4)
Load Progress	pivot_table	load_progress	Existing chart (ID: 6)
Location Backlog	bar	location_backlog	Existing chart (ID: 7)
RFID Daily Volume	echarts_timeseries_bar	rfid_daily_trend	New
Active Readers	big_number_total	rfid_daily_trend	Device count

Dashboard 4: Data Quality & Processing

Purpose: Engineering metrics for data quality monitoring

New Datasets

Dataset: dead_letter_analysis

SELECT
    processor_name,
    CASE
        WHEN last_error LIKE '%Camera decode did not yield%' THEN 'Empty Barcode'
        WHEN last_error LIKE '%No stack item%' THEN 'No Stack in Oracle'
        WHEN last_error LIKE '%basket%' OR last_error LIKE '%RFID%' THEN 'No RFID Match'
        ELSE 'Other'
    END as failure_reason,
    COUNT(*) as count,
    MAX(last_failure_utc) as last_occurrence
FROM bbu.processing_dead_letters
WHERE is_dead_lettered = true
GROUP BY processor_name,
    CASE
        WHEN last_error LIKE '%Camera decode did not yield%' THEN 'Empty Barcode'
        WHEN last_error LIKE '%No stack item%' THEN 'No Stack in Oracle'
        WHEN last_error LIKE '%basket%' OR last_error LIKE '%RFID%' THEN 'No RFID Match'
        ELSE 'Other'
    END

Dataset: format_comparison

SELECT
    file_format,
    COUNT(*) as total,
    COUNT(CASE WHEN crp.processing_status = 'Matched' THEN 1 END) as matched,
    ROUND(100.0 * COUNT(CASE WHEN crp.processing_status = 'Matched' THEN 1 END) / NULLIF(COUNT(*), 0), 1) as match_pct
FROM iot.dl_camera_reads dcr
LEFT JOIN bbu.camera_read_processing crp ON crp.dl_camera_read_id = dcr.id
WHERE dcr.event_timestamp >= CURRENT_DATE
GROUP BY file_format

Charts for Dashboard 4

Chart Name	Type	Dataset	Description
Dead Letter Breakdown	pie	dead_letter_analysis	By failure reason
Processing Failures	bar	dead_letter_analysis	Count by reason
TXT vs XML Success	bar	format_comparison	Format comparison
Dead Letter Trend	timeseries	dead_letters (with time)	Failures over time

Implementation Steps

Step 1: Get Fresh Auth Tokens

# Get JWT
curl -X POST http://localhost:8088/api/v1/security/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin","password":"admin","provider":"db"}'

# Get CSRF
curl http://localhost:8088/api/v1/security/csrf_token/ \
  -H "Authorization: Bearer $TOKEN"

Step 2: Create SQL Datasets (Virtual Tables)

For each SQL dataset above:

curl -X POST http://localhost:8088/api/v1/dataset/ \
  -H "Authorization: Bearer $TOKEN" \
  -H "X-CSRFToken: $CSRF" \
  -H "Content-Type: application/json" \
  -d '{
    "database": 1,
    "schema": "bbu",
    "table_name": "lifecycle_metrics_summary",
    "sql": "SELECT ... (the SQL query)",
    "owners": [1]
  }'

Step 3: Create Charts

For each chart:

curl -X POST http://localhost:8088/api/v1/chart/ \
  -H "Authorization: Bearer $TOKEN" \
  -H "X-CSRFToken: $CSRF" \
  -H "Content-Type: application/json" \
  -d '{
    "slice_name": "Stacks Tracked",
    "viz_type": "big_number_total",
    "datasource_id": <dataset_id>,
    "datasource_type": "table",
    "params": "{...chart params...}",
    "owners": [1]
  }'

Step 4: Create Dashboards

curl -X POST http://localhost:8088/api/v1/dashboard/ \
  -H "Authorization: Bearer $TOKEN" \
  -H "X-CSRFToken: $CSRF" \
  -H "Content-Type: application/json" \
  -d '{
    "dashboard_title": "BBU Shipment Lifecycle",
    "published": true,
    "owners": [1]
  }'

Step 5: Add Charts to Dashboard

Update dashboard with position_json containing chart layouts.

Execution Order

Phase 1: Create all SQL datasets (6 new datasets)
Phase 2: Create Dashboard 2 charts (the star dashboard)
Phase 3: Create Dashboard 2 and add charts
Phase 4: Create remaining dashboards and charts
Phase 5: Review and iterate based on feedback

Success Metrics

After implementation:

4 dashboards created
~20 charts total
~10 new datasets
All dashboards accessible at http://localhost:8088/dashboard/list/

Key Talking Points for Stakeholders

Scale: "We're tracking 1.5M+ passports, 3.6M identifiers, 860K+ catalog items"
Integration: "Real-time RFID and camera data correlation - 70K+ tag reads, 36K+ camera events"
Visibility: "For the first time, we can see which baskets are on which stacks - 127 stacks with 1,997 basket correlations"
Quality: "29% match rate on the new format vs 1% on the old - 26x improvement"
Volume: "Processing ~18K RFID reads per day, correlated with camera events in real-time"

Documentation

BBU Analytics Dashboards via Superset API

Executive Summary

Current State

Existing Assets

Data Volumes (Live)

Shipment Lifecycle Metrics (New Feature!)

API Configuration

Dashboard 1: Data Platform Overview

New Datasets (SQL-based)

Charts for Dashboard 1

Dashboard 2: Shipment Lifecycle Tracking (STAR DASHBOARD)

New Datasets

Charts for Dashboard 2

Dashboard 3: Operations Dashboard

Datasets (Use existing views)

New Dataset: rfid_daily_trend

Charts for Dashboard 3

Dashboard 4: Data Quality & Processing

New Datasets

Charts for Dashboard 4

Implementation Steps

Step 1: Get Fresh Auth Tokens

Step 2: Create SQL Datasets (Virtual Tables)

Step 3: Create Charts

Step 4: Create Dashboards

Step 5: Add Charts to Dashboard

Execution Order

Success Metrics

Key Talking Points for Stakeholders