Power BI Risk Reporting for Financial Services: A FINRA-Aware Playbook

Financial services Power BI engagements fail in the same three ways often enough that we wrote down the architecture pattern that does not fail. This is that pattern. It is opinionated. It survives the regulator. It survives the M&A. It survives the CFO asking why the number changed.

EPC Group has shipped this architecture into broker-dealers, asset managers, regional banks, insurers, and the regulated mid-market — the bulk of the work referenced on the Financial Services Power BI practice page. The Regulated Large-User Power BI Rollout pattern at /case-studies/patterns is the operational shape.

The three failure modes

1. RLS designed in Excel, translated to DAX

Row-level security is not "the rules in Excel." It is a security boundary inside a data model, and the translation from spreadsheet to DAX expression loses information every single time. The hierarchy edge cases — what happens when a broker is reassigned mid-quarter, what happens when an entity is sold, what happens when a fund-of-funds owner has visibility into the underlying funds but not the other fund-of-funds in the family — these are exactly the cases the Excel rules never captured because they were not in the example data when the rules were written.

2. Semantic models nobody certifies, everybody forks

A semantic model without a named owner is a semantic model that gets forked. A forked semantic model is a second number on the same KPI. A second number on the same KPI is the conversation a CFO does not want to have in a quarterly meeting. The fix is mechanical: one certified model per risk domain, version-controlled DAX, signed-off release, and a deprecation policy for the personal-workspace experiments analysts inevitably create.

3. Audit trails that are not audit trails

"Power BI workspace activity logs" is not an audit trail. It is a tenant-level access log. What the regulator wants — and what the Microsoft Purview lineage view actually produces — is the end-to-end story of a number: source system, ingestion job, semantic model field, DAX measure, dashboard tile, viewer. Purview produces this. Workspace logs do not. The distinction matters six months in, when the regulator arrives, and the difference between “we have it” and “we need three weeks” is the entire engagement.

The architecture pattern

Source data layer

Order management system, position keeping, custodian feeds, market data, reference data. Microsoft Fabric Data Factory or Azure Data Factory ingests via CDC where the source allows it, batch where it does not. Raw immutable bronze layer on OneLake — preserve the wire format because audit cares about it.

Conformed / silver layer

Cleansed, conformed, and joined to reference data. Master data (entities, books, desks, instruments, counterparties) governed in a master data store with named owner. Reference data versioned. Slowly changing dimensions handled explicitly — the engagement always discovers a slowly-changing dimension nobody flagged in scope.

Risk domain marts (gold layer)

One mart per risk domain — counterparty exposure, market risk, operational risk, regulatory capital, position reporting. Materialized in OneLake for Direct Lake semantic models. Schema versioned; breaking changes go through a release cycle.

Certified semantic model layer

One certified semantic model per risk domain. DAX in version control (Tabular Editor or analogous tooling). Deployment pipelines push from dev through test to production. RLS roles defined declaratively against the security dimension table — not by composing dimensions from the business marts. Test cases per role exercised on every release.

Distribution layer

Power BI workspaces aligned to risk domain, distribution controls explicit, Premium per capacity if scale warrants. Apps for end-consumer distribution, not raw workspace access. Subscription controls audited quarterly.

Governance plane (Microsoft Purview)

Purview spans all layers. Classification, sensitivity labels, lineage from source through to dashboard. DLP policies for risk data egress. Audit log retention configured for FINRA Rule 17a-4 (or your equivalent regulatory regime). This is the deliverable that survives the regulator visit.

Row-level security design discipline

1. Write the test cases first. Per role, what should this user see? What should they NOT see? What happens during reorganization, M&A, departure? Test cases drive the RLS design; the DAX is the implementation.
2. Security dimension separate from business dimensions. The dimension that defines who can see what is not the same as the dimension that defines books or desks. It usually has its own ownership pattern (HR + compliance), its own change cadence, and its own SLA. Model it accordingly.
3. Hierarchy-aware RLS. Desk-level access usually means "this desk and child desks" — not just the row marked “Desk 12.” Use path-based RLS or recursive DAX patterns; test the hierarchy edge cases.
4. Fail closed default. A user without an explicit role assignment sees nothing. Not “sees everything” — sees nothing. Then the access-review process gives them what they need.
5. Quarterly access review. Microsoft Entra ID Access Reviews scheduled on the security dimension table assignment, with manager or compliance officer as reviewer. The control that survives turnover.

Certified semantic model release discipline

1. Named owner per model. Single senior architect on the risk reporting team accountable. Not a committee.
2. DAX in version control. Tabular Editor or analogous tooling exporting the model definition to a Git repository.
3. Deployment pipelines. Dev → Test → Production via Power BI deployment pipelines or Azure DevOps. Manual changes in production trigger an audit finding (by policy).
4. Release cadence with sign-off. Quarterly release minimum, with risk officer and compliance sign-off. Hotfix path exists but is documented.
5. Deprecation policy for personal experiments. Analysts will create personal-workspace experiments. Policy: those are exploration, not reporting. Reporting goes through the certified model.

Microsoft Purview lineage discipline

1. Classification before ingestion. Source data classified in Purview before it lands in OneLake. PII, MNPI, position-sensitive data — labels travel.
2. Lineage end-to-end. Connectors from source systems through Data Factory through OneLake through semantic models through Power BI workspaces. The lineage view answers “where did this number come from” — print it for the regulator.
3. DLP for risk-data egress. Sensitivity-label-aware DLP on export and external sharing. Risk data does not leave the platform without explicit policy approval.
4. Audit retention for FINRA Rule 17a-4. Configure Purview audit log retention for the regulatory period (typically 6 years for broker-dealers; check your specific regime). The retention is the producibility.
5. Quarterly evidence package. Pre-generated compliance reports — policy effectiveness, classification coverage, access review completion, lineage completeness. The reports the auditor will ask for before they ask.

What varies per engagement

• Broker-dealer: Position reporting, FINRA Rule 17a-4 recordkeeping emphasis, market data integration, supervision and surveillance hooks.
• Asset manager: Counterparty exposure, fund-level vs entity-level aggregation, performance attribution against benchmarks, fund-of-funds hierarchies.
• Regional bank: Regulatory capital aggregation (Basel framework), stress testing under regulatory scenarios, OREO and credit risk reporting.
• Insurer: Reserves, ALM, NAIC reporting, Solvency II adjacencies for international exposure.
• Hedge fund / alternatives: Real-time NAV, swap exposure, gross/net leverage, alternative-asset valuation oversight.

The architecture pattern survives across all five. The risk domain marts and the certified semantic model content change. The plumbing does not.

How EPC Group ships this

This is the Modernize stage of The EPC Group Lifecycle for financial services. Modernize delivers the architecture; Govern delivers the Purview discipline; Operate runs it 24/7 with regulator-ready SLAs. The work is fixed-fee, with a milestone cadence and a named senior architect on the engagement — see Premium by Design for the long-form on why.

Test cases first. One certified model. Purview lineage end-to-end. Multiple models. One truth. Audit accordingly.