How to Add OLS Rules in Power BI Desktop Using XMLA Endpoint via SSMS?

[HamidBee]

Hi All,

I've been exploring the implementation of Object-Level Security (OLS) in Power BI Desktop and came across a section in the Microsoft documentation stating that OLS rules can be added using an XML for Analysis (XMLA) endpoint (https://learn.microsoft.com/en-us/training/modules/enforce-power-bi-model-security/3-restrict-access...).

My goal is to understand the specific steps for implementing this, particularly using SQL Server Management Studio (SSMS), which the documentation suggests as a viable tool. I'm keen on using SSMS for this task and am looking for guidance on the following:

1. A detailed, step-by-step guide on adding OLS rules to a Power BI Desktop model via an XMLA endpoint, specifically using SSMS.
2. Insights on any particular scripts or configurations required within SSMS for this process.
3. Experiences and practical examples from anyone who has successfully set up OLS in Power BI using SSMS and the XMLA endpoint.

I'm reaching out to see if anyone in the community has tackled this and can share their approach or direct me to resources that specifically focus on using SSMS for setting up OLS in Power BI Desktop.

Your expertise and any help offered would be incredibly valuable.

Thanks in advance.

[HamidBee]

Thank you. I have used Tabular Editor some time back but this time I was trying to see if it's possible to implement OLS via SQL. There are mainly two reasons for this:

1. Merely out of curiosity

2. I'd like to implement it at work and Tabular editor will prove a pain to get the organization to install. The IT department has recently made it a lengthy process to request softwares to be installed.

I probably should have changed SSMS to MS Fabric as ultimately I wanted to implement this on the Data Warehouse in MS Fabric but then the MS docs specifically mentioned SSMS so I'm not sure if the warehouse has to be accessed from SSMS and the OLS must be implemented from there. 

[lbendlin · Accepted solution]

SSMS is just one option - any tool that can connect to an XMLA endpoint will do

Semantic model connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft...

The manipulation itself is done via TMSL

Here's a good example:

powerbi - Object Level Security update using TMLS on the power bi service dataset - Stack Overflow

Note that Power BI Desktop is improving coverage of OLS. Tables can already been hidden, but columns still need external tools, afaik.

[v-nikhilan-msft]

Hi @HamidBee 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

[lbendlin]

Before we dive into the finer points of XMLA programming - what is preventing you from using Tabular Editor to set up OLS?

Object-level security (OLS) with Power BI - Power BI | Microsoft Learn