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.