We are Replacing HR analytics reports from the following system: i.e. Cognos to OBIEE. Currently we are planning to implement the data level security in OBIEE taking Cognos row level security as Reference. Right now in Cognos, the Hr modules access(i.e.Turnover Access,Census Access,Staffing,Workforce Efficiency) is stored in a table for each employee wise. In this table, they are storing the access for each employee in column wise. Let's employee
User Turnover Access Census Access Staffing
501978504 ((( [Physical Layer].[PERSON_SEC] ((1=2)) ((1=1))
[CNTRY_REGION] = 'EMEA' AND
So in cognos they are using session parameters in fact tables associated with the particular module. So my question is how do we convert the above same code used in Cognos to our BI Logic. Client has asked us to use the same logic implemented in cognos.
I am trying this approach: First we are populating data into our datawarehouse tables same as the cognos code i.e maintaining the module wise access for each employee. We are changing the ((( [Physical Layer].[PERSON_SEC] [CNTRY_REGION] = 'EMEA' AND [Physical Layer].[PERSON_SEC].[SBG]= 'JPT'))) to our BI code to like this "Oracle Data Warehouse"."Catalog"."dbo"."W_GEO_COUNTRY_D"."Country_code" = 'EMEA' and "Oracle Data Warehouse"."Catalog"."dbo"."W_INT_ORG_D".SBG='JPT'.-----WF_Turnover_access.
So in our OBIEE we are using three session variables for applying data level security.
Firstly we are checking the employee id and then if the employee exists then provide the access applicable with respect to that module. Suppose we have few reports built on WC_TURNOVER_F, here we are applying the data level filter at the content level of the fact. In the content we r using this formula case when valueof(NQSession.Login)= valueof(NQSession.user) then valueof(NQSession.WF_Turnover_access)End .
But this approach didn't work. User is able to see all the modules Reports. So in this regard i would like to know whether our approach is correct or wrong.