SYMPTOM
In projects set to use intersect (AND) security filters may see no data presented when logging in as a user who has two security filters on the same attribute.
STEPS TO REPRODUCE
In Strategy tutorial:
1. Enter project configuration>securityfilter>general and select intersect
2. Create a new user group and add a security filter for Northwest security filter
3. Create a new user group and add a security filter for South security filter
4. Create a report in web with profit and region
5. When running the report no data is returned:
CAUSE
While using "Union (OR) Security Filters on related attributes, intersect (AND) Security Filters on unrelated attributes" option, they get the following SQL, which is not equivalent to the above:
SELECT SALES
FROM FACT
WHERE REGION IN 'EMEA'
OR (REGION IN 'APAC')
AND STORETYPE IN '1')
In circumstances that the following SQL is desired:
SELECT SALES
FROM FACT
WHERE (REGION IN 'EMEA'
OR REGION IN 'APAC')
AND STORETYPE IN '1'
Using "Union (OR) Security Filters on related attributes, intersect (AND) Security Filters on unrelated attributes" option, the following SQL, which is not equivalent to the above is acheived:
SELECT SALES
FROM FACT
WHERE REGION IN 'EMEA'
OR (REGION IN 'APAC')
AND STORETYPE IN '1')
Unfortunately, there is no OOTB way to get the SQL they want and they would need to redesign the security filter to achieve this SQL.
WORKAROUND
Redesign the user group to have 3 groups with following security filter definition:
Group1 = SF1 (REGION IN 'EMEA')
Group2 = SF2 (REGION IN 'APAC')
Group3 = SF3 (STORETYPE IN '1')
Attach the user to all the 3 groups and keep "Union (OR) Security Filters on related attributes, intersect (AND) Security Filters on unrelated attributes" option. This way, the desired SQL should be achieved:
SELECT SALES
FROM FACT
WHERE (REGION IN 'EMEA'
OR REGION IN 'APAC')
AND STORETYPE IN '1'
ACTION
This issue was reviewed by our Technology team for feasibility and will not be scoped for a code fix in any upcoming Strategy release.
