We have a requirement to implement the data level security for a OBIEE setup with Essbase cubes integrated with it. Basically, the dashboards are having prompts built out of Oracle (relational) DB and the reports are built using Essbase cubes.
Based on the type of user login to OBIEE we would need to apply the data level security. For example: Lets have 2 users i.e., User 1 and User 2. Also, we have a Geography dimension in the model. The users should see data pertaining to their user geography only in the dashboard. To handle this, we have created a security table which will have the users and their geography association in the relational DB and have joined it with the Geography dimension. Data filter has been applied on this security table in RPD. With this approach, whenever we use the geography dimension in the prompt or in the report filter, the data is pre-filtered based on the user's access to their corresponding geography information.
However, when the user doesn't select any value in the prompt or it is set the 'All Column Values' then the geography dimension will not the considered in the report query and hence data security will not be applied. Another constrain is that we cannot directly apply the filter on the Fact table by forcing a inner join with dimension or security table since it is built out of Essbase cube source.
Please suggest any possible solution for the above issue. Also, please share the standard approach for data level security in case of OBIEE-Essbase intergration.