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.
To edit a filter in the Redwood Interface, locate the Filters tab by selecting the cube and then selecting the Customization page. Then, edit the filter by clicking the filter name and making your changes in the filter editor. To edit an existing row, double click in that row.
To edit a filter in the Classic Web Interface, go to the Filters tab in the inspector, and edit the 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.
@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
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.
- Identify a source of data, whether it is a file or a relational source.
- Define the connection and the Datasource in Essbase, either globally or at the application level.
- Create filters at the cube level,
- In the Redwood Interface, navigate to the cube, choose Customization and then choose Filters.
-
In the Classic Web Interface, use the Filters section of the database inspector.
- 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. - Assign the filters to users or groups.
- If you assigned the filter to a group, assign the group to the
application to be filtered,
- In the Redwood Interface, navigate to the application, choose Customization and then choose Permissions.
- In the Classic Web Interface, use 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.
![Dynamic filter with three rows granting metaread access to the logged in user. Dynamic filter with three rows granting metaread access to the logged in user.](img/metaread_filter.png)
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.