Essbase Security Filter Samples via Maxl

Here are some examples of other types of filters you may need to create:


/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget"';


/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & LEVEL 0 MARKET */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget", @LEVMBRS("Market",0)';




/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & LEVEL 0 EAST */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget", @RELATIVE("EAST",0)';

The @RELATIVE function allows you to take any member within the hierarchy and get an associated level of that member. So we are able get all Level 0 members of East without having to take all Markets



/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & LEVEL 0 EAST (except NY) */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget", @REMOVE(@RELATIVE("EAST",0),@LIST("New York")';



If you manually created each filter in Essbase Administration Services, you would put a READ statement on line 1 and a WRITE statement on Line 2. Well, with MAXL, simply seperate the lines with commas and put the actual filter in single quotes, like this:
/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget"', READ on '@IDESCENDANTS("Scenario")';



Let's say you have data in the Sample.Basic Essbase cube, but don't want a given set of users to have access to it, you have two options: You can give them no access to that member or you can actually remove that member from the users profile (meaning, if they do a member selection, the will not even see that the member exists in the cube)

/*-------------------------------------------------------------------------------------------------------------*/
/* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' NONE on '"Budget"', READ on '@IDESCENDANTS("Scenario")';

or

/*-------------------------------------------------------------------------------------------------------------*/
/* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' NONE on '"Budget", @REMOVE(@IDESCENDANTS("Scenario",0),@LIST("Budget")';

The two examples above will give the end user a NOACCESS when doing a retrieval. The below example will give the user a "Unknown Member" Error message when doing a retrival since you are removing that member from the database for that user (even though the member is still there):

/*-------------------------------------------------------------------------------------------------------------*/
/* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' READ on '@IDESCENDANTS("Scenario")', META_READ on '@REMOVE(@IDESCENDANTS("Scenario",0),@LIST("Budget"));