powerSSAS: Listing Role information

After my last post on how to list the aggregations for a given attribute using Powershell and PowerSSAS, miltruenos commented, asked if it was possible to do a similar script to show roles and allowed/denied sets, so here is just such a script.

add-PSSnapin powerSSAS
new-PSDrive
ssas powerssas localhost

$roles = gi "\Databases\Adventure Works DW\"  `

   | % {$_.Dimensions} `
   | % {$_
.DimensionPermissions} `
   | % {$_
.AttributePermissions} `
   | add-Member ScriptProperty DimensionName {$this.Parent.Parent.Name}-passthru
`
   | add-Member ScriptProperty RoleName {$this.Parent.Role}-passthru


$roles |sort-Object RoleName '
  
|Format-list DimensionName, Attribute, AllowedSet, DeniedSet -groupby
RoleName

However I realise that not everyone may be able to load PowerSSAS on their machines. So I thought I might also show how you could do the same thing with "pure" PowerShell. You can see that the core of the script is essentially the same, powerSSAS is really just a thin wrapper over AMO. The main difference is at the top where I have to load the AMO library and connect to the server and get a reference to the database.

$svrName = "localhost"
$DBname = "Adventure Works DW"
### load the AMO library (redirect to null to 'eat' the output from assembly loading process)
[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices") > $null
# connect to the AS Server
$svr = New-Object Microsoft.AnalysisServices.Server
$svr.Connect($svrName)
# get a reference to the database
$db= $svr.Databases.Item($DBname)

$roles = $db | % {$_.Dimensions} `
   | % {$_.DimensionPermissions} `
   | % {$_.AttributePermissions} `
   | add-Member ScriptProperty DimensionName {$this.Parent.Parent.Name} -passthru `
   | add-Member ScriptProperty RoleName {$this.Parent.Role} -passthru
$roles |sort-Object RoleName '
  
|Format-list DimensionName, Attribute, AllowedSet, DeniedSet -groupby
RoleName
$svr.disconnect()
 

I tried to find a way to make the format-list command put a break between each object, but I could not see a way of doing it. If you prefer you could replace the format-list with format-table and add the -wrap parameter, but I think that makes the allowed and denied sets a bit harder to read.

Note: Last year I added a report to the Analysis Services Stored Procedure project which also shows this sort of information. I think it still might be a bit rough, but it does give some idea of what is possible.

Technorati Tags: ,

Print | posted on Thursday, May 1, 2008 10:29 PM

Comments on this post

# re: powerSSAS: Listing Role information

Requesting Gravatar...
Hey Darren,

Can you please explain why PowerShell is better than AMO?

10x
Left by Miky Schreiber on May 02, 2008 12:35 AM

# re: powerSSAS: Listing Role information

Requesting Gravatar...
Thanks very much for your reply. An unvaluable source of knowledge for students like me.
Left by miltruenos on May 05, 2008 7:18 PM

# re: powerSSAS: Listing Role information

Requesting Gravatar...
Hi, I need help Im new in Analysis Services 2005 and for my assigment I need to add 2,500 roles this is insane doing by hand, so I wonder if there is a way to add roles using sql script or loading from excel dont know...Im just thinking out loud...
thank you in advanced for your answers
atte.
adriana legorreta

adriana.legorreta@gmail.com
Left by adriana legorreta on Jan 10, 2009 6:43 AM

Your comment:

 (will show your gravatar)