Superset Dashboards from Desktop App Reports
Date: 2026-04-15 Scope: Extract Desktop App report SQL, design interactive dashboards, document deployment to PSX Superset
What Was Done
1. Extracted SQL from 6 Desktop App Report Definitions
Translated PBSELECT format to standard T-SQL for all 6 reports in the reports/ directory:
| Report | Query File | Source Format |
|---|---|---|
| Position Recon Summary | 01_pos_recon_summary.sql | PBSELECT (13 COMPUTE/SUM aggregates) |
| Position Recon Detail | 02_pos_recon_detail.sql | PBSELECT (19 columns, no aggregation) |
| Cross Ratios | 03_cross_ratios.sql | Native SQL (complex self-join with DPCs) |
| Cost of Hedging | 04_cost_of_hedging.sql | PBSELECT with JOIN |
| SEC 105 Value Change | 05_sec105_value_change.sql | PBSELECT with computed columns |
| SEC 109 Value Change | 06_sec109_value_change.sql | PBSELECT with BUBD/MSR price composition |
2. Validated Data Availability in PS_DemoData
| Table | Rows | Status |
|---|---|---|
rmcat_risk_pos_recon | 11,206 | Ready |
rmcat_risk_pos_recon_arc | 4,997,091 | Ready (historical trend) |
rmcat_bestex_analysis | 1,826 | Ready (354 loans, 6 investors) |
rmcat_closing_ratios | 1,309 | Ready |
rmrep_position_detail | 32,835 | Ready |
rmrep_trades_prep | 807 | Ready |
rmcat_sec105 | 0 | Empty |
rmcat_sec109 | 0 | Empty |
3. Designed BestEx Scorecard Dashboard (5 Charts)
The value discovery centerpiece. Key findings from PS_DemoData:
- 354 loans evaluated against 6 investors (GNMA, FNMA, FHLMC, FHLB Cash, FNMA Cash, FHLMC Cash)
- GNMA dominates: 236 rank-1 wins out of 354 loans
- Total portfolio profit: -$7.6M (DemoData is from a rising-rate environment where all investors are underwater)
- Spread analysis: top 10 loans show $3K-$7.6K spread between best and second-best investor
Charts: Investor Summary, Product Breakdown, Loan Detail, Spread Analysis, Rate Analysis.
4. Designed Position Recon Dashboard (4 Charts)
Replaces the daily desk report with interactive drill-down plus a historical trend chart the Desktop App cannot produce.
- 4 segments: Agency, HFA, Portfolio, Private Investor
- Historical archive: 5M rows enabling time-series analysis back to June 2022
- Superset-exclusive: trend chart showing position evolution over time
Charts: Net Position by Segment, By Position Group, Loan/Trade Detail, Historical Trend.
5. Wrote Setup Guide
Connection configuration, dataset creation instructions, and credential management for the PSX infrastructure team to deploy dashboards to bi.staging.powerseller.com.
Files Created
infra/superset/queries/01_pos_recon_summary.sqlthrough06_sec109_value_change.sql(6 report translations)infra/superset/queries/07_bestex_scorecard_summary.sqlthrough11_bestex_scorecard_rate_analysis.sql(5 BestEx charts)infra/superset/queries/12_posrecon_net_position.sqlthrough15_posrecon_historical_trend.sql(4 Position Recon charts)docs-site/docs/superset/dashboard-designs.md— layout specs, cross-filter behavior, KPIsdocs-site/docs/superset/setup-guide.md— connection config, dataset creation, credential notes
Key Decisions
- No Superset in PSSaaS Docker Compose — use PSX's existing instance at
bi.staging.powerseller.com - PS_DemoData as data source — complete dataset, read/write access, no production risk
- SEC 105/109 queries written but empty — need a client that has run the reserve calculation
mkt_investoris NULL in DemoData — optimal vs. actual comparison not available; used rank spread analysis instead
What's Next
- PSX infrastructure team deploys dashboards to Superset staging
- Phase 2 dashboards: Pipeline Health, Trading Summary, Cost of Hedging, Cross Ratios
- Validate with Greg (Position Recon) and Jay (BestEx) for accuracy