IndexCol (Part 2)

The IndexCol feature of Oracle Business Intelligence is not limited to dimensions as discussed in the previous post, but extends to measures.

Let us start with a hypothetical example – you are building a repository for Sales Analysis for a large auto maker. While the number of cars sold, and the revenue generated are interesting by themselves, the requirements include a number of derived measures – Average Selling Price of a car, Growth rate since last year, Effective tax rate for each car sold and so on…

To make this more complicated, let us assume that this car manufactures has independent business units, run in Europe, Asia and U.S.A – with their own data marts. It is expected that all queries will be querying from one (and only one) of these data marts depending on the user logged on – but it is required that all BUs use similar definitions for the various metrics.

One (painful) way to implement this within OBI-EE would be to setup separate subject areas for each BU – and grant access to employee within that BU – only access to their own subject area. However, you would end up duplicating all of the derived measures.

A far more effective method of development is to map simple measures to the respective data marts – add a layer of IndexCols to do My Revenue – and then define derived metrics on that abstraction layer. For example,

Simple Measures:

• America.Revenue= sum(USA_Datamart.Revenue)
• Europe.Revenue= sum(Europe_Datamart.Revenue)
• Asia.Revenue=sum(Asia_Datamart.Revenue)

IndexCol Abstraction

• MyRevenue = IndexCol(VALUEOF(NQ_SESSION.BU), America.Revenue, Europe.Revenue, Asia.Revenue)

Finally, we can build our hierarchy of derived measures based on this one column. Here are some examples:

• LastYearSales = Ago(MyRevenue, 1, YEAR)
• Yearly Growth = (MyRevenue- AGO(MyRevenue, 1, YEAR))/Ago(MyRevenue, 1, YEAR)
• SalesForRedCars = FILTER(MyRevenue USING Color=’Red’)

Note that these measure now only need be created once – and will work equally well regardless of which BU you are in.

IndexCol

One of the features I am using in my current project is IndexCol, which is a really powerful feature that can save lots of metadata work, and provide extraordinary flexibility to the content you provide users.

The syntax for IndexCol is simply:

IndexCol(<Constant>, <Expr0>, <Expr1>, <Expr2> …).

The constant above can be a session variable – and usually will be. And that is where the power of this construct comes from.

Functionally, IndexCol just picks the nth Column based on the value of the constant – and so is equivalent to a case statement:

case <constant>

when 0  then Expr0

when 1 then Expr1

when 2 then Expr2

end

What makes this different from the case statement though, is that the BI server will evaluate the case at the time it receives the query – not when it executes it. This allows for significant gains in the actual queries that are executed. In the next few posts, I will try and cover the various cases in which this may be useful.

Employee Level Hierarchy

It is a common requirement to start out a report at the level of the user. For example, consider the hierarchy below:

CEO

COO

         VP1

         VP2

CFO

          VP3

CMO

           VP4

 

which is stored as:

 

Manager Level 0	Manager Level 1	Employee	Level of Employee
CEO	CEO	CEO	0
CEO	COO	COO	1
CEO	COO	VP1	2
CEO	COO	VP2	2
CEO	CFO	CFO	1
CEO	CFO	VP3	2
CEO	CMO	CMO	1
CEO	CMO	VP4	0

 

In the above table, when the CEO logs to on to see the revenue for all his reports – he wants to see the report:

Select ManagerLevel0, Sales from SubjectArea

However, when the COO logs on to see the same report – He would like to start out at his level – namely:

Select ManagerLevel1, Sales from SubjectArea

This is accomplished rather simply using IndexCol. One needs to create a derived column which represents the level of the user.

The report can then be written as:

Select MyLevel, Sales from SubjectArea

This will then automatically switch to the correct semantics depending on who logs on.

Two things that are noteworthy:

1. Drills work correctly. When the CEO is logged on, and he drills from his MyLevel, he will be taken to Level1….
2. Aggregate Navigation works correctly. i.e., if you have an aggregate at ManagerLevel1, this report will hit the aggregate where possible.

Both of the above would not have been possible if one uses the case statement equivalent.