Extract SSAS DMV Data using PowerShell

• This is for Power BI Premium capacity workspace admins?
• Their need is to manage the RLS user access effectively?
• The current limitation with XMLA endpoint we can connect to each database model?and run the SSAS DMV queries each time to get the metadata result set which is a repetitive task.?
• The solution is to use Power shell script (Windows Powershell ISE) with which we can connect to XMLA endpoint, run the SSAS DMV queries inside Power shell script and save the result in csv, excel or SQL table.?
• Below is the Power Shell Script implementation,?

1. Get the list of database model names which was stored in a specific format (excel, csv or SQL table)?
2. Store the list of values into a variable?
3. Create a Foreach loop for the variable to run for each database model?
4. Connect to the XMLA endpoint and run the SSAS DMV??
5. Store the result in a specific format (excel, csv or SQL table)??

?

• We came across a situation where we need to manage the users effectively??

1. Each premium capacity workspace will be have many reports?
2. Most of the reports will be with RLS user access?
3. We need to get the user list across all the reports and remove the users in RLS if?they are not in the organization.?
4. If the users are added inside security groups, and are added in RLS?then we can manage them easily.?

• We were able to get the benefit of managing the RLS user access effectively with the above the solution as with the execution of script we can get the result in a single and use it for internal purpose.