使用PowerShell创建SSAS Role

PowerShell, SSAS, Role, DatabasePermission, Cube, Dimension, CubePermission, CubeDimensionCube

在SSAS中创建Role是一个比较繁琐的操作，一般都会涉及到如下操作：创建Role、选择Database Permission、添加Memeberships、选择Cube的Access权限、Cell Data、选择Dimension的Access权限以及DimensionData中Attribute的Allowed/Denied Set.

个人结合工作中的实际情况，通过Poweshell完成了Role的创建，代码如下：

.CSV格式

# ---------------------------------------------------------
# AUTHOR: Niko / hewuquan@outlook.com / 2016-10-18
# For XX BI Project - Create Roles In SSAS
# Steps: Start-Commands => Create-Roles => Insert-Role 
# Param: RoleList.csv (DealerNo, DealerName, CompanyID)
# Function: Create 3 Roles for each dealer
# ---------------------------------------------------------

# ---------------------------------------------------------
# Load Assemblys And Modules
# ---------------------------------------------------------
If (![System.reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”))
{
    Write-Host "[ERROR] Microsoft.AnalysisServices Assembly couldn't be loaded. Script will stop!"
    Exit 1
}
If (![System.reflection.Assembly]::LoadWithPartialName(“System.IO”))
{
    Write-Host "[ERROR] System.IO Assembly couldn't be loaded. Script will stop!"
    Exit 1
}

# ---------------------------------------------------------
# Static Variables
# ---------------------------------------------------------
$path = Split-Path -parent $MyInvocation.MyCommand.Definition
$newpath = $path + "\RoleList.csv"
$log = $path + "\Log.txt"
$date = Get-Date
$iRow = 1
$logMsg

$server = New-Object Microsoft.AnalysisServices.Server
$server.Connect("Put You Server Instance Here")
$dbName = "Put You SSAS Database Name Here"
$database = $server.Databases.Item($dbName)

# ---------------------------------------------------------
# Start Function
# ---------------------------------------------------------
Function Start-Commands
{
    Create-Roles
}

# ---------------------------------------------------------
# Function to Log
# ---------------------------------------------------------
Function Write-Log
{
    $logMsg = "{0}: {1}" -f $date.ToString("yyyy-MM-dd hh:mm:ss"),$logMsg
    Write-Host $logMsg
    $logMsg | Out-File $log -Append
}

# ---------------------------------------------------------
# Detail: Create Role In SSAS
# Check Role If Exists => Crate Role => Grant Database Access
#    => Grant Cube Access Pemission
#    => Grant Dimension Permission
# ---------------------------------------------------------
Function Insert-Role 
{
    param
    (
        [String] $RoleName,
        [Int] $iRow,
        [Int] $CompanyID,
        [String] $RoleType #All, Parts, Service
    )
    begin
    {
        Write-Host "`r`n"
        $logMsg = "[INFO]  Processing Role $($RoleName)..."
        Write-Log
    }
    process
    {
        # Check If Exists
        If ( $database.Roles.FindByName($RoleName) )
        {
            #Write-Host $RoleName, $iRow, $CompanyID
            $logMsg = "[WARN]  Role $($roleName) is already exists in database. Processing skipped for line $($iRow)"
            Write-Log
            Return
        }
        # Create Role
        $RoletoCreate = New-Object Microsoft.AnalysisServices.Role($RoleName)
        Try
        {
            $database.Roles.Add($RoletoCreate)
            $RoletoCreate.Update()
            $logMsg = "[INFO]  Creat Role ($($RoleName)) Succeed."
            Write-Log
        }
        Catch
        {
            $logMsg = "[ERROR] Create Role ($($RoleName)) Failed. $($_.Exception.Message)"
            Write-Log
            Return
        }
        # Grant Database Access Permission
        Try
        {
            $dbPermission = $database.DatabasePermissions.Add($RoletoCreate.ID)
            $dbPermission.ReadDefinition = [Microsoft.AnalysisServices.ReadDefinitionAccess]::Allowed
            $dbPermission.Update()
            $logMsg = "[INFO]  Grant Role ($($RoleName)) Database Access Permission Succeed."
            Write-Log
        }
        Catch
        {
            $logMsg = "[ERROR] Grant Role ($($RoleName)) Database Access Permission Failed. $($_.Exception.Message)"
            Write-Log
        }
        # Grant Cube Access Pemission
        Foreach($cubeItem in $database.Cubes)
        {
            Try
            {
                $cubePermission = $cubeItem.CubePermissions.Add( $RoletoCreate.ID )
                $cubePermission.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

                # Grant Dimension Permission
                # Parts & Service: (Company, Dealer); Clocking (Company); Sales: (Dealer)
                if ( $cubeItem.Name -eq "Parts" -Or $cubeItem.Name -eq "Service")
                {
                    $dimItem = $database.Dimensions.FindByName("Company")
                    $attItem = $dimItem.Attributes.FindByName("BICompany")
                    $cubeDimPermission = $cubePermission.DimensionPermissions.Add( $dimItem.ID )
                    $cubeDimPermission.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed
                    $attPermission = $cubeDimPermission.AttributePermissions.Add( $attItem.ID )
                    $attPermission.AllowedSet = "{[Company].[BICompany].&[$($CompanyID)]}"

                    $dimItem = $database.Dimensions.FindByName("Dealer")
                    $attItem = $dimItem.Attributes.FindByName("BICompany")
                    $cubeDimPermission = $cubePermission.DimensionPermissions.Add( $dimItem.ID )
                    $cubeDimPermission.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed
                    $attPermission = $cubeDimPermission.AttributePermissions.Add( $attItem.ID )
                    $attPermission.AllowedSet = "{[Dealer].[BICompany].&[$($CompanyID)]}"
                }
                if ( $cubeItem.Name -eq "Clocking")
                {
                    $dimItem = $database.Dimensions.FindByName("Company")
                    $attItem = $dimItem.Attributes.FindByName("BICompany")
                    $cubeDimPermission = $cubePermission.DimensionPermissions.Add( $dimItem.ID )
                    $cubeDimPermission.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed
                    $attPermission = $cubeDimPermission.AttributePermissions.Add( $attItem.ID )
                    $attPermission.AllowedSet = "{[Company].[BICompany].&[$($CompanyID)]}"
                }
                if ( $cubeItem.Name -eq "Sales")
                {
                    $dimItem = $database.Dimensions.FindByName("Dealer")
                    $attItem = $dimItem.Attributes.FindByName("BICompany")
                    $cubeDimPermission = $cubePermission.DimensionPermissions.Add( $dimItem.ID )
                    $cubeDimPermission.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed
                    $attPermission = $cubeDimPermission.AttributePermissions.Add( $attItem.ID )
                    $attPermission.AllowedSet = "{[Dealer].[BICompany].&[$($CompanyID)]}"
                }

                 #1,All: All Cubes; 2,Parts: Parts Cube; 3,Service: Service Cube
                If ( $RoleType -eq "All" -Or ( $RoleType -eq "Parts" -and  $cubeItem.Name -eq "Parts" ) -Or ( $RoleType -eq "Service" -and  $cubeItem.Name -eq "Service" ) )
                {
                    $cubePermission.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed
                }
                else
                {
                    $cubePermission.Read = [Microsoft.AnalysisServices.ReadAccess]::None
                }
                $cubePermission.Update()

                $logMsg = "[INFO]  Grant Cube ($($cubeItem.Name)) Read Permission To ($($RoleName)) Succeed."
                Write-Log
            }
            Catch
            {
                $logMsg = "[ERROR] Grant Cube ($($cubeItem.Name)) Read Permission To ($($RoleName)) Failed. $($_.Exception.Message)"
                Write-Log
                Return
            }
        }
        # Grant Dimension Permission
    }
}

# ---------------------------------------------------------
# Read CSV to Create SSAS Roles
# Read CSV => Create 3 Role for each row
# ---------------------------------------------------------
Function Create-Roles
{
    $logMsg = "[INFO]  Processing started"
    Write-Log

    Import-Csv $newpath | ForEach-Object{
        If ( ($_.DealerNo -eq "") -Or ($_.DealerName -eq "") -Or ($_.CompanyID -eq "") )
        {
            $logMsg = "[ERROR] Please provide valid DealerNo, DealerName and CompanyID. Processing skipped for line $($iRow)"
            Write-Log
        }
        Else
        {
            $roleName = $_.DealerNo + " " + $_.DealerName  #  Role 1
            $roleNameParts = $roleName + " Parts"  #  Role Parts
            $roleNameService = $roleName + " Services"  #  Role Service
            
            Insert-Role  $roleName $iRow $_.CompanyID "All"
            Insert-Role  $roleNameParts $iRow $_.CompanyID "Parts"
            Insert-Role  $roleNameService $iRow $_.CompanyID "Service"
        }
        $iRow ++
    }

    $server.Disconnect()

    Write-Host "`r`n"
    $logMsg = "[INFO]  Processing stopped"
    Write-Log
}

Start-Commands
Write-Host -NoNewline "Press [Enter] to Exit..."
Read-Host