Hi Experts,
I am new to FM modeling and mostly work on cubes . Now i have a model with fm and having issues making the dynamic security . I figured out most part but stuck with one last piece.
I am explaining my current use case and any assistance is highly apreciated,
I have a star schema like model with dimension and facts . Cognos security is driven from sales force , so we have a table from sales force which is formatted liek below format.
CamiD SecurityLevel Security Value
ABC GroupLevel1 Group1Data
BCD GroupLevel2 Group2Data
Now in my fm model i create link bewtween my securtiy table and my fact table and have the below join condition defined (1:N from security table to fact table)
( dwh_security.security_level ='GroupLevel1' AND vw_f_billings_all.Group1ERPCode = dwh_security.security_value )
OR
( dwh_security.security_level ='GroupLevel2' AND vw_f_billings_all.Group2ERPCode = dwh_security.security_value )
Once I have this condition , now i have to filter on my fact
I think this where I am stuck and not sure how to make different or
(#sq(CAMIDList())# contains [Database Layer].[dwh_security].[CAMID])
so I am saying if grouplevel1 user logs in then filter all data with that grouplevel1 or in this case whoever is in that camidsecurtiy table .
Now say for example ,I want to give access to some suer groups whose not part of the security table and they should see everything , how can i change the filter on fact table to allow the link on security as well as show everything for few predefined groups in cognos which is not in there like admins etc...
Any help here is appreciated , I need this one piece to work to publish this model .
Thanks,