Microsoft Fabric Migration with Row-Level Security for Multi-Client Analytics

Fragmented Multi-Source Data Stack

The client's data was spread across MongoDB, AWS RDS, Google Sheets, and HubSpot, and was moved into reporting using AWS Glue, S3, and a Power BI Gateway running on an EC2 instance. This fintech data captured finance activity and end-user engagement, but combining it reliably for each customer required significant manual pipeline effort.

Secure External Embedding Required Dedicated Capacity

The client needed to embed a single, per-user filtered dashboard for external users logging into their portal. This app-owns-data embedding requires dedicated capacity to run securely and at scale, which shared, standalone Power BI was not provisioned to deliver, so serving each client from one governed report was not viable on the existing setup.

Duplicate Dashboards for Every Client

To work around the missing security layer, the team maintained a separate dashboard for each downstream client. This duplication multiplied development and maintenance work, introduced version drift between reports, and made even small changes slow to roll out across the estate.

Rising Infrastructure and Licensing Cost

Running multiple semantic models across standalone Power BI, alongside a growing AWS RDS footprint, pushed infrastructure and licensing costs steadily higher. The client wanted a single reporting layer that removed this duplication without disturbing the AWS pipelines already in production.

01.

S3 Shortcut into the Fabric Lakehouse

NeenOpal used a Fabric S3 shortcut to access the client's existing raw data directly from the AWS S3 bucket, landing it as the bronze layer inside a Fabric Lakehouse. This approach reused the stable AWS raw layer as the single ingestion point and avoided duplicating storage during the Microsoft Fabric migration.

02.

Notebook-Driven Medallion Transformation

Using scheduled Fabric notebooks, the team transformed the bronze data into a cleansed silver layer and then into a reporting-ready gold layer. This medallion architecture standardized the data for analytics and preserved historical versions, so the client could quickly return to an earlier state of the data when needed.

03.

Single Semantic Model and Reporting Layer

On top of the gold layer, NeenOpal built one centralized semantic model that powered every Power BI report inside Fabric. Consolidating the previous multiple semantic models into a single reporting layer removed duplication and established a genuine single source of truth for all downstream clients.

04.

Dynamic Row-Level Security with DAX

The client supplied a mapping table linking each user email to the domains they were allowed to see. NeenOpal structured the model around a central client table containing every domain name, related outward to the rest of the model so a single filter could propagate across all tables. Because the mapping table contained duplicate rows where many users mapped to one domain, a standard one-to-many relationship was not possible. The team solved this inside the security role by writing DAX that filtered the central client table directly using the logged-in user principal name, rather than relying on a physical relationship, which delivered accurate dynamic row-level security without restructuring the model.

05.

Service Principal Authentication and Embedding

For secure, automated authentication, NeenOpal configured a service principal in the Fabric connections and gateways page and granted it the required workspace and semantic model permissions. The service principal generated the embed token and passed each logged-in user's email as the effective identity, which is how Fabric evaluated and enforced the row-level security role at query time.

06.

Edge-Case Handling and Pipeline Continuity

During client testing, email addresses containing a plus symbol failed to resolve correctly, so the team adjusted the URL encoding logic to handle those identities reliably. Throughout the engagement, NeenOpal ensured the client's existing AWS data pipelines remained stable and undisturbed while the new Fabric reporting layer and requirement changes were introduced in parallel.

Single Dashboard for Every Client

The client replaced a fleet of duplicate reports with one unified dashboard that filters itself by the logged-in user's email. Reporting became faster to deliver and maintain, because a single change now propagates everywhere instead of being reworked across many separate dashboards.

Automated, Secure Role-Based Access

Dynamic row-level security now ensures every user sees only the data tied to their permitted domains. This governed access model removed the manual workarounds the client previously depended on and hardened the security posture of their shared analytics.

Historical Data Access and Fast Rollback

The medallion architecture inside Fabric preserved historical versions of the data across the bronze, silver, and gold layers. The team could quickly return to a previous state of the data, which improved auditability and reduced the risk associated with transformations.

Lower Infrastructure and Licensing Cost

Consolidating multiple semantic models and dashboards into a single reporting layer, while retiring redundant reporting infrastructure, reduced the client's ongoing AWS RDS and Power BI costs. The single-model approach delivered the same coverage with materially less to run and maintain.

Scalable Single Source of Truth

With one semantic model governed by row-level security, the client gained a scalable foundation that grows with their customer base without adding new dashboards. The platform is now positioned to onboard additional clients and data sources with minimal incremental effort.