Oracle BI EE 10.1.3.4.1 – BI Server Query Parameters - Driving Tables and Parameterized Sub-Queries

Venkatakrishnan J -

Whenever BI Server is used for creating reports containing data from multiple databases, BI Server acts like a mini-database engine. It does a lot of operations itself like joins, sorts, group bys etc, everything in its own memory space. Since it acts like a database engine, it has to have certain parameters that can influence the decision making execution plan or the queries it generates. For example, for the purpose of analogy, in Oracle 9i and before the execution plan generated by the Oracle database were determined by certain rules(RBO). In 10g and above, it is based on CBO where certain statistics are maintained and they internally determine the execution plan. Along similar lines, BI Server itself depends on quite a few parameters which in turn determine the execution plan of the logical queries i.e the physical queries generated.  I have listed some of the parameters here

  1. Driving Tables
  2. Row Counts – Imagine this as updating the stats of a database table. This is significant in certain situations which i will cover later.
  3. Number of Members in a Level (more importantly the ratio of number members across levels)

In this blog entry i will cover the concept of driving tables. In many cases when a cross-database join is performed, we might not want an equi-join to be performed between a dimension table in one database and a fact table in another database within the BI Server memory space. Instead, we might want to convert the dimension table(the column on which the join is made) into a set of literal values and then push those literal values as a filter to the fact table. That way, the queries will be constrained only to their respective databases and BI Server does not have to do any in-memory stitch joins. The diagram below explains the concept

As you see, all we want to do is to negate the BI Server in-memory join and convert the where clauses to individual literal filters applied directly as a physical query filter. To demonstrate this, i shall be using the standard SH schema. And to simulate multiple databases, i will model the CHANNELS dimension to be from one database and the SALES Fact to be from another database as shown below

Now, include these 2 tables in a BMM. But while doing a complex join between these 2 tables, enable the driving table as the Channels logical table

Create a simple report as shown below.

The above report will result in 2 queries. Both the queries are given below

select T8916.CHANNEL_CLASS as c1,
     T8916.CHANNEL_ID as c2
from
     CHANNELS T8916
order by c2
select T8924.CHANNEL_ID as c2,
     sum(T8924.AMOUNT_SOLD) as c3
from
     SALES T8924
where  ( T8924.CHANNEL_ID in (::PARAM1, ::PARAM2, ::PARAM3, ::PARAM4,
::PARAM5, ::PARAM6, ::PARAM7, ::PARAM8, ::PARAM9, ::PARAM10,
::PARAM11, ::PARAM12, ::PARAM13, ::PARAM14,
::PARAM15, ::PARAM16, ::PARAM17, ::PARAM18, ::PARAM19, ::PARAM20) )
group by T8924.CHANNEL_ID
order by c2

If you notice the 2nd query, you would notice that the CHANNEL_ID is actually converted into individual literals and then pushed as a filter. You would also notice that there are 20 such parameters. There are 2 parameters that determine the maximum number of parameters and the maximum number of queries required to generate such parameters. They are

  1. MAX_PARAMETERS_PER_DRIVE_JOIN

  2. MAX_QUERIES_PER_DRIVE_JOIN

Both these parameters are actually part of the database features that can be altered.

Just be aware that if your dimension has more members than that determined by the MAX_PARAMETERS_PER_DRIVE_JOIN, then that will throw an error. It is recommended not to use this for big dimensions as that will negate any gains we can achieve through this method. But this is very useful if you have a simple small lookup table or excel sheet somewhere that would have to act as your dimension.