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.