Oracle BI 11g: Verifying Logical Table Sources in Analyses

Inspired by a nice instance of the Oracle BI 11g Build Repositories class I delivered last week as an LVC, I decided to share a little trick with you.

What if we could verify the name of the logical table source in Answers (aka "Analysis Editor")? For example, when you create multiple table source for aggregates or partitioned data, it is important to verify your implementation.

I found the following procedure to be quite helpful with that.

Create a new logical column that displays the name of the logical table source.

Within your logical dimension or fact, create a new logical column and map it (physically) in each table source. As the expression use a string identifying the logical table source. Just as in the screenshot below.

Click to enlarge.

In the above example I specified 'LTS2 F2 Revenue Aggregate' (the exact name of the LTS) as the constant value for the new column. This specific LTS is mapped to an aggregate fact table at the quarter level. In the other logical table sources, I do the same but of course using the name of the respective LTS.

Next, I exposed the new column in a separate presentation table, just to make it easier to control the visibility of these 'developer tools'.

After deploying the RPD, we can now expose the 'LTS column' in our analyses.

Click to enlarge.

The above analysis shows the revenue per quarter (within the boundaries of the aggregate table) and the LTS column proves that the LTS for the aggregate table is used.

Next is the same analysis after a drill down to the month level (not available from the aggregate table).

As we can see, the LTS column displays the name of the LTS that maps to the detail table.

Summary

Knowing which LTS(s) are used while testing OBIEE is adding more Intelligence to your BI.

have a nice day

@lex