OBIEE - IndexCol Function - Handle hierarchy Level security

Obiee Ceim

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.

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.

Obiee Security User Indexcol

Create the session variable

select GEO_LEVEL from HIERARCHY_LEVEL where USER_NAME = ':USER'

Obiee Geo Level Session Variable

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.

Reference





Discover More
Obiee Ceim
OBIEE - Choose Function

This function takes an arbitrary number of parameters and returns the first item in the list that the user has permission to see. For example, a single query can be written to return security-based...
Obiee User Group Permission Filter
OBIEE - Data security (visibility of data)

This article is data security or authorization in OBIEE. Data-level security controls the visibility of data (content rendered in subject areas, dashboards, Oracle BI Answers, and so on) based on the...
Obiee Ceim
OBIEE - Functions

The home page of OBIEE functions of the logical sql and from BI Server. See Logical SQL Reference
Obiee Integer Format Wide Default
OBIEE - Integer Datatype

In OBIEE, the integer datatype: follows the rule of an integer division (without the decimal part) is mandatory for some OBIEE intern use such as the LOGLEVEL variable or the indexcol function ...
Obiee11g Saw Preference Currency
OBIEE 11G - Currency

the Currency box on the My Account dialog: Preferences tab the Currency Prompt option on the Definition pane of the Prompt editor. The currency value is stored in the system session variable. This...
Obiee11g Lookup Table
OBIEE 11G - Lookup Function

A lookup in OBIEE is performed: in 10g with the help of a outer of inner join in 11G with the lookup function In 11g, the lookup function was introduced in a localization perspective. It permits...



Share this page:
Follow us:
Task Runner