You can create dynamic filters based on external source data to reduce the number of filter definitions needed.
@datasourceLookupand the variables
$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.
This variable stores the value of the current logged in user at runtime. It can be used in conjunction with the
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.
This method fetches records from a Datasource.
@datasourcelookup (dataSourceName, columnName, columnValue, returnColumnName)
The name of the external Datasource defined in Essbase. For an application-level Datasource, prefix the name with the application name and a period.
The name of the Datasource column to search for a given columnValue.
The value to search for in columnName.
The name of the Datasource column from which to return a list of values.
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.
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.
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.
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.
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, using the Filters section of the database inspector.
- Define filter rows for each filter, using the dynamic filter syntax to employ the
$logingroupvariable, and the
@datasourcelookupmethod 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, 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.