使用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 / [email protected] / 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