Oracle BI EE 10g/11g - Querying Directly on the BMM layer - SELECT_BUSINESS_MODEL

Though we are now almost 7 months after the 11g release, there still are some hidden gems in 10g that sometimes seem to come up time and again(atleast for me). This week, i chanced upon a method of querying the BMM layer directly while working on one of our utilities(somehow this does not seem to be documented anywhere - or did i miss it). If you have read my posting here, i have shown how 11g now uses a clause called as SELECT_PHYSICAL to go directly against the physical layer. This is something new that has been introduced in 11g. But so far i wasn't aware of a 10g supported clause called as SELECT_BUSINESS_MODEL that can query the BMM layer. This still works in 11g as well.

So, theoretically SELECT_BUSINESS_MODEL clause allows us to use RPD functions (that are not exposed in Answers) directly from the UI layer. For example, lets take the simple 10g repository as shown below. There basically are 2 presentation layer objects (SH1 and SH2) derived out of a single BMM layer SH.

As you see SH1 contains 2 dimensions (Channels & Products) and SH2 contains 2 other dimensions (Channels & Times). The only common dimension used is Channels. Using Logical SQL SELECT the only way to report on Channels, Products, Times & Sales together is to fire a logical SQL on SH1 and then fire another on SH2, then combine them together through an outer logical SQL. This not only is cumbersome but also will result in BI Server doing in-memory stitch joins (also there will be 2 physical sqls generated). Of course, the easiest workaround is to include all the dimensions in a single subject area. This is not always possible in production environments where there are strict policies on how many subject areas can be made available.

With SELECT_BUSINESS_MODEL, we can report directly on the BMM layer. The security etc that are all defined within the repository will still be honored. For example, the query below will produce data even though we did not have a single subject area containing all the dimensions.

SELECT_BUSINESS_MODEL
"CHANNEL_CLASS",
"PROD_CATEGORY_DESC",
"CALENDAR_YEAR",
"AMOUNT_SOLD"
FROM
"SH"."Channels",
"SH"."Products",
"SH"."Times",
"SH"."Sales"

The idea is to use the Logical column/table names instead of the presentation object names. Also, this paves way for us to use the RPD functions. If you think about this, from a BI EE logical layer standpoint, we should be able to use RPD functions and Answers functions interchangeably(the more you think about it, it makes more sense as the Function UI in Answers did not even exist in Siebel days). To test our theory, lets take the example of INDEXCOL function. This function does not exist in Answers but exists only within the RPD. So, lets first test whether this function works using SELECT_BUSINESS_MODEL clause.

SELECT_BUSINESS_MODEL
INDEXCOL(1,"CHANNEL_CLASS","PROD_CATEGORY_DESC") as Test,
"CALENDAR_YEAR",
"AMOUNT_SOLD"
FROM
"SH"."Channels",
"SH"."Products",
"SH"."Times",
"SH"."Sales"

So far so good. INDEXCOL works through the SELECT_BUSINESS_MODEL query as expected. Now, lets take a look at the Function UI in Answers. This does not have the INDEXCOL function.

So, now lets just enter INDEXCOL in the function window of Answers and see what happens.

As you see it works as expected. The actual logical SQL fired uses SELECT.

SELECT
INDEXCOL(1,Channels.CHANNEL_TOTAL,Products.PROD_CATEGORY_DESC) saw_0,
Sales.AMOUNT_SOLD saw_1
FROM
SH
ORDER BY saw_0

In theory, we should be able to use all the functions like Choose, Ago, Todate etc directly using the SELECT clause itself. So, is it possible to actually use Ago & Todate functions from the UI in 10g (remember in 11g these functions are now available in the UI)? Lets test the theory.

SELECT_BUSINESS_MODEL
"CHANNEL_CLASS",
"CALENDAR_YEAR",
"AMOUNT_SOLD",
AGO("AMOUNT_SOLD","Dim - Time"."Year",1) as TA
FROM
"SH"."Channels",
"SH"."Times",
"SH"."Sales"

Unfortunately this does not work. The reason for this is, AGO and ToDATE functions refer to Dimension/Hierarchy (instead of logical columns) levels which 10g(11g does) does not support through Logical SQLs. But generally any other function that does not refer to the levels directly can be used interchangeably in Answers and RPD.