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 Oracle Analytics Cloud – Essbase web interface.