BI Blogs

Just wanted to let everyone know that you can subscribe to my Twitter updates by accessing the RSS feed.  I have included a link on the top right on my blog main page that you can use to subscribe to.

Nice feature, so now you don’t have to miss out on any of my Twitter updates if you currently only subscribe to my blog RSS feed.  I have been posting quite a few items on Twitter, so if you don’t utilize a tool to follow people’s tweets you can utilize this option to add my Twitter feed to your RSS subscription tool of choice.  But if you do use a tool to monitor people on Twitter you can simply add me to your list.

In the previous two postings in this series, I looked at the architecture of the Oracle BI Server, and how it processes incoming queries from Oracle BI Answers. In the latter article I touched on the concept of BI Server in-memory joins, and in this article I want to expand on this topic and look at just what goes on when the BI Server is called upon to combine data from multiple sources.

When the BI Server executes a query plan, it handles the data in four separate stages:

• Firstly, filters and functions are applied to the data from each logical table source
• Then, these data sources are aggregated as required
• Then they are joined together (or “stitched” together), and
• Then, any calculations and/or aggregations that are applied across data sources are applied

In simple OBIEE environments, data used by a request will come from a single database, and therefore any joins that need to be performed by the BI Server will automatically be “pushed down” to the underlying database. In the cases though where more than one physical database is being used to provide data for a query, this join will instead need to be performed by the BI Server “in memory”. This ability to “federate” data sources, and therefore produce reports and analysis that span multiple data sources, but present the data to users as if it was a single database, is one of the key unique features of OBIEE and sets it apart from tools like Discoverer which are really restricted to reporting against single data sources.

So given this capability, how does it work under the covers? When does the BI Server perform a join in-memory, and when does it get done at the underlying database level? Where can we see what is happening, and can we predict what method the BI Server will use when performing a join? Finally, what algorithm does the BI Server use when performing these joins, and how does it use memory and disk when during the process?

To illustrate how the process works, there are a number of join scenarios that we need to consider. Some relate to joining fact and dimension tables together, and others relate to joining fact tables that share conforming dimensions, or hold conforming data sets of differing granularity.

Joining Fact and Dimension Tables Together

The BI Server semantic layer requires you to organize your business model and mapping layer into a star schema. This star schema may have one or more logical dimension tables, that join to one or more logical fact tables. The logical fact tables typically have conforming dimensions, so that you can create requests that span multiple fact tables and multiple dimension tables.

Taking for the moment joins between fact and dimension tables, depending on how the underlying physical or logical table source joins are set up in the semantic model, these may be either inner joins, left outer joins, right outer joins or full outer joins. The simple example to consider is a business model that is mapped to a single physical database, so that all logical table sources point to the same underlying data source, as shown in the screenshot below:

In this case, if we issued a request against this business model that required data from a dimension table and a fact table, the BI Server would push the join between logical table sources down to the underlying database, a single SQL query would be generated and the execution plan from a level 5 query log entry would look like this:

-------------------- Execution plan:

RqList <<2105>> [for database 3023:2820:orcl3,44]
PRODUCTS.PROD_SUBCATEGORY_DESC as c1 GB [for database 3023:2820,44],
sum(SALES.QUANTITY_SOLD by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c2 GB [for database 3023:2820,44]
Child Nodes (RqJoinSpec): <<2136>> [for database 3023:2820:orcl3,44]
PRODUCTS T2874
SALES T2911
DetailFilter: PRODUCTS.PROD_ID = SALES.PROD_ID [for database 0:0]
GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC]  [for database 3023:2820,44]
OrderBy: c1 asc [for database 3023:2820,44]

The same would apply to a left outer join between table sources in the same database, a right outer join or a full outer join. The BI Server doesn’t do any work here except to issue a single SQL query, and you can see just the one “RqList” (request list) in the execution plan, indicating again that the BI Server thinks it only needs to put together one query to satisfy the request.

If, however, one of the logical dimension tables had its logical table source re-pointed to a separate physical database, as shown in the screenshot below, the BI Server would now have to do the join itself, as it can’t be pushed down to the underlying database (as there are now two of them).

In this case, two SQL queries would be issued, one against each of the two physical databases, and the BI Server would do the join in-memory (or to disk, I’ll elaborate on this later on). The corresponding logical execution plan from a level 5 log file would now look like this:

-------------------- Execution plan:

RqBreakFilter <<2465>>[1] [for database 0:0,0]
RqList <<2466>> [for database 0:0,0]
D1.c2 as c1 [for database 3023:2500,44],
sum(D1.c5 by [ D1.c2]  at_distinct [ D1.c2, D1.c3] ) as c2 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<2478>> [for database 0:0,0]
(
RqList <<2482>> [for database 0:0,0]
D902.c1 as c2 GB [for database 3023:2500,44],
D901.c2 as c3 [for database 3023:132,44],
D901.c3 as c5 [for database 3023:132,44]
Child Nodes (RqJoinSpec): <<2490>> [for database 0:0,0]

(
RqList <<2495>> [for database 3023:132:orcl,44]
SALES.PROD_ID as c2 [for database 3023:132,44],
sum(SALES.QUANTITY_SOLD by [ SALES.PROD_ID] ) as c3 [for database 3023:132,44]
Child Nodes (RqJoinSpec): <<2504>> [for database 3023:132:orcl,44]
SALES T211
GroupBy: [ SALES.PROD_ID]  [for database 3023:132,44]
OrderBy: c2 asc [for database 3023:132,44]
) as D901
InnerJoin <<2492>> On D901.c2 = D902.c2; actual join vectors:  [ 0 ] =  [ 1 ]

(
RqList <<2517>> [for database 3023:2500:orcl2,44]
PRODUCTS.PROD_SUBCATEGORY_DESC as c1 GB [for database 3023:2500,44],
PRODUCTS.PROD_ID as c2 [for database 3023:2500,44]
Child Nodes (RqJoinSpec): <<2523>> [for database 3023:2500:orcl2,44]
PRODUCTS T2502
OrderBy: c2 asc [for database 3023:2500,44]
) as D902
OrderBy: c2, c3 [for database 0:0,0]
) as D1
OrderBy: c1 asc [for database 0:0,0]

Notice the “InnerJoin <<2492>> On D901.c2 = D902.c2; actual join vectors: [ 0 ] = [ 1 ]“ that is in the middle of the execution plan, between the two main Rqlists – this tells you that the BI Server is doing the join, as it would only appear here if it couldn’t be pushed down to the underlying database. You might also find references to LeftOuterJoin, RightOuterJoin and FullOuterJoin here, depending on how the join between the tables is defined in the physical or logical table source joins in your semantic layer.

Joining Facts with Conforming Dimensions Together

Another situation occurs when you are joining fact tables together that share conforming dimensions. A simple example of this is where you create a request that requires data from two or more fact tables that share conforming dimensions, such as those shown in the screenshot below:

As requests such as these can potentially lead to “fan trap” issues (explained in this blog post), the BI Server knows that it has to generate two logical queries and join, or “stitch” them together to avoid the fan trap. If both fact tables are sourced from the same physical database, and this database supports subquery factoring (the “WITH” clause that you see in Oracle 10gR2/11g SQL statements) then it will generate the following execution plan, which has a FullOuterStitchJoin between the two inner RqList blocks:

RqBreakFilter <<3571>>[3] [for database 0:0,0]
    RqList <<3462>> [for database 3023:2820:orcl3,46]
        D1.c1 as c1 GB [for database 3023:2820,46],
        D2.c1 as c2 GB [for database 3023:2820,46],
        case  when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end  as c3 GB [for database 3023:2820,46]
    Child Nodes (RqJoinSpec): <<3567>> [for database 3023:2820:orcl3,46]
        (
            RqList <<3474>> [for database 3023:2820:orcl3,46]
                sum(COSTS.UNIT_COST by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,46],
                PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,46]
            Child Nodes (RqJoinSpec): <<3507>> [for database 3023:2820:orcl3,46]
                PRODUCTS T2874
                COSTS T2830
            DetailFilter: COSTS.PROD_ID = PRODUCTS.PROD_ID [for database 0:0]
            GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC]  [for database 3023:2820,46]
        ) as D1 FullOuterStitchJoin <<3565>> On D1.c2 = D2.c2
        (
            RqList <<3511>> [for database 3023:2820:orcl3,46]
                sum(SALES.AMOUNT_SOLD by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,46],
                PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,46]
            Child Nodes (RqJoinSpec): <<3544>> [for database 3023:2820:orcl3,46]
                PRODUCTS T2874
                SALES T2911
            DetailFilter: PRODUCTS.PROD_ID = SALES.PROD_ID [for database 0:0]
            GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC]  [for database 3023:2820,46]
        ) as D2
    OrderBy: c3 asc [for database 3023:2820,46]

The BI Server Navigator then generates a single SQL statement off of this execution plan, which queries both fact tables using subquery factoring, and then brings the results together in the main body of the statement:

-------------------- Sending query to database named orcl3 (id: <<3462>>):
WITH
SAWITH0 AS (select sum(T2830.UNIT_COST) as c1,
     T2874.PROD_SUBCATEGORY_DESC as c2
from
     PRODUCTS T2874,
     COSTS T2830
where  ( T2830.PROD_ID = T2874.PROD_ID )
group by T2874.PROD_SUBCATEGORY_DESC),
SAWITH1 AS (select sum(T2911.AMOUNT_SOLD) as c1,
     T2874.PROD_SUBCATEGORY_DESC as c2
from
     PRODUCTS T2874,
     SALES T2911
where  ( T2874.PROD_ID = T2911.PROD_ID )
group by T2874.PROD_SUBCATEGORY_DESC)
select distinct SAWITH0.c1 as c1,
     SAWITH1.c1 as c2,
     case  when SAWITH0.c2 is not null then SAWITH0.c2 when SAWITH1.c2 is not null then SAWITH1.c2 end  as c3
from
     SAWITH0 full outer join SAWITH1 On SAWITH0.c2 = SAWITH1.c2
order by c3

If the physical database doesn’t support subquery factoring, such as Oracle Database 10gR1 or higher, then the BI Server generates a slightly different execution plan, again with a FullOuterStitchJoin, like this:

-------------------- Execution plan:

RqBreakFilter <<3115>>[3] [for database 0:0,0]
    RqList <<3006>> [for database 0:0,0]
        D903.c1 as c1 GB [for database 3023:2820,44],
        D903.c2 as c2 GB [for database 3023:2820,44],
        case  when D903.c3 is not null then D903.c3 when D903.c4 is not null then D903.c4 end  as c3 GB [for database 3023:2820,44]
    Child Nodes (RqJoinSpec): <<3117>> [for database 0:0,0]
        (
            RqList <<3160>> [for database 0:0,0]
                D901.c1 as c1 GB [for database 3023:2820,44],
                D902.c1 as c2 GB [for database 3023:2820,44],
                D901.c2 as c3 [for database 3023:2820,44],
                D902.c2 as c4 [for database 3023:2820,44]
            Child Nodes (RqJoinSpec): <<3163>> [for database 0:0,0]

                    (
                        RqList <<3018>> [for database 3023:2820:orcl3,44]
                            sum(COSTS.UNIT_COST by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,44],
                            PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,44]
                        Child Nodes (RqJoinSpec): <<3051>> [for database 3023:2820:orcl3,44]
                            PRODUCTS T2874
                            COSTS T2830
                        DetailFilter: COSTS.PROD_ID = PRODUCTS.PROD_ID [for database 0:0]
                        GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC]  [for database 3023:2820,44]
                        OrderBy: c2 asc [for database 3023:2820,44]
                    ) as D901 FullOuterStitchJoin <<3109>> On D901.c2 = D902.c2; actual join vectors:  [ 1 ] =  [ 1 ]

                    (
                        RqList <<3055>> [for database 3023:2820:orcl3,44]
                            sum(SALES.AMOUNT_SOLD by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,44],
                            PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,44]
                        Child Nodes (RqJoinSpec): <<3088>> [for database 3023:2820:orcl3,44]
                            PRODUCTS T2874
                            SALES T2911
                        DetailFilter: PRODUCTS.PROD_ID = SALES.PROD_ID [for database 0:0]
                        GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC]  [for database 3023:2820,44]
                        OrderBy: c2 asc [for database 3023:2820,44]
                    ) as D902
        ) as D903
    OrderBy: c3 asc [for database 0:0,0]

This is then resolved for this database into two separate SQL statements, which then joined “in-memory” together by the BI Server.

-------------------- Sending query to database named orcl3 (id: <<3018>>):

select sum(T2830.UNIT_COST) as c1,
     T2874.PROD_SUBCATEGORY_DESC as c2
from
     PRODUCTS T2874,
     COSTS T2830
where  ( T2830.PROD_ID = T2874.PROD_ID )
group by T2874.PROD_SUBCATEGORY_DESC
order by c2

+++Administrator:2a0000:2a0005:----2010/02/28 15:05:31

-------------------- Sending query to database named orcl3 (id: <<3055>>):

select sum(T2911.AMOUNT_SOLD) as c1,
     T2874.PROD_SUBCATEGORY_DESC as c2
from
     PRODUCTS T2874,
     SALES T2911
where  ( T2874.PROD_ID = T2911.PROD_ID )
group by T2874.PROD_SUBCATEGORY_DESC
order by c2

Joining Table Sources within a Logical Fact

Another situation is a fact table may have more than one logical table source, because individual measures are sourced from different data sources or perhaps measures may be mapped in at differing levels of granularity (this blog post describes such a scenario). In this case, again the BI Server will initially try and push the join down to the underlying database, something that may be possible if a single physical database is used and we can use a technique like subquery factoring; more likely though it will require the BI Server to issue two or more physical SQL statements and then bring the results back together again using a FullOuterStitchJoin.

-------------------- Execution plan:

RqList <<7829>> [for database 0:0,0]
    D1.c1 as c1 [for database 0:0,0],
    D1.c2 as c2 [for database 0:0,0],
    D1.c3 as c3 [for database 0:0,0],
    D1.c4 as c4 [for database 3023:4210,44]
Child Nodes (RqJoinSpec): <<7842>> [for database 0:0,0]
    (
        RqList <<7809>> [for database 0:0,0]
            D1.c1 as c1 [for database 0:0,0],
            D1.c2 as c2 [for database 0:0,0],
            D1.c3 as c3 [for database 0:0,0],
            D1.c4 as c4 [for database 3023:4210,44],
            D1.c5 as c5 [for database 0:0,0]
        Child Nodes (RqJoinSpec): <<7824>> [for database 0:0,0]
            (
                RqBreakFilter <<7808>>[1,2,5] [for database 0:0,0]
                    RqList <<7604>> [for database 0:0,0]
                        case  when D903.c1 is not null then D903.c1 when D903.c2 is not null then D903.c2 end  as c1 GB [for database 0:0,0],
                        case  when D903.c3 is not null then D903.c3 when D903.c4 is not null then D903.c4 end  as c2 GB [for database 0:0,0],
                        D903.c5 as c3 GB [for database 0:0,0],
                        D903.c6 as c4 GB [for database 3023:4210,44],
                        case  when D903.c7 is not null then D903.c7 when D903.c8 is not null then D903.c8 end  as c5 GB [for database 0:0,0]
                    Child Nodes (RqJoinSpec): <<7844>> [for database 0:0,0]
                        (
                            RqList <<7915>> [for database 0:0,0]
                                D901.c1 as c1 [for database 0:0,0],
                                D902.c1 as c2 [for database 3023:4210,44],
                                D902.c2 as c3 [for database 3023:4210,44],
                                D901.c2 as c4 [for database 0:0,0],
                                D901.c3 as c5 GB [for database 0:0,0],
                                D902.c3 as c6 GB [for database 3023:4210,44],
                                D901.c4 as c7 [for database 0:0,0],
                                D902.c4 as c8 [for database 3023:4210,44]
                            Child Nodes (RqJoinSpec): <<7918>> [for database 0:0,0]

                                    (
                                        RqList <<7851>> [for database 0:0,0]
                                            D1.c2 as c1 [for database 0:0,0],
                                            D1.c3 as c2 [for database 0:0,0],
                                            D1.c1 as c3 GB [for database 0:0,0],
                                            D1.c4 as c4 [for database 0:0,0]
                                        Child Nodes (RqJoinSpec): <<7854>> [for database 0:0,0]
                                            (
                                                RqBreakFilter <<7687>>[2,3] [for database 0:0,0]
                                                    RqList <<8040>> [for database 0:0,0]
                                                        D1.c1 as c1 [for database 0:0,0],
                                                        D1.c2 as c2 [for database 0:0,0],
                                                        D1.c3 as c3 [for database 0:0,0],
                                                        D1.c4 as c4 [for database 0:0,0]
                                                    Child Nodes (RqJoinSpec): <<8058>> [for database 0:0,0]
                                                        (
                                                            RqList <<7972>> [for database 3023:4483:Quotas,2]
                                                                sum(QUANTITY_QUOTAS.QUOTA by [ CATEGORY.CATEGORY, MONTHS.MONTH_MON_YYYY] ) as c1 [for database 3023:4483,2],
                                                                MONTHS.MONTH_MON_YYYY as c2 [for database 3023:4483,2],
                                                                CATEGORY.CATEGORY as c3 [for database 3023:4483,2],
                                                                MONTHS.MONTH_YYYYMM as c4 [for database 3023:4483,2]
                                                            Child Nodes (RqJoinSpec): <<7682>> [for database 3023:4483:Quotas,2]
                                                                CATEGORY T4486
                                                                MONTHS T4488
                                                                QUANTITY_QUOTAS T4492
                                                            DetailFilter: CATEGORY.CATEGORY = QUANTITY_QUOTAS.CATEGORY and MONTHS.MONTH_YYYYMM = QUANTITY_QUOTAS.MONTH_YYYYMM [for database 0:0]
                                                            GroupBy: [ CATEGORY.CATEGORY, MONTHS.MONTH_YYYYMM, MONTHS.MONTH_MON_YYYY]  [for database 3023:4483,2]
                                                        ) as D1
                                                    OrderBy: c2, c3 [for database 0:0,0]
                                            ) as D1
                                        OrderBy: c1 asc, c2 asc [for database 0:0,0]
                                    ) as D901 FullOuterStitchJoin <<7800>> On D901.c1 =NullsEqual D902.c1 and D901.c2 =NullsEqual D902.c2; actual join vectors:  [ 0 1 ] =  [ 0 1 ]

                                    (
                                        RqList <<7880>> [for database 3023:4210:orcl4,44]
                                            D2.c2 as c1 [for database 3023:4210,44],
                                            D2.c3 as c2 [for database 3023:4210,44],
                                            D2.c1 as c3 GB [for database 3023:4210,44],
                                            D2.c4 as c4 [for database 3023:4210,44]
                                        Child Nodes (RqJoinSpec): <<7883>> [for database 3023:4210:orcl4,44]
                                            (
                                                RqBreakFilter <<7760>>[2,3] [for database 3023:4210:orcl4,44]
                                                    RqList <<7989>> [for database 3023:4210:orcl4,44]
                                                        sum(ITEMS.QUANTITY by [ PRODUCT.CATEGORY, TIMES.MONTH_MON_YYYY] ) as c1 [for database 3023:4210,44],
                                                        TIMES.MONTH_MON_YYYY as c2 [for database 3023:4210,44],
                                                        PRODUCT.CATEGORY as c3 [for database 3023:4210,44],
                                                        TIMES.MONTH_YYYYMM as c4 [for database 3023:4210,44]
                                                    Child Nodes (RqJoinSpec): <<7755>> [for database 3023:4210:orcl4,44]
                                                        PRODUCT T4256
                                                        TIMES T4264
                                                        ITEMS T4239
                                                        ORDERS T4248
                                                    DetailFilter: ITEMS.ORDID = ORDERS.ORDID and ITEMS.PRODID = PRODUCT.PRODID and ORDERS.ORDERDATE = TIMES.DAY_ID [for database 0:0]
                                                    GroupBy: [ PRODUCT.CATEGORY, TIMES.MONTH_MON_YYYY, TIMES.MONTH_YYYYMM]  [for database 3023:4210,44]
                                            ) as D2
                                        OrderBy: c1 asc, c2 asc [for database 3023:4210,44]
                                    ) as D902
                        ) as D903
                    OrderBy: c1, c2, c5 [for database 0:0,0]
            ) as D1
        OrderBy: c5 asc, c2 asc, c4 asc [for database 0:0,0]
    ) as D1

Again, notice the FullOuterStitchJoin in the execution plan – this indicates that facts (as opposed to facts and dimensions) are being joined together.

This in turn leads to two separate SQL statements. The one against the “orcl” database is more complex because the results then need to be mapped to the aggregation level that the second source, “quotas”, comes in at:

-------------------- Sending query to database named Quotas (id: <<7972>>):
select sum(T4492."QUOTA") as c1,
     T4488."MONTH_MON_YYYY" as c2,
     T4486."CATEGORY" as c3,
     T4488."MONTH_YYYYMM" as c4
from
     "CATEGORY" T4486,
     "MONTHS" T4488,
     "QUANTITY_QUOTAS" T4492
where  ( T4486."CATEGORY" = T4492."CATEGORY" and T4488."MONTH_YYYYMM" = T4492."MONTH_YYYYMM" )
group by T4486."CATEGORY", T4488."MONTH_YYYYMM", T4488."MONTH_MON_YYYY"

+++Administrator:2b0000:2b000a:----2010/02/24 17:18:51

-------------------- Sending query to database named orcl4 (id: <<7880>>):

select D2.c2 as c1,
     D2.c3 as c2,
     D2.c1 as c3,
     D2.c4 as c4
from
     (select D1.c1 as c1,
               D1.c2 as c2,
               D1.c3 as c3,
               D1.c4 as c4
          from
               (select sum(T4239.QUANTITY) as c1,
                         T4264.MONTH_MON_YYYY as c2,
                         T4256.CATEGORY as c3,
                         T4264.MONTH_YYYYMM as c4,
                         ROW_NUMBER() OVER (PARTITION BY T4256.CATEGORY, T4264.MONTH_MON_YYYY ORDER BY T4256.CATEGORY ASC, T4264.MONTH_MON_YYYY ASC) as c5
                    from
                         PRODUCT T4256,
                         TIMES T4264,
                         ITEMS T4239,
                         ORDERS T4248
                    where  ( T4239.ORDID = T4248.ORDID and T4239.PRODID = T4256.PRODID and T4248.ORDERDATE = T4264.DAY_ID )
                    group by T4256.CATEGORY, T4264.MONTH_MON_YYYY, T4264.MONTH_YYYYMM
               ) D1
          where  ( D1.c5 = 1 )
     ) D2
order by c1, c2

So, to summarize things so far:

• Where possible, the BI Server will try and generate a single SQL statement to resolve a request
• And if possible, any joins that are required between tables will be pushed down to the database
• If table data sources are located on separate physical databases, the BI Server will request the individual data source data blocks, and then join the results together in-memory using an inner, left outer, right outer or full outer join as appropriate
• If facts (or measures within a fact) are being joined together, the BI Server will need to generate one logical query per logical table source, and bring the data together with a full outer stitch join
• As mentioned above, if it’s possible to do this stitch join at the database level (using, for example, a WITH clause), it’ll do so
• Otherwise the BI Server will generate separate SQL statements and join the data together in-memory

When an in-memory BI Server join happens between two tables, it will bring back both sets of data from the two (or more) table sources and then perform a sort-merge join to bring the data together. If possible, it will push the sort back to the underlying database and just do the “merge” part of the join, and it’ll in all likelihood page some of the temporary data to TMP files in $ORACLEBIDATA/tmp depending on the load on the server, available memory and the number of concurrent queries that it is running. The NQSConfig.INI BI Server parameter VIRTUAL_TABLE_PAGE_SIZE determines the point at which temporary data is paged to disk, and on a Unix server you can experiment with increasing it from its default setting if you have lots of unused memory available (the docs suggest that this will probably not have much of a positive effect, though).

Driving Tables (Parameterized Nested Loop Joins)

I mentioned in the paragraph above that BI Server joins are typically done using the sort-merge algorithm. One variation on this though is when you set one of the two tables in a business model and mapping logical join to be a driving table, typically because you are federating fact and dimension tables and one table is much smaller than the other, as shown in the screenshot below.

The first thing to understand with driving tables is that they are regarded as a “hint” by the BI Server, and the BI Server may well choose to ignore the setting if it makes more sense to perform the join as normal (presumably, when both tables are relatively small). If the driving table instruction is followed, though, the BI Server will always do the join in-memory, even if both tables come from logical table sources pointing to the same physical database. In the execution plan shown below, you can see the InnerJoin (left drive) that indicates a parameterized nested loop join (PNLJ) will be required, and as the name suggests the BI Server will perform a nested loop join rather than the sort-merge join that it usually uses to join tables together.

-------------------- Execution plan:
RqBreakFilter <<8705>>[1] [for database 0:0,0]
    RqList <<8972>> [for database 0:0,0]
        D1.c2 as c1 [for database 3023:2500,44],
        sum(D1.c5 by [ D1.c2]  at_distinct [ D1.c2, D1.c3] ) as c2 [for database 0:0,0]
    Child Nodes (RqJoinSpec): <<8984>> [for database 0:0,0]
        (
            RqList <<8463>> [for database 0:0,0]
                D901.c1 as c2 GB [for database 3023:2500,44],
                D902.c2 as c3 [for database 3023:5035,44],
                D902.c3 as c5 [for database 3023:5035,44]
            Child Nodes (RqJoinSpec): <<8707>> [for database 0:0,0]

                    (
                        RqList <<8757>> [for database 3023:2500:orcl2,44]
                            PRODUCTS.PROD_NAME as c1 GB [for database 3023:2500,44],
                            PRODUCTS.PROD_ID as c2 [for database 3023:2500,44]
                        Child Nodes (RqJoinSpec): <<8760>> [for database 3023:2500:orcl2,44]
                            PRODUCTS T2502
                        DetailFilter: PRODUCTS.PROD_NAME = '128MB Memory Card' or PRODUCTS.PROD_NAME = '3 1/2" Bulk diskettes, Box of 100' or PRODUCTS.PROD_NAME = '5MP Telephoto Digital Camera' or PRODUCTS.PROD_NAME = '64MB Memory Card' or PRODUCTS.PROD_NAME = 'Deluxe Mouse' or PRODUCTS.PROD_NAME = 'Envoy Ambassador' or PRODUCTS.PROD_NAME = 'Envoy External 8X CD-ROM' or PRODUCTS.PROD_NAME = 'Martial Arts Champions' or PRODUCTS.PROD_NAME = 'Model A3827H Black Image Cartridge' or PRODUCTS.PROD_NAME = 'Model C93822D Wireless Phone Battery' or PRODUCTS.PROD_NAME = 'Model CD13272 Tricolor Ink Cartridge' or PRODUCTS.PROD_NAME = 'PCMCIA modem/fax 28800 baud' or PRODUCTS.PROD_NAME = 'SIMM- 16MB PCMCIAII card' or PRODUCTS.PROD_NAME = 'Smash up Boxing' or PRODUCTS.PROD_NAME = 'Unix/Windows 1-user pack' [for database 0:0]
                        OrderBy: c2 asc [for database 3023:2500,44]
                    ) as D901
                InnerJoin  (left drive) <<8806>> On D901.c2 = D902.c2; actual join vectors:  [ 1 ] =  [ 0 ]

                    (
                        RqList <<8790>> [for database 3023:5035:orcl5,44]
                            SALES.PROD_ID as c2 [for database 3023:5035,44],
                            sum(SALES.AMOUNT_SOLD by [ SALES.PROD_ID] ) as c3 [for database 3023:5035,44]
                        Child Nodes (RqJoinSpec): <<8793>> [for database 3023:5035:orcl5,44]
                            SALES T5126
                        DetailFilter: SALES.PROD_ID = ?1 or SALES.PROD_ID = ?2 or SALES.PROD_ID = ?3 or SALES.PROD_ID = ?4 or SALES.PROD_ID = ?5 or SALES.PROD_ID = ?6 or SALES.PROD_ID = ?7 or SALES.PROD_ID = ?8 or SALES.PROD_ID = ?9 or SALES.PROD_ID = ?10 or SALES.PROD_ID = ?11 or SALES.PROD_ID = ?12 or SALES.PROD_ID = ?13 or SALES.PROD_ID = ?14 or SALES.PROD_ID = ?15 or SALES.PROD_ID = ?16 or SALES.PROD_ID = ?17 or SALES.PROD_ID = ?18 or SALES.PROD_ID = ?19 or SALES.PROD_ID = ?20 [for database 0:0]
                        GroupBy: [ SALES.PROD_ID]  [for database 3023:5035,44]
                        OrderBy: c2 asc [for database 3023:5035,44]
                    ) as D902
            OrderBy: c2, c3 [for database 0:0,0]
        ) as D1
    OrderBy: c1 asc [for database 0:0,0]

Then then leads to the following parameterized SQL statements being issued, with the first statement representing the “driving” query, and the second the “probing” one against the larger table.

-------------------- Sending query to database named orcl2 (id: <<8757>>):

select T2502.PROD_NAME as c1,
     T2502.PROD_ID as c2
from
     PRODUCTS T2502
where  ( T2502.PROD_NAME in ('128MB Memory Card', '3 1/2" Bulk diskettes, Box of 100', '5MP Telephoto Digital Camera', '64MB Memory Card', 'Deluxe Mouse', 'Envoy Ambassador', 'Envoy External 8X CD-ROM', 'Martial Arts Champions', 'Model A3827H Black Image Cartridge', 'Model C93822D Wireless Phone Battery', 'Model CD13272 Tricolor Ink Cartridge', 'PCMCIA modem/fax 28800 baud', 'SIMM- 16MB PCMCIAII card', 'Smash up Boxing', 'Unix/Windows 1-user pack') )
order by c2

+++Administrator:2c0000:2c000a:----2010/02/24 21:06:47

-------------------- Sending query to database named orcl5 (id: <<8790>>):

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

In reality you rarely see driving table joins being used as there are much better solutions to bringing together small and large tables together – the main one being to co-locate the tables and then push the join down to the database, rather than bring both datasets together and have the BI Server join them in memory instead (this also applies to a lesser degree to all BI Server joins). But this could be a useful “quick fix” until such time as you can co-locate the data, and its useful to remember that these types of joins are always done by the BI Server due to the need to iterate through drive/probe operations.

Persist Connnection Pools

One final variation on BI Server execution plans and join types is when you set up a “persist connection pool”. Persist connection pools are typically used in two scenarios; firstly, where Oracle/Siebel Marketing is being used, and secondly, where the underlying physical database doesn’t handle large numbers of values in an IN-list. In this case, you can set up a second connection pool within a physical database and specify it as the persist connection pool, as shown in the screenshot below:

I’ve never encountered a persist connection pool “in the wild”, so to speak, but an example query log output from when one was used is shown below. In this instance, the first query was sent to a MS Analysis Services database, and a persist connection pool was used to materialize the in-list results into a database table which is then joined back to the ORDERS table in the final query, rather than have the BI Server do the join in-memory.

-------------------- Sending query to database named FoodMart (id: <<10980>>):
With
  member [Measures].[YearAnc] as 'ancestor([Time].Currentmember,[Time].[Year]).name'
  set [Q] as '{{[Time].[Year].members}}'
  select
    {[measures].[YearAnc]} on columns,
    {[Q]} on rows
  from     [Sales]

-------------------- Sending query to database named SQLDB_Northwind (id: CreateTable TransGateway):
CREATE TABLE TTCH5C5DEL554110000020000003 ( column1 VARCHAR2(8) )

-------------------- Sending query to database named SQLDB_Northwind (id: <<11057>>):
select distinct TO_NUMBER(TO_CHAR(T1864.OrderDate, 'yyyy'), '9999') as c1
from
     Orders T1864
where  ( TO_NUMBER(TO_CHAR(T1864.OrderDate, 'yyyy'), '9999') in (select column1 from TTCH5C5DEL554110000020000003) )

Conclusions

So, there you have it. The join strategy of the BI Server, as is the case with functions and calculations, is to wherever possible push them down to the underlying database. If this can’t be done, because either the database version doesn’t support features like subquery factoring, or if the data for the request is being sourced from more than one physical databas, the BI Server will do the join itself, initially in-memory but usually with temporary data being paged to disk.

There are two main types of BI Server join; regular (inner, left outer, right outer and fullouter) joins for bringing together fact and dimension tables; and full outer stitch joins, for bringing together facts and measures. There are also variations for handling joins from very small tables to very large tables (driving tables, or parameterized nested loop joins), or when the physical database doesn’t support large in-lists, however these issues are usually better handled by co-locating data or upgrading the database.

Finally, even though the BI Server is pretty clever at doing these types of joins, you’re usually better trying to invest your time in physically bringing your data together into a data mart or data warehouse than spending too much time fine-tuning these joins, though a knowledge of how they work (and how to read a level 5 execution plan) can be useful if you have to understand, or tune, an existing system in-place.

For now though, that’s it, and I’ll continue this series of BI Server postings in the next few days by taking a look a what happens when you set up your BI Server environment for clustering.

On to the 4th puzzle in this Puzzle series. This is a very interesting Puzzle at least from the perspective of BI EE Security. We all know that BI EE provides comprehensive security within the repository. For example, the screenshot below shows that the column CHANNEL_DESC can be accessed only by the exec user.

Now when we log in as any user(users who do not belong to Administrators group) and open the report containing this secured column, we will either be getting an error or will be shown as NULL in reports depending on the PROJECT_INACCESSIBLE_COLUMN_AS_NULL property in the NQSConfig.ini. The question is how do we bypass the security and still show the CHANNEL_DESC column(with their values of course) in reports. I know this sounds a bit malicious and can even be read as a security hack, but this is currently possible in BI EE(in the solution i will let you know how to use a workaround to disable this though). The question or the Puzzle today is how do we achieve this. There are some potential use cases of this

1. If you do not have access to the repository but still want to look at certain security protected columns to validate certain reports

2. If you do not have a column in the repository at all (but exists in the database) and you still want to access it.

Remember this is not about enabling Direct Database Requests as in most cases that will always be disabled. For example, if you look at the report below, CHANNEL_DESC has become null for the user i have logged in as this user does not have access to this column(PROJECT_INACCESSIBLE_COLUMN_AS_NULL  is set to YES in my case).

If you look at the SQL, you will notice that the CHANNEL_DESC column is not even pushed back to the database.

WITH
SAWITH0 AS (select sum(1) as c1,
     T4167.CHANNEL_ID as c2
from
     CHANNELS T4167
group by T4167.CHANNEL_ID)
select distinct SAWITH0.c2 as c1,
     cast(NULL as  VARCHAR ( 1 ) ) as c2,
     SAWITH0.c1 as c3
from
     SAWITH0
order by c1, c2

which is good and as expected. Now, the puzzle is to somehow bypass this security and display the CHANNEL_DESC column as shown below.

Remember, there are 2 pre-conditions to this

1. No Direct Database Requests

2. No changing the repository to add a new column in the presentation layer

I wanted to thank everyone that attended the meeting either in-person or virtually.  We had around 60 people all together, so it was another good turn out.  I also wanted to thank both our presenters, they did a wonderful job and I enjoyed the audience participation and interaction with the group this month.  It is really fun to watch when the meetings turn into a collaborative effort and come to life.

Just wanted to let everyone know that all of the presentation materials and the meeting announcements have all been posted and are available to download.  You will need to login to the PASSMN website and then you can access the materials from the Resources—>Documents section.  Kalen Delaney has also made here materials available on her site, http://sqlserverinternals.com and you can access the materials in the Conference and Materials section.

And a special thanks to Microsoft for providing the room for our meetings and Benchmark Learning for sponsoring this month’s meeting and providing the food and beverage.

Here are some additional reference links in regards to partitioning that might be of interest:

• Partitioned Tables and Indexes in SQL Server 2005
• Partitioned Table and Index Strategies Using SQL Server 2008
• Project REAL: Data Lifecycle - Partitioning (relational and cube)

A

A

A

This story is long overdue and no its NOT about the Oracle Database 11g Database File System (DBFS). Its about an “undocumented” NFS functionality that, maybe someday, will be serviced by the XMLDB XDB Protocol Adapter. This post is “long overdue” because the actual attempts to try to figure it out were done during the […]

After almost a week of me giving out the Puzzle 3, there was hardly any interest for this one (just couple of odd replies requesting clarification of the Puzzle). Probably this is because this one has no direct practical usage and also there are lots of possibilities. But to me this is one very important Puzzle as in many cases when we are called in for repository tuning, the first question that we normally get in such situations is, why does BI EE generate such a big query when the same report can be solved by a very simple query. In such cases, we need to know where to look at and also understand what can cause BI EE to generate long SQLs. Remember, there is no theoretical limit to the length of the SQL generated (we can make it to generate as big a SQL as we want). The puzzle was meant primarily to know the possibilities of what can make BI EE to generate long SQLs. I always try to visualize a repository by looking at the SQL. That generally helps in doing further analysis on a pre-built repository.

Solution 1 – Conforming Dimensions:

This is probably the easiest and the most common reason why BI EE generates a lot of sub-queries. Always conforming dimensions should be used only when absolutely necessary as that will start generating sub-queries for every fact. For example, if you look at the repository below, it shows a very simple Business Model and Mapping layer containing one dimension with one Logical table source pointing to the physical CHANNELS table.It also contains 3 Facts each having a count metric (Mapped to 1 in the BMM layer for all the 3 columns)

There is also one more Logical Column that basically adds all the 3 columns together using a logical calculation

Now, when you generate a report using the CHANNEL_TOTAL and the logical calculated column, you will notice that BI EE will generate 3 sub-queries and then will bring them together as shown below

So the solution is you can create n number of conforming dimensions like this to make BI EE to produce n sub-queries thereby making the SQL very long. In this case there is no need for actually using conforming dimensions. The same SQL can actually be converted into a single SQL with all the counts (without the sub-queries). This basically demonstrates a bad use of Conforming Dimensions.

Solution 2 – Fragmentation:

This is another way of generating big SQLs. Same source can be made to appear as part of UNION ALL queries using Fragmentation. For example, if you look at the repository below

there are basically 3 logical table sources that contribute to the Fact Count. Each logical table source is modeled in a way such that all of them contribute to the Count and follow Parallel Fragmentation as shown below

And if you look at the SQL, you will notice that BI EE will fire 3 UNION ALLs to generate the count. You can make this query as big as you want by adding more and more logical table sources.

Solution 3 – Level Based Measures:

This is another possible solution where incorrect use of Level-Based aggregation can start generating pretty complex queries. In your queries, if you start noticing Partition By using ROW_NUMBER or SUM() OVER functions then that means level based measures are being used somewhere (not in all cases but in most of them). For example, lets look at the repository below

As you notice we basically have have 3 count columns each dependent on the other. Count1 is a normal measure assigned to a constant 1 and to the lowermost level in the Channel Dimension. Count 1 is a measure which is equal to measure Count(logically calculated) but assigned to the Channel Class level. Count3 is equal to measure Count2(logically calculated) but assigned to the Total level.

As you see, this basically demonstrates why logically calculated measures can have different level assignments than their base members. If you look at the SQL generated,

you will notice a number of sub-queries which will equal to the number of level assignments for each dependent measure.

In all the cases above, the queries were generated using a single Physical table and from just using 2 report attributes. Much more complex queries are possible using other methods but most of them will be a variation of the 3 listed above. Puzzle 4 to follow tomorrow.

Pentaho Agile Business Intelligence Outperforms SAP and IBM in several critical areas

Orlando, Fla., February 26, 2010 – Pentaho, the commercial open source alternative for business intelligence (BI), went toe to toe with IBM and SAP at this week’s “Developing Your BI Tool Strategy and BI Bake-Off” at TDWI World Conference Las Vegas, and garnered the audience’s popular vote in the categories of best dashboard, best reporting, and best “Cool Stuff.” With this recognition, Pentaho takes its rightful seat at the enterprise table, showing that it provides the preferred BI Suite with a full spectrum of innovative capabilities all at a fraction of the cost of proprietary offerings.
Highlights

* Pentaho Agile BI: Pentaho won the audience vote in the “Cool Stuff” category. Attendees gained a sneak peek at development products that will soon be announced as part of Pentaho’s recently unveiled Agile BI initiative. Pentaho Agile BI, launched in November of last year, has forever changed the way people build and deploy applications, setting the standard for the rest of the industry to follow. Pentaho presents a radically different, integrated environment that collapses all of the steps from developer to end user, from data integration through end user visualization, so developers can work more closely with users to get value from business intelligence. With Agile BI, both novice and expert BI professionals are now empowered to rapidly create and use rich BI applications.
* Pentaho Dashboards: Pentaho’s bake-off demonstration of its industry leading dashboard capabilities won audience approval for speed and simplicity to design and deploy. Pentaho provides immediate insight into individual, departmental, or enterprise performance. Dashboards are the best way to present and interact with information to business users, and Pentaho raised the bar for delivering key metrics in an attractive and intuitive visual interface, giving business users the critical information they need to understand and improve organizational performance.
* Pentaho Reporting: Pentaho won the audience vote for best reporting. Reporting has long been considered the core business intelligence need and is typically the first BI application deployed. Until now, BI heavyweights got most of the attention for reporting, but the bake-off audiences were wowed by how easily the lightweight contender Pentaho Reporting allows organizations to access, format, and distribute information to employees, customers, and partners.

Quote

“For years, the BI giants have had customers up against the ropes with high priced, complex BI offerings that are difficult to develop and even more difficult to use. The resounding support for Pentaho at this year’s TDWI event is evidence that smart companies see Pentaho as the next BI champion. Over five million people have downloaded Pentaho in our short five years in the market, and we don’t expect that momentum to stop any time soon.”
– Richard Daley, CEO of Pentaho Corporation

A

I meant to blog about Harvard Business Review’s Decade in Management Ideas back in January when it was first published. I bookmarked the article and jotted down a few notes in an email. And then I forgot all about it.

This week, Anna Brown, SAS Business Report Editor, mentioned HBR’s best management ideas in her latest newsletter, which reminded me to re-read it and point you towards recent articles from or about SAS for each of the twelve topics.

1. Shareholder Value as a Strategy. HBR quotes Jack Welch: “Shareholder value is a result, not a strategy. Your main constituencies are your employees, your customers and your products.” And I’ll quote Jim Davis: “Positioning a product in the tech marketplace isn’t just about the product - you need relationships. This is where our strong employee base comes into play. Make no mistake: people want to do business with companies that treat people well.”

2. IT as a Utility. HBR talks about cloud computing. In the latest issue of sascom we called it, Analytics: Hot, Fresh and On Demand.

3. The Customer Chorus. Dave Carroll’s “United Breaks Guitars” video is mentioned in the HBR post. Earlier this week, Jonathan Hornby wrote about having lunch with Dave Carroll.
Continue reading “Aligning with Harvard’s 12 best management ideas”

The session that I’m giving at the BI Forum in Brighton in May is entitled “Inside the Oracle BI Server”, and I’m aiming to take a closer look at the architecture and functionality of this key OBIEE component. We’re all fairly aware of what the BI Server does at a high level, but I thought it’d be interesting to take a closer look at what the BI Server does, particularly when it parses queries and joins datasets together.

At a very high level, the main function of the BI Server is to process inbound SQL requests against against a virtual database model, build and execute one or more physical database queries, process the data and then return it to users. The BI Server is one part of the Oracle BI Enterprise Edition Plus product family, and presents itself to query tools as one or more databases in a simple relational (star schema) model, that can then point to a much more complex set of relational, multidimensional, file and XML data sources (and in 11g, ADF objects).

Taking the standard OBIEE architecture diagram, the BI Server sits in the middle of the OBIEE set of servers and provides the query capability, security, interfaces to data sources and calculation logic for OBIEE (all of this is based on the current, 10g set of products).

The BI Server communicates with the BI Presentation Server via ODBC, and then connects out to the various supported data sources through ODBC, OCI, XML/A, the Essbase Client API and other native protocols. A key function of the BI Server is to create a three-layer metadata model, stored in a file-based repository along with security settings, database passwords, BI Server settings, startup macros and variable definitions.

The BI Server Logical Components

Taking a look specifically at the BI Server, it has a number of logical components.

• The ODBC interface, that is used by Oracle BI Answers and other third-party tools to pass requests to the BI Server, and to receive the output from queries;
• The Logical Business Model, the three-layer metadata model that describes the data available for queries;
• The Intelligent Request Generator, a module responsible for taking the incoming queries and turning them into physical queries against the connected data source, which is made up of several sub-components including:
• The Navigator, probably the most important part of the BI Server, and the part that takes the incoming query, compares it against cached answers, navigates the logical model and generates the physical queries that will best return the data required for the query
• Within the Navigator, there are modules for determining whether multiple physical queries are needed, whether stored aggregates can be used, and whether fragmented data sources can be used for partitioned measures;
• An Optimized Query Rewrite engine for handling aggregate navigation and fragments, and for translating to the correct physical SQL dialect, and
• An Execution Engine for firing off the queries to the relational, multi-dimensional, file and XML sources required to satisfy the query.
• Cache Services stores the results of previously run queries, matches incoming SQL against that used before and returns data from the cache rather than making the BI Server query the underlying databases again

In addition, various supporting technologies, modules and services provide the infrastructure for the BI Server, including:

• Data Source Adapters for Oracle, ODBC, SQL Server, DB/2, Teradata, file, XML and other sources;
• System and Performance Monitoring through JMX counters and other technologies;
• Security Services for setting up users and groups in the RPD, filters, subject area security, links to outside LDAP servers and custom authenticators;
• Query Governance, for placing limits on numbers of rows returned and length of query execution for users and groups;
• Load Balancing, and Session Management

Taking a Look at the BI Server Process

Now whilst the BI Server has many characteristics of a database, compared to running Oracle on Unix which exposes many of its components (SMON, PMON, MMON, LGWR etc) as separate processes, the BI Server is just a single executable that runs under the name NQSServer.exe (or just nqsserver under Unix). The screenshot below is a view of this service (along with sawserver.exe, the BI Presentation Server) as shown in the Windows Task Manager utility.

We’ll get on to memory usage in a future posting in this series, but in general the amount of memory taken up by the BI Server is initially determined by the size and complexity of the repository (RPD) that is running online, with further chunks taken up by concurrent sessions and then intermittent spikes of memory when in-memory (stitch) joins take place between data sources. The BI Server creates TMP (temporary) files in the $ORACLEBIDATA/tmp directory as data is further totalled and calculated, and as cross-database joins are paged to file.

If you take a closer look at the NQSServer.exe process using a tools such as Microsoft’s Process Explorer utility, you can see that it’s a multi-threaded server application:

You can see that the BI Server is a C++ application that uses the Microsoft Visual C++ runtime, whilst taking a look at one of the running threads shows the various DLLs that are being used:

Another Conceptual View of the BI Server

Another conceptual view of the BI Server architecture can be found in the old Siebel Analytics Administration Tool documentation, which shows the BI Server (or the Siebel Analytics Server as it was called then) having several layered components:

• The Security Model, presumably the users and groups in the RPD, plus the filters and subject area security in the repository;
• The Business Model, the three-layer metadata model;
• Aggregate Navigation, for rewriting queries to use mapped in aggregate tables;
• SQL Generation Engine and Multi-database Query Processing, presumably the bit that takes the database capabilities matrix and generates the correct physical SQL for the various data sources;
• The Computation Engine, for performing in-memory stitch joins, post-aggregation filters and functions, and sorting,
• Query cachiing
• The Metadata Repositories that can be connected to the BI Server (with one marked as “default”, and
• The various data sources, such as Oracle, DB/2, Informix and SQL Server

Conclusions

So the BI server has some of the characteristics of a BI tool (metadata model, connectivity to data sources, security etc) and some of a regular relational database (query processing, optimization, rewrite, aggregate navigation etc) but without OLTP database features such as transactions. Its primary job is to process incoming requests against this metadata model and translate them into the physical queries required to get the data from the underlying data sources, acting more as a query broker with no data being stored locally except that held in the cache. If you’re interested in a bit more history of the BI Server, including its origins as a search engine called the nQuire Query Server, take a look at this old blog post on the origins of Siebel Analytics and OBIEE where I’ve written up some of the original origins of the OBIEE product set.

The BI Server has one main configuration file, held at $ORACLEBI/server/config/NQSConfig.INI, which contains parameter settings in plain text. The full set of possible parameters are held in the Server Administrators’ Guide within the Oracle docs, and this method of holding parameter settings looks like it’ll be carried across to 11g, although the settings themselves will be maintained through Enterprise Manager rather than the Administration tool as is the case with 10g and earlier.

For now though, that’s it for architecture and components and in the next posting, I’ll be looking at how the BI Server, and in particular the Navigator, handles incoming requests.

Subversion is a great source control system. One of the great features is it’s branching and merging support. Although many developers avoid it, branching is very powerful and useful and should not be something to be afraid off but something to be familiar with. And for the stable and controlled development is it almost a […]

This article contains the resources for an article on Complex Event Processing (using Oracle CEP) that is published in the March 2010 issue of the Dutch Java Magazine. This article describes the interaction between CEP and Java Applications, using examples of temperature sensors that are monitored (aggregating their readings and looking out for any broken […]

A lot of current projects are implementing some sort of service based architecture. Testing in this architecture becomes more complex. When implementing an OSB project with Scrum you test-automation is imperative. Scrum will require more frequent testing of your system. This is only feasible (in time and money) when you automate as much as possible.
 
Using […]