We are seeking for your assistance for populating filtered data from SSAS Multidimensional cube on the Reports, which are deployed on PowerBI Report Server (on prem).
Currently, when Power BI Connect with SSAS Via live connect, all the users who have access the report will the all the data.
To overcome this problem, we implemented the SSAS Multidimensional Security Roles. SSAS Multidimensional Security Role is working fine with Excel and MS SQL Cube Browser.
When the user Connect normally, he is able to see the information of all regions
However, when the user connect as a role
Can only see his region, only.
Below is the SSAS Role Configuration
We use built in username() function to get login user information.
Please note that every branch comes under a specific region, and we already did the mapping between the branches and users and create a bridge table.
In nutshell, SSAS Security Roles are working on the MS SQL Cube Browser.
On the other hand, below is the PowerBI Report Server, we allow everyone in the organization see the report, however, our goal is data will be populated as their associated branch as per the mapping exists in the bridge table.
To connect with the data source, we provided configuration and connect with service account.
But whenever we check the below option, the error will appear. So, we are unable to impersonate the user.
Our goal is simple, we want to connect PowerBI Report Server (on prem) Reports with SSAS Multidimensional Security Role to populate filtered data from cube on the report.
Or anyone have any other solution for populating filtered data from SSAS Multidimensional cube on the PowerBI Report Server (on prem) Reports?
