COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Framework Manager => Topic started by: cognos05 on 25 Dec 2020 09:23:24 pm

Title: FM Security -Issue
Post by: cognos05 on 25 Dec 2020 09:23:24 pm
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,



Title: Re: FM Security -Issue
Post by: cognos05 on 04 Jan 2021 09:01:22 pm
Other than camidlist what other generic elements or functions  we have to get dynamic security in cognos for a user.