Rittman Mead - Delivered Intelligence

In the first article on this series about the Oracle BI Server, I looked at the architecture and functions within this core part of the OBIEE product set. in this article, I want to look closer at what happens when a query (or “request”) comes into the BI Server, and how it translates it into the SQL, MDX, file and XML requests that then get passed to the underlying data sources.

In the previous article, I laid out a conceptual diagram of the BI Server and talked about how the Navigator turned incoming queries into one or more physical database queries. As a recap, here’s the architecture diagram again:

Now as we all know, the BI Server uses a three-layer metadata model that exposes one or more databases (or “subject areas”) for ODBC-compliant query tools to run queries against. Here’s a typical metadata model that takes a number of physical data sources, joins them together into a smaller number of business model and mapping models, and then presents them out to the query tool (usually, Oracle BI Answers) as a set of databases made up of relational tables, columns and joins.

Usually you access this metadata model using Oracle BI Answers, which presents you with an initial choice of subject areas (databases in ODBC terminology) and then displays the contents of one of them as a list of tables and columns (in 11g, you’ll be able to to include tables from multiple subject areas in queries as long as there are tables in common between them).

Other ODBC-compliant query tools, such as Microsoft Excel, Cognos or Business Objects, can access these subject areas and run queries against them just as if it was a regular database. Here’s Microsoft Excel 2007 building a query against the same subject area:

What Happens When the BI Server Handles a Query?

So just what happens then, when a query (or “request’) comes in from one of these sources, and needs to be processed in order to return results to the user? As you’re probably aware, the BI Server doesn’t itself hold data (except cached results from other queries, when this feature is enabled); instead, it translates the incoming “logical” query into one or more outgoing “physical” queries against the relevant data sources. As such, a logical model presented to users might be mapped to data in an Oracle data warehouse, an E-Business Suite application, some data in a Teradata data warehouse, some measures in an Essbase cube and even some data in an Excel spreadsheet. The BI server resolves this complexity by creating a simplified, star schema business model over these data sources so that the user can query it as if it’s a single source of data.

If you’re used to the Oracle database, you’ll probably know that it has various components that are used to resolve queries – the library cache, query rewrite, table and system statistics, etc – and both rule-based and cost-based optimizers that are used to generate a query plan. For most modern Oracle systems, a statistics-based cost-based optimizer (most famously documented by Jonathan Lewis in this book) is used to generate a number of potential execution plans (which can be displayed in a 10035 trace), with the lowest cost being chosen to run the query. Now whilst the equivalent process isn’t really documented for the BI Server, what it appears to do is largely follow a rule-based approach with a small amount of statistics being used (or not used, as I’ll mention in a moment). In essence, the following sources of metadata information are consulted when creating the query plan for the BI Server;

• The presentation (subject area) layer to business model layer mapping rules;
• The logical table sources for each of the business columns used in the request;
• The dimension level mappings for each of the logical table sources;
• The “Number of Elements at this Level” count for each dimension level (potentially the statistics bit, though anecdotally I’ve heard that these figures aren’t actually used by the BI Server);
• Whether caching is enabled, and if so, whether the query can be found in the cache;
• What physical features are enabled for the particular source database for each column (and whether they are relational, multi-dimensional, file, XML or whatever)
• Specific rules for generating time-series queries, binning etc, and
• Security settings and filters

As far as I can tell, there are no indexes, no statistics (apart from the dimension level statistics mentioned above) and no hints; there is however query rewrite and aggregates, as the BI Server allows aggregate tables to be defined which are then mapped in to specific levels in a dimension hierarchy. Cleverly, the back-end data source doesn’t even have to be an SQL database, and can in fact be a multi-dimensional database such as Essbase, Oracle OLAP or Microsoft Analysis Services, with the multi-dimensional dataset that they return converted into a row-based dataset that can be joined to other data coming in from a more traditional relational database.

“A Day in the Life of a Query”

A good way of looking at what Oracle has termed “A day in the life of a query”, is to take a look at some slides from a presentation that Oracle used regularly around the time of the introduction of Oracle BI EE. I’ll go through it slide by slide and add some interpretation from myself.

1. A query comes in from Answers or any other ODBC query tool, asking for one or more columns from a subject area. Overall, the function within the BI Server that deals with this is called Intelligent Request Generation, marked in yellow in the diagram below.

2. The query is then passed to the Logical Request Generation engine, marked in yellow in the diagram below. The request itself requires the Brand, Closed Revenue (ultimately held in the GL system), Service Requests (held in the CRM system) and Share of Revenue (a calculated, or derived, measure). As such it’s going to require multiple physical SQL queries and multi-pass calculations, all of which will be worked out by another part of the BI Server architecture, the Navigator.

3. Once the logical request has been generated but before its passed off to the Navigator, a check is made (if this feature is enabled) as to whether the logical request can be found in the cache. Cache Services will either do a fast, or more comprehensive match of the incoming request against those stored in the query cache, and if found, return the results from there rather than have the BI Server run physical SQL against the business model’s data sources.

For a more detailed look at what Cache Services does, the old Siebel Analytics Administration Tool documentation has a good flowchart that explains what goes on:

The key bit is the Cache Hit step. In general, a cache hit will occur if the following conditions are met:

• Caching is enabled (CACHE=Y in the NQSConfig.INI file);
• The WHERE clause in the logical SQL is semantically the same, or a logical subset of a cached statement;
• All of the columns in the SELECT list have to exist in the cached query, or they must be able to be calculated from them;
• It has equivalent join conditions, so that the resultant joined table of any incoming query has to be the same as (or a subset of) the cached results
• If DISTINCT is used, the cached copy has to use this attribute as well
• Aggregation levels have to be compatible, being either the same or more aggregated than the cached query
• No further aggregation (for example, RANK) can be used in the incoming query
• Any ORDER BY clause has to use columns that are also in the cached SELECT list

In addition, there are two NQSConfig.INI parameters that I think were added in the last few releases (as I can’t find them mentioned in the Siebel Analytics documentation) are USE_ADVANCED_HIT_DETECTION and MAX_SUBEXPR_SEARCH_DEPTH. The latter determines how many levels into an expression (for example, SUM(MAX(SIN(COS(TAN(ABS(TRUNC(PROFIT)))))))) that the cache hit detector will go in trying to get a match, whilst the former turns on some additional cache hit searches that you might want to enable if caching is important but not otherwise happening. Unfortunately the docs don’t really expand on what these additional searches are or the performance impact that they can introduce, so if anyone has any more information on this, I’d be glad to hear.

4. If the cache can’t provide the answer to the request, the request then gets passed to the Navigator. The Navigator handles the logical request “decision tree” and determines how complex the request is, what data sources (logical table sources) need to be used, whether there are any aggregates that can be used, and overall what is the best way to satisfy the request, based on how you’ve set up the presentation, business model and mapping, and physical layers in your RPD.

5. Within the Navigator, the Multi-Pass / Sub-Request Logic function analyzes the incoming request and works out the complexity of the query. It works out whether it requires multiple passes (for example, calculates the average of two aggregated measures), or whether the request is based on the results of another request (in other words, uses a sub-request). The BI Server then uses this information to work out the optimal way to gather the required data and do the calculations; in the example used in the slides, the revenue share calculation is based on the other two measures and is therefore considered “multi-pass”.

6. A measure used within the business model and mapping layer may be “fragmented”, which means that it is logically partitioned so that historic information, for examples, comes from a data warehouse whilst current information comes from an OLTP application. The Fragment Optimization Engine within the Navigator sits between the incoming request and the Execution Engine and where appropriate, transforms the base-level SQL into “fragmented” SQL against each of the data sources mapped into the fragmented measure. For more background information on fragmentation, check out this old blog post on the subject.

7. The final function within the Navigator is the Aggregate Navigator, which uses the logical table source mappings together (in theory) with the dimension level statistics to determine the most efficient table to fetch the data from (i.e. the table with the least number of records to successfully fulfil a request).

8. The Optimized Query Rewrites function within the BI Server then takes the query plan generated by the Navigator and rewrites it to use the features of the underlying database engines, adding RANK(OVER()) calculations if Oracle is being used, for example (referred to as “function shipping”) or just getting the raw data and having the BI Server do the calculations afterwards, if working with a database that doesn’t support analytic SQL functions. This part of the BI Server is also responsible for generating XML queries, or MDX queries for OLAP sources,
which are then sent to the underlying physical databases, in parallel, so that they can retrieve their relevant data sets.

9. Once the data is retrieved, the results combined together and any further calculations applied, the results are returned to the calling application via the ODBC interface, and also copied to the cache along with the logical SQL query if caching is enabled.

The BI Server’s knowledge of what each source database can support, in terms of SQL functions, is determined by the contents of the DBFeatures.INI configuration file which can in turn be over-ridden by the “Features” tab in the Database settings in the physical database model.

I think I’ve also noticed that, from release to release of OBIEE, the way that time-series queries, for example, get resolved into physical SQL queries changes over time, as Oracle get better at generating efficient SQL queries to resolve complex calculations. It’s also the case that currently, for Essbase data sources, very few of the functions used by the BI Server get function-shipped to their equivalent MDX functions, though this is meant to be improving in the forthcoming 11g release (and in the meantime, you can use EVALUATE and EVALUATE_AGGR to call MDX functions directly).

Level 5 Logging, and Logical Execution Plans

You can see what goes on when a complex, multi-pass request that requires multiple data sources is sent through from Answers and gets logged in the NQQuery.log file with level 5 logging enabled. The query requests “quantity” information that is held in an Oracle database, “quotas” that comes from an Excel spreadsheet, and “variance” which is derived from quantity minus quotas. Both columns need to be aggregated before the variance calculation can take place, and you can see from the logs the Navigator being used to resolve the query.

Starting off, this is the logical request coming through.

-------------------- Logical Request (before navigation):

RqList
    Times.Month Name as c1 GB,
    Quantity:[DAggr(Items.Quantity by [ Times.Month Name, Times.Month ID] )] as c2 GB,
    Quota:[DAggr(Items.Quota by [ Times.Month Name, Times.Month ID] )] as c3 GB,
    Quantity:[DAggr(Items.Quantity by [ Times.Month Name, Times.Month ID] )] - Quota:[DAggr(Items.Quota by [ Times.Month Name, Times.Month ID] )] as c4 GB,
    Times.Month ID as c5 GB
OrderBy: c5 asc

Then the navigator breaks the query down, works out what sources, multi-pass calculations and aggregates can be used, and generates the logical query plan.

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

RqList <<993147>> [for database 0:0,0]
    D1.c1 as c1 [for database 0:0,0],
    D1.c2 as c2 [for database 3023:491167,44],
    D1.c3 as c3 [for database 0:0,0],
    D1.c4 as c4 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<993160>> [for database 0:0,0]
    (
        RqList <<993129>> [for database 0:0,0]
            D1.c1 as c1 [for database 0:0,0],
            D1.c2 as c2 [for database 3023:491167,44],
            D1.c3 as c3 [for database 0:0,0],
            D1.c4 as c4 [for database 0:0,0],
            D1.c5 as c5 [for database 0:0,0]
        Child Nodes (RqJoinSpec): <<993144>> [for database 0:0,0]
            (
                RqBreakFilter <<993128>>[1,5] [for database 0:0,0]
                    RqList <<992997>> [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],
                        D903.c3 as c2 GB [for database 3023:491167,44],
                        D903.c4 as c3 GB [for database 0:0,0],
                        D903.c3 - D903.c4 as c4 GB [for database 0:0,0],
                        case  when D903.c5 is not null then D903.c5 when D903.c6 is not null then D903.c6 end  as c5 GB [for database 0:0,0]
                    Child Nodes (RqJoinSpec): <<993162>> [for database 0:0,0]
                        (
                            RqList <<993219>> [for database 0:0,0]
                                D902.c1 as c1 [for database 0:0,0],
                                D901.c1 as c2 [for database 3023:491167,44],
                                D901.c2 as c3 GB [for database 3023:491167,44],
                                D902.c2 as c4 GB [for database 0:0,0],
                                D902.c3 as c5 [for database 0:0,0],
                                D901.c3 as c6 [for database 3023:491167,44]
                            Child Nodes (RqJoinSpec): <<993222>> [for database 0:0,0]

                                    (
                                        RqList <<993168>> [for database 3023:491167:ORCL,44]
                                            D1.c2 as c1 [for database 3023:491167,44],
                                            D1.c1 as c2 GB [for database 3023:491167,44],
                                            D1.c3 as c3 [for database 3023:491167,44]
                                        Child Nodes (RqJoinSpec): <<993171>> [for database 3023:491167:ORCL,44]
                                            (
                                                RqBreakFilter <<993051>>[2] [for database 3023:491167:ORCL,44]
                                                    RqList <<993263>> [for database 3023:491167:ORCL,44]
                                                        sum(ITEMS.QUANTITY by [ TIMES.MONTH_MON_YYYY] ) as c1 [for database 3023:491167,44],
                                                        TIMES.MONTH_MON_YYYY as c2 [for database 3023:491167,44],
                                                        TIMES.MONTH_YYYYMM as c3 [for database 3023:491167,44]
                                                    Child Nodes (RqJoinSpec): <<993047>> [for database 3023:491167:ORCL,44]
                                                        TIMES T492004
                                                        ITEMS T491980
                                                        ORDERS T491989
                                                    DetailFilter: ITEMS.ORDID = ORDERS.ORDID and ORDERS.ORDERDATE = TIMES.DAY_ID [for database 0:0]
                                                    GroupBy: [ TIMES.MONTH_MON_YYYY, TIMES.MONTH_YYYYMM]  [for database 3023:491167,44]
                                            ) as D1
                                        OrderBy: c1 asc [for database 3023:491167,44]
                                    ) as D901 FullOuterStitchJoin <<993122>> On D901.c1 =NullsEqual D902.c1; actual join vectors:  [ 0 ] =  [ 0 ]

                                    (
                                        RqList <<993192>> [for database 0:0,0]
                                            D2.c2 as c1 [for database 0:0,0],
                                            D2.c1 as c2 GB [for database 0:0,0],
                                            D2.c3 as c3 [for database 0:0,0]
                                        Child Nodes (RqJoinSpec): <<993195>> [for database 0:0,0]
                                            (
                                                RqBreakFilter <<993093>>[2] [for database 0:0,0]
                                                    RqList <<993319>> [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]
                                                    Child Nodes (RqJoinSpec): <<993334>> [for database 0:0,0]
                                                        (
                                                            RqList <<993278>> [for database 3023:496360:Quotas,2]
                                                                sum(QUANTITY_QUOTAS.QUOTA by [ MONTHS.MONTH_MON_YYYY] ) as c1 [for database 3023:496360,2],
                                                                MONTHS.MONTH_MON_YYYY as c2 [for database 3023:496360,2],
                                                                MONTHS.MONTH_YYYYMM as c3 [for database 3023:496360,2]
                                                            Child Nodes (RqJoinSpec): <<993089>> [for database 3023:496360:Quotas,2]
                                                                MONTHS T496365
                                                                QUANTITY_QUOTAS T496369
                                                            DetailFilter: MONTHS.MONTH_YYYYMM = QUANTITY_QUOTAS.MONTH_YYYYMM [for database 0:0]
                                                            GroupBy: [ MONTHS.MONTH_YYYYMM, MONTHS.MONTH_MON_YYYY]  [for database 3023:496360,2]
                                                        ) as D1
                                                    OrderBy: c2 [for database 0:0,0]
                                            ) as D2
                                        OrderBy: c1 asc [for database 0:0,0]
                                    ) as D902
                        ) as D903
                    OrderBy: c1, c5 [for database 0:0,0]
            ) as D1
        OrderBy: c5 asc [for database 0:0,0]
    ) as D1

Notice the “FullOuterStitchJoin” in the middle of the plan? We’ll look into this more in the next posting in this series. For now though, this logical query plan is then passed to the Optimized Query Rewrites and Execution Engine, which then generates in this case two physical SQL statements that are then passed back, and “stitch joined”, by the BI Server, before performing the post-aggregation calculation required for the variance measure.

-------------------- Sending query to database named ORCL (id: <<993168>>):

select D1.c2 as c1,
     D1.c1 as c2,
     D1.c3 as c3
from
     (select D1.c1 as c1,
               D1.c2 as c2,
               D1.c3 as c3
          from
               (select sum(T491980.QUANTITY) as c1,
                         T492004.MONTH_MON_YYYY as c2,
                         T492004.MONTH_YYYYMM as c3,
                         ROW_NUMBER() OVER (PARTITION BY T492004.MONTH_MON_YYYY ORDER BY T492004.MONTH_MON_YYYY ASC) as c4
                    from
                         CUST_ORDER_HISTORY.TIMES T492004,
                         CUST_ORDER_HISTORY.ITEMS T491980,
                         CUST_ORDER_HISTORY.ORDERS T491989
                    where  ( T491980.ORDID = T491989.ORDID and T491989.ORDERDATE = T492004.DAY_ID )
                    group by T492004.MONTH_MON_YYYY, T492004.MONTH_YYYYMM
               ) D1
          where  ( D1.c4 = 1 )
     ) D1
order by c1

+++Administrator:2b0000:2b000e:----2010/02/23 16:04:42

-------------------- Sending query to database named Quotas (id: <<993278>>):
select sum(T496369."QUOTA") as c1,
     T496365."MONTH_MON_YYYY" as c2,
     T496365."MONTH_YYYYMM" as c3
from
     "MONTHS" T496365,
     "QUANTITY_QUOTAS" T496369
where  ( T496365."MONTH_YYYYMM" = T496369."MONTH_YYYYMM" )
group by T496365."MONTH_YYYYMM", T496365."MONTH_MON_YYYY"

Memory Usage and Paging Files

If you follow the BI Server at the process level during these steps, you’ll find that memory usage is largely determined at startup time by the size and complexity of the RPD thats attached online, and then goes up by around 50MB when the first query is executed. After that, memory usage tends to go up the more concurrent sessions that are run, and also when cross-database joins are performed. You’ll also find TMP files being created in $ORACLEBIDATA/tmp directory, which are used by the BI Server to hold temporary data as it pages out from memory, again typically when cross-database joins are used but also when it needs to perform additional aggregations that can’t be put into the physical SQL query.

These files can get fairly big (up to 2GB in some cases) and can be created even when a single data source is used, typically for grouping data or as we’ll see in the next posting, when joining data across fact tables. They are usually cleared down when the BI Server and Presentation Server are restarted, but bear in mind when creating complex calculations that they can get pretty I/O intensive on the BI Server hardware.

So that’s the basics in terms of how basic queries are processed by the BI Server, and how the various BI Server components and engines process the query as it goes through the various stages. Again, if anyone knows any more, please add it as a comment, but for now that’s it and I’ll be back in a few days with part 3, on BI Server In-Memory Joins.

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

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.

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.

One of the things that I often come across is the “up-dateable fact”, that is a fact that starts life “incomplete” and changes overtime. Examples include things such as support calls that start life as “open” then progress through “responded”, “resolved” and finally “closed”; statuses in the sales cycle such as ordered, paid, shipped; stock movements in a warehouse – goods received and dispatched. Of course the business, rightly, needs to measure the times between stages or the number or value of transactions at each stage.

As a principle, I hate the idea of having to update a fact. A fact has happened, it is not going to change. I suppose to be more accurate a “change” is a new event, a new fact, a new fact occurring at a different time. So how to model this? – well instinctively I would go for a table that is only inserted (preferably appended to – think set based!) containing whatever dimensions are needed (don’t forget the ‘when’ dimension) PLUS an ‘EVENT’ dimension (one row per expected status) and the measures (how many, how much etc). To report on this we need to rotate the table so that the events that belong to single item appear in the same row. Before Oracle 11g we would need to construct some SQL using a mix of case statements and analytic functions to rotate the data. But now we have a potentially better way the, Oracle 11g Pivot operator.

Here we define a set of dimensions for the row (similar to the dimensions in a Group BY clause), the aggregation operators for the pivoted measures – which of course could include MIN() or MAX() for the cases when want to pivot DATE types. We also need to define the dimensions we want to pivot by, and here we can actually choose multiple dimensions; this again is somewhat similar to the GROUP BY of traditional SQL. Remember though when we pivot we sometimes only expect to ‘aggregate’ a single row – if we want to pivot order date and dispatch date then we probably have just one of each!.

So how does it look? Well the Oracle 11g documentation describes the syntax and gives some examples – here I am showing a slightly more complicated case where we are pivoting by two dimensions, each with a known set of code values. This example is based on two of the examples in the Oracle 11g Data Warehousing Guide

	SELECT * FROM	(
		SELECT product, channel, quarter, quantity_sold FROM sales_view
		) PIVOT (SUM(quantity_sold) as SUMQ, SUM(amount_sold) as SUMS
			FOR (channel, quarter) IN
			((5, '02') AS CATALOG_Q2,
		 	(4, '01') AS INTERNET_Q1,
		 	(4, '04') AS INTERNET_Q4,
		 	(2, '02') AS PARTNERS_Q2,
		 	(9, '03') AS TELE_Q3
			) );

The query returns a column for the product and for each of the specified pairs of channel and quarter a column for each measure. So we get columns for:

PRODUCT, CATALOG_Q2_SUMQ, CATALOG_Q2_SUMS, INTERNET_Q1_SUMQ, INTERNET_Q1_SUMS, INTERNET_Q4_SUMQ, INTERNET_Q4_SUMS, PARTNERS_Q2_SUMQ, INTERNET_Q4_SUMS, TELE_Q3_SUMQ, and TELE_Q3_SUMS

Note how the the measure name is concatenated to the alias in the in list.
As you can see we don’t need to specify each combination of channel and quarter – just the ones we want in our pivoted view. We also don’t use a GROUP BY clause – we specify the columns we want to see (both the dimensions and the aggregations) and Oracle implicitly groups by all of the columns not in aggregated functions.

In my example I used SELECT * to wrap the inline pivot, in practice I would explicitly select the columns and perhaps alias them to more meaningful names than the concatenated ones generated by Oracle. I would also expose the pivot as database view and thus access it from OWB or OBIEE where it appears to be just another table or view.

Another point to note is that you might see null values in the pivoted measures and these can be due to one of two reasons: the value stored for that combination of dimensions (in our case channel and quarter) is actually NULL, or that the combination does not exist. If you need to (and you may not need to) you can differentiate by using a COUNT measure; if the count is zero then the combination does not exist in the source table, if one or more then the source has NULLs stored for the combination.

We used a similar pivot view to the one above to monitor stock movements in a warehouse – in this case we needed to track individual batches of product from multiple potential suppliers, so in addition to the product dimension we had dimensional columns for batch id (a degenerate dimension) and supplier. The view was then exposed to OWB to allow us to include the aggregated result set in our ETL process – we needed to calculate some additional measures based on the difference between two of the pivoted columns. The Pivot operator greatly simplified our ETL for this fact – we could easily write an ETL process with a straight aggregation then pivot the results with CASE statements or DECODES or whatever – but that would have been less clear and also increased the number of “moving parts”.

We have had no problems with performance with our data set – 80 million rows pivoted on Exadata to just a few seconds. But it was not too slow on our non-exadata development machine either.