Query Generation in OBIEE server

OBIEE Server will sometimes generate queries that look like this:

NVL(“Column1”,’q’)=NVL(“Column2”,’q’) and NVL(“Column1”,’z’) =NVL(“Column2”,’z’)

which seems a bit redundant – both the conditions are the same except for the constant q and z…

The reason both parts of this condition are required is to guard against “Column1” or “Column2” having the values ‘q’ or ‘z’ as legitimate values. Let us do a truth table:

 

Truth Table for NVL(“Column1”,’q’)=NVL(“Column2”,’q’)

	NULL	q	z	foo	bar
NULL	True	True
q	True	True
z			True
foo				True
bar					True

 

Note the values are true when Column1=’q’ and Column2 is NULL – or vice versa.

Truth Table for NVL(“Column1”,’z’)=NVL(“Column2”,’z’)

	NULL	q	z	foo	bar
NULL	True		True
q		True
z	True		True
foo				True
bar					True

This can be true if either column is ‘z’ and the other is NULL.

However, if we and the two, we get:

Truth Table for NVL(“Column1”,’q’)=NVL(“Column2”,’q’) and NVL(“Column1”,’z’)=NVL(“Column2”,’z’)

	NULL	q	z	foo	bar
NULL	True
q		True
z			True
foo				True
bar					True

 

which is equivalent to the much more readable:

“Column1”=”Column2” or (“Column1” is null and “Column2” is null)

In fact, the latter formulation is what OBIEE used to generate for these joins in previous versions.

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.

Version Control

OBIEE – provides a strong multi-user environment that you should use if you are building repositories in large teams. It allows segmentation of the metadata into projects – which are divided up by areas of responsibility – and so can can make development more modular and pleasant.

Sometimes, though, you may not have a large repository and/or a team, and may want to use a version control system instead. You may want to do this for managing complex branching needs, or for continuous integration of the rpd with quality checks.

In my current project, I am using TortoiseSVN, and I will demonstrate how to integrate rpd development into that control system, using admintool automation commands.

To begin with you will need the following python scripts to be installed in a known location say c:\. They convert the file-based admintool automation commands to the corresponding command line versions.

Script 1 – compare.py

import os, sys, tempfile, subprocess, time
import msvcrt
msvcrt.setmode(sys.stdout.fileno(), os.O_BINARY)

original=sys.argv[1]
modified=sys.argv[2]

cmd=tempfile.mktemp()
f=file(cmd, 'w')
f.write("Open "+original+" Administrator tc\n")
f.write("Compare "+modified+" Administrator tc\n")
f.close()

p=subprocess.Popen(r"c:\oraclebi\server\bin\admintool.exe -command "+cmd)  

-------------------------------------------------------------

Script 2 – merge.py

import os, sys, tempfile, subprocess, time
import msvcrt
msvcrt.setmode(sys.stdout.fileno(), os.O_BINARY)

original=sys.argv[1]
modified=sys.argv[2]
current=sys.argv[3]

dest=sys.argv[4]
cmd=tempfile.mktemp()

print cmd
f=file(cmd, 'w')
f.write("Open "+current+" Administrator tc\n")
f.write("Merge "+original+" "+modified+" Administrator tc Administrator tc\n")
f.write("SaveAs "+dest+"\n")
f.close()

p=subprocess.Popen(r"c:\oraclebi\server\bin\admintool.exe -command "+cmd)  

while (True):
    try:
        time.sleep(10)
        f=open(dest,'rb')   
        if f:
            time.sleep(10)
            os._exit(0)
    except IOError:
        time.sleep(5)

print original, modified, current

---------------------------------------------------------------

Now all that remains is to tell your source control to use these scripts for compare and merge respectively. This is how I did this on Tortoise:

 

Go to the settings page and Click Diff Viewer:

Click the Advance button and then Add. In the Extention of mim-type box – enter .rpd, and in the external program exnter the following:

<Path_TO_PYTHON>/python.exe c:\compare.py

Similarly, go to the merge tool, and add the .rpd extension:

Once you have configured this correctly, you will be able to use admintool to compare version straight from source control.

For example:

brings up admintool, with the comparison between the two repositories: