If you have different kind of role in a SSAS cube you want to know who is in your role. If you search online it’s very difficult find a perfect solution and this solutions are manuale. I combine PowerShell with power Query to create a Report to show these informations.

First of all go on your Virtual machine where is SSAS oper Powershell copy this script:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
    # Get the SSAS database
    $SSASDatabase = $SSASServer.Databases.Item($DB.name)

    # Get the roles available within the SSAS database and loop thru each of them
    foreach ($Role in $SSASDatabase.Roles)
    {
        # Get the members within the role and loop thru each one of them
        foreach ($UserName in $Role.Members)
        {
            # Create a new object that would store the database name, role name and member user name
            $ItemResult = New-Object System.Object
            $ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
            $ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
            $ItemResult | Add-Member -type NoteProperty -name UserName -value $UserName.Name

            # Put the item result and append it to the result object
            $Result +=$ItemResult
        }
    }
}

$Result | Select DatabaseName, RoleName, UserName | format-table -auto -wrap | Out-String

Change ServerNamer with you server name and paste it on powerShell ( right click). Press enter and you have all the list fo your User and roles in a table

But it’s not easy  use and understand. So select with you mouse all the rows and copy them. (right click). After tahta open a new excel. Create a table with one column and title :”” after that paste the copied text. this is the result:

But we have some problems because all the informations are in one column. to resolve it we use this script in pwoer query:

let
Origine = Excel.CurrentWorkbook(){[Name=”Tabella1″]}[Content],
#”Modificato tipo” = Table.TransformColumnTypes(Origine,{{“tabella di configurazione”, type text}}),
#”Suddividi colonna in base alla posizione” = Table.SplitColumn(#”Modificato tipo”,”tabella di configurazione”,Splitter.SplitTextByPositions({0, 20}, false),{“tabella di configurazione.1”, “tabella di configurazione.2″}),
#”Modificato tipo1″ = Table.TransformColumnTypes(#”Suddividi colonna in base alla posizione”,{{“tabella di configurazione.1”, type text}, {“tabella di configurazione.2″, type text}}),
#”Sostituito valore” = Table.ReplaceValue(#”Modificato tipo1″,”         “,”–“,Replacer.ReplaceText,{“tabella di configurazione.2″}),
#”Sostituito valore1″ = Table.ReplaceValue(#”Sostituito valore”,” “,”–“,Replacer.ReplaceText,{“tabella di configurazione.2″}),
#”Suddividi colonna in base al delimitatore” = Table.SplitColumn(#”Sostituito valore1″,”tabella di configurazione.2″,Splitter.SplitTextByEachDelimiter({“-“}, QuoteStyle.Csv, false),{“tabella di configurazione.2.1”, “tabella di configurazione.2.2″}),
#”Modificato tipo2″ = Table.TransformColumnTypes(#”Suddividi colonna in base al delimitatore”,{{“tabella di configurazione.2.1”, type text}, {“tabella di configurazione.2.2″, type text}}),
#”Sostituito valore2″ = Table.ReplaceValue(#”Modificato tipo2″,”-“,””,Replacer.ReplaceText,{“tabella di configurazione.2.2″}),
#”Rinominate colonne” = Table.RenameColumns(#”Sostituito valore2″,{{“tabella di configurazione.1”, “DAtabase”}, {“tabella di configurazione.2.1”, “Ruolo”}, {“tabella di configurazione.2.2”, “Utenti”}})
in
#”Rinominate colonne”

Using this Excel create 3 column :

• Database name
• Database Role
• User Mapped

know you can use it on Excel in a power pivot table to analyze role and users