SSAS: AMO Coding Tips

Some coding tips when using Analysis Management Objects (AMO) methods

1. Supply Object ID instead of Object Name as parameter to Find() methods.

SSAS objects like Database, Cube, Dimension, Attributes and Roles have an Object ID and Object Name. While the Object Name can be modified anytime, the Object ID is non-editable and remains constant after first build. For e.g. the Name of the dimension can be changed from Org Chart to Organisation Hierarchy. However Object ID still remains as Org Chart. In other words, the Object Name and Object ID need not be same and could have different values. Find() is a common AMO method used to return an instance of SSAS object from their respective collection. Object ID should be passed to Find() instead of Object Name. Otherwise you might get exceptions especially when the Object ID has a different value from Object Name. Alternatively you could use FindByName() method instead where available. Some objects like AttributePermission do not have FindByName() method and you have to use the Find() method by
passing the Object ID.

AttributePermission dimAttrPermission = dimPermission.AttributePermissions.Find(attribID);

However take note when passing MDX expressions use Object Names instead of Object ID.

2. Run AMO Code from SSAS Server

AMO methods are memory intensive. SSAS responds to commands in XMLA format. XMLA commands are issued to SSAS when Update () method is invoked. For performance reasons it is recommended to run AMO batch jobs from the SSAS server

3. Judicious use of Update() method

AMO operations are translated to XMLA command when the Update() method is invoked. So it is necessary to make judicious use of Update () method in your AMO code. For e.g. in this post – Using AMO to secure Analysis Service Cube , the overloaded GrantDatabaseRead() method invokes the Update () method only once at the end of the operation after read permission has been granted to ALL roles in the database instead of calling once for each role.

4. Cube Dimension Permission vs. Database Dimension Permission

By default, granting read permission to a cube implicitly grants read permission to all cube dimensions without the need to explicitly grant read access to related database dimensions. Only if you are defining dimension data access, then you need to explicitly grant read permission to the required database dimensions.

5. Grant Database Read Definition permission if using SSMS

Database Read Definition permission is usually not required to browse cube, however might be helpful for troubleshooting role permissions using SSMS.

6. Catch Exceptions

Apart from the generic AmoException, it is recommended to catch the following exception in the given order

  • OutOfMemoryException
  • ConnectionException
  • ArgumentNullException
  • OperationException
  • AmoException

7. Deploy with care

Changes to SSAS cube AFTER the roles and membership have been established must be carried out with care; otherwise you run the risk of wiping out the already existing roles, membership and cube security when deploying your latest cube. In particular if you are using SSAS Deployment Wizard, choose the option to Retain roles and members or Deploy Roles and retain members.

Related Posts

  • SSAS: Using AMO to Secure Analysis Service Cube
  • 5 thoughts on “SSAS: AMO Coding Tips

    1. Are you sure about point #1? Can you give an Adventure Works example? I believe that FindByName will either return the object you’re looking for or a null. (So you have to code for looking for the null.) GetByName will either return the object you’re looking for or throw an exception. But both functions, as far as I know, operate based upon the name. If you’re trying to look up something by ID, then you can just use the Item property.

      1. Thanks Greg. You are right. It should be Find() instead of FindByName() – post has been modified. I did find that not all collections had FindByName() method for e.g AttributePermission does not have FindByName() method and must use Find() to get the instance.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s