Hi community,
I'm looking for a way that would allow us to add/ remove members to/from SSAS roles dynamically, i. e. I'm looking for a data-driven solution.
We have an Azure SSAS data model which we plan to roll out soon to a significant number of users (at least hundreds, more likely a few thousand).
Access to the model should be driven by an external database, let's say the HR database which holds all relevant information about an employee.
A simple scenario is that based on an employee's job, we'd like to assign them to a specific role. For simplicity, let's say we only want two roles in the data model:
Employee
Manager
Then, we would like to add all Managers to the "Manager" role, and all non-managers to the "Employee" role.
Furthermore, if somebody leaves the organization, we would like to remove that person automatically from all roles in SSAS. In other words, we'd want to keep the users in the data model in sync with the users in the HR database.
In a very simple world, I would hope for something like this:
Import a "User" table into the model that holds the email address and the role name:
User, Role
jane@company.com, Manager
john@company.com, Employee
Then, "somehow" I'd want the model to understand that the "Role" in that table refers to the "Role" in the data model. As a result, it would make Jane a member of the "Manager" role, and John a member of the "Employee" role.
I've seen suggestions to do this via XMLA scripts, but I do not see (understand) how this could be automated.
Users do not authenticate via Windows login, but by logging into Power BI online (app.powerbi.com). In the model, we can identify each user by their email address via this measure: UserName:=USERNAME()
We are using Azure SSAS (not on-premise).
Could somebody put help me out here and put me on the right track?
Thanks a lot in advance,
Gert