1. Database Administrator's Guide for Oracle Essbase
  2. Control Access to Database Cells Using Security Filters
  3. Create Filters

Create Filters

You can create a filter for each set of access restrictions you need to place on Essbase database values. You need not create separate filters for users with the same access needs. After you have created a filter, you can assign it to multiple users or groups of users.

Note:

If you use a calculation function that returns a set of members, such as children or descendants, and it evaluates to an empty set, the security filter is not created. An error is written to the application log stating that the region definition evaluated to an empty set.

Before creating a filter, perform the following actions:

  • Connect to the server and select the database associated with the filter.

  • Check the naming rules for filters in Limits.

See Create Filters.

To create a filter, you can also use the create filter MaxL statement.

Filtering Members Versus Filtering Member Combinations

Access to Essbase database values can be controlled by filtering entire members, or by filtering member combinations. Filtering members separately affects whole slices of data for those members. Filtering member combinations affects data at the member intersections.

Figure 30-1 How Filters Affect Data AND/OR Relationships


This image shows two cubes: the left cube illustrates the impact of OR relationships; the right cube illustrates the impact of AND relationships.

Note:

Filtering on member combinations (AND relationship) does not apply to metaread. Metaread filters each member separately (OR relationship).

Filter Members Separately

To filter all the data for one or more Essbase outline members, define access for each member on its own row. Filter definitions on separate rows of a filter are treated with an OR relationship.

For example, to block access to Sales or Jan, assume that user KSands is assigned the following filter:

  • Access: None. Member specification: Sales.

  • Access: None. Member specification: Jan.

The next time user KSands connects to Sample Basic, her spreadsheet view of the profit margin for Qtr1 shows that she has no access to data values for the member Sales or the member Jan, which are marked with #NOACCESS. All data for Sales is blocked from view, as well as all data for January, inside and outside of the Sales member. Data for COGS (Cost of Goods Sold), a sibling of Sales and a child of Margin, is available, with the exception of COGS for January.

Figure 30-2 Results of Filter Blocking Access to Sales or Jan


This images shows a spreadsheet in which cells blocked by filters are marked with #NOACCESS.

Filter Member Combinations

To filter data for Essbase member combinations, define the access for each member combination using a row in Filter Editor. In filter definitions, two member sets separated by a comma are treated as union of those two member sets (an AND relationship).

For example, assume that user RChin is assigned the following filter: Access: None. Member specification: Sales, Jan.

The next time user RChin connects to Sample Basic, her spreadsheet view of the profit margin for Qtr1 shows that she has no access to the data value at the intersection of members Sales and Jan, which is marked with #NoAccess. Sales data for January is blocked from view. However, Sales data for other months is available, and non-Sales data for January is available.

Figure 30-3 Results of Filter Blocking Access to Sales, Jan


This images shows a spreadsheet in which Sales data for Jan is blocked for the user.

Filter Using Substitution Variables

Essbase substitution variables enable you to manage information that changes regularly. Each variable has an assigned name and value. A Database Manager can change the value any time. When a substitution variable is specified in a filter, the value at the time the data is accessed is applied.

For example, if you want a group of users to see data only for the current month, you can set up a substitution variable named CurMonth and define a filter (MonthlyAccess) wherein you specify access, using &CurMonth for the member name. Using an ampersand (&) at the beginning of a specification identifies it as a substitution variable instead of a member name to Essbase. Assign the MonthlyAccess filter to the appropriate users.

Each month, you need to change only the value of the CurMonth substitution variable to the member name for the current month, such as Jan, Feb, and so on. The new value will apply to all assigned users.

See Implement Variables for Changing Information.

Filter with Attribute Functions

You can use filters to restrict access to Essbase data for base members sharing a particular attribute. To filter data for members with particular attributes defined in an attribute dimension, use the attribute member in combination with the @ATTRIBUTE function or the @WITHATTR function.

Note:

@@ATTRIBUTE and @WITHATTR are member set functions. Most member set functions can be used in filter definitions.

For example, assume that user PJones is assigned this filter: Access: None. Member specification: @ATTRIBUTE("Caffeinated_False").

The next time user PJones connects to Sample Basic, his spreadsheet view of first-quarter cola sales in California shows that he has no access to the data values for any base dimension members associated with Caffeinated_False. Sales data for Caffeine Free Cola is blocked from view. Note that Caffeine Free Cola is a base member, and Caffeinated_False is an associated member of the attribute dimension Caffeinated (not shown in the spreadsheet view).

Figure 30-4 Results of Filter Blocking Access to Caffeine-free Products


This images shows a spreadsheet in which Sales data for Caffeine Free Cola is blocked for the user.

Metadata Filters

Metadata filtering provides data filtering and an additional layer of security. With metadata filtering, an Essbase administrator can remove outline members from a user's view, providing access only to those members that are of interest to the user.

When a filter is used to apply MetaRead permission on a member:

  1. Data for all ancestors of that member are hidden from the filter user’s view.

  2. Data and metadata (member names) for all siblings of that member are hidden from the filter user’s view.

Dynamic Filters

You can create dynamic Essbase security filters based on external source data, to reduce the number of filter definitions needed. You do this using dynamic filter definition syntax, including the method @datasourceLookup and the variables $LoginUser and $LoginGroup.

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,
    • 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.

  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,
    • 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.

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.