Skip to main content

​
Dashboard & Workbook Architecture

​
Core Principle: Analytics Data Mart for Reporting

IMPORTANT ARCHITECTURAL DECISION: All dashboards and workbooks MUST use the analytics_dm star schema (dimensional model), NOT transactional schemas.

​
Why analytics_dm?

The analytics_dm schema is a data mart specifically designed for reporting, business intelligence, and analytics:

​
✅ Benefits:

  • Optimized denormalized structure - Pre-joined data for fast dashboard queries
  • Pre-aggregated metrics - Daily/monthly aggregations computed in advance
  • Type 2 Slowly Changing Dimensions (SCDs) - Track historical changes for trend analysis
  • Query performance - Star schema design optimized for analytical queries
  • Data consistency - Single source of truth for reporting metrics

​
❌ Don’t Use Transactional Schemas:

  • investment.* - OLTP normalized structure, frequent writes, locking issues
  • validation.* - Operational validation data, not optimized for reporting
  • performance.* - Raw calculation tables, not aggregated
  • risk.* - Point-in-time risk data, not historical trends

​
Schema Structure

​
Fact Tables (Metrics & Measurements):

  • analytics_dm.fact_portfolio_valuation - Daily NAV and valuations
  • analytics_dm.fact_portfolio_valuation_monthly - Monthly aggregations
  • analytics_dm.fact_holdings - Position-level holdings
  • analytics_dm.fact_transactions - Trade activity
  • analytics_dm.fact_validation_results - Validation check results
  • analytics_dm.fact_performance - Performance metrics (returns, Sharpe, etc.)
  • analytics_dm.fact_fees - Fee breakdowns and TER

​
Dimension Tables (Descriptive Attributes):

  • analytics_dm.dim_date - Calendar dimension (business days, fiscal periods)
  • analytics_dm.dim_organization - Organization master (Type 2 SCD)
  • analytics_dm.dim_portfolio - Portfolio hierarchy (Type 2 SCD)
  • analytics_dm.dim_security - Security master (Type 2 SCD)
  • analytics_dm.dim_share_class - Share class attributes (Type 2 SCD)
  • analytics_dm.dim_validation_rule - Validation rule definitions

​
Dashboard Widget Configuration

​
Example Widget Config (Correct ✅):

{
  "id": "total-nav-widget",
  "type": "metric",
  "title": "Total NAV",
  "config": {
    "dataSource": "analytics_dm.fact_portfolio_valuation",
    "metric": "nav_total",
    "format": "currency"
  }
}

​
Example Widget Config (Wrong ❌):

{
  "id": "total-nav-widget",
  "type": "metric",
  "title": "Total NAV",
  "config": {
    "dataSource": "investment.portfolios",  // ❌ Transactional schema
    "metric": "total_nav",
    "format": "currency"
  }
}

​
Exceptions

Operational Workflows: The workflow.* schema is allowed for operational workflow tracking since it represents current state, not historical analytics:
  • workflow.workflow_instances - Current workflow execution state
  • workflow.workflow_steps - Active workflow steps

​
Data Flow

Transactional Schemas          Analytics Data Mart          Dashboards/Workbooks
  (OLTP - Writes)           (Star Schema - Reads)          (Optimized Queries)

investment.* ──┐
validation.* ──┼──> ETL Pipeline ──> analytics_dm.* ──> DashboardRenderer
performance.* ─┘                     (Fact + Dim)        (Supabase Client)
risk.* ────────┘

​
Implementation

​
Migration 045:

  • Defines all dashboard widgets with analytics_dm.* dataSources
  • Documents this architectural decision
  • Populates 7 production dashboards

​
DashboardRenderer Component:

  • Located: components/dashboard/DashboardRenderer.tsx
  • Fetches data from widget.config.dataSource (analytics_dm schema)
  • Uses Supabase client: supabase.from(tableName).select('*')

​
Supabase API Configuration:

  • Exposed schemas: analytics_dm, workflow, and transactional schemas
  • Extra search path includes analytics_dm for default resolution
  • All schemas accessible via schema.table notation

​
Best Practices

  1. Always query analytics_dm - Use fact and dimension tables for dashboards
  2. Use appropriate fact tables - Choose the right grain (daily, monthly, transaction-level)
  3. Join dimensions as needed - Leverage Type 2 SCDs for historical context
  4. Filter on date dimensions - Use dim_date for calendar-based filtering
  5. Pre-aggregate when possible - Use monthly fact tables for year-over-year trends

​
ETL & Data Pipeline

The analytics_dm schema is populated by ETL processes (not shown in this codebase):
  • Incremental daily loads from transactional schemas
  • Type 2 SCD updates for dimension changes
  • Pre-aggregation of monthly metrics
  • Data quality validation
Note: Direct writes to analytics_dm should only occur via ETL pipelines, not application code.
Last Updated: October 4, 2025 Migration: 045_populate_dashboard_widgets.sql Related Files:
  • supabase/migrations/031_analytics_star_schema.sql
  • components/dashboard/DashboardRenderer.tsx