Strategies for Controlling PII Access in Microsoft Fabric and Power BI

When building analytics on Microsoft Fabric with Power BI, chances are your data contains Personally Identifiable Information (PII) or other sensitive data that not everyone should see. Customer names, email addresses, phone numbers, physical addresses — the kind of data that specific teams need for their work but that should be invisible to everyone else.

The challenge isn't just about hiding a few columns. The part that requires more design is doing so in a way that scales, doesn't create maintenance overhead, and gives your report builders a workable experience. In this post, I'll walk through the main architectural approaches available today, their trade-offs, and where Microsoft's platform is heading.

The Scenario

Consider a common pattern: a Fabric Lakehouse holds transactional data with a customer dimension. Most users work with anonymised identifiers and aggregated metrics. But certain business groups — customer service, marketing, compliance — need access to specific PII attributes alongside the same core data. Crucially, these groups often need different subsets of PII, and the users aren't just report consumers — they're report builders who need to see (and work with) the relevant fields in the Power BI field list.

Understanding the impact of the Storage Mode

Before evaluating options, it's essential to understand how the semantic model's storage mode shapes what's available. This is the single most consequential architectural decision for PII access control design.

Import mode brings data into an in-memory copy within PowerBI. Security must be enforced within the semantic model itself — OneLake Security roles have no effect because queries don't read from OneLake at runtime.

Direct Lake mode queries the Delta Parquet files directly from OneLake. This means OneLake Security (including column-level and row-level security) is enforced by the engine at query time. Security defined once at the data layer propagates automatically to Power BI, Spark, and the SQL Analytics Endpoint.

If you're on Direct Lake, you have access to the full range of options. If you're on Import, your choices are more limited — but still workable.

The Approaches

1: Separate Semantic Models per Group

The simplest conceptual approach: publish a different semantic model for each PII access group, each containing the base data plus only the PII columns relevant to that group.

In Direct Lake mode, the fact tables aren't physically copied — they reference the same underlying Delta Parquet files in OneLake. Only the sensitive dimension would need to be duplicated with different column selections. So the storage overhead is modest.

The cost here is operational. Every model carries its own measures, relationships, calculated columns, and security definitions. A change to a core measure needs to be replicated across every model. Report builders may need to rebuild work if they move between groups. The maintenance burden grows linearly with the number of PII groups, and model drift becomes a genuine risk.

When this makes sense: Almost never. It can work as a quick tactical fix for a single additional group, but it's not a sustainable architecture.

2: Semantic Model Object Level Security (OLS) with Separated PII Tables

Within a single semantic model, PII fields are extracted from the main customer dimension into narrow purpose-built tables — one per access group. Each table contains only a linking key and the specific PII columns for that group. Object-Level Security (OLS) configured in the semantic model hides the tables that a given user shouldn't see.

For example, the field list might contain:

• Customer — the primary dimension, visible to everyone, containing segment, acquisition channel, loyalty tier, etc.
• Customer (Personal Details) — visible only to Group A, containing name and postal address
• Customer (Contact Info) — visible only to Group B, containing name, phone and email

Each PII table relates back to the main customer dimension via the shared linking key. The relationship chain resolves correctly, so visuals and measures work as expected.

The trade-off is the authoring experience. PII-authorised report builders see PII fields in a separate table in the field list, not alongside the other customer attributes. This is manageable with clear display naming, pre-built measures, display folders, and documentation — but it's a visible seam in the model.

For users without PII access, the experience is completely clean. They see only the base customer dimension and have no awareness that restricted tables exist.

Strengths: Single model with unified measures; GA features only; works with Import, DirectQuery, and Direct Lake; well-understood pattern.

Weaknesses: Split field list for PII users; OLS roles are static metadata definitions; security is scoped to the semantic model only (Spark and SQL Endpoint aren't covered).

3: OneLake Security with Column-Level Security (CLS)

This is where things get interesting — and where Microsoft's platform is heading. OneLake Security roles are defined at the Lakehouse layer, and Column-Level Security (CLS) hides PII columns from unauthorised users directly at the storage tier.

The experience for report builders is seamless: all columns live in a single table, and the engine dynamically shows or hides columns based on the user's role membership. No separate PII tables, no split field list, no relationship workarounds. A user in the marketing role simply sees name and email in the customer dimension; a user in the general role doesn't see them and doesn't know they exist.

CLS operates via columnar projection — the engine excludes restricted columns from the Parquet read path. For Delta Parquet, this is a native operation and carries negligible performance overhead. Row-Level Security (RLS) can be layered on top within the same OneLake Security role using SQL WHERE clause predicates.

Key constraint to be aware of: OneLake Security does not currently support the combination of two roles where one contains CLS rules and another contains RLS rules on the same table. If a user is a member of both, queries will fail. This means CLS and RLS for a given table must be defined within a single role. Design your roles accordingly — each role should be self-contained with both the column access grants and row-level predicates it needs.

Other important considerations:

• Workspace Admin, Member, and Contributor roles bypass OneLake Security entirely. Users subject to PII controls must be Viewers with read-only item permissions.

• The SQL Analytics Endpoint uses intersection (deny) semantics for CLS, while other engines use union semantics. This means a column denied in any role is denied in the SQL Endpoint, even if another role grants access.

• Dynamic RLS and multi-table RLS predicates are not yet supported.

• This requires Direct Lake mode on OneLake — it cannot be used with Import mode.

The catch: OneLake Security (including CLS and RLS) is currently in Public Preview as of early 2026. Preview features do not carry SLA guarantees and may undergo breaking changes. For organisations handling PII in production, this is a material consideration.

Strengths: Zero data duplication; single-table UX; security enforced across all Fabric engines; aligns with Microsoft's strategic direction; negligible performance overhead for CLS.

Weaknesses: Public Preview maturity; requires Direct Lake; CLS+RLS single-role constraint; Workspace Admin/Member/Contributor bypass.

4: OneLake Security OLS with Separate PII Tables

A hybrid of the previous two approaches. PII columns are split into separate Lakehouse tables (as in the OLS approach), but secured using OneLake Security's table-level access controls rather than semantic model OLS. This avoids the CLS+RLS single-role constraint because each PII table can have its own RLS predicate without interacting with CLS rules on the same table.

This approach is worth considering if your RLS requirements are complex — for instance, if different PII groups need materially different row-level filters that would be difficult to express within a single OneLake Security role. It also inherits the cross-engine enforcement benefit of OneLake Security.

The downside is the same split field list UX as the semantic model OLS approach, plus the operational overhead of maintaining separate PII tables in the Lakehouse pipeline.

What About Dynamic Data Masking?

Fabric Warehouses and Lakehouse SQL Analytics Endpoints support Dynamic Data Masking (DDM) via T-SQL. It's tempting to think of this as a PII solution, but it has fundamental limitations.

Microsoft explicitly documents that DDM is not a security boundary. Users with query access can infer masked values through techniques like predicate-based probing. More critically, DDM does not propagate to Direct Lake semantic models — Power BI queries bypass masking entirely. And it only applies when querying through the SQL Endpoint or Warehouse engine, not through Spark or direct OneLake access.

DDM may have a role as a defence-in-depth layer for ad hoc SQL queries, but it should not be relied upon as a primary PII access control.

Don't Forget Purview

Regardless of which technical approach you choose, Microsoft Purview provides a valuable governance overlay. Sensitivity labels can automatically classify columns containing PII. Data Loss Prevention policies can detect when semantic models or lakehouses contain sensitive data and trigger alerts. Lineage tracking shows the flow of PII through your architecture. None of this replaces a technical access control — but it provides the audit and compliance layer that security teams will expect.

Making the Decision

The choice comes down to two factors: your semantic model's storage mode, and your organisation's risk appetite for Preview-stage features.

If you're on Import mode, Semantic model OLS with separate PII tables is your best option. It's GA, well-understood, and workable with good UX mitigations.

If you're on Direct Lake and need GA maturity for PII workloads, use semantic model OLS now and plan a migration path to OneLake Security CLS when it reaches GA. The migration is straightforward: consolidate PII columns back into the main table and define CLS roles.

If you're on Direct Lake and comfortable with Preview features, OneLake Security with CLS is the architecturally preferred solution. It's the cleanest design, the best user experience, and the direction Microsoft is investing in. Just go in with eyes open about what Preview means for a production PII workload.

Whichever path you choose, run a proof of concept with non-production data first. Validate the end-to-end security enforcement, test the report builder experience, and confirm that the operational model works for your team. PII is not the place to skip validation.