❗UPDATE: This post has been superseded by SSAS: Using AMO to Secure Analysis Service Cube. Updates include new methods specifically those related to Dimension Data Security and all methods are now in C#

Analysis Management Objects (AMO) allows you to programmatically interact with the object model of Analysis Services. The AMO layer interacts with Analysis Services by issuing XMLA commands. In this post I will explain the steps to automate the process of securing an Analysis Service Cube by building wrappers around AMO objects.

The fundamental tasks involved in securing a cube are:

1. Role Creation.

2. Role Membership.

3. Cube Permission.

4. Dimension Permission.

5. Dimension Data Security (mostly optional).

That’s what the AMO Wrappers described in subsequent sections are set out to achieve. A simple implementation of the AMO Wrappers would be as shown below.

Imports BennyAustin.SSAS.Wrappers

Module CallAMOWrapper

Sub Main()

Try

'Instantiate Major Objects

Dim objSec As AMOWrapper = New AMOWrapper("localhost", "Adventure Works DW", "Adventure Works DW")

'Backup AS Database before proceeding...

objSec.BackupDatabase("d:\backups\AW.abf")

Dim roleName As String = "Reader"

'Add New Role

If objSec.AddRole(roleName) Then

'Give Read Permission on Cube to New Role

objSec.GrantCubeRead(roleName)

'Give Read Permission on all Dimensions to the New Role

objSec.GrantDimensionRead(roleName)

'Add Members to the New Role

objSec.AddMemberToRole(roleName, "\benny.austin")

End If

Catch ex As Exception

'log exception using your exception handler

End Try

End Sub

End Module

❗IMPORTANT: When passing Database Name and Cube Name to AMO methods, pass the ID instead of the Name. Usually the ID is same as the Name, but not always. For e.g. if you restore a cube from a backup and rename the cube, the ID of the cube is different from the name.

Assembly

This AMO wrapper is a VB assembly. The assembly references AnalysisServices.dll having namespace Microsoft.AnalysisServices and usually located in \Program Files\Microsoft SQL Server\90\SDK\Assemblies.

The skeleton of the AMO wrapper would be as shown below:

Imports Microsoft.AnalysisServices

Imports System.String

Namespace BennyAustin.SSAS.Wrappers

'AMOWrapper

'//////////////////////////////////////////////////////////////////////////////

' AMOWrapper: Wrapper class built around Analysis Management Objects (AMO).

' Primarily caters to automate permissions on cube.

' Exceptions are thrown to the caller method.

' Caller method needs to handle exceptions thrown from this class.

'

' Namespace : BennyAustin.SSAS.Wrappers

'

'

' Author: Benny Austin

'

' Revisions:

' Initial Version

'//////////////////////////////////////////////////////////////////////////////

Public Class AMOWrapper

End Class

End Namespace

Major Objects

Before we could start working with the Security classes, the fundamental objects must be instantiated as the starting point. Analysis Server, Analysis Service Database and Cube are the fundamental objects and are also called as Major Objects. The code for instantiating the Major Objects are given below along with the associated Getter and Setter methods. Exception is thrown to the caller method by raising AMOException.

GetServer()

'Get instance of Analysis Server

Public Function GetServer(ByVal parmConnectionString) As Server

Dim objServer As Server = New Server()

Try

objServer.Connect(parmConnectionString)

Catch ServerNotFoundException As ConnectionException

Throw ServerNotFoundException

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

Return objServer

End Function

'Property - ServerName

Private Property Server() As String

Get

Return _ServerName

End Get

Set(ByVal value As String)

_ServerName = value

End Set

End Property

'Property - ServerObject

Private Property ServerObj() As Server

Get

Return _ServerObj

End Get

Set(ByVal value As Server)

_ServerObj = value

End Set

End Property

GetDatabase()

'Get instance of Database

Public Function GetDatabase(ByVal parmDatabase As String) As Database

Dim DatabaseNotFoundException As AmoException = New AmoException("Analysis Service Database " + parmDatabase + " not found in Analysis Server " + Me.ServerObj.Name)

Dim objDatabase As Database

Try

objDatabase = Me.ServerObj.Databases.FindByName(parmDatabase)

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

If objDatabase Is Nothing Then

Throw DatabaseNotFoundException

End If

Return objDatabase

End Function

'Property - DatabaseName

Private Property Database() As String

Get

Return _Database

End Get

Set(ByVal value As String)

_Database = value

End Set

End Property

'Property - DatabaseObject

Private Property DatabaseObj() As Database

Get

Return _DatabaseObj

End Get

Set(ByVal value As Database)

_DatabaseObj = value

End Set

End Property

GetCube()

'Get instance of Cube

Public Function GetCube(ByVal parmCube As String) As Cube

Dim CubeNotFoundException As AmoException = New AmoException("Cube " + parmCube + _

" not found in Analysis Service Database " + _

Me.DatabaseObj.Name + " at Analysis Server " + Me.ServerObj.Name)

Dim objCube As Cube

Try

objCube = Me.DatabaseObj.Cubes(parmCube)

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

If objCube Is Nothing Then

Throw CubeNotFoundException

End If

Return objCube

End Function

'Property - CubeName

Private Property Cube() As String

Get

Return _CubeName

End Get

Set(ByVal value As String)

_CubeName = value

End Set

End Property

'Property - CubeObject

Private Property CubeObj() As Cube

Get

Return _CubeObj

End Get

Set(ByVal value As Cube)

_CubeObj = value

End Set

End Property

Constructor

Constructor is a good place to instantiate the Major Objects by calling the GetServer(), GetDatabase() and GetCube() methods created in the previous section.

❗IMPORTANT: The major objects must be instantiated in this sequence only – Server, Database, Cube

'Constructor

Public Sub New(ByVal parmServer As String, _

ByVal parmDatabaseName As String, _

ByVal parmCubeName As String)

Me.Server = parmServer

Me.Database = parmDatabaseName

Me.Cube = parmCubeName

'Instantiates major AMO objects

Try

Me.ServerObj = GetServer(parmServer)

Me.DatabaseObj = GetDatabase(parmDatabaseName)

Me.CubeObj = GetCube(parmCubeName)

Catch ex As ConnectionException 'raise AMO connection exception

Throw ex

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

End Sub

Destructor

The Destructor performs mopping up operation. The Major Objects are disposed in the correct sequence and the existing Analysis Server session is disconnected.

Public Overloads Sub Finalize()

If Me.ServerObj.Connected Then

Me.CubeObj.Dispose()

Me.DatabaseObj.Dispose()

Me.ServerObj.Disconnect()

Me.ServerObj.Dispose()

End If

MyBase.Finalize()

End Sub