Essbase Security Using MaxL

About 3 years ago I had a client whose entire Hyperion environment crashed and burned to smithereens due to some hardware issues.  Because of a good backup process, we were able to get the environment back up and running in about 8 hours, but one thing that didn’t get restored correctly was the security.  They had a large ASO cube that had many users worldwide.  Unfortunately, Shared Services was in a vegetative state and we were not able to recreate the security using Shared Services.  However, I was able to recreate the filters, groups and group users using MaxL.

This little MaxL option has resurfaced as a great way to do security on a current app I am working with.  It is also a large ASO cube that has ~5400 filters on ~100 groups for ~400 users worldwide.  There was no way I was doing that by hand.  It is fun to note that I was able to create the groups and add users to groups for HFM via the same process as it shares with Essbase in Shared Services.  Since the syntax is the same, I created an Excel spreadsheet that created the scripts for me.  Saved me hours or days of work!

Below are some examples of the scripts I used.

Creating a group:
CREATE OR REPLACE GROUP ‘Midwest_Read’;

Creating a write filter for Essbase:
CREATE OR REPLACE FILTER Sample.Basic.’FilterName’ WRITE on ‘”Ohio”‘;

Creating a write filter for IDESCENDANTS of a member:
CREATE OR REPLACE FILTER Sample.Basic.’FilterName’ WRITE on ‘”Ohio”‘, READ on ‘@IDESCENDANTS(“Midwest”)’;

Creating a write filter to multiple members while excluding another member:
CREATE OR REPLACE FILTER Sample.Basic.’FilterName’ WRITE on ‘”Ohio”, @REMOVE(@RELATIVE(“Midwest”,0),@LIST(“Indiana”)’;

Granting filtered access to a group:
GRANT FILTER App.Db.’FilterName’ to ‘Midwest_Read’;

You can also do more with excluding members, cherry picking members, etc.  Info is in the DBAG.

Enjoy!

One comment

  1. MaxL was a godsend for security administration. We actually built a very tedious and fragile WInRunner script to automate the group and filter creation for 500 users in 6 databases before we could properly automate it. Eventually we constructed a self-service web application to let our clients identify the outline members to which they needed access. Once the security requests were approved, they would be picked up and processed by a recurring server side script that dynamically built and executed the corresponding MaxL scripts.

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