[OBIEE 10g] IndexCol Functions

OBIEE - IndexCol Function

The INDEXCOL function helps to select the appropriate column and behave as the IF THEN structure.

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.

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.

NOTE:  expr1,expr2,..exprn are SAME  DATA-TYPE;  if not same data type, we will casting data type

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

)

Note : GEO LEVEL on Oracle must be NUMBER(1) as the data type become a INT in OBIEE

Create the OBIEE user

Create the OBIEE users with the same user names.

Create logical column in BMM layer

write column formula

IndexCol( VALUEOF( NQ_SESSION.GEOGRAPHY_LEVEL ), Country, State, City )

Note: Here country ,state ,city are same data type

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.