All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
24 min
Fine-grained security: rows, columns, objects and files
Restrict rows, columns, tables and files in warehouses, lakehouses and semantic models.
By the end of this lesson you can
- Build static and dynamic row-level security roles in a semantic model
- Hide tables and columns from a role with object-level security
- Secure a Warehouse with GRANT, column and row security, and masking
- Pick the right layer, and predict how Direct Lake treats each rule
From earlier lessons · 1 of 2
A Juniper analyst must build new reports on the sales semantic model but never change it. Which permission fits?
Pick one answer.
From earlier lessons · 2 of 2
A partner must read one Lakehouse's tables from a Spark notebook. Which share option do you tick?
Pick one answer.
Juniper Coffee's store managers now open the sales report. Then the North manager spots the South region's numbers. Finance asks that supplier cost columns stay hidden from managers altogether. And the loyalty team's T-SQL users must never see full customer emails. Workspace roles can't do any of this. You need security inside the items.
Which layer to secure
Exam depth: know it cold.
A rule protects data only on the path it sits on. So choose the layer by who reads the data, and how.
| Consumer | Secure it in | Controls |
|---|---|---|
| Report users only. | The semantic model. | RLS roles with DAX, OLS. |
| T-SQL users of a Warehouse or SQL endpoint. | Warehouse T-SQL. | GRANT and DENY, column-level security, RLS policy, masking. |
| Spark, SQL and Direct Lake users of a Lakehouse. | OneLake security. | Roles on tables and folders, row and column rules. |
Row-level security (RLS) limits rows. Column-level security (CLS) limits columns. Object-level security (OLS) hides whole tables or columns.
Decision rule: rules for one model go in the model; rules for every engine go in OneLake security.
Semantic model RLS: roles with DAX filters
Exam depth: know it cold.
In a semantic model, RLS lives in roles. A role holds one DAX filter per table. The filter is tested on every row and returns TRUE or FALSE. Rows that return FALSE disappear for the role's members.
In Power BI Desktop, select Modeling > Manage roles > New. Pick a table and write the filter. Rules that use DAX functions need the DAX editor.
A static rule compares to a fixed value, such as [Region] = "North". You need one role per region.
Filters flow from that table along active relationships, so put them on a dimension table. A person in two roles sees the union of both.
Dynamic RLS with USERPRINCIPALNAME
Exam depth: know it cold.
Dynamic RLS uses one role for everyone. The filter compares a column to the signed-in user. USERPRINCIPALNAME() returns that user's sign-in name (UPN), such as [email protected].
The pattern needs a security table: one row per user and region, such as UserRegion(ManagerEmail, Region). Hide it from report view.
- Filter the security table:
[ManagerEmail] = USERPRINCIPALNAME(). - Relate it to the Region dimension. The filter must flow from the many side to the one side. So set cross-filter direction to Both and tick Apply security filter in both directions.
- Region then filters Store, and Store filters Sales.
-- Static role "North", filter on table Region:[Region] = "North"-- Dynamic role "Store managers", filter on table UserRegion:[ManagerEmail] = USERPRINCIPALNAME()-- Relationships the dynamic role relies on:-- UserRegion[Region] *-1 Region[Region] (Both, apply security filter in both directions)-- Region[Region] 1-* Store[Region]-- Store[StoreKey] 1-* Sales[StoreKey]
You are an analytics engineer at Juniper Coffee. A role filters UserRegion with [ManagerEmail] = USERPRINCIPALNAME(). UserRegion is related many-to-one to Region, single direction. Region filters Store and Sales. Managers in the role still see every region. You need each manager to see only their regions, with one role for all. What should you do?
Pick one answer.
Assign members, test, and who bypasses RLS
Exam depth: know it cold.
- Assign members in the service, not in Desktop. Open the model's ... menu > Security, pick the role and add people. Only workspace Contributors or higher see Security and can assign members. Use Entra security groups, mail-enabled groups or distribution groups. Microsoft 365 groups aren't supported.
- Test in Desktop with Modeling > View as. Pick a role. For dynamic rules, also tick Other user and enter a UPN.
- Test in the service with the role's ... > Test as role.
Who bypasses RLS: anyone with Write on the model. That includes every workspace Admin, Member and Contributor. RLS filters Viewers, and people with Read or Build, even in Analyze in Excel. When RLS exists, those users see no data until they join a role.
You are an analytics engineer at Juniper Coffee. Dynamic RLS works for most managers. The West manager is in the Store managers role and has the Contributor role in the workspace. She sees every region. She must see only West. What should you do?
Pick one answer.
Object-level security: hide tables and columns
Exam depth: know it cold.
RLS can't hide columns. A user who sees a row sees all its columns. For that you need object-level security (OLS): it hides whole tables or columns from a role, names included.
OLS also lives in roles. You set it in TMDL view or an external tool such as Tabular Editor. Set the table's or column's permission to None to hide it, or Read to show it. Then publish and assign members as for RLS.
For a role member, secured objects act as if they don't exist. A visual that uses one shows an error that the field can't be found. Like RLS, OLS applies to Viewers only, not to Admin, Member or Contributor.
createOrReplacerole 'Store managers'modelPermission: readtablePermission ProductcolumnPermission UnitCostmetadataPermission: none
You are an analytics engineer at Juniper Coffee. Store managers must not see the Product[UnitCost] column in the sales semantic model. They must not even learn that the column exists. Every other column must stay visible. What should you configure?
Pick one answer.
Warehouse and SQL endpoint: T-SQL security
Exam depth: know it cold.
- Objects: share with Read only, then GRANT what's needed. DENY blocks; REVOKE removes a grant or deny.
- CLS:
GRANT SELECTwith a column list. A query that touches any other column fails, soSELECT *fails too. - RLS: a predicate function returns a row for allowed rows. A security policy binds it to a table as a filter predicate. Unlike model RLS, the policy applies to everyone, workspace Admins included.
- Dynamic data masking: shows a disguised value, such as
[email protected]. Stored data doesn't change. Functions:default(),email(),random(),partial(). UNMASK shows real values. Workspace Admins, Members and Contributors always see real values.
Masking isn't encryption: a clever WHERE clause can still guess values. If someone must not reach a column at all, use CLS.
GRANT SELECT ON dbo.Supplier (SupplierID, Name, Region) TO [Analysts]; -- CLSGOCREATE FUNCTION sec.fn_region(@ManagerEmail AS varchar(100))RETURNS TABLE WITH SCHEMABINDINGAS RETURN SELECT 1 AS ok WHERE @ManagerEmail = USER_NAME();GOCREATE SECURITY POLICY sec.RegionFilter -- RLSADD FILTER PREDICATE sec.fn_region(ManagerEmail) ON dbo.RegionSalesWITH (STATE = ON);GOALTER TABLE dbo.Loyalty ALTER COLUMN EmailADD MASKED WITH (FUNCTION = 'email()'); -- masking
OneLake security: tables, folders and files
Exam depth: know it cold.
OneLake security puts roles on a Lakehouse's data. Admins and Members create them under Manage OneLake security. Older pages call them OneLake data access roles and mark them preview. Enforcement is now GA in Spark, the Lakehouse, Direct Lake on OneLake and the SQL endpoint.
A role grants Read (or ReadWrite) on chosen schemas, tables or folders, plus optional row and column rules. Folder access is how you secure files. Row rules are fixed SQL filters; unlike model RLS, they can't be dynamic per user.
- Roles narrow access for Viewers and people with Read only. Admins, Members and Contributors already see everything.
- Remove narrowed users from DefaultReader, or they keep full access.
- The SQL endpoint enforces the roles only in User's identity access mode.
Direct Lake and data security
Exam depth: know it cold.
By default Direct Lake reads as the report user, through single sign-on (SSO). You can bind it to a cloud connection with a fixed identity instead.
| Rule lives in | Direct Lake on SQL analytics endpoint | Direct Lake on OneLake |
|---|---|---|
| Semantic model RLS or OLS. | Works, stays in memory. | Works, stays in memory. |
| SQL endpoint RLS, or a view. | Query falls back to DirectQuery. | Not applicable. |
| SQL endpoint CLS on a used column. | Visual shows an error. | Not applicable. |
| OneLake security roles. | Not applicable. | Enforced for the user, stays in memory. |
Direct Lake on OneLake never falls back. With SSO, users can query the source directly and skip model rules. So pair model-only rules with a fixed identity.
You are an analytics engineer at Juniper Coffee. Managers read sales through a Direct Lake model and data scientists through Spark. Both must see only their region, from one set of rules. Report queries must stay in memory. What should you do?
Pick one answer.
Try it in Power BI Desktop
Use any model with a Region column. Publishing needs a workspace on a trial capacity.
- Select Modeling > Manage roles > New. Name the role North.
- Select the Region table. Select Switch to DAX editor and enter
[Region] = "North". Select Save. - Select Modeling > View as, tick North, and select OK. Check that visuals show only North.
- Publish the model to your workspace.
- In the workspace, select ... next to the model, then Security. Add a test user to North and select Add.
Exam-style practice
You are an analytics engineer at Juniper Coffee. Loyalty-team users query the Warehouse with T-SQL. They must see the first letter of each member's email to spot typos. They must never read the PaymentToken column. What should you configure?
Pick one answer.
Worth keeping
- Model RLS: roles with DAX filters; dynamic RLS uses USERPRINCIPALNAME() and a security table.
- Write on the model bypasses RLS and OLS, so Admins, Members and Contributors see everything.
- OLS hides tables and columns; RLS only hides rows.
- Warehouse: GRANT and CLS, a predicate function plus policy for rows, masking for hints.
- Rules for every engine go in OneLake security; Direct Lake on OneLake enforces them in memory.
Each manager now sees only their region. But how does anyone browsing the catalog know the sales model holds customer data, or that it's the trusted copy? Next lesson: sensitivity labels and endorsement.
Leaderboard
Loading…
50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.
Sources for this lesson (17)
Each fact in the lesson is marked with the number of the page it comes from.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [150]Microsoft Learn: SQL granular permissions in Microsoft Fabric
- [151]Microsoft Learn: Secure your Fabric Data Warehouse
- [152]Microsoft Learn: Column-level security in Fabric Data Warehouse
- [209]Microsoft Learn: Row-level security (RLS) with Power BI
- [247]Microsoft Learn: Integrate Direct Lake security
- [268]Microsoft Learn: Data security in OneLake
- [273]Microsoft Learn: Semantic model permissions
- [274]Microsoft Learn: Share items in Microsoft Fabric
- [276]Microsoft Learn: Read data secured with OneLake security
- [277]Microsoft Learn: OneLake security for SQL analytics endpoints
- [278]Microsoft Learn: Table, column, and row-level security in OneLake
- [279]Microsoft Learn: Object-level security (OLS)
- [280]Microsoft Learn: Row-level security (RLS) guidance in Power BI Desktop
- [281]Microsoft Learn: Dynamic data masking in Fabric Data Warehouse
- [282]Microsoft Learn: How to implement dynamic data masking in Fabric Data Warehouse
- [283]Microsoft Learn: Row-level security in Fabric Data Warehouse