A data engineer is working on a Microsoft Fabric semantic model that consumes data from a large Azure SQL Database. The model has been configured in Import mode for optimal query performance. However, some tables contain sensitive information, and data analysts should only see data from their assigned business unit. The business units are defined in a separate 'BusinessUnit' table and linked to the 'Sales' fact table. How can the data engineer implement this row-level security efficiently in the semantic model?
- AImplement Row-Level Security (RLS) using DAX expressions that reference the 'BusinessUnit' table.
- BApply a filter in the Power Query step for each business unit.
- CCreate separate semantic models for each business unit.
- DChange the storage mode of the 'Sales' table to DirectQuery and apply filters at the source.
Show answer & explanationAnswer & explanation
Correct answer: A. Implement Row-Level Security (RLS) using DAX expressions that reference the 'BusinessUnit' table.
Row-Level Security (RLS) is the standard and most efficient way to filter rows of data within a semantic model based on user roles and identities. By using DAX expressions, the data engineer can define rules that reference the 'BusinessUnit' table, ensuring users only see data relevant to their assigned unit. Since the model is in Import mode, RLS will filter the data in memory.
Why the other options are wrong
- B. Applying filters in Power Query for each business unit would require creating separate queries or parameters, which is not a dynamic RLS solution and would require manual intervention for each user/role.
- C. Creating separate semantic models for each business unit is an inefficient and high-maintenance approach compared to a single model with RLS.
- D. Changing to DirectQuery would impact performance for the entire model, and the requirement is for RLS within the existing Import mode model, not source-side filtering.
Row-Level Security (RLS)
Row-Level Security (RLS) in Microsoft Fabric semantic models allows you to restrict data access at the row level based on user roles and identities. It's implemented using DAX filter expressions defined within the model, ensuring users only see the data they are authorized for.
- Filters rows of data dynamically based on user context.
- Implemented using DAX filter expressions on tables.
- Works with Import, DirectQuery, and Dual storage modes.
- Essential for granular data access control within a model.
Memory trick: For row security, use RLS with DAX, it's the best tax.