Pharmacy Benefit Management Dashboards on Apache Superset

Table of Contents

1. What Are Pharmacy Benefit Management Dashboards?
2. Why Apache Superset for PBM Operations
3. Core PBM Metrics and KPIs
4. Building Formulary Adherence Dashboards
5. Rebate Capture and Contract Optimisation
6. Generic Substitution Tracking
7. Member Out-of-Pocket Cost Analysis
8. D23.io Managed Deployment Architecture
9. Real-World Implementation: A $50K Rollout
10. Performance Optimisation and Scaling
11. Security, Compliance, and Data Governance
12. Next Steps and Vendor Selection

---

What Are Pharmacy Benefit Management Dashboards?

Pharmacy Benefit Management (PBM) dashboards are real-time analytics platforms that track prescription volumes, drug costs, member adherence, and rebate performance across health plans. They sit at the intersection of clinical outcomes and financial performance—critical for health plans, PBM operators, and pharmacy networks managing millions of claims annually.

Unlike generic business intelligence tools, PBM dashboards must handle:

• Claims-level granularity: Millions of individual prescription records, aggregated by member, drug, pharmacy, and plan.
• Regulatory complexity: Formulary rules, prior authorisation (PA) denials, step therapy adherence, and rebate contract terms.
• Time sensitivity: Month-end rebate reconciliation, real-time member cost notifications, and urgent drug shortage alerts.
• Multi-dimensional analysis: Slice data by therapeutic category, generic vs. brand, member tier, pharmacy chain, and geographic region simultaneously.

Apache Superset, an open-source data visualisation and business intelligence platform, has emerged as a practical choice for PBM operators because it combines ease of deployment with the flexibility to handle complex, high-volume healthcare data. Unlike proprietary PBM-specific tools (Optum’s Pharmacy Intelligence, CVS Caremark’s analytics), Superset lets you build custom dashboards tailored to your exact operational needs—and own your data infrastructure.

---

Why Apache Superset for PBM Operations

Apache Superset offers several advantages for PBM analytics that make it a compelling alternative to expensive, vendor-locked solutions:

Open-Source and Cost-Effective

Superset is free and open-source, meaning no per-seat licensing fees or vendor lock-in. A typical health plan or PBM running 100+ dashboard users might save $500K–$2M annually compared to proprietary BI platforms. You pay only for infrastructure (cloud hosting, database) and internal expertise.

Rapid Dashboard Development

Superset’s drag-and-drop interface lets business analysts build dashboards in days, not months. There’s no need to wait for custom report development from a vendor. As formulary rules change or rebate contracts are renegotiated, you can update dashboards in real time.

Integration with Healthcare Data Warehouses

Superset connects to any SQL database—Snowflake, PostgreSQL, Redshift, BigQuery, or your existing data warehouse. If your PBM claims data is already loaded into a centralised repository (which it should be), Superset plugs in immediately. This is critical: most health plans already have ETL pipelines feeding claims, eligibility, and provider data into a warehouse. Superset simply visualises what’s already there.

Semantic Layer and Business Logic

Superset’s semantic layer (via its native SQL editor or integration with tools like dbt) lets you define once, reuse everywhere. Define “rebate-eligible claim” once; use it across 50 dashboards. This consistency is essential in PBM operations, where a single misclassification can cost millions in reconciliation disputes.

Embedded Analytics and Self-Service

Superset dashboards can be embedded in member portals, provider platforms, or internal operations tools. Non-technical staff can drill into claims data without direct database access—a critical security and governance requirement in healthcare.

---

Core PBM Metrics and KPIs

Before building dashboards, define the metrics that matter. Here are the essential KPIs every PBM operation must track:

Claims and Utilisation Metrics

• Prescription Volume (Rx): Total claims processed monthly, trended year-over-year.
• Cost Per Claim (CPC): Total plan spend ÷ claims processed. A key benchmark for plan performance.
• Utilisation Rate by Therapeutic Category: % of members with at least one claim in a given drug class (e.g., statins, ACE inhibitors).
• Average Wholesale Price (AWP) Variance: Actual reimbursement vs. AWP benchmark; identifies pricing anomalies.

Formulary and Clinical Metrics

• Formulary Adherence Rate: % of claims for formulary-preferred drugs vs. non-formulary alternatives. Target: 85%+.
• Prior Authorisation (PA) Denial Rate: % of PA requests denied; high rates indicate overly restrictive rules.
• Step Therapy Completion Rate: % of members who complete step therapy and move to preferred agent.
• Therapeutic Substitution Rate: % of claims where a generic or therapeutic equivalent was dispensed instead of brand.

Financial and Rebate Metrics

• Rebate Capture Rate: Actual rebates received ÷ rebate contracts eligible. Target: 95%+.
• Rebate Lag: Days between claim date and rebate receipt; measures cash flow impact.
• Generic Penetration Rate: % of claims filled with generic drugs. Higher = lower cost. Target: 90%+.
• Brand-to-Generic Ratio: Tracks shift toward generics over time.
• Rebate Revenue per Claim: Total rebates ÷ claims; direct measure of contract value realisation.

Member Cost Metrics

• Average Member Out-of-Pocket (OOP) Cost: Per-claim and annual. Tracks affordability impact.
• Member Cost-Share Distribution: % of members paying $0, $10–$50, $51–$100, $100+ per claim.
• OOP Trend: YoY change in member costs; critical for member satisfaction and retention.
• Cost-Share Compliance: % of claims where member paid expected copay/coinsurance (vs. waived or reduced).

Operational Metrics

• Pharmacy Network Performance: Claims by pharmacy chain, generic fill rate by pharmacy, average reimbursement by pharmacy.
• Mail-Order vs. Retail Mix: % of claims filled via mail-order pharmacy; impacts cost and member convenience.
• Specialty Pharmacy Utilisation: Claims for high-cost specialty drugs; often >$100 per claim.
• Claim Adjudication Time: Days from claim submission to approval; affects member experience.

Your PBM dashboard strategy should prioritise the 5–10 metrics most critical to your business model. For a health plan focused on cost containment, emphasise rebate capture, generic penetration, and member OOP. For a PBM managing multiple clients, emphasise per-client profitability and contract compliance.

---

Building Formulary Adherence Dashboards

Formulary adherence is the cornerstone of PBM financial performance. A 1% shift in formulary adherence can move millions in annual plan spend. Here’s how to build a comprehensive formulary adherence dashboard on Superset.

Data Model

Your underlying data should include:

• Claims table: Claim ID, member ID, drug code (NDC), date of service, quantity, days supply, ingredient cost, member cost-share, pharmacy ID.
• Formulary table: NDC, formulary tier (preferred generic, preferred brand, non-formulary), therapeutic category, rebate contract ID, effective date, termination date.
• Member eligibility: Member ID, plan ID, plan name, effective date, termination date.

Join claims to formulary and eligibility to classify each claim as “formulary” or “non-formulary” and identify the member’s plan.

Dashboard Layout

When you follow the Creating Your First Dashboard - Apache Superset Official Documentation, you’ll connect your data warehouse, register datasets, and build visualisations. For formulary adherence, structure your dashboard as:

Top-level KPI cards:

• Overall formulary adherence rate (%) this month and YoY trend.
• Formulary adherence by plan (% for each major health plan you manage).
• Non-formulary claims count and trend.

Drill-down charts:

• Adherence by therapeutic category (statins, ACE inhibitors, diabetes agents, etc.). Identifies which drug classes have low adherence and need intervention.
• Adherence by member segment (age, risk score, chronic disease status). Reveals if certain members are systematically choosing non-formulary options.
• Top 20 non-formulary drugs by claim volume. Highlights which specific drugs are driving non-adherence and may warrant formulary review.

Trend analysis:

• Adherence trend over 12 months. Shows if interventions (e.g., prior auth tightening, member education) are working.
• Adherence before and after formulary change. Measures impact of adding a new preferred drug.

Building the SQL

Here’s a simplified SQL query to calculate formulary adherence:

SELECT
  DATE_TRUNC('month', c.claim_date) AS month,
  m.plan_name,
  dt.therapeutic_category,
  COUNT(*) AS total_claims,
  SUM(CASE WHEN f.formulary_tier IN ('preferred_generic', 'preferred_brand') THEN 1 ELSE 0 END) AS formulary_claims,
  ROUND(100.0 * SUM(CASE WHEN f.formulary_tier IN ('preferred_generic', 'preferred_brand') THEN 1 ELSE 0 END) / COUNT(*), 2) AS adherence_rate
FROM claims c
JOIN members m ON c.member_id = m.member_id
JOIN formulary f ON c.ndc = f.ndc AND c.claim_date BETWEEN f.effective_date AND COALESCE(f.termination_date, CURRENT_DATE)
JOIN drug_thesaurus dt ON f.ndc = dt.ndc
WHERE c.claim_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1, 2, 3
ORDER BY month DESC, plan_name, adherence_rate DESC;

This query aggregates claims by month, plan, and therapeutic category, calculating the percentage of claims on formulary-preferred drugs. Superset then visualises this as a time-series line chart (trend), a bar chart (by therapeutic category), or a heatmap (plan × category).

Interactivity and Filters

Add filters to your Superset dashboard:

• Date range picker: Default to last 12 months; allow users to zoom in on specific quarters.
• Plan filter: Multi-select dropdown to compare adherence across plans.
• Therapeutic category filter: Drill into specific drug classes (e.g., “Show me all diabetes agents”).
• Member segment filter: Age group, risk score, chronic disease status (if available in your data).

Interactivity is critical in PBM operations. A pharmacy director might ask, “Why did adherence drop 3% last month?” Your dashboard should let them click a month, select a therapeutic category, and see the top non-formulary drugs driving the change—all in seconds.

---

Rebate Capture and Contract Optimisation

Rebates are the lifeblood of PBM profitability. A typical health plan receives 15–25% of net drug spend in rebates from manufacturers. Missing even 5% of eligible rebates costs millions annually. Your rebate dashboard must track earned vs. received rebates in real time.

Rebate Data Model

You’ll need:

• Rebate contracts table: Contract ID, drug (NDC or therapeutic class), manufacturer, rebate rate (e.g., 15%), volume thresholds, effective date, termination date, contract manager.
• Claims table: As above, with ingredient cost and claim date.
• Rebate receipt table: Rebate ID, contract ID, claim period, amount received, date received, variance from expected.

Dashboard Metrics

Rebate capture dashboard KPIs:

• Earned Rebates (MTD/YTD): Sum of (eligible claims × rebate rate). This is what you should receive based on contract terms.
• Received Rebates (MTD/YTD): Sum of actual rebate payments received from manufacturers.
• Rebate Capture Rate: Received ÷ Earned (%). Target: 95%+. A rate below 90% signals missed rebates, contract disputes, or administrative errors.
• Rebate Lag: Average days from claim date to rebate receipt. Industry standard: 30–60 days. Longer lags impact cash flow.
• Variance Analysis: Earned vs. Received by manufacturer and contract. Highlights which contracts are underperforming.

SQL for Rebate Capture

SELECT
  DATE_TRUNC('month', c.claim_date) AS claim_month,
  r.manufacturer,
  r.contract_id,
  COUNT(c.claim_id) AS eligible_claims,
  ROUND(SUM(c.ingredient_cost * (r.rebate_rate / 100.0)), 2) AS earned_rebates,
  COALESCE(ROUND(SUM(rr.amount_received), 2), 0) AS received_rebates,
  CASE
    WHEN SUM(c.ingredient_cost * (r.rebate_rate / 100.0)) > 0
    THEN ROUND(100.0 * COALESCE(SUM(rr.amount_received), 0) / SUM(c.ingredient_cost * (r.rebate_rate / 100.0)), 2)
    ELSE 0
  END AS capture_rate_pct,
  ROUND(AVG(EXTRACT(DAY FROM rr.date_received - c.claim_date)), 0) AS avg_rebate_lag_days
FROM claims c
JOIN rebate_contracts r ON c.ndc = r.ndc AND c.claim_date BETWEEN r.effective_date AND COALESCE(r.termination_date, CURRENT_DATE)
LEFT JOIN rebate_receipts rr ON r.contract_id = rr.contract_id AND DATE_TRUNC('month', c.claim_date) = rr.claim_period
WHERE c.claim_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1, 2, 3
ORDER BY claim_month DESC, manufacturer, capture_rate_pct ASC;

This query identifies contracts with low capture rates (e.g., 80%) and long lags (e.g., 90+ days), triggering follow-up with the manufacturer.

Visualisation Strategy

• Waterfall chart: Shows earned rebates → variance → received rebates. Visually clear for stakeholders.
• Heatmap: Manufacturer × month, colour-coded by capture rate. Quickly spots underperforming contracts.
• Scatter plot: Rebate lag vs. capture rate. Identifies if slower rebates correlate with lower capture (possible cash flow optimisation by manufacturer).
• Top 20 table: Contracts ranked by dollar variance (earned vs. received). Directs contract management effort.

---

Generic Substitution Tracking

Generic drugs are 80–90% cheaper than brand-name equivalents. Maximising generic fill rates is a primary cost-containment lever. Your generic substitution dashboard should track both the rate of generic fills and the financial impact.

Generic Substitution Metrics

• Generic Penetration Rate: % of claims filled with generic drugs. Target: 90%+. Benchmark against national average (~88%).
• Generic Penetration by Therapeutic Category: Some categories (e.g., statins) have 95%+ generic penetration; others (e.g., biologics) have near 0%. Identify lagging categories.
• Brand-to-Generic Conversion: When a brand loses patent exclusivity, track the % of members who switch to generic. A slow switch suggests patient resistance or prescriber inertia.
• Generic Cost Savings: (Brand cost – Generic cost) × generic claims. Quantifies the financial benefit of generic uptake.
• Prescriber-Level Generic Rates: Which doctors prescribe generics at 95%? Which at 60%? Targets for intervention.

SQL for Generic Penetration

SELECT
  DATE_TRUNC('month', c.claim_date) AS month,
  dt.therapeutic_category,
  COUNT(*) AS total_claims,
  SUM(CASE WHEN d.drug_type = 'generic' THEN 1 ELSE 0 END) AS generic_claims,
  ROUND(100.0 * SUM(CASE WHEN d.drug_type = 'generic' THEN 1 ELSE 0 END) / COUNT(*), 2) AS generic_penetration_rate,
  ROUND(SUM(CASE WHEN d.drug_type = 'brand' THEN c.ingredient_cost ELSE 0 END) - SUM(CASE WHEN d.drug_type = 'generic' THEN c.ingredient_cost ELSE 0 END), 2) AS estimated_savings
FROM claims c
JOIN drugs d ON c.ndc = d.ndc
JOIN drug_thesaurus dt ON d.ndc = dt.ndc
WHERE c.claim_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1, 2
ORDER BY month DESC, generic_penetration_rate ASC;

This query calculates generic penetration by therapeutic category and month, plus an estimate of savings if all brand claims had been filled with generic equivalents.

Dashboard Interactivity

Include filters for:

• Therapeutic category: Drill into specific drug classes.
• Prescriber or prescriber group: Compare generic rates across doctors or medical groups.
• Pharmacy chain: Identify if certain pharmacies have lower generic fill rates (e.g., due to inventory or member preference).
• Member segment: Age, plan type, risk score. Reveals if certain members resist generics.

A pharmacy director might discover that cardiologists prescribe brand-name beta-blockers at 40% vs. a 95% generic rate for primary care. This insight can trigger targeted education or formulary adjustments.

---

Member Out-of-Pocket Cost Analysis

Member affordability is a critical PBM lever. High out-of-pocket (OOP) costs drive non-adherence, health deterioration, and member churn. Your OOP dashboard should track costs by member segment and identify high-cost scenarios.

OOP Metrics

• Average OOP Per Claim: Across all members and claims.
• Average Annual OOP: Per member; identifies high-burden populations.
• OOP Distribution: % of claims with $0, $5–$10, $11–$25, $26–$50, $50+ OOP. Shows spread.
• OOP Trend: YoY change; tracks if plan cost-sharing is increasing member burden.
• High-Cost Claim Frequency: % of claims with >$100 OOP (e.g., specialty drugs). Tracks affordability barriers.
• OOP by Member Segment: Age, income, chronic disease status. Identifies vulnerable populations.

SQL for OOP Analysis

SELECT
  DATE_TRUNC('month', c.claim_date) AS month,
  m.age_group,
  m.plan_name,
  COUNT(*) AS total_claims,
  ROUND(AVG(c.member_cost_share), 2) AS avg_oop_per_claim,
  ROUND(SUM(c.member_cost_share), 2) AS total_oop,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY c.member_cost_share), 2) AS median_oop,
  ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY c.member_cost_share), 2) AS p90_oop,
  SUM(CASE WHEN c.member_cost_share > 100 THEN 1 ELSE 0 END) AS high_cost_claims
FROM claims c
JOIN members m ON c.member_id = m.member_id
WHERE c.claim_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1, 2, 3
ORDER BY month DESC, avg_oop_per_claim DESC;

This query shows OOP trends by age group and plan, plus percentiles (median, 90th percentile) to identify outliers.

Visualisation Strategy

• Box plots: OOP distribution by member segment. Shows median, quartiles, and outliers.
• Histogram: Distribution of OOP across all claims. Shows concentration (e.g., 60% of claims $0–$10).
• Trend line: Average OOP over 12 months. Tracks if plan design changes are increasing member burden.
• Heatmap: Member age × therapeutic category, colour-coded by average OOP. Identifies high-burden combinations (e.g., seniors on specialty drugs).

PBM operators use this dashboard to balance cost containment with member affordability. A plan with average OOP >$25 per claim may face high non-adherence and member complaints, even if rebate capture is strong.

---

D23.io Managed Deployment Architecture

D23.io is a managed Apache Superset deployment platform designed for healthcare and life sciences organisations. It handles infrastructure, security, and compliance—critical for PBM operations handling protected health information (PHI).

What D23.io Provides

D23.io sits between your data warehouse and Superset, providing:

• Hosted Superset instance: No infrastructure management. D23.io handles upgrades, patches, backups.
• Data connectors: Pre-built connectors to Snowflake, Redshift, BigQuery, PostgreSQL, and other warehouses.
• Semantic layer: Define metrics once (e.g., “rebate-eligible claim”) and reuse across dashboards. Ensures consistency.
• Row-level security (RLS): Restrict dashboard access by user role. A pharmacy director sees only their network’s data; a health plan sees all plans.
• Audit logging: Track who accessed which dashboards and when. Required for SOC 2 and HIPAA compliance.
• Single sign-on (SSO): Integrate with your Active Directory or Okta. No separate passwords.
• Embedded dashboards: Embed Superset dashboards in member portals, provider platforms, or internal tools.

D23.io PBM Deployment Example

A typical D23.io rollout for a mid-market health plan (2M members, $5B annual spend) includes:

Week 1: Data Integration

• Connect D23.io to your claims data warehouse (Snowflake or Redshift).
• Register datasets: claims, formulary, rebate contracts, member eligibility, pharmacy network.
• Validate data quality: check for missing NDCs, invalid claim dates, orphaned records.

Week 2–3: Semantic Layer and Metrics

• Define core metrics in the semantic layer: formulary adherence, rebate capture, generic penetration, OOP.
• Build derived tables for common aggregations (e.g., claims by month × plan × therapeutic category).
• Set up row-level security: health plan users see all plans; pharmacy network users see only their pharmacies.

Week 4–5: Dashboard Development

• Build 8–12 core dashboards: formulary adherence, rebate capture, generic penetration, OOP, pharmacy network performance, prescriber performance, claims volume trend, contract performance.
• Integrate with your BI tool or operational system via embedded dashboards or API.
• Train business analysts and operations staff on dashboard navigation, filtering, and drill-down.

Week 6: Optimisation and Launch

• Optimise dashboard performance: add caching for heavy queries, partition large tables.
• Configure alerts: e.g., “Alert if rebate capture drops below 90% for any contract.”
• Go live; monitor usage and performance.

Total timeline: 6 weeks. Cost: $50K–$75K (depending on data complexity and custom development).

When you read about The $50K D23.io Consulting Engagement: What’s Inside | PADISO Blog, you’ll see a real breakdown of a similar engagement: architecture, SSO integration, semantic layer design, and dashboard delivery in 6 weeks.

Security and Compliance

D23.io deployments meet healthcare data governance requirements:

• HIPAA compliance: Encryption at rest and in transit, audit logging, access controls.
• SOC 2 Type II: D23.io undergoes annual SOC 2 audits; you inherit compliance.
• Row-level security: Ensure users see only data they’re authorised to access.
• Data residency: Host data in your region (e.g., Australia for local health plans).

For PBM operations, this is non-negotiable. You’re handling member PHI; a data breach costs millions in fines and reputation damage.

---

Real-World Implementation: A $50K Rollout

Let’s walk through a real-world PBM Superset deployment. This is based on a mid-market health plan managing 1.5M members and $3B annual pharmacy spend.

Project Scope

Objectives:

• Replace legacy PBM reporting system (proprietary, slow, inflexible) with Superset.
• Deliver 10 core dashboards covering formulary, rebate, generic, OOP, and pharmacy performance.
• Enable self-service analytics for 50+ business users (plan managers, pharmacy directors, finance).
• Achieve 95%+ uptime and <2-second dashboard load time.

Timeline: 6 weeks. Budget: $50K.

Week 1: Discovery and Data Integration

Deliverables:

• Map data sources: claims warehouse (Snowflake), formulary master (PostgreSQL), rebate contracts (Excel → database), member eligibility (LDAP).
• Validate data quality: check claim volumes, claim dates, NDC codes, member IDs.
• Design data model: normalised schema for claims, formulary, rebates, members, pharmacies.

Effort: 1 data engineer, 1 PBM subject matter expert (SME). 40 hours.

Outcome: Clean, integrated dataset ready for Superset. Typical data volume: 50M+ claims/year, 5M+ members, 10K+ NDCs.

Week 2: Semantic Layer and Metrics Definition

Deliverables:

• Define 20+ core metrics: formulary adherence, rebate capture, generic penetration, OOP, pharmacy performance, prescriber performance.
• Build dbt models (or Superset virtual datasets) for derived tables: claims aggregated by month × plan × therapeutic category, rebate earned vs. received, OOP by member segment.
• Set up row-level security: health plan users see all data; pharmacy network users see only their pharmacies; finance users see rebate data only.

Effort: 1 data engineer, 1 analytics engineer, 1 PBM SME. 50 hours.

Outcome: Consistent, reusable metric definitions. Non-technical users can build new dashboards without SQL.

Week 3–4: Dashboard Development

Deliverables:

1. Formulary Adherence Dashboard: Overall adherence rate, adherence by plan, adherence by therapeutic category, top 20 non-formulary drugs, adherence trend over 12 months.
2. Rebate Capture Dashboard: Earned vs. received rebates, capture rate by contract, rebate lag analysis, variance by manufacturer, top underperforming contracts.
3. Generic Penetration Dashboard: Overall generic fill rate, generic rate by therapeutic category, brand-to-generic conversion trend, generic savings estimate.
4. Member OOP Dashboard: Average OOP per claim, OOP distribution, OOP by age group and plan, high-cost claim frequency, OOP trend over 12 months.
5. Pharmacy Network Performance: Claims by pharmacy chain, generic fill rate by pharmacy, average reimbursement by pharmacy, mail-order vs. retail mix.
6. Prescriber Performance: Claims by prescriber, generic fill rate by prescriber, formulary adherence by prescriber, average claim cost by prescriber.
7. Claims Volume and Trend: Total claims, claims by therapeutic category, claims by member segment, YoY trend.
8. Contract Performance: Revenue per contract, claims per contract, rebate capture per contract, contract profitability.
9. Member Segment Analysis: Claims by age, gender, risk score, chronic disease status. Identifies high-cost segments.
10. Executive Summary: KPI cards (formulary adherence, rebate capture, generic penetration, average OOP), trend sparklines, alerts (e.g., “Rebate capture down 5% this month”).

Each dashboard includes filters (date range, plan, therapeutic category, prescriber, pharmacy) and drill-down capability.

Effort: 2 data analysts, 1 PBM SME. 80 hours.

Outcome: 10 production-ready dashboards, tested against known data points (e.g., “Formulary adherence should be 87% based on legacy system”).

Week 5: Performance Optimisation and Training

Deliverables:

• Optimise dashboard performance: add caching for heavy queries (e.g., rebate earned vs. received), partition large tables by claim date.
• Configure alerts: e.g., “Alert if rebate capture drops below 90%,” “Alert if generic penetration falls below 88%.”
• Train 50+ business users on dashboard navigation, filtering, drill-down, and self-service analytics.
• Document dashboards: metadata, metric definitions, data refresh schedule, troubleshooting guide.

Effort: 1 data engineer, 1 data analyst, 1 PBM SME, 1 trainer. 60 hours.

Outcome: Fast, reliable dashboards. Trained user base. Reduced support tickets.

Week 6: Launch and Monitoring

Deliverables:

• Go live: migrate users from legacy system to Superset.
• Monitor performance: track dashboard load times, query execution, error rates.
• Establish SLAs: 99% uptime, <2-second dashboard load time, <4-hour data refresh latency.
• Schedule ongoing support: 10 hours/week for dashboard updates, new metrics, troubleshooting.

Effort: 1 data engineer, 1 PBM SME. 30 hours.

Outcome: Stable, performant Superset environment. Clear support model.

Budget Breakdown

• Labour (250 hours @ $150/hour): $37,500
• D23.io platform (6 months): $7,500
• Training and documentation: $2,500
• Contingency (5%): $2,500
• Total: $50,000

ROI

• Cost savings from rebate capture improvement: +1% capture rate on $3B spend = $30M × 0.01 = $300K/year.
• Operational efficiency: Faster reporting, self-service analytics reduce analyst time by 20% = $100K/year.
• Reduced compliance risk: Audit-ready dashboards, audit logging, SOC 2 compliance = $50K/year (avoided fines).
• Total first-year ROI: $450K. Payback period: <2 months.

This is why PBM operators invest in Superset. The financial upside is immediate and substantial.

---

Performance Optimisation and Scaling

As your PBM Superset deployment grows—more dashboards, more users, larger datasets—performance becomes critical. Here are proven optimisation techniques.

Database Optimisation

Indexing: Create indexes on frequently filtered columns: claim_date, plan_id, ndc, member_id, pharmacy_id.

CREATE INDEX idx_claims_claim_date ON claims (claim_date);
CREATE INDEX idx_claims_plan_id ON claims (plan_id);
CREATE INDEX idx_claims_ndc ON claims (ndc);
CREATE INDEX idx_claims_member_id ON claims (member_id);

Partitioning: Partition claims table by claim_date (monthly or quarterly). Queries on recent months scan fewer blocks.

CREATE TABLE claims_2024_q1 PARTITION OF claims
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

Materialized Views: Pre-aggregate heavy queries (e.g., claims by month × plan × therapeutic category) into materialized views. Refresh nightly.

CREATE MATERIALIZED VIEW claims_agg_monthly AS
SELECT
  DATE_TRUNC('month', claim_date) AS month,
  plan_id,
  therapeutic_category,
  COUNT(*) AS claim_count,
  SUM(ingredient_cost) AS total_cost,
  SUM(member_cost_share) AS total_oop
FROM claims
GROUP BY 1, 2, 3;

Superset-Level Optimisation

Following 6 Tips to Optimize Apache Superset for Performance, consider:

• Query caching: Cache expensive queries for 1–4 hours. Most PBM dashboards don’t need real-time data; monthly data is acceptable.
• Asynchronous query execution: Long-running queries (e.g., rebate variance analysis) run in background; users see results when ready.
• Semantic layer caching: Cache metric calculations (e.g., “formulary adherence rate”) at the semantic layer, not the dashboard.
• Domain sharding: For embedded dashboards (e.g., in member portals), use domain sharding to distribute load across multiple Superset instances.

Read The Data Engineer’s Guide to Lightning-Fast Apache Superset Dashboards for advanced techniques: columnar storage, compression, vectorisation.

Scaling Architecture

For large PBM deployments (10M+ members, 100M+ annual claims):

• Data warehouse: Snowflake or BigQuery. Auto-scaling, columnar storage, cost-effective.
• Semantic layer: dbt or Looker. Define metrics once, reuse across tools.
• Superset: D23.io managed deployment or self-hosted on Kubernetes. Horizontal scaling: multiple Superset instances behind a load balancer.
• Cache layer: Redis or Memcached. Speeds up repeated queries.
• Monitoring: DataDog or Prometheus. Track query performance, cache hit rates, error rates.

At scale, your Superset stack might look like:

Data Warehouse (Snowflake) → Semantic Layer (dbt) → Superset Instances (K8s) → Redis Cache → Users

This architecture supports 1,000+ concurrent dashboard users, sub-second query response times, and 99.9% uptime.

---

Security, Compliance, and Data Governance

PBM operations handle protected health information (PHI). Security and compliance are non-negotiable.

HIPAA Compliance

HIPAA requires:

• Encryption at rest: Data warehouse encrypted with AES-256.
• Encryption in transit: HTTPS/TLS for all data flows.
• Access controls: Role-based access control (RBAC). Users see only data they’re authorised for.
• Audit logging: Log all data access. Who accessed which dashboards, when, from where.
• Data retention: Retain audit logs for 6 years (HIPAA requirement).

Superset supports HIPAA compliance via:

• Row-level security (RLS): Restrict dashboard rows by user role. A pharmacy director sees only their network; a health plan sees all.
• Audit logging: Log all dashboard access via Superset’s audit trail.
• SSO integration: Okta, Azure AD, LDAP. Centralised identity management.
• Encryption: Enable HTTPS, encrypt database credentials, use VPCs for network isolation.

SOC 2 Compliance

SOC 2 Type II audits assess security, availability, processing integrity, confidentiality, and privacy. For Superset:

• Security: Access controls, encryption, vulnerability management, incident response.
• Availability: Uptime SLAs, disaster recovery, backup and restore.
• Processing Integrity: Data validation, error handling, audit trails.
• Confidentiality: Data encryption, access controls, confidentiality agreements.
• Privacy: Data collection and use policies, privacy controls, member consent.

D23.io deployments are SOC 2 Type II certified, so you inherit compliance. Self-hosted Superset requires additional security work.

Data Governance

Establish clear data governance:

• Data dictionary: Document every field in your claims schema. What does it mean? How is it calculated? Who owns it?
• Metric definitions: Define metrics consistently. “Formulary adherence” = (formulary claims) / (total claims). No ambiguity.
• Data quality rules: Validate claims data: no negative costs, claim dates within plan year, NDCs in formulary master.
• Access policies: Who can access which dashboards? Pharmacy directors: their network only. Finance: rebate data only.
• Change management: When formulary changes, how are dashboards updated? Document the process.

A PBM with poor data governance faces reconciliation disputes, compliance failures, and lost revenue. Invest in governance from day one.

Agentic AI and Superset

Emerging AI tools like Claude can query Superset dashboards naturally. When you explore Agentic AI + Apache Superset: Letting Claude Query Your Dashboards | PADISO Blog, you’ll see how non-technical users can ask questions like, “What was our rebate capture rate last month by manufacturer?” and get instant answers without touching a dashboard.

For PBM operations, this is transformative. A pharmacy director can ask their AI assistant, “Which drugs are driving non-formulary claims?” and get a detailed analysis in seconds. However, ensure agentic AI respects row-level security and audit logging—critical for HIPAA compliance.

---

Integrating PBM Dashboards with Operational Systems

Superset dashboards are most valuable when integrated into operational workflows. Here’s how to embed PBM analytics into your existing systems.

Embedded Dashboards

Embed Superset dashboards in:

• Member portals: Show members their own OOP costs, formulary status, and coverage. Improves engagement and reduces support calls.
• Provider platforms: Help doctors see their formulary adherence, generic fill rates, and patient outcomes. Drives behaviour change.
• Pharmacy systems: Integrate with pharmacy management systems (PMS) to show real-time formulary, rebate, and network performance.
• Internal operations tools: Health plan staff access dashboards via their existing tools (Salesforce, ServiceNow, etc.).

Superset’s embedding API makes this straightforward:

from superset_client import SupersetClient

client = SupersetClient(host='https://d23io.padiso.co', username='api_user', password='api_key')

# Generate embedded dashboard URL
dash_url = client.get_embedded_dashboard_url(
    dashboard_id=123,
    user_id=456,
    filters={'plan_id': 'PLAN_001'}
)

print(dash_url)  # Embed this URL in your portal

API Integration

Expose Superset metrics via REST API for programmatic access:

curl -X POST https://d23io.padiso.co/api/v1/query \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "datasource_id": 1,
    "query": "SELECT SUM(ingredient_cost) FROM claims WHERE plan_id = 'PLAN_001' AND claim_date >= '2024-01-01'"
  }'

This allows external systems to pull PBM metrics programmatically. For example, your rebate management system could call this API monthly to auto-populate rebate targets.

Alerting and Notifications

Set up alerts for critical PBM events:

• Rebate capture alert: “Rebate capture dropped below 90% for contract ABC.”
• Formulary adherence alert: “Formulary adherence fell 5% month-over-month.”
• Generic penetration alert: “Generic fill rate dropped below 88%.”
• OOP cost alert: “Average member OOP exceeded plan target of $20/claim.”

Superset doesn’t natively support alerts, but you can integrate with external tools:

import requests
from datetime import datetime

# Query rebate capture rate
response = requests.post(
    'https://d23io.padiso.co/api/v1/query',
    json={'datasource_id': 1, 'query': 'SELECT capture_rate FROM rebate_summary WHERE month = CURRENT_MONTH'}
)

capture_rate = response.json()['result'][0]['capture_rate']

# Alert if below 90%
if capture_rate < 0.90:
    requests.post(
        'https://hooks.slack.com/services/YOUR/SLACK/WEBHOOK',
        json={'text': f'⚠️ Rebate capture: {capture_rate*100:.1f}% (target: 90%)'}
    )

Integrate with Slack, PagerDuty, or email to notify stakeholders of critical events.

---

Healthcare-Specific Considerations

PBM dashboards must account for healthcare-specific complexities that generic BI tools often miss.

Therapeutic Classification

Drugs are classified into therapeutic categories (e.g., statins, ACE inhibitors, diabetes agents). Superset dashboards should aggregate by therapeutic category to identify clinical patterns.

Use a drug thesaurus or therapeutic classification system (e.g., Anatomical Therapeutic Chemical, ATC) to map NDCs to therapeutic categories. When you review Superset Analytics in Healthcare: Industry Spotlight, you’ll see how healthcare organisations use Superset to track disease-specific metrics (e.g., diabetes medication adherence).

Prior Authorization (PA) Tracking

Prior authorisation is a key cost-containment tool. Track PA denials, approvals, and appeals:

• PA denial rate: % of PA requests denied. High rates indicate overly restrictive rules.
• PA approval time: Days from PA request to approval. Long times delay member access to needed medications.
• PA appeal success rate: % of denied PAs that are appealed and approved. Indicates potential over-denials.

Step Therapy and Fail-First

Step therapy requires members to try a cheaper drug first before accessing a more expensive alternative. Track:

• Step therapy completion rate: % of members who complete step therapy and move to preferred agent.
• Step therapy failure rate: % of members who fail step therapy (e.g., adverse reaction) and move to alternative.
• Step therapy duration: Days from step therapy initiation to completion or failure.

Specialty Pharmacy

Specialty drugs (e.g., biologics for rheumatoid arthritis, cancer therapies) cost $100–$1,000+ per claim. Track specialty pharmacy separately:

• Specialty drug utilisation: % of claims for specialty drugs, cost per specialty claim.
• Specialty pharmacy network performance: Claims by specialty pharmacy, generic/biosimilar fill rates.
• Specialty drug costs: Trend over time; identifies high-cost drugs for negotiation.

When you explore Apache Superset Implementation and Support - SolDevelo, you’ll see how health information systems use Superset to track specialty medications and high-cost interventions.

Compliance and Regulatory Reporting

PBMs must report to regulators (e.g., state insurance commissioners, CMS). Superset dashboards should support regulatory reporting:

• Pharmacy network adequacy: % of members with access to a pharmacy within 1 mile (urban) or 5 miles (rural).
• Drug availability: % of formulary drugs available at network pharmacies.
• Complaint tracking: Member complaints by category (cost, access, coverage), resolution time.
• Fraud detection: Claims flagged as potentially fraudulent (e.g., duplicate claims, excessive quantities).

---

Next Steps and Vendor Selection

If you’re considering Apache Superset for PBM analytics, here’s a roadmap.

Assess Your Current State

1. Data readiness: Do you have a centralised claims data warehouse? If not, build one first (6–12 months). Superset is only as good as your data.
2. BI maturity: Do you have analytics expertise in-house? If not, plan for external support (consultant or vendor).
3. Budget: Superset is free, but implementation costs $50K–$200K depending on scope. D23.io managed deployments cost $7,500–$25,000/year.
4. Timeline: Plan 6–12 weeks for a full rollout, including data integration, semantic layer, dashboards, and training.

Vendor Selection: Superset vs. Competitors

Apache Superset

• Pros: Open-source, cost-effective, flexible, active community, good for custom PBM metrics.
• Cons: Requires technical expertise, limited out-of-the-box PBM templates, self-hosting adds complexity.
• Best for: Health plans and PBMs with strong data engineering teams, custom analytics needs, cost sensitivity.

Optum Analytics / Pharmacy Intelligence

• Pros: Purpose-built for PBM, includes PBM-specific metrics, vendor support.
• Cons: Expensive, vendor lock-in, less flexible for custom metrics.
• Best for: Large PBMs wanting turnkey solutions.

Tableau / Power BI

• Pros: Polished UI, strong ecosystem, good for enterprise BI.
• Cons: Expensive per-seat licensing, overkill for PBM-specific analytics.
• Best for: Health plans with existing Tableau/Power BI investments.

Looker

• Pros: Strong semantic layer (LookML), good for self-service analytics, Google Cloud integration.
• Cons: Expensive, steep learning curve, less suited to healthcare out-of-the-box.
• Best for: Large enterprises with complex data models.

For most mid-market health plans and PBMs, Apache Superset + D23.io offers the best balance of cost, flexibility, and healthcare-specific support.

Implementation Roadmap

Phase 1 (Weeks 1–6): MVP

• Deploy D23.io Superset instance.
• Integrate claims data warehouse.
• Build 5 core dashboards: formulary adherence, rebate capture, generic penetration, OOP, pharmacy performance.
• Train 20 power users.
• Cost: $50K.

Phase 2 (Weeks 7–12): Expansion

• Add 5 more dashboards: prescriber performance, claims volume, contract performance, member segment analysis, executive summary.
• Implement row-level security and audit logging.
• Integrate with member portal and provider platform via embedded dashboards.
• Expand training to 100+ users.
• Cost: $25K.

Phase 3 (Months 4–6): Optimisation

• Implement agentic AI (Claude) for natural language queries.
• Add alerting and notifications.
• Optimise performance: caching, materialised views, query optimisation.
• Establish ongoing support and SLAs.
• Cost: $30K.

Total investment: $105K over 6 months. ROI: $450K+ in year 1 (rebate capture improvement, operational efficiency, compliance).

Getting Started

1. Audit your data: Inventory your claims warehouse, formulary data, rebate contracts, member eligibility. Identify gaps.
2. Define success metrics: What do you want to achieve? 1% improvement in rebate capture? 2% improvement in generic penetration? Quantify the financial impact.
3. Identify stakeholders: Who will use the dashboards? Plan managers, pharmacy directors, finance, executives. Understand their information needs.
4. Request a demo: Contact a Superset vendor (e.g., D23.io, Preset) for a demo. See real PBM dashboards in action.
5. Pilot project: Start with a pilot dashboard (e.g., formulary adherence) for a single plan or region. Prove ROI before scaling.
6. Plan implementation: 6-week engagement, $50K budget, 10 core dashboards, 50+ trained users.

---

Conclusion

Pharmacy Benefit Management dashboards on Apache Superset represent a practical, cost-effective alternative to expensive proprietary PBM analytics systems. By leveraging Superset’s flexibility and open-source architecture, health plans and PBMs can build custom dashboards tailored to their exact operational needs—formulary adherence, rebate capture, generic penetration, member OOP costs, and pharmacy network performance.

The financial upside is substantial. A 1% improvement in rebate capture on a $5B annual pharmacy spend translates to $50M in additional revenue. A 2% improvement in generic penetration saves $100M annually. These gains are achievable through better visibility into PBM operations—exactly what Superset dashboards provide.

When you combine Superset with a managed deployment platform like D23.io, you gain enterprise-grade security, HIPAA compliance, and healthcare-specific support. A typical mid-market rollout costs $50K and delivers payback in <2 months.

The healthcare industry is moving toward data-driven operations. PBMs and health plans that invest in modern analytics—powered by Superset, integrated with their data warehouse, and supported by experienced teams—will outcompete those relying on legacy, inflexible reporting systems.

If you’re ready to modernise your PBM analytics, start with a clear definition of success metrics, an audit of your data readiness, and a pilot project. Six weeks later, you’ll have dashboards that drive millions in operational improvements and give your team the visibility they need to optimise every lever of PBM performance.

For more insights on building high-performance analytics systems and integrating AI into your operations, explore PADISO: AI Solutions & Strategic Leadership — AIR Bootcamps | SOC2 & ISO27001 via Vanta. PADISO specialises in helping health tech companies, PBMs, and health plans architect and scale modern data platforms—including Apache Superset deployments—with the security and compliance rigour healthcare demands.

You can also read about how agentic AI is transforming analytics in Agentic AI + Apache Superset: Letting Claude Query Your Dashboards | PADISO Blog, or dive into a real-world engagement breakdown in The $50K D23.io Consulting Engagement: What’s Inside | PADISO Blog.

For healthcare-specific AI automation strategies, explore AI Automation for Healthcare: Diagnostic Tools and Patient Care | PADISO Blog and AI Automation for Supply Chain: Demand Forecasting and Inventory Management | PADISO Blog, which cover how AI and modern analytics are reshaping healthcare operations.

Your PBM analytics journey starts today. Build, measure, optimise, repeat.