OLS security in Composite Models

[VeemalS]

I would be interested to know whether OLS Security can work in a composite model environment. To be precise I have a model with many fact tables. But not all fact tables need to be visible and accessible to all users. I have a central model where all dimensions have been included in import mode, Where as the Fact Tables have been added remotely ( PBI Datasets). The purpose of doing it this way is to limit the size of the model to less than 1 GB as we are only using Power BI Pro Licenses. However, we noticed that OLS will not work on the remote tables. So, those tables will remain accessible when deployed to users.

Is there any workaround for the above constraint?

Thanks

Veemal

[bcdobbs]

Hi,

If you look down https://powerbi.microsoft.com/en-us/blog/directquery-for-power-bi-datasets-and-azure-analysis-servic... you will see that in the "Behaviour to Note" it specifically talks about RLS but the same applies to OLS.

I think if you define the OLS rules in the central dataset rather than your local model it should work.

One thing to be careful of is how you've granted build permissions on the central dataset. These are needed for composite models to work however if you give people member/contributed/admin roles on the workspace of the central dataset it disables RLS/OLS. You want to remove users from the workspace and directly assign just build permissions and have your report in a separate workspace. (Cross workspace datasets might need to be enabled in tennant settings)

[VeemalS]

in fact this is the issue that I am facing. In the central dataset ( Imported Dimensions) , when I try to set the OLS security on the Direct query Tables I encounter an error on Tabular editor as follows:

So I am not able to set any security on the Direct Query Tables at the moment.

[bcdobbs]

That looks like you're trying to apply the OLS in your composite model (it's saying those tables are being accessed via direct query).

To use OLS you need to set the roles relevant to those tables in their own dataset (eg the one you're currently connecting to)

[VeemalS]

I did that and I tested it on PBI desktop from the main dataset. I have checked same using 'view as' function but I don't see any diiference i.e the fact tables that I have made hidden via OLS can still be seen when I use the role of that particular user.

Do i need to publish the main dataset model and check whether it is working on the workspace?

Thanks

Veemal

[bcdobbs]

Hi @VeemalS, The good news is OLS certainly does work in composite models. However it's fiddle to check!.

I'll refer to the two models as follows:

Base - your main power bi dataset.

Composite - your new composite model.

To protect tables in the Base model you need to set the OLS/RLS within that model.

If you publish both to the service and add user to the correct roles in the Base model it will work. 

However I can't find an easy way to test it within the service. "View As Role" on the composite model only impersonates the roles of that model not the base one.

The only way I could test it was as follows (needs premium per user / premium capacity to connect to xlma end point).

1) Add a test user to the OLS role in the Base dataset (user needs build permissions on both datasets but can't be anything higher than viewer in workspace.)

2) Open DAX Studio and connect to your XLMA end point for workspace but expand Advanced Options and enter your test user in "Effective User Name"

3) Use drop down to select your composite model:

4) In my example RemoteMember has OLS applied to it. It looks like it hasn't worked but if you try to query it you get the following:

Hope that helps. If you don't have XMLA end point you'd have to get a user to test with you. You can't test yourself because you'll have higer rights on the workspace.

I might try and write this up as a Blog post giving a bit more detail.

[bcdobbs]

I've just realised there is an easier way to test direct in service!

Same setup as above but I added an empty security role in the composite dataset just to enable stuff in the service. Once done if you go into security for the composite model and test as role:

This on it's own won't do it as you're only testing the role local to the composite model. However once there at the top you can then impersonate a test user:

[bcdobbs]

I'm away from a computer at the moment but as soon as I can will set up a test environment to check it can work.