Implement RLS by defining security filters at the data source and binding them to tenant identifiers in the BI model.
Step‑by‑step implementation
1. Add a tenant key to every fact table (e.g., TenantID INT NOT NULL). Ensure the column is indexed.
2. Create a security view that joins the fact table to a tenant‑mapping table and filters on SESSION_CONTEXT('TenantID') (SQL Server) or current_user() (Snowflake).
```sql
CREATE OR REPLACE VIEW dbo.Fact_Sales_RLS AS
SELECT f.
FROM dbo.Fact_Sales f
JOIN dbo.TenantMap tm ON f.TenantID = tm.TenantID
WHERE tm.TenantID = CAST(SESSION_CONTEXT('TenantID') AS INT);
```
3. Expose the view to the BI tool instead of the base table.
4. Configure the BI platform:
- Power BI: Create a role, add DAX filter USERPRINCIPALNAME() = LOOKUPVALUE(TenantMap[UserEmail], TenantMap[UserEmail], USERPRINCIPALNAME()). Assign users to the role.
```dax
[TenantID] = LOOKUPVALUE(TenantMap[TenantID], TenantMap[UserEmail], USERPRINCIPALNAME())
```
- Tableau: Use a user filter on TenantID via Data > Permissions > Add > User Filter* and set "Only" to the appropriate value.
- Looker: Define an access filter in model.lkml.
```lookml
access_filter: {
field: tenant_id
user_attribute: tenant_id
}
```
5. Automate tenant assignment: Populate the TenantID user attribute in Azure AD, Okta, or Snowflake with a SCIM sync.
6. Test with a non‑admin account; verify that only rows for the assigned tenant appear.
Quick comparison
| Platform | RLS definition | Typical API/Function | Granularity |
|----------|----------------|----------------------|-------------|
| Power BI | Role‑based DAX filter | USERPRINCIPALNAME() | Row level |
| Tableau | User filter on datasource | USER() in calculated field | Row level |
| Looker | Access filter in model | user_attribute | Row level |
Checklist before go‑live
- [ ] All tables include TenantID and are indexed.
- [ ] Security view returns only rows matching SESSION_CONTEXT('TenantID').
- [ ] User attributes are synced to the identity provider.
- [ ] Each BI role/user filter is assigned to the correct tenant.
- [ ] Auditing logs capture TenantID on every query.