Power BI Where It Wins, Native Where It Doesn't: How InsightUW Builds Submission-Time Peer Lookup Without Re-Implementing the BI Team's Dashboards

From "the executive dashboard exists in Power BI but UWs can't pull a peer-pricing comparison while reviewing this submission because Power BI's dataset refreshed six hours ago and the workspace is filtered to last quarter" to "live native peer panel + NAICS benchmark on the submission workspace, embedded Power BI tiles via a registry that mints RLS-overlaid embed tokens with audit, and one fallback mode when the BI workspace isn't provisioned" — through five small tables, two services, and a hybrid UI that respects what each tool is actually good at.

The Problem

Sarah is reviewing Acme Construction's $50M Excess Casualty submission. She wants two things:

1. Show me 10 similar submissions — same NAICS, similar TIV, last 12 months, with what we did (bound / quoted / declined) and the premiums. She wants this now, while the bind decision is in front of her.
2. Show me the premium-by-product-by-region rollup — the executive dashboard her manager looks at every Monday. She wants to see how this account's quoted premium compares to the org-wide pattern.

The org has Power BI. Reports exist. The BI team built the executive dashboard, the production-by-broker view, the lob-rollup, the loss-ratio-by-region heat map. They refresh every 4–6 hours from the warehouse.

The naive approach: build everything native in the workstation. Duplicate every Power BI report. Two sources of truth, two audit trails, two refresh cadences, two teams maintaining the same logic. The BI team's reports become canonical for "the dashboard people see in the morning meeting" but are stale for decision-time. The native dashboards are live but diverge from the warehouse over time.

The other naive approach: embed Power BI everywhere. Now Sarah's submission-workspace peer panel comes from a 4-hour-old dataset, doesn't include the submission she's reviewing right now, and the parameter binding for "this insured's NAICS" is awkward because Power BI tiles want static filters.

Neither works. The org has two distinct decision tempos — retrospective (yesterday's submissions, this week's bound count, this quarter's loss ratio) and decisioning (this submission, this peer comparison, this NAICS in the last hour). Power BI is the right tool for the first; live SQL on the operational store is the right tool for the second.

The InsightUW Approach

A hybrid: native panels for decisioning-time peer lookup + NAICS benchmark, Power BI embedding for retrospective rollups. Don't fight the strengths of each tool.

Native: live peer lookup with similarity scoring

uw_native_bi_service.find_peers(submission_guid, caller_role, limit, include_broker_match):

1. Resolve current submission's signals: NAICS, TIV bucket (revenue-as-proxy until Location rollup is wired), employee bucket, LOB, state, broker.
2. Score every submission in Submission Queue (last 24mo, configurable per-LOB via BIPeer Scoring Config):
   - NAICS exact: +50
   - Same NAICS prefix (3-digit): +25
   - Same TIV bucket (±20%): +30
   - Same employee bucket: +15
   - Same LOB: +20
   - Same state: +10
   - Same broker: +5 (off by default — anti-bias)
3. Filter by min score (default 30), sort by score desc, take top N (default 10).
4. For each peer, look up its outcome: bound (with policy + premium), quoted (with quote + premium), declined (with reason), in_review.
5. Anonymize broker + insured names by default. caller_role ∈ {manager, exec, compliance, admin} un-anonymizes — and writes a Audit Entry row so compliance can see who looked up which peer when.

Per-LOB tuning lives in BIPeer Scoring Config. Cyber's "small" TIV bucket is different from property's; specialty teams can set their own weights. A default row acts as the org-wide fallback under BUILTIN_WEIGHTS.

Native: NAICS benchmark on the submission workspace

uw_native_bi_service.naics_benchmark(naics_code, lookback_months, lob_filter):

• Submissions for insureds with this NAICS, last N months
• Win rate (bound / submitted)
• Avg + median premium across bound policies
• LOB breakdown with progress bars
• Top 5 decline reasons (from Auto Decline)
• Top 5 lost-quote reasons (from Follow Up.cancellation_reason)

Zero new tables. Lives entirely on existing schema. Drop-in for the submission workspace; UW sees how the current submission compares to its industry context without leaving the page.

Power BI: registry + tile pinning + RLS overlay

The BI team's reports are the canonical source for retrospective views. Don't duplicate. Don't rebuild. Embed.

BIReport — catalog row per Power BI report the workstation surfaces. location name references the existing /api/power_bi provider connection (the credentials live there, not here). category ∈ executive | production | premium | forecasting | benchmarking | peer_analysis | claims | other drives the side-nav grouping. allowed roles json gates visibility.

BITile — pin a report (or specific page/visual within it) to a workstation surface. anchor_to ∈ submission_workspace | insured_360 | broker_360 | account_360 | quote_workspace | dashboard | rating_workbench. parameter bindings json carries Jinja-style tokens:

When the workstation page renders, bi-tile-host substitutes the tokens against the page's context dict and passes the resolved filters to the embed-token mint.

BIRow Security Profile — per-user (or per-team) RLS overlay. Maps a UW to the Power BI dataset roles + filter values they should see. Resolution chain:

1. user-specific + this report
2. user-specific + any report
3. team-scoped + this report
4. team-scoped + any report
5. nothing (token mints with no RLS overlay)

The Cyber UW gets LOB_Cyber role applied + filter_lobs=["cyber"]; their embed token only renders rows for their LOB.

BIEmbed Audit Log — append-only row per token grant. Compliance can answer "who saw which report when, with which filters, from which IP." Mirrors the audit story for Pas Message, BORConflict, News Item Action.

Embed-token mint with graceful fallback

The fallback mode path matters: the workstation ships without a Power BI workspace configured. Native panels work day 1. Embedded tiles render a clear "Power BI not configured" alert with a runbook link. Once the BI team provisions the workspace and the admin registers reports, the same UI lights up — no code changes.

Worked Example: Sarah's Decision on Acme Construction

She's looking at a $50M Excess Casualty quote for Acme Construction (NAICS 23 — Construction). Her manager has a Power BI dashboard for production metrics; she has a submission workspace with both native panels embedded.

The peer panel (native)

She opens the Similar submissions panel on the right rail. The native scorer runs:

• Subject: NAICS 23, TIV bucket "midmarket" (revenue $80M), employees "1000-5000", LOB Excess Casualty, state TX
• 142 candidate submissions in the last 24mo
• 23 score above the min_score=30 threshold
• Top 10:
• 9 of 10 share NAICS 23 (exact match, +50)
• 6 share TIV bucket (+30)
• 4 share state TX (+10)
• 8 share LOB Excess Casualty (+20)
• Outcomes: 4 bound, 3 quoted (one lost), 2 declined, 1 in_review

The panel shows score badges, outcome chips, premium delta vs. her current quote, and match reasons. Median peer premium is $487K. Her current quote is $625K. She sees the +28% delta highlighted in amber.

She clicks one of the bound peers (anonymized as "T R" since she's a UW, not a manager). The bound row carries final_premium=$510K, days_to_decision=11. Match reasons: "NAICS exact (23), TIV bucket (midmarket), LOB (Excess Casualty), state (TX)."

She can't see the broker name (anonymized). Her manager could; if she needed it, she'd ask.

The NAICS benchmark (native)

Below the peer panel, the Industry benchmark card. NAICS 23, last 12 months:

• 87 submissions
• Win rate: 41%
• Avg premium: $412K
• Median premium: $385K
• LOB breakdown: 38% GL, 24% Excess Casualty, 18% Property, 12% Auto, 8% other
• Top decline reasons: "loss ratio > 75% over 3yr" (12), "no current loss runs" (8), "operations include demolition" (5)
• Top lost-quote reasons: "broker bound elsewhere" (9), "premium too high" (6)

She compares: her quote at $625K is well above NAICS median ($385K). The peer median ($487K) is closer because peers match on TIV bucket too, but her quote still runs +28% over peers. She makes a note in the rating-rationale (cap UW Notes — auto-routes to PAS audit trail) explaining the rate is driven by recent loss frequency at this insured.

The Power BI tile (retrospective)

The submission workspace also embeds a Power BI tile registered against anchor_to=submission_workspace — the BI team's "Production Year-to-Date" tile. The tile's parameter bindings json includes {"lob": "{{ submission.lob }}"}. When Sarah loads the page, bi-tile-host resolves the token to "Excess Casualty" and mints an embed token with that filter.

Her RLS profile sets her to LOB_ExcessCasualty role; the tile only shows Excess Casualty rows. She sees: $42M YTD bound premium in Excess Casualty (refreshed 4 hours ago — the BI team's overnight refresh). Her $625K quote would add ~1.5% to that figure if bound.

The tile is from the BI team's report. The dashboard her manager looks at every Monday. Same data, same audit, same governance — embedded in the place she's making the decision.

Compliance audit later

Three months later, an internal audit looks at how peer comparisons influenced pricing. They query BIEmbed Audit Log for granted_via='tile' against the Production YTD report — it shows every embed grant with timestamp + filters + IP. Cross-reference with Audit Entry for entity_type='bi'/action='peer_lookup_unanonymized' — they see Sarah un-anonymized 0 peers (she stayed within UW privileges). Audit closes clean.

What's Deferred (Phase 2)

A few things scoped out:

• powerbi-client SDK integration. The bi-dashboard-host uses an iframe with token query-param hint. For richer interactivity (filter binding from outside the iframe, in-place token refresh, drill-through events), swap in the official powerbi-client-angular npm package. ~1-2 hr enhancement; not blocking.
• Q&A natural-language embed. "Show me bound policies in CA last quarter" via Power BI Q&A. Requires Q&A enabled on the workspace; client conversation, not a code one.
• TIV computation from Location rollup. Native peer scorer uses Insured.revenue as a proxy. Lift to true TIV (sum of Location.total_tiv) when the submission has location data; revenue-only fallback when it doesn't.
• Per-org cross-sell denominator config. Native peer panel inherits the org-wide LOB book. A specialty MGA might want only their own LOBs in the denominator, not the parent carrier's. Add config when asked.
• End-to-end trace from peer panel. Click a peer row → land on the data integration hub trace viewer scoped to that submission's correlation_id. Cross-module composition; one route addition.

What This Means for Underwriters

1. Two tools, two tempos. Native for decisioning-time peer lookup; Power BI for retrospective rollups. Don't fight either tool's strengths.
2. Day-1 value with no external dependency. Peer panel + NAICS benchmark work the moment the workstation deploys. No Power BI workspace required.
3. Power BI wakes up when configured. Same UI; flipping the workspace credentials in /api/power_bi lights up the embeds without code changes. Fallback mode degrades gracefully.
4. RLS overlay protects what the dataset role permits. A Cyber UW can't see Property data even if a Property tile gets embedded by mistake — Power BI's RLS enforces it; the registry just maps users to roles.
5. Audit on every grant. BIEmbed Audit Log answers "who saw what when" without bolting an extra logging system on top of Power BI.
6. Per-LOB peer scoring. BIPeer Scoring Config lets specialty teams tune what "similar" means without a code release. Construction's "small" isn't cyber's "small."
7. Anonymization by default for non-managers. Peer broker + insured names hidden until a manager-role user explicitly un-anonymizes — and that un-anonymization writes an audit row. Compliance gets the trail.
8. The BI team owns the dashboards. UW workstation owns the registry and the embed surface. The BI team keeps authoring reports in the workspace they already use; admins register them in /uw/admin/bi-reports to surface them.
9. Cross-sell finder rides the same data. The naics_benchmark service knows what the org writes; the customer-360 cross-sell panel reuses it to surface "LOBs this insured hasn't been pitched."
10. Soft-fail philosophy. Power BI provider unavailable? Fall back, show the alert, the rest of the page works. Native panel data missing for an insured? Empty state with a reason. Never block the UW because of an external system.

What's Next

Customer 360 hybrid: how Pulse owns the depth-of-data while the workstation owns the contract — Pulse As Adapter, Workstation As Decision Surface: How InsightUW Builds a Customer 360 Without Replicating InsightPulse.

Want to see how the same hybrid pattern — "live where decisions happen, embedded where retrospection lives" — handles peer pricing without forcing UWs into a stale dashboard? Request a demo.