Documentation

fsds/superset-dashboard-expansion-plan.md

Superset Dashboard Expansion Plan

Overview

Expand Superset from 4 BBU-focused dashboards to a comprehensive data insight portal with 4 new dashboards covering Catalog Analytics, Prism Identifier Analytics, Enhanced Shipment Lifecycle, and Device Health Monitoring.

Implementation Approach:

  • All dashboards implemented together
  • SQL views centralized in BBU component (following existing pattern)
  • Full API automation for datasets, charts, and dashboards

Current State

Dashboards Datasets Charts
4 (BBU Shipment Lifecycle, Data Quality & Processing, Data Platform Overview, BBU Operations) 27 41

Deliverables

New Dashboards (4)

  1. Catalog Analytics - Item types, status distribution, location heat maps, expiration tracking
  2. Prism Identifier Analytics - Identifier type usage, coverage by entity type, value patterns
  3. Enhanced Shipment Lifecycle - Daily volumes, stuck shipments, return rates, full status tracking
  4. Device Health Monitoring - Connection status, uptime history, disconnect patterns, problematic devices

New SQL Views (20)

All views will be created in the bbu schema with cross-schema joins, following existing pattern.

Implementation Stages

Stage 1: Create SQL Views Migration

File: engines/bbu/src/Acsis.Dynaplex.Engines.Bbu.Database/Migrations/YYYYMMDDHHMMSS_SupersetExpansionViews.cs

Catalog Views (5)

-- bbu.catalog_item_type_distribution
-- bbu.catalog_item_status_summary
-- bbu.catalog_item_location_heat
-- bbu.catalog_expiration_timeline
-- bbu.catalog_batch_lot_analysis

Prism Views (5)

-- bbu.identifier_type_usage
-- bbu.identifier_coverage_by_platform_type
-- bbu.identifier_daily_trend
-- bbu.identifier_value_patterns
-- bbu.multi_identifier_objects

Transport Views (5)

-- bbu.daily_shipment_volume
-- bbu.shipment_status_distribution
-- bbu.stuck_shipments
-- bbu.delivery_completion_trend
-- bbu.return_rate_analysis

IoT Device Health Views (5)

-- bbu.device_current_status
-- bbu.device_connection_history
-- bbu.device_daily_uptime
-- bbu.device_disconnect_patterns
-- bbu.problematic_devices

Stage 2: Create Superset Automation Script

File: projects/bbu-rfid/scripts/superset-expansion.py

Script will:

  1. Authenticate with Superset API
  2. Create datasets for each new view
  3. Configure column types and metrics
  4. Create charts with appropriate visualization types
  5. Assemble dashboards with layout
  6. Export for version control

Stage 3: Update Export Script

File: projects/bbu-rfid/resources/superset/export-dashboards.sh

Update to include new dashboards in export.

Stage 4: Documentation

Update any relevant docs to reflect new analytics capabilities.

Detailed View Specifications

Catalog Analytics Views

bbu.catalog_item_type_distribution

Joins: catalog.items -> catalog.item_types -> catalog.item_categories
Key Metrics: item_count, total_quantity, unique_locations, expired_count, expiring_soon_count
Dimensions: tenant_id, item_type_name, category_name

bbu.catalog_item_status_summary

Joins: catalog.items -> catalog.item_statuses -> catalog.item_types
Key Metrics: item_count, total_quantity, avg_quantity_per_item
Dimensions: status_name, item_type_name, category_name

bbu.catalog_item_location_heat

Joins: catalog.items -> spatial.locations -> catalog.item_types
Key Metrics: item_count, total_quantity, distinct_item_types, expired_items
Dimensions: location_name, location_full_name, item_type_name

bbu.catalog_expiration_timeline

Joins: catalog.items -> catalog.item_types
Key Metrics: item_count, total_quantity
Dimensions: expiration_week, item_type_name, urgency_band (EXPIRED/THIS_WEEK/THIS_MONTH/FUTURE)

bbu.catalog_batch_lot_analysis

Joins: catalog.items -> catalog.item_types
Key Metrics: item_count, total_quantity, distributed_locations
Dimensions: batch_lot_number, item_type_name, lot_status

Prism Identifier Analytics Views

bbu.identifier_type_usage

Joins: core.identifier_types -> prism.identifiers -> prism.passports
Key Metrics: usage_count, unique_objects
Dimensions: identifier_code, identifier_name, category, source_system

bbu.identifier_coverage_by_platform_type

Joins: prism.platform_types -> prism.passports -> prism.identifiers -> core.identifier_types
Key Metrics: total_objects, objects_with_identifier, coverage_percentage
Dimensions: platform_type_name, identifier_code

bbu.identifier_daily_trend

Joins: prism.identifiers -> core.identifier_types -> prism.passports -> prism.platform_types
Key Metrics: identifiers_created, unique_objects
Dimensions: created_day, identifier_code, platform_type_name

bbu.identifier_value_patterns

Joins: prism.identifiers -> core.identifier_types
Key Metrics: occurrence_count
Dimensions: identifier_code, value_length, value_pattern (NUMERIC/ALPHABETIC/ALPHANUMERIC/HEX/MIXED)

bbu.multi_identifier_objects

Joins: prism.passports -> prism.platform_types -> prism.identifiers -> core.identifier_types
Key Metrics: identifier_type_count, total_identifiers
Dimensions: platform_type_name, identifier_types_list

Enhanced Shipment Lifecycle Views

bbu.daily_shipment_volume

Table: transport.shipments -> transport.deliveries
Key Metrics: shipment_count, total_items, unique_destinations
Dimensions: activity_day, shipment_type, shipment_status

bbu.shipment_status_distribution

Table: transport.shipments
Key Metrics: shipment_count, total_items, avg_hours_in_status
Dimensions: status, shipment_type

bbu.stuck_shipments

Table: transport.shipments
Key Metrics: hours_in_status, item_quantity
Dimensions: tracking_number, status, health_status (DELAYED/STALE_CREATED/STUCK/NORMAL)

bbu.delivery_completion_trend

Joins: transport.deliveries -> transport.shipments
Key Metrics: delivery_count, items_delivered, avg_hours_from_shipment_to_delivery
Dimensions: completion_day, delivery_status

bbu.return_rate_analysis

Table: transport.shipments
Key Metrics: total_shipments, return_shipments, return_rate_percent
Dimensions: week_start

Device Health Monitoring Views

bbu.device_current_status

Joins: iot.devices -> spatial.locations -> sysenv.organizations
Key Metrics: hours_connected, hours_since_last_seen
Dimensions: device_name, serial_number, device_type, location_name, connection_status

bbu.device_connection_history

Joins: iot.device_connection_events -> iot.devices
Key Metrics: session_duration_hours
Dimensions: device_name, event_type, event_day, event_hour

bbu.device_daily_uptime

Derived from: iot.device_connection_events -> iot.devices
Key Metrics: connected_hours, uptime_percentage
Dimensions: device_name, session_day

bbu.device_disconnect_patterns

Joins: iot.device_connection_events -> iot.devices
Key Metrics: disconnect_count, reconnect_count
Dimensions: device_name, device_type, event_day, event_hour

bbu.problematic_devices

Derived from: iot.device_connection_events -> iot.devices
Key Metrics: total_disconnects, disconnects_24h, disconnects_7d
Dimensions: device_name, device_type, health_status (CRITICAL/WARNING/OFFLINE/HEALTHY)

Dashboard Chart Layouts

Catalog Analytics Dashboard

Row Charts
1 Item Types Treemap (treemap), Items by Category (pie)
2 Status Distribution (stacked bar), Location Heat (bar)
3 Expiration Timeline (area), Expired Items Alert (big number)
4 Batch/Lot Tracker (table)

Prism Identifier Analytics Dashboard

Row Charts
1 Identifier Types by Usage (horizontal bar), Source System Breakdown (pie)
2 Coverage Heatmap (heatmap)
3 Identifier Creation Trend (line), Value Pattern Distribution (sunburst)
4 Multi-ID Objects Histogram (histogram), Identifier Category Stats (big numbers)

Enhanced Shipment Lifecycle Dashboard

Row Charts
1 Daily Shipment Volume (time-series bar), Status Breakdown (donut)
2 Stuck Shipments Alert (big number), Stuck Shipments by Health (bar)
3 Delivery Trend (area), Avg Time to Delivery (line)
4 Weekly Return Rate (line+bar combo), Stuck Shipments Table (table)

Device Health Monitoring Dashboard

Row Charts
1 Device Status Overview (3 big numbers: Connected/Disconnected/Unknown)
2 Connected Devices List (table), Disconnected Devices Alert (table)
3 Daily Uptime Heatmap (heatmap)
4 Disconnect Patterns by Hour (heatmap), Problematic Devices (table)
5 Health Status Distribution (pie), Device Type Reliability (bar)

Critical Files

Existing patterns to follow:

  • engines/bbu/src/Acsis.Dynaplex.Engines.Bbu.Database/Analytics/SupersetAnalyticsViewsSql.cs
  • projects/bbu-rfid/resources/superset/docker-entrypoint.sh
  • projects/bbu-rfid/resources/superset/export-dashboards.sh

Schema reference files:

  • engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/CatalogDb.cs
  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/PrismDb.cs
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/TransportDb.cs
  • engines/iot/src/Acsis.Dynaplex.Engines.Iot.Database/IotDb.cs
  • engines/core-data/src/Acsis.Dynaplex.Engines.CoreData.Database/CoreDataDb.cs
  • engines/spatial/src/Acsis.Dynaplex.Engines.Spatial.Database/SpatialDb.cs

Entity files:

  • engines/iot/src/Acsis.Dynaplex.Engines.Iot.Database/DeviceConnectionEvent.cs
  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/Identifier.cs
  • engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/Item.cs

Success Criteria

  • All 20 SQL views created and migrated successfully
  • All 4 new dashboards visible in Superset
  • Charts render correctly with real data
  • Dashboards exported to projects/bbu-rfid/resources/superset/exports/
  • Dashboard filters (tenant_id, date range) work correctly