When I upload excel workbooks that are connected to Analysis Services RLS doesn't work

[shera276]

Hi - With the October release of PBI Desktop and Report Server, you are allowed to upload excel workbooks and run queries connected to Analysis Services.  This is great! We have RLS implemented but when viewing the report in excel on Report Server, it's not working, i.e. everyone can see the same data.  It works fine for regular powerbi desktop reports but not excel.  Is there a way to make this work when viewing excel reports on the Report Server.

[pbx]

How have you implemented RLS with Excel? How Office Online Server is connecting to SSAS?

[shera276]

Hi @pbx - That's the thing, we haven't and not sure how to.  We did implement it at the model/analysis server level.  We use direct query to connect to Analysis Services.

[pbx]

I assume you have a SSAS model that depending on who connects shows them what they have access to. Now when you put an Excel file connected to that model on PBI RS, and assuming you have configured integration of PBI RS and Office Online Server, the Excel will render in the portal through OOS when someone clicks on it. If the spreadsheet that was loaded contains data, it will be shown to the users. The SSAS security model will kick in, only when you refresh. In normal Excel you can force a pivot table to refresh when a file is opened, but not sure if that can be forced in OOS. Now when the user refreshes, your infrastructure should pass the credentials from PBI RS portal to SSAS. You have two options, the difficult way, Kerberos configuration, the easy way, configuring Excel service of OOS to use effective username. If I were you, I would go with the Kerberos way.
Hope this help.