About Filters

Filters control security access to data values in a cube. Filters are the most granular form of security available.

When you create a filter, you designate a set of restrictions on particular cube cells or on a range of cells. You can then assign the filter to users or groups.

Your own security role determines if you can create, assign, edit, copy, rename, or delete filters:

• If you have the Application Manager role, then you can manage any filter for any user or group. Filters do not affect you.
• If you have the Database Update role, then you can manage filters for the applications that you created.
• If you have the Database Manager role, then you can manage filters within your applications or cubes.
• If you have the Database Access role (default), then you have read access to data values in all cells, unless your access is further restricted by filters.

Create Filters

You can create multiple filters for a cube. If you edit a filter, modifications made to its definition are inherited by all users of that filter.

See Controlling Access to Database Cells Using Security Filters.

1. On the Applications home page, expand the application.
2. From the Actions menu, to the right of the cube name, launch the inspector.
3. Select the Filters tab.
4. Click Add .
5. Enter a filter name in the Filter Name text box.
6. In the Filter Editor, click Add
7. Under Access, click and use the drop-down menu to select an access level.

   • None: No data can be retrieved or updated

   • Read: Data can be retrieved but not updated

   • Write: Data can be retrieved and updated

   • MetaRead: Metadata (dimension and member names) can be retrieved and updated

     The MetaRead access level overrides all other access levels. Additional data filters are enforced within existing MetaRead filters. Filtering on member combinations (using AND relationships) does not apply to MetaRead. MetaRead filters each member separately (using an OR relationship).

8. Select the row under Member Specification and enter member names.

   You can filter members separately, or you can filter member combinations. Specify dimension or member names, alias names, member combinations, member sets that are defined by functions, or substitution variable names, which are preceded by an ampersand (&). Separate multiple entries with commas.

9. Create additional rows for the filter as needed.

   If filter rows overlap or conflict, more detailed cube area specifications apply over less detailed, and more permissive access rights apply over less permissive. For example, if you give a user Read access to Actual and Write access to Jan, then the user would have Write access to Jan Actual.

10. Click Validate to ensure that the filter is valid.
11. Click Save.

On the Filters tab in the inspector, you can edit a filter by clicking the filter name and making your changes in the Filter Editor.

You can copy, rename, or delete a filter by clicking the Actions menu to the right of the filter name and choosing an option.

After creating filters, assign them to users or groups.

Create Efficient Dynamic Filters

You can create dynamic filters based on external source data to reduce the number of filter definitions needed.

Instead of managing a set of hard-coded data-access filters for many users, you can filter access to cube cells from external source data, based on member and user names.

You do this using dynamic filter definition syntax, including the method @datasourceLookup and the variables $LoginUser and $LoginGroup. Your external source data is a csv file or a relational table. For relational source data, you can load the .csv to a relational table.

• Dynamic Filter Syntax
• Workflow to Create Dynamic Filters
• Example of a Dynamic Filter

Dynamic Filter Syntax

Use dynamic filter syntax to create flexible filters you can assign to multiple users and groups.

Filter rows can contain the following elements as part of their definition, in addition to member expressions.

$loginuser

This variable stores the value of the current logged in user at runtime. It can be used in conjunction with the @datasourcelookup method.

$logingroup

This variable stores the value of all the groups that current logged-in user belongs to. It includes both direct and indirect groups. When used in conjunction with the @datasourcelookup method, each group is individually looked up against the Datasource.

@datasourcelookup

This method fetches records from a Datasource.

Syntax

@datasourcelookup (dataSourceName, columnName, columnValue, returnColumnName)

Parameter	Description
dataSourceName	The name of the external Datasource defined in Essbase. For an application-level Datasource, prefix the name with the application name and a period.
columnName	The name of the Datasource column to search for a given columnValue.
columnValue	The value to search for in columnName.
returnColumnName	The name of the Datasource column from which to return a list of values.

Description

A @datasourcelookup call is equivalent to the following SQL query:

select returnColumnName from dataSourceName where columnName=columnValue

@datasourcelookup looks up the given Datasource and searches for records where columnName contains columnValue. If you specify columnValue as $loginuser, this method will search for records where columnName contains the name of the currently logged in user.

Essbase forms the filter definition row by combining the list elements as a comma-separated string. If any record contains special characters, spaces, or only numbers, they are enclosed in quotation marks.

Examples

Enclose the parameters within quotation marks.

The following call looks up a global Datasource, and returns a list of store names where Mary is the store manager.

@datasourceLookup("StoreManagersDS","STOREMANAGER","Mary","STORE")

The following call looks up an application-level Datasource, and returns a list of store names where the currently logged in user is the store manager.

@datasourceLookup("Sample.StoreManagersDS","STOREMANAGER","$loginuser","STORE")

The following call looks up an application-level Datasource, and returns a list of store names where the store department matches any of the groups to which the logged in user belongs.

@datasourceLookup("Sample.StoreManagersDS","STORE_DEPARTMENT","$logingroup","STORE")

If the logged in user belongs to 3 groups, then the above @datasourcelookup method returns all the matching column values for each group.

Workflow to Create Dynamic Filters

Use the following general workflow to create dynamic filters.

This dynamic filters workflow assumes you already have a cube, and have provisioned users and groups.

1. Identify a source of data, whether it is a file or a relational source.
2. Define the connection and the Datasource in Essbase, either globally or at the application level.
3. Create filters at the cube level, using the Filters section of the database inspector.
4. Define filter rows for each filter, using the dynamic filter syntax to employ the $loginuser variable, the $logingroup variable, and the @datasourcelookup method as needed.
5. Assign the filters to users or groups.
6. If you assigned the filter to a group, assign the group to the application to be filtered, using the Permissions section of the application inspector.

Example of a Dynamic Filter

The following dynamic filter works with the cube called Efficient.UserFilters, available in the gallery as a sample template.

To learn how to create and apply this dynamic filter, download the workbook template, Efficient_Filters.xlsx, from the Technical section of the gallery, and follow the README instructions in the workbook. The gallery is available in the Files section of the Essbase web interface.