Hi Experts,
I am facing a security issue where my fact rows are some times doubled. I am explaining my usecase here and any suggestions is greatly appreciated .
My fact table has some fact data and few additional columns for security on fact table
Ex:
Sales TerritoryID AreaID District ID Agency Region
1000 Enday123 60021 40010 Enday 001
So i have five levels of security group one for territory , Area,district,agency and region
So user can belong to any of this level and my fact gets filtered on the column based on their security level .
So I have a filter on my fact table that refers to my column in security table refering the camid
[Database Layer].[dwh_security].[CAMID] in (#csv(strip(split(',',camidlist())))#)
Then I have a security table which looks like
CamiD securitylevel security value
cam123 district 40010
cam234 Area 60021
Now the relationship between my security table and fact table is defined as
( dwh_security.security_level ='Territory' AND vw_f_billings_all.Ship-To Territory ERP Code = dwh_security.security_value )
OR
( dwh_security.security_level ='Area' AND vw_f_billings_all.Ship-To Area ERP Code = dwh_security.security_value )
OR
( dwh_security.security_level ='Agency Partner' AND ( vw_f_billings_all.Ship-To Agency ERP Code = dwh_security.security_value OR vw_f_billings_all.D00 Agency ERP = dwh_security.security_value ) )
OR
( dwh_security.security_level ='District' AND vw_f_billings_all.Ship-To District ERP Code = dwh_security.security_value )
OR
( dwh_security.security_level ='Region' AND ( vw_f_billings_all.Ship-To Region ERP Code = dwh_security.security_value OR vw_f_billings_all.D00 Region ERP = dwh_security.security_value ) )
So now what happens based on the camid of the user who logins it does a join with security table and fac table and filters the corresponding data .
Everything works fine now , but when a User is part of Area and a Territory which is 2 groups with similar data , then it doubles the fact rows because of the join .
So I am not sure how to resolve this issue when a user belongs to more than one group , my fact can just do a distinct on the result fact rows, not sure how do i specifiy that in fm relation or a more straight forward approach to achieve the right result.
I would like to apply same in data modules as well .
Any help is appreciated on this.
Thanks,
Other than camidlist what other generic elements or functions we have to get dynamic security in cognos for a user.