superset-dashboard-automation

name	superset-dashboard-automation
description	Apache Superset dashboard automation for Finance SSC, BIR compliance, and operational analytics. Auto-generate dashboards, datasets, and charts. Tableau/Power BI alternative saving $8,400/year in licenses.

Superset Dashboard Developer Agent (Tableau Alternative)

Transform Claude into an Apache Superset expert that creates enterprise BI dashboards automatically with production-grade DigitalOcean deployment.

What This Skill Does

Auto-generate dashboards - BIR compliance, finance SSC, operational analytics
Create datasets - Optimized SQL from Supabase/Odoo
Build charts - 20+ visualization types with best practices
Deploy to DigitalOcean - Production-ready App Platform configuration
Apply templates - Pre-built dashboards for common use cases
Schedule refreshes - Automated data updates

Annual Savings: $8,400 (vs Tableau 10-user license)

Quick Start

When asked to create dashboards:

1. Deploy Superset: Choose deployment method
   • App Platform (easier, $27/month): Zero maintenance
   • Droplets (flexible, $28/month): Full control
2. Initialize database: Via console or automated script
3. Create dataset: Write optimized SQL query
4. Build charts: Select appropriate visualization
5. Assemble dashboard: Layout charts with filters
6. Apply template: Use pre-built for common scenarios

Deployment Quick Links

• App Platform Guide - Managed, zero maintenance
• Droplet Guide - Self-hosted, full control
• Comparison Guide - Which to choose?

DigitalOcean Deployment Options

Option 1: App Platform (Managed, Zero Maintenance)

Best for: Teams without DevOps, want zero maintenance

Complete guide

Corrected App Spec (DO Best Practices)

name: superset
region: sgp  # Or your preferred region

services:
  - name: superset
    image:
      registry_type: DOCKER_HUB
      registry: apache
      repository: superset
      tag: "3.1.0"  # Pin to stable version (not 'latest')
    
    instance_size_slug: professional-xs  # Minimum 1GB RAM
    instance_count: 1
    http_port: 8088
    
    envs:
      # Required: Database connection
      - key: DATABASE_URL
        value: postgresql://postgres:[e-mail]:5432/postgres
        type: SECRET
        scope: RUN_TIME
      
      # Required: Secret key for sessions
      - key: SUPERSET_SECRET_KEY
        value: your-secret-key-here
        type: SECRET
        scope: RUN_TIME
      
      # Required: Redis for caching
      - key: REDIS_URL
        value: redis://redis-host:6379/0
        type: SECRET
        scope: RUN_TIME
      
      # Production settings
      - key: SUPERSET_ENV
        value: production
        scope: RUN_TIME
      
      - key: ENABLE_PROXY_FIX
        value: 'True'
        scope: RUN_TIME
      
      - key: ALLOWED_HOSTS
        value: "*"  # Change to your domain after setup
        scope: RUN_TIME
      
      - key: SUPERSET_LOAD_EXAMPLES
        value: 'False'
        scope: RUN_TIME
      
      - key: WTF_CSRF_ENABLED
        value: 'True'
        scope: RUN_TIME
    
    # CRITICAL: Persist Superset home directory
    volumes:
      - name: superset-data
        mount_path: /app/superset_home
    
    health_check:
      initial_delay_seconds: 180
      period_seconds: 30
      timeout_seconds: 10
      failure_threshold: 3
      http_path: /health

domains:
  - domain: superset.yourdomain.com
    type: PRIMARY

Why Volume Persistence is Critical

Without volumes, you'll lose:

• ✗ User accounts and permissions
• ✗ Custom dashboards and charts
• ✗ Database connections
• ✗ Uploaded files and assets
• ✗ All configuration on every deploy

With volumes (/app/superset_home), you keep:

• ✓ All user data
• ✓ Dashboard configurations
• ✓ Database connections
• ✓ Upload persistence
• ✓ Session state

Instance Sizing Guide

Size	RAM	vCPU	Use Case	Monthly Cost
basic-xxs	512MB	Shared	❌ Too small	$5
professional-xs	1GB	1 vCPU	✅ Minimum for production	$12
professional-s	2GB	1 vCPU	✅ Recommended for 10-20 users	$25
professional-m	4GB	2 vCPU	✅ For 50+ users	$50

Manual Initialization (DO-Aligned Approach)

❌ DO NOT use POST_DEPLOY or PRE_DEPLOY jobs - they hang because doctl apps console requires interactive TTY.

✅ DO THIS instead:

# Step 1: Deploy app without init job
doctl apps update <app-id> --spec superset.yaml

# Step 2: Wait for deployment
doctl apps get <app-id> --wait

# Step 3: Open interactive console
doctl apps console <app-id> superset

# Step 4: Inside console, run these commands:
superset db upgrade
superset fab create-admin \
  --username admin \
  --firstname Jake \
  --lastname Tolentino \
  --email [e-mail] \
  --password YourSecurePassword
superset init
exit

Redis Configuration

Add managed Redis to your app:

databases:
  - name: superset-redis
    engine: REDIS
    production: true
    version: "7"

Then reference it:

- key: REDIS_URL
  value: ${superset-redis.REDIS_URL}
  scope: RUN_TIME

Option 2: Droplets (Self-Hosted, Full Control)

Best for: DevOps teams, cost optimization, need root access

Quick Start (Automated):

# SSH into your Droplet
ssh root@YOUR_DROPLET_IP

# Run automated installer
curl -fsSL https://raw.githubusercontent.com/your-repo/setup.sh | bash

# Or manual installation:
wget https://example.com/droplet-setup.sh
chmod +x droplet-setup.sh
./droplet-setup.sh

What's included:

• ✅ Docker + Docker Compose
• ✅ Superset 3.1.0
• ✅ PostgreSQL 15
• ✅ Redis 7
• ✅ Nginx reverse proxy
• ✅ Automated backups
• ✅ UFW firewall
• ✅ Health checks

Manual Installation:

See complete guide: Droplet Deployment Guide

Docker Compose Stack:

version: '3.8'
services:
  superset:
    image: apache/superset:3.1.0
    ports:
      - "8088:8088"
    environment:
      DATABASE_HOST: postgres
      REDIS_HOST: redis
      SUPERSET_SECRET_KEY: ${SECRET_KEY}
    volumes:
      - superset-home:/app/superset_home
  
  postgres:
    image: postgres:15-alpine
    volumes:
      - postgres-data:/var/lib/postgresql/data
  
  redis:
    image: redis:7-alpine
    volumes:
      - redis-data:/data

Cost Comparison:

Deployment	Monthly Cost	Maintenance	Best For
App Platform	$27	0 hours	Small teams, no DevOps
Droplets	$28	2-3 hours	Full control, custom setup

See: App Platform vs Droplets Comparison

Core Workflows

Workflow 1: BIR Compliance Dashboard

User asks: "Create BIR filing status dashboard"

Steps:
1. Deploy Superset to DO App Platform (if not done)
2. Initialize via console
3. Connect to Supabase database
4. Create dataset: bir_filing_summary
5. Build charts:
   - 1601-C monthly trends (timeseries)
   - 2550Q status by agency (pivot table)
   - ATP expiry calendar (table)
   - Tax payable big number (KPI)
6. Apply filters: agency, period, status
7. Set refresh schedule: daily 6am

Result: Live BIR compliance dashboard

See examples/bir-dashboard.md.

Workflow 2: Create Optimized Dataset

User asks: "Create dataset for expense analytics"

Steps:
1. Write SQL query joining Odoo tables
2. Add calculated columns
3. Set proper column types
4. Configure cache timeout
5. Add metrics (SUM, AVG, COUNT)
6. Define dimensions (groupby fields)
7. Test query performance

Result: Performant dataset for charts

See examples/create-dataset.md.

Workflow 3: Chart Best Practices

User asks: "Show AP aging in Superset"

Steps:
1. Select chart type: Pivot Table v2
2. Configure:
   - Rows: Vendor name
   - Columns: Aging buckets
   - Metrics: Sum(amount)
   - Color scale: Red (overdue) to Green (current)
3. Add conditional formatting
4. Set refresh interval

Result: Interactive AP aging analysis

See examples/chart-selection.md.

Pre-Built Templates

Template 1: BIR Monthly Filing

{
  "dashboard_title": "BIR Compliance Tracker",
  "charts": [
    {
      "title": "1601-C Withholding Summary",
      "viz_type": "pivot_table_v2",
      "dataset": "bir_withholding_monthly"
    },
    {
      "title": "2550Q VAT Status",
      "viz_type": "big_number_total",
      "dataset": "bir_vat_quarterly"
    },
    {
      "title": "Filing Deadline Calendar",
      "viz_type": "echarts_timeseries",
      "dataset": "bir_filing_schedule"
    }
  ],
  "filters": ["agency", "period", "status"]
}

Template 2: Finance SSC Executive

{
  "dashboard_title": "Finance SSC Overview",
  "charts": [
    {
      "title": "Cash Position",
      "viz_type": "big_number_total",
      "comparison": "month_over_month"
    },
    {
      "title": "AP Aging",
      "viz_type": "echarts_bar"
    },
    {
      "title": "Month-End Progress",
      "viz_type": "echarts_gauge"
    }
  ]
}

Template 3: Expense Analytics

{
  "dashboard_title": "Travel & Expense Analytics",
  "charts": [
    {
      "title": "Expense by Category",
      "viz_type": "echarts_pie"
    },
    {
      "title": "Monthly Trend",
      "viz_type": "echarts_timeseries"
    },
    {
      "title": "Top Spenders",
      "viz_type": "table"
    }
  ]
}

Dataset SQL Patterns

Pattern 1: Supabase + Odoo Integration

-- Materialized view for Superset
CREATE MATERIALIZED VIEW superset_odoo_financials AS
SELECT 
  a.code as agency_code,
  acc.code as account_code,
  acc.name as account_name,
  SUM(l.debit) as total_debit,
  SUM(l.credit) as total_credit,
  m.date as transaction_date
FROM odoo_account_move_line l
JOIN odoo_account_account acc ON l.account_id = acc.id
JOIN odoo_account_move m ON l.move_id = m.id
JOIN agencies a ON m.agency_id = a.id
WHERE m.state = 'posted'
GROUP BY 1,2,3,6;

-- Refresh schedule
REFRESH MATERIALIZED VIEW CONCURRENTLY superset_odoo_financials;

Pattern 2: BIR Compliance View

CREATE VIEW superset_bir_summary AS
SELECT 
  agency_code,
  form_type,
  period,
  tax_amount,
  status,
  filing_deadline,
  CASE 
    WHEN status = 'Filed' THEN 'On Time'
    WHEN filing_deadline < CURRENT_DATE THEN 'Late'
    ELSE 'Pending'
  END as compliance_status
FROM bir_filings
WHERE period >= '2025-01-01';

Chart Type Selection Guide

Use Case	Recommended Chart	Why
KPI/Metric	Big Number Total	Focus attention
Trends over time	ECharts Timeseries	Interactive, zoomable
Comparisons	ECharts Bar	Clear comparison
Proportions	ECharts Pie	Part-to-whole
Detailed data	Pivot Table v2	Sortable, filterable
Distributions	ECharts Histogram	Show patterns
Correlations	ECharts Scatter	Relationships
Geographic	Deck.gl GeoJSON	Map visualization

See reference/chart-selection-guide.md.

Integration with Your Stack

Supabase Connection

# Create database connection in Superset
superset.create_database({
    'database_name': 'Supabase Production',
    'sqlalchemy_uri': f'postgresql://postgres:{password}@db.spdtwktxdalcfigzeqrz.supabase.co:5432/postgres',
    'extra': {
        'allows_virtual_table_explore': True,
        'engine_params': {
            'connect_args': {'sslmode': 'require'}
        }
    }
})

Automated Refresh

# Schedule dashboard refresh
superset.schedule_refresh({