Lesson: Semantic Model Access in Power BI

Introduction: The Foundation of Data Governance

In the modern data landscape, the semantic model serves as the single source of truth for an organization. It is the layer where raw data is transformed into meaningful business logic, defined by relationships, measures, and calculated columns. When users interact with a report in Power BI, they are not actually interacting with the database directly; they are interacting with the semantic model. Because this model contains the logic that drives decision-making, controlling who can access it and what they can see is the most critical aspect of your Power BI security strategy.

If you fail to secure your semantic models, you risk exposing sensitive information to unauthorized users, causing confusion through inconsistent metrics, or overwhelming your system with inefficient queries. Effective security is not just about locking data away; it is about ensuring that the right people have the right access at the right time. This lesson will guide you through the technical mechanisms of semantic model access, the hierarchy of permissions, and the governance frameworks necessary to maintain a clean, secure, and performant environment.

Understanding the Power BI Security Hierarchy

Before diving into specific access controls, you must understand the two primary ways access is granted in Power BI: Workspace permissions and Item-level permissions. These two systems interact to define the user experience.

Workspace Permissions

Workspace permissions are broad and relate to the management of content. If you grant someone "Member" or "Admin" access to a workspace, they inherently have access to all semantic models located within that workspace. This is often where organizations make their first mistake by granting overly broad workspace access to users who only need to view a single report.

Item-Level Permissions (The "Build" Permission)

The "Build" permission is the gatekeeper for semantic models. Unlike workspace access, which is about management, "Build" is about usage. If a user has the Build permission on a semantic model, they can create new reports based on that model, export data, or use the model as a source for composite models. This is the granular control that allows you to separate report creators from report viewers.

Callout: Access vs. Visibility It is important to distinguish between "access" to a model and "visibility" of data within that model. Access (via Workspace roles or Build permissions) determines if a user can connect to the model to create content. Visibility (via Row-Level Security) determines which specific rows of data that user is permitted to see once they are connected.

Configuring Semantic Model Permissions

Managing access effectively requires a disciplined approach to how you publish and share your models.

Step-by-Step: Granting Build Permissions

To grant a user the ability to build new content on top of an existing semantic model without giving them edit rights to the entire workspace, follow these steps:

1. Navigate to the workspace where the semantic model resides.
2. Locate the specific semantic model in the item list.
3. Click the "More options" (the three dots) icon next to the model name.
4. Select "Manage permissions."
5. Click the "Add user" button.
6. Enter the email address or security group name.
7. Ensure the "Build" permission is checked.
8. Click "Grant."

Tip: Always prefer using Microsoft Entra ID (formerly Azure Active Directory) security groups over individual user accounts. Managing access at the individual level becomes impossible as your organization grows. By assigning permissions to a group like "Finance Analysts" or "Sales Managers," you centralize your security management.

The Role of "Read" Permission

The "Read" permission is the minimum requirement for a user to see a report connected to a semantic model. When you share a report with a user, Power BI automatically grants them Read access to the underlying semantic model. Without Read access, the report visual will return a "Content not available" error because the user cannot query the model to populate the visual.

Implementing Row-Level Security (RLS)

While "Build" and "Read" permissions govern the ability to connect to a model, Row-Level Security (RLS) governs the data itself. RLS allows you to restrict data rows based on the user's identity.

Defining Roles in Power BI Desktop

RLS is defined within the Power BI Desktop file before the model is published. You create roles using DAX expressions that filter the data.

1. Go to the "Modeling" tab in Power BI Desktop.
2. Click "Manage Roles."
3. Click "Create" and name your role (e.g., "RegionalManager").
4. Select the table you want to filter.
5. Enter a DAX filter expression, such as [Region] = "North".
6. Save the role.

Testing Roles

Before publishing, you must verify your logic. In the "Modeling" tab, click "View as" to simulate how different roles see the data. This ensures your DAX expressions are not inadvertently filtering out too much or too little data.

Assigning Users to Roles in the Service

Once the model is published, you must map your security groups to these roles:

1. In the Power BI Service, go to the semantic model's "Security" settings.
2. You will see the list of roles you created in Desktop.
3. Click on the role name.
4. Add the email addresses or security groups that should belong to that role.
5. Click "Save."

Warning: Row-Level Security is not a substitute for data obfuscation. If a user has "Build" permissions on a model, they might be able to create new measures or tables that bypass your RLS filters if you are not careful. Always audit the permissions of users who have "Build" access to ensure they are not abusing their ability to create new content.

Advanced Access Control: Object-Level Security (OLS)

Sometimes, RLS is not enough. Perhaps you have a column in your "Employee" table called "Salary" that only HR should see. Even if you use RLS to filter rows, the column remains visible to everyone. This is where Object-Level Security (OLS) comes in.

OLS allows you to restrict access to entire tables or columns. Unlike RLS, which filters rows, OLS removes the object from the user's view entirely. If a user is restricted from the "Salary" column, it will not appear in their field list, and they cannot use it in any visual or calculation.

Configuring OLS

Currently, OLS cannot be configured directly in the Power BI Desktop interface. You must use external tools such as Tabular Editor.

1. Connect Tabular Editor to your Power BI Desktop instance.
2. Navigate to the "Roles" folder.
3. Select the role you are modifying.
4. Expand the "Table Permissions" or "Column Permissions" section.
5. Set the permission level to "None" for the objects you wish to hide.
6. Save the changes back to the model.

Callout: RLS vs. OLS Row-Level Security (RLS) is for restricting data (the rows). Object-Level Security (OLS) is for restricting metadata (the tables and columns). Use RLS to ensure a user only sees their own region's sales; use OLS to ensure a user cannot see sensitive columns like PII (Personally Identifiable Information) or financial payroll data.

Best Practices for Semantic Model Governance

Governance is the process of ensuring your semantic models remain secure, accurate, and performant over time. Without governance, models suffer from "sprawl," where dozens of versions of the same data exist, leading to conflicting numbers.

1. Centralize Your Semantic Models

Avoid having a different semantic model for every report. Instead, adopt a "Golden Dataset" approach. Create one highly curated semantic model that contains all the necessary business logic and share it across multiple reports. This reduces the surface area for security errors.

2. Implement a Naming Convention

Clear naming conventions prevent confusion. A model named "Sales_Model_Final_V2_DoNotUse" is a security risk because it indicates a lack of control. Use a standardized naming scheme such as [Department]_[Topic]_[Environment]. For example: FIN_Revenue_Prod.

3. Regular Access Audits

Permissions tend to accumulate over time. Users change roles, projects end, and individuals leave the company, but their access often remains. Perform quarterly audits to ensure that users who no longer need access are removed.

4. Use App Workspaces for Consumption

Never share reports directly from a workspace if you can avoid it. Instead, use the Power BI "App" feature. Apps provide a cleaner, more controlled interface for end-users, and they allow you to manage access to the app separately from the workspace. This adds a layer of abstraction between the user and the raw development environment.

5. Document Your Model Logic

Security is not just about permissions; it is about transparency. Document the DAX measures and the RLS logic within the model itself using descriptions. If a user does not understand what a measure does, they are more likely to create their own, leading to "shadow IT" and potential security loopholes.

Common Pitfalls and How to Avoid Them

Pitfall 1: Granting "Admin" to Too Many People

Many organizations grant "Admin" rights to workspace owners so they can manage the content. However, an Admin can see everything in the workspace. If you only need someone to update a report, give them the "Member" or "Contributor" role instead.

Pitfall 2: Over-Reliance on RLS

Some developers try to use RLS to solve complex business requirements that should be handled at the database level. RLS is meant for security, not for complex data shaping. If you find your RLS DAX expressions becoming hundreds of lines long, reconsider your data architecture.

Pitfall 3: Ignoring "Build" Permission Implications

When you give a user "Build" permission, they can export the data to Excel or create their own reports. If the model contains sensitive data, this might violate compliance policies. Always consider whether the user actually needs "Build" or if "Read" is sufficient.

Pitfall 4: Missing "Read" Permission on Dependent Models

If you have a report that uses a composite model (a model that connects to another Power BI dataset), the user needs "Read" access to both the report's dataset and the underlying "Golden" dataset. Users often forget this, leading to reports that fail to refresh for end-users.

Table: Comparison of Permission Levels

Technical Deep Dive: Managing Security via API

For large organizations, manual management of permissions is not scalable. You can use the Power BI REST API to automate the assignment of permissions. This is particularly useful when onboarding new employees or moving users between departments.

Example: Using PowerShell to Grant Permissions

You can use the MicrosoftPowerBIMgmt module to automate access.

# Connect to Power BI
Connect-PowerBIServiceAccount

# Define the user and the semantic model
$userEmail = "[email protected]"
$datasetId = "your-dataset-guid-here"

# Grant Build permissions to the user
Add-PowerBIWorkspaceUser -Scope Organization -DatasetId $datasetId -PrincipalIdentifier $userEmail -PrincipalType User -AccessRight Build

Note: The code above is a simplified example. Always test your automation scripts in a development workspace before applying them to production environments.

This approach ensures that your security posture is consistent and reproducible. When a user joins a team, a script can automatically grant them the necessary permissions based on their Active Directory group, eliminating the risk of human error in the management portal.

Troubleshooting Semantic Model Access

When users complain they cannot access a report, the troubleshooting process should be systematic. Follow these steps to diagnose the issue:

1. Check the Report Level: Is the user in the App audience? If they are not in the audience list, they cannot see the report, regardless of their model permissions.
2. Check the Semantic Model Level: Does the user have at least "Read" permission on the model? If they were granted access to the report but the model sharing failed, they will see an error.
3. Check for RLS: Is the user part of an RLS role that is filtering out all their data? If the user is not assigned to any role and you have defined RLS, they might see nothing.
4. Check for OLS: Is the user trying to access a visual that contains a column they are restricted from seeing via OLS? This often causes the visual to simply disappear or show an error.
5. Check for Dependent Models: If the report uses multiple datasets, ensure the user has Read access to every single one of them.

Governance: The Human Element

While technical controls are vital, security is ultimately a cultural practice. You must foster a "security-first" mindset within your data team. This means encouraging developers to ask, "Who really needs to see this?" before they hit the publish button. It also means educating business users on why they cannot simply download raw data and share it via email, which is a common way that sensitive data escapes the secure environment of Power BI.

Encourage your team to use "Certified" datasets. In Power BI, you can mark a semantic model as "Certified" if it meets the organization's quality and security standards. This signals to users that the model is trustworthy and secure, reducing the likelihood that they will create their own "shadow" models.

Key Takeaways

1. Hierarchy Matters: Distinguish clearly between Workspace permissions (management) and Item-level permissions (usage). Never grant more access than is necessary for the task at hand.
2. Prioritize "Build": Use the "Build" permission to empower power users to create their own content without giving them administrative control over your workspaces.
3. Layer Your Security: Use RLS for row-level filtering and OLS for column/table-level obfuscation. These two tools work together to create a robust security boundary.
4. Automate Governance: Move away from manual permission management as soon as possible. Use security groups and APIs to ensure consistent, auditable access control.
5. Standardize and Certify: Implement a "Golden Dataset" strategy. By centralizing logic and certifying models, you reduce the risk of security vulnerabilities and ensure data consistency across the organization.
6. Audit Regularly: Security is not a "set it and forget it" task. Schedule periodic reviews of user access to prune permissions that are no longer required.
7. Educate the Users: Technical controls are only half the battle. Train your users on the importance of data handling and the risks of exporting sensitive information outside the Power BI environment.

By adhering to these principles, you turn the semantic model from a potential security liability into a reliable, high-performing asset that drives value throughout your organization. Secure access is not an obstacle to productivity; it is the framework that allows productivity to flourish safely and sustainably.