Superset Dashboard Designs

Interactive BI dashboards deployed to PSX's Superset staging instance (bi.staging.powerseller.com), replacing static Desktop App reports with drill-down, cross-filtering, and time-series capabilities.

Data source: PS_DemoData on Azure SQL MI.

Dashboard 1: BestEx Scorecard

Purpose: Value discovery centerpiece. Shows optimal investor per loan, profit/loss analysis, and the dollar spread between best and second-best execution. Directly supports the Value Discovery Strategy.

Data: rmcat_bestex_analysis — 1,826 rows, 354 loans, 6 investors, 1 profile (BestEx).

Charts

#	Chart	Type	Query File	Key Insight
1	Investor Summary	Horizontal bar	07_bestex_scorecard_summary.sql	Which investor wins most often, total P&L per investor
2	Product Breakdown	Treemap / grouped bar	08_bestex_scorecard_by_instrument.sql	Which product/investor combos are most profitable
3	Loan Detail	Table with conditional formatting	09_bestex_scorecard_loan_detail.sql	All investors evaluated per loan, ranked by price
4	Spread Analysis	Histogram + scatter + KPIs	10_bestex_scorecard_spread.sql	Dollar spread between best and second-best per loan
5	Rate Analysis	Stacked bar / line	11_bestex_scorecard_rate_analysis.sql	Profitability by note rate bucket

Layout (3-column grid)

┌─────────────────────────────────────────────────────────┐
│  KPI Row: Total Loans | Total Volume | Total Profit     │
│           Avg Spread  | Worst Loan   | Best Loan        │
├───────────────────────┬─────────────────────────────────┤
│  Chart 1: Investor    │  Chart 5: Rate Analysis         │
│  Summary (bar)        │  (stacked bar)                  │
├───────────────────────┼─────────────────────────────────┤
│  Chart 2: Product     │  Chart 4a: Spread Distribution  │
│  Breakdown (treemap)  │  (histogram)                    │
├─────────────────────────────────────────────────────────┤
│  Chart 3: Loan Detail Table (full width, paginated)     │
│  Click any row to see all investor options for that loan │
├─────────────────────────────────────────────────────────┤
│  Chart 4b: Spread vs Loan Amount (scatter, full width)  │
└─────────────────────────────────────────────────────────┘

Filters

• Profile Name — dropdown (currently only BestEx)
• Instrument Name — multi-select for product filtering
• Investor — multi-select to isolate specific investors
• Note Rate Range — slider for rate band filtering

Cross-Filter Behavior

• Click an investor bar in Chart 1 → filters Charts 2, 3, 5
• Click a product cell in Chart 2 → filters Charts 3, 5
• Click a loan row in Chart 3 → shows all investor options for that loan

Key Metrics (Big Number Charts)

Metric	Query	Formula
Total Portfolio Profit	07	SUM(profit_amount) WHERE rank_number = 1
Average Profit BPS	07	AVG(profit_price) WHERE rank_number = 1
Total Spread (best vs 2nd)	10	SUM(dollar_spread)
Loans with Spread > $1K	10	COUNT(*) WHERE dollar_spread > 1000
Worst Single Loan	07	MIN(profit_amount) WHERE rank_number = 1

---

Dashboard 2: Position Recon Summary

Purpose: The daily desk report. Shows net position changes by segment, with drill-down to individual loans/trades. Replaces the static Desktop App Position Summary Reconciliation report with interactive filtering and historical trending.

Data: rmcat_risk_pos_recon (11,206 current rows), rmcat_risk_pos_recon_arc (5M archived rows across multiple dates).

Charts

#	Chart	Type	Query File	Key Insight
1	Net Position by Segment	Table + waterfall	12_posrecon_net_position.sql	Day-over-day position changes by segment
2	By Position Group	Grouped bar + table	13_posrecon_by_group.sql	Which coupon/product buckets drive each segment
3	Loan/Trade Detail	Paginated table	14_posrecon_loan_detail.sql	Individual records driving position changes
4	Historical Trend	Time series line	15_posrecon_historical_trend.sql	Position evolution over time (Superset-exclusive)

Layout

┌─────────────────────────────────────────────────────────┐
│  KPI Row: Total Gain | Total Settled | Net Position     │
│           Unexplained | Record Count                    │
├───────────────────────┬─────────────────────────────────┤
│  Chart 1: Segment     │  Chart 4: Historical Trend      │
│  Summary (table)      │  (time series — NEW capability) │
├───────────────────────┴─────────────────────────────────┤
│  Chart 2: Position Group Breakdown (grouped bar)        │
├─────────────────────────────────────────────────────────┤
│  Chart 3: Loan/Trade Detail (full width, paginated)     │
└─────────────────────────────────────────────────────────┘

Filters

• Profile Name — dropdown (currently SV2)
• Segment — multi-select (Agency, HFA, Portfolio, Private Investor)
• Class — toggle (L = Loans, T = Trades, or both)
• Date Range — for historical trend chart (archive data)

Cross-Filter Behavior

• Click a segment row in Chart 1 → filters Charts 2, 3
• Click a position group bar in Chart 2 → filters Chart 3
• Date range slider on Chart 4 controls the archive query window

Desktop App Limitations This Solves

Limitation	Superset Solution
Static report, no drill-down	Click segment → group → individual loan
No historical trend	Archive table powers time-series chart
No cross-filtering	Click any element to filter all related charts
Fixed grouping	Dynamic group-by: segment, class, position group
Print-oriented layout	Interactive, resizable, exportable

---

Future Dashboards (Phase 2)

These dashboards have validated data in PS_DemoData but are lower priority than the two above.

Pipeline Health

• Source: loan table + status columns
• Charts: Loan counts by status, aging distribution, lock expiration timeline
• Value: Real-time pipeline visibility vs. the Desktop App's batch-processed view

Trading Summary

• Source: rmrep_trades_prep (807 rows)
• Charts: P&L by trade, settlement variance, pair-off analysis
• Value: Trade-level drill-down that the Desktop App shows as flat tables

Cost of Hedging

• Source: rmcat_coh_results + rmcat_coh_parameters
• Charts: Hedge cost trending by lock month, volume-weighted analysis
• Value: Time-series view of hedging efficiency
• Query: 04_cost_of_hedging.sql

Cross Ratios

• Source: rmcat_dpc_values + rmcat_inst_benchmark_values
• Charts: Cross-hedge ratio scatter, instrument comparison
• Value: Interactive validation of hedge ratios
• Query: 03_cross_ratios.sql

SEC 105 / SEC 109 Reserve Reports

• Source: rmcat_sec105, rmcat_sec109
• Status: Tables are empty in PS_DemoData. Queries are ready (05, 06) but need a client that has run the reserve calculation process.
• Value: Fair value adjustment tracking with drill-down by instrument and pipeline status

---

Data Availability Summary

Dashboard	Table	Rows	Status
BestEx Scorecard	rmcat_bestex_analysis	1,826	Ready
Position Recon (current)	rmcat_risk_pos_recon	11,206	Ready
Position Recon (archive)	rmcat_risk_pos_recon_arc	4,997,091	Ready
Position Recon (prior)	rmcat_risk_pos_recon_prior	11,631	Ready
Position Detail	rmrep_position_detail	32,835	Ready
Trade Prep	rmrep_trades_prep	807	Ready
Closing Ratios	rmcat_closing_ratios	1,309	Ready
Cost of Hedging	rmcat_coh_results	TBD	Query ready
Cross Ratios	rmcat_dpc_values	TBD	Query ready
SEC 105	rmcat_sec105	0	Empty
SEC 109	rmcat_sec109	0	Empty