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