Use these steps to specify a functional group for application roles with different data access filters on the same repository object, usually a logical fact table.
When there are no functional groups defined, all the security filters applied to a given table, regardless of the associated role, and are combined using the OR operator. Using the OR operator works in most cases because a user can view a union of all the rows selected by the security filters. For example, consider the following filters:
Role A is assigned the filter, Product = 'Coke'
Role B is assigned the filter, Product = 'Pepsi'
If a user is given Role A and Role B, then the user can view data for both the Coke and Pepsi products.
When the two security filters from the same table are combined in the query, the filter conditions are combined using the OR operator, this is appropriate for most security filters defined on dimension tables, for example:
Product = 'Coke' OR Product = 'Pepsi'
Using functional groups are necessary is when securing a single fact table, using data filters from different dimensions.
In this example, a fact table is secured using the following filters:
Role A is assigned the filter, Product = 'Coke'
Role B is assigned the filter, Product = 'Pepsi'
Role C is assigned the filter, Region = 'Southwest'
If you do not use functional groups, a user with roles A, B, and C would have all three filter conditions combined in the query using the OR operator, for example:
(Product = 'Coke' OR Product = 'Pepsi' OR Region = 'Southwest')
Combining the results of Role A, B, and C does not make sense because Product and Region are independent dimensions. Combining data filters from different dimensions using OR operator provides the user access to more data values than the user should view.
In this example, the user can see data for all products within the Southwest region as well as data for all regions within the Pepsi and Coke products.
To get the expected behavior, that is allowing the user to see data only for the Pepsi and Coke products within the Southwest region, you need to change the filter to combine the product filters with the region filter using the AND operator, for example:
(Product = 'Coke' OR Product = 'Pepsi') AND (Region = 'Southwest')
To achieve this using functional groups, assign the security filters to functional groups as follows:
Role A is assigned the filter, Product = 'Coke' with functional group "Product"
Role B is assigned the filter, Product = 'Pepsi' with functional group "Product"
Role C is assigned the filter, Region = 'Southwest' with functional group "Region"
All the filters in the same functional group are combined using the OR operator and all sets of filters in different functional groups are combined using the AND operator. By choosing the functional groups associated with each security filter, you can control how the filters are combined using the OR and AND operators.
To create a data filter, see Setting Up Data Filters in the Repository.