The INDEXCOL function helps to select the appropriate column and behave as the IF THEN structure.
It can then use external information to return the appropriate column for the logged-in user to see.
The function choose can do also the same job but in a other way
It's a common function to handle hierarchy Level security. Company ABC has a geography dimension with the hierarchy Country of State, City. The CEO can access the Country level down to the City level, and the sales manager can access the State and City levels, and the sales people can only access the City level.
The Oracle BI Server handles this function in the following ways:
The query cache will use the resulting translated expression for cache hit detection
When the first argument to IndexCol is a session variable and if a default expression is expected to be returned even if the init block fails, then the Oracle BI Administrator should set a default value for the session variable. Otherwise, the query will fail because the session variable has no value definition.
IndexCol( integer literal, expr1, expr2, ... )
The IndexCol function takes in an integer literal value as its first argument, followed by a variable length expression list and translates to a single expression from the expression list. The literal value is the 0-based index of the expression in the expression list to translate to.
Consider the following expression:
IndexCol( integer literal, expr1, expr2, ... )
If the literal value is 0, the above expression is the same as expr1. If the literal value is 1, then the value is the same as expr2, and so on.
Example made with the sh schema.
The company has a geography dimension with the hierarchy Country of State, City.
The CEO can access the Country level down to the City level but the sales manager can access only the State and City levels.
CREATE TABLE HIERARCHY_LEVEL
(
USER_NAME VARCHAR2(30 BYTE),
USER_DESCRIPTION VARCHAR2(30 BYTE),
GEO_LEVEL NUMBER(1) DEFAULT 5
)
GEO LEVEL on Oracle must be NUMBER(1) as the data type become a INT in OBIEE
And full it in with this values :
USER_NAME | GEO_LEVEL |
---|---|
CEO | 0 |
manager | 1 |
people | 2 |
Create the OBIEE users with the same user names.
Case sensitive : User CEO is not the same that the user ceo.
select GEO_LEVEL from HIERARCHY_LEVEL where USER_NAME = ':USER'
IndexCol( VALUEOF( NQ_SESSION.GEOGRAPHY_LEVEL ), Country, State, City )
With the amount sold.
The Ceo logs in and IndexCol translates to the Country column because the GEOGRAPHY_LEVEL session variable is 0. He will get the same result and be able to drill down on Country to State as if he had used SELECT Country, Revenue from Sale.
The manager logs in and IndexCol translates to the State column because the GEOGRAPHY_LEVEL session variable for Jackson is 1. He will get the same result and be able to drill down on State to City as if he had used SELECT State, Revenue from Sales.
The people logs in and IndexCol translates to the City column because the GEOGRAPHY_LEVEL session variable for Mike is 2. He will get the same result and won't be able to drill down on City as if he had used SELECT City, Revenue from Sales.