OBIEE - IndexCol Function - Handle hierarchy Level security
About
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:
- ODBC Procedures : NQSGetLevelDrillability and NQSGenerateDrillDownQuery return the context-specific drill-down information based on the expression translated from IndexCol. This applies to both IndexCol expressions specified in the logical SQL query and IndexCol expressions specified in a derived logical column.
- Query Log and cache. The logical SQL with IndexCol function appears in the SQL string in the query log. But the logical request will not show the IndexCol function because Oracle BI Server will translate IndexCol to one of the expressions in its expression list in the logical request generator.
The query cache will use the resulting translated expression for cache hit detection
- Usage Tracking. Usage tracking will insert the logical SQL query string with the IndexCol function.
- Security. As long as the user has the privileges to access the column(s) in the expression translated from IndexCol, then the query will execute.
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.
Articles Related
Syntax
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 With Hierarchy Levels
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 the table
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 user
Create the OBIEE users with the same user names.
Case sensitive : User CEO is not the same that the user ceo.
Create the session variable
select GEO_LEVEL from HIERARCHY_LEVEL where USER_NAME = ':USER'
Create a column formula
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.