Security Filter - Urgent

[cognos05]

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,

[bus_pass_man]

My approach to using security filters was to put the security filters into the security filters section of the dimension and fact tables.  I did not create relationships between anything in the model and the security lookup table.  They are a little hidden away (Edit the query subject and choose query information / options).

I dealt with the problem of needing to allow certain users access to everything in a dimension by having two parameter maps.  One would map the user identity to a access value (an example, sales regions, which included an object which I defined in the parameter map, which I called everywhere). I then created a second parameter map where I used those access values as the keys and had the actual filter expression.



The first parameter map I called Region members.  The second parameter map I called Regions.
I've sanitized my parameter maps and included a picture of them.

I then put a security filter.  This is this the relevant part for the dimension in question.  It shows how to nest a parameter map into another one in a macro.


[Business view].[Employee by region].[Region code] 

 #$Regions{$[Region members]{$account.personalInfo.userName}}#


You could use an expression such as not in ({some key value which does exist})  for your all access users.

[cognos05]

Hi Thanks for the suggestion ,

I am going to check what you have done, but i was thinking if i can modify my filter on fact somehow to work for people with all access.

So here is the problem i am having , so my security table and fact table is joine like below  for all possible user groups

( dwh_security.security_level ='Territory' AND vw_f_billings_all.ShipToTerritoryERPCode = dwh_security.security_value )
OR
( dwh_security.security_level ='Area' AND vw_f_billings_all.ShipToAreaERPCode = dwh_security.security_value )
OR
( dwh_security.security_level ='Agency' AND vw_f_billings_all.ShipToAgencyERPCode = dwh_security.security_value )
OR
( dwh_security.security_level ='District' AND vw_f_billings_all.ShipToDistrictERPCode = dwh_security.security_value )
OR
( dwh_security.security_level ='Region' AND vw_f_billings_all.ShipToRegionERPCode = dwh_security.security_value )

and then the filter on fact table is

(#sq(CSVIdentityNameList())# contains('System Administrators'))
OR
(#sq(CAMIDList())# contains [Database Layer].[dwh_security].[CAMID])



so now what happens is it works fine for users in security table and for example my id which is admin is not in securty group but is an admin
gives me the result which is 4 times the original result .

Any idea why would a join be created if my camid is not in security table and there should be no filter on fact right