Superset Setup Guide for PS_DemoData

Instructions for the PSX infrastructure team to connect PSX's Superset staging instance to the PS_DemoData database on Azure SQL MI and deploy the PSSaaS dashboards.

Prerequisites

• Access to PSX Superset at bi.staging.powerseller.com
• Superset Admin role
• Network connectivity from Azure staging to Azure SQL MI (both are in Azure, so this should work via Azure backbone)

Step 1: Add the Database Connection

1. In Superset, navigate to Settings → Database Connections → + Database
2. Select Microsoft SQL Server as the database type
3. Use this SQLAlchemy URI:

mssql+pymssql://kevin_pssaas_dev:M0th3rFuck1ng%2444%24@hostedps-sql.086ea791c2f1.database.windows.net:1433/PS_DemoData

Private Endpoint

Superset in AKS reaches SQL MI via VNet peering (PSS-vnet-to-sqlmi) on port 1433. Never use the public endpoint (port 3342) from within the cluster.

Password Encoding

The $ characters in the password must be URL-encoded as %24. The password M0th3rFuck1ng$44$ becomes M0th3rFuck1ng%2444%24 in the URI.

4. Set the Display Name to PS_DemoData (PSSaaS Demo)
5. Under Advanced → Security, check Allow DML only if you need write access (not recommended for demo data)
6. Under Advanced → SQL Lab, check:
   • Allow Multi Schema Metadata Fetch
   • Expose in SQL Lab
7. Click Test Connection to verify, then Connect

Alternative: If pymssql isn't available

If the Superset instance uses pyodbc instead of pymssql:

mssql+pyodbc://kevin_pssaas_dev:M0th3rFuck1ng%2444%24@hostedps-sql.086ea791c2f1.database.windows.net:1433/PS_DemoData?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes&Encrypt=yes

Step 2: Create Datasets

For each dashboard chart, create a Superset dataset from the SQL query files in infra/superset/queries/.

BestEx Scorecard Datasets

Dataset Name	Query File	Notes
BestEx - Investor Summary	07_bestex_scorecard_summary.sql	Remove -- from WHERE clause, use Jinja for profile filter
BestEx - By Instrument	08_bestex_scorecard_by_instrument.sql	Same
BestEx - Loan Detail	09_bestex_scorecard_loan_detail.sql	Large result set, enable pagination
BestEx - Spread Analysis	10_bestex_scorecard_spread.sql	CTE query — paste as SQL Lab query, then save as dataset
BestEx - Rate Analysis	11_bestex_scorecard_rate_analysis.sql	Bucketed aggregation

Position Recon Datasets

Dataset Name	Query File	Notes
PosRecon - Net Position	12_posrecon_net_position.sql	Main summary
PosRecon - By Group	13_posrecon_by_group.sql	Drill-down level 2
PosRecon - Loan Detail	14_posrecon_loan_detail.sql	Drill-down level 3
PosRecon - Historical	15_posrecon_historical_trend.sql	5M rows — add date filter

Creating a Dataset from SQL

1. Go to SQL Lab → SQL Editor
2. Select the PS_DemoData database
3. Paste the query from the .sql file
4. Uncomment and configure any Jinja template filters
5. Run the query to verify results
6. Click Save → Save as Dataset
7. Name it per the table above

Step 3: Build the Dashboards

Follow the layout specifications in the Dashboard Designs doc.

Dashboard 1: BestEx Scorecard

1. Create a new dashboard named BestEx Scorecard
2. Add filter box with: Profile Name, Instrument Name, Investor, Note Rate Range
3. Add charts in the layout order specified in the design doc
4. Enable cross-filtering between charts
5. Set the dashboard to auto-refresh every 5 minutes (optional for demo)

Dashboard 2: Position Recon Summary

1. Create a new dashboard named Position Recon Summary
2. Add filter box with: Profile Name, Segment, Class, Date Range
3. Add charts in the layout order specified in the design doc
4. Enable cross-filtering
5. For the Historical Trend chart, set a default date range filter to limit the 5M-row archive query

Step 4: Desktop App Report Queries

The original 6 Desktop App reports are also available as SQL queries for reference or ad-hoc analysis in SQL Lab:

Report	Query File	Data Available
Position Recon Summary	01_pos_recon_summary.sql	Yes (11K rows)
Position Recon Detail	02_pos_recon_detail.sql	Yes (11K rows)
Cross Ratios	03_cross_ratios.sql	Verify tables exist
Cost of Hedging	04_cost_of_hedging.sql	Verify tables exist
SEC 105 Value Change	05_sec105_value_change.sql	No data (table empty)
SEC 109 Value Change	06_sec109_value_change.sql	No data (table empty)

Network Notes

• Private endpoint (from AKS): hostedps-sql.086ea791c2f1.database.windows.net on port 1433 via VNet peering (PSS-vnet-to-sqlmi). ~1 second latency on Azure backbone.
• Public endpoint (from local workstation): hostedps-sql.public.086ea791c2f1.database.windows.net on port 3342. Used for local dev only.
• The kevin_pssaas_dev user has read-only access to PS_DemoData
• If connectivity issues arise from AKS, verify VNet peering status: az network vnet peering list -g PSS-rg --vnet-name PSS-vnet

Credential Rotation

When the kevin_pssaas_dev password is rotated:

1. Update the SQLAlchemy URI in Superset's database connection settings
2. URL-encode any special characters in the new password
3. Test the connection before saving