11g Bug: Issue with equality operator on content filters

Share

I ran into an weird issue yesterday trying to troubleshoot data security for my current gig. What it came down to is the fact that for some reason the equality operator in the expression below wasn’t being resolved correctly by OBI:

LOWER( TRIM(“Data Warehouse”.””.”SCHEMA_NAME”.”ALS_BI_SEC_USER_DEPT”.”EMAIL_ADDRESS”)) = ( LOWER( TRIM( VALUEOF(NQ_SESSION.”USER”))))

Even after toying with :USER, applying the TRIM function to both sides of the expression and copying the physical SQL from the session log to my query tool and confirming that replacing the variable reference with my username would return the correct number of rows I still wasn’t getting any rows in the table I tried to filter.

The solution came to be modifying my content filter to look like the expression below, using the IN operator instead of an equality. This is definitely yet another bug in 11g.

LOWER( TRIM(“Focus Warehouse”.””.”DFOCUSBV”.”ALS_BI_SEC_USER_GROUPS”.”EMAIL_ADDRESS”)) IN ( LOWER( TRIM( VALUEOF(NQ_SESSION.”USER”))))

One thing to look for when a filter on a logical table source causes your queries to return no rows is to verify that you use the right operator in your expression. This happened to me when implementing data level security in OBIEE 11g…

Tags: , , , , , ,

Leave a Comment