Performance impact on Row level security - Is there a way to force Cognos ....

[siyad]

Hi All,

As one of the expected performance impact after adding the secuirty filter at the row level of the Dimension. As the security is based on a granular level column in the dimension (eg: Organization, which have business unit, country, state, cost centre), Cognos pulls the granular level data from the Oracle table when you just wants to select all countries and applying the security (and distinct) by itself as well( eg query :- select country, cost centre from D_Org). However if i use the same query without the security filter on , Cognos sends the query to bring only the countries from the table(select distinct country from D_Org). This is the same case when I drag any measures into cognos report, the secured package brings all the data at the cost centre level and aggregates itself by Cognos. But the same report without security filter sends an efficient query by using the Oracle OLAP function to aggregate the data and it works faster. I am looking to improve the performance with secured model and anybody can help for the following 2 queries

1. Is there a way to force the Cognos to add the security filter on the query before sending to Oracle. some thing like Select country from org where cos centre in ('a', 'b'), instead of bringing all rows to Cognos

2. For top level aggregations, Is there a way to create any rules to take the data from different aggregate tables based on the security level. I am looking to create different aggregate tables for different level of users in Organization heirarchy

In case, any other suggestions would be appreciated 

Thanks in advance
Siyad

[IceTea]

2. For top level aggregations, Is there a way to create any rules to take the data from different aggregate tables based on the security level. I am looking to create different aggregate tables for different level of users in Organization heirarchy

This can be done with dynamic cubes.

[Rency]

For Perfomance improvement , its suggested that you can create multiple aggregate tables as same as your organization Hierarchy level . create different reports on each level and use drill through to  navigate to the detail level . Also you can aggregate the data on Time hierachy  , Year , Qtr <month ,Date  like wise which also serve the purpose .

[siyad]

Thanks for all your comments