Rittman Mead

(1)

Rittman Mead -

Deliver

ed Intelligence

Inside the Oracle BI Server Part 1

: The BI Server

Architecture

February 25th, 2010 by

February 25th, 2010 by Mark RittmanMark Rittman The session that I’m giving at the

The session that I’m giving at the BI Forum in Brighton in MayBI Forum in Brighton in Mayis entis entitled “Inside the Oracle BIitled “Inside the Oracle BI Server”, and I’m aiming to take a

Server”, and I’m aiming to take a closer look at the architecture and closer look at the architecture and functionality of this keyfunctionality of this key OBIEE component. We’re all fairly aware of what the

OBIEE component. We’re all fairly aware of what the BI Server does at a high level, BI Server does at a high level, but Ibut I thought it’d be interesting to take a clos

thought it’d be interesting to take a closer look at what the er look at what the BI Server does, particularly when itBI Server does, particularly when it parses queries and joins datasets together.

parses queries and joins datasets together. At a very high level, the

At a very high level, the main function of the BI Server is to process inbomain function of the BI Server is to process inbound SQL requestsund SQL requests against against a virtual database model, build and execute one or more physical database against against a virtual database model, build and execute one or more physical database queries, process the data and then

queries, process the data and then return it to users. The BI Server is one preturn it to users. The BI Server is one part of the Oracle BIart of the Oracle BI Enterprise Edition Plus product family, and presents itself to query tools as o

Enterprise Edition Plus product family, and presents itself to query tools as o ne or morene or more databases in a simple relational (star schema) model, that can

databases in a simple relational (star schema) model, that can then point to a much then point to a much more complexmore complex set of relational, multidimensional, file and XML data sources (and in

set of relational, multidimensional, file and XML data sources (and in 11g, ADF objects).11g, ADF objects). Taking the standard OBIEE architecture diagram, the

Taking the standard OBIEE architecture diagram, the BI Server sits in the middle of the OBIEEBI Server sits in the middle of the OBIEE set of servers and provides the query cap

set of servers and provides the query capability, security, interfaces to data sources andability, security, interfaces to data sources and calculation logic for OBIEE (all of this is based on

calculation logic for OBIEE (all of this is based on the current, 10g set of products).the current, 10g set of products).

The BI Server communicates with the BI Presentation Server via ODBC, and then connects out The BI Server communicates with the BI Presentation Server via ODBC, and then connects out to the various supported data sources through

(2)

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.

 TheODBC 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;

 TheLogical Business Model, the three-layer metadata model that describes the data

available for queries;

 TheIntelligent 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:

(3)

 TheNavigator, 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 whethermultiple physical

queries are needed, whether storedaggregates can be used, and whetherfragmented

data sources can be used for partitioned measures;

 AnOptimized Query Rewrite engine for handling aggregate navigation and fragments,

and for translating to the correct physical SQL dialect, and

 AnExecution 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

(4)

We’ll get on to memory usage in a future posting in this series, but in general the amou nt 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 $ORAC LEBIDATA/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:

(5)

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:

(6)

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:

(7)

 TheSecurity Model, presumably the users and groups in the RPD, plus the filters and

subject area security in the repository;

 TheBusiness Model, the three-layer metadata model;

 Aggregate Navigation, for rewriting queries to use mapped in aggregate tables;

 SQL Generation Engine andMulti-database Query Processing, presumably the bit

that takes the database capabilities matrix and generates the correct physical SQL for the various data sources;

 TheComputation Engine, for performing in-memory stitch joins, post-aggregation

filters and functions, and sorting,

 Query cachiing

 TheMetadata Repositories that can be connected to the BI Server (with one marked as

“default”, and

 The variousdata 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,

(8)

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 B I 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.

Localization of Oracle BI Server Components

The Oracle Business Intelligence Presentation Services user interface works in different

languages, with a default Error Message language of English. Any error or warning messages appear in English. To receive localized Oracle BI Server messages (such as error, warning and information messages), you must set the locale during the platform installation, in the Error Message Language screen, as described in Oracle BI Installer Screens and Prompts.

The primary mechanism for displaying localized table and column names is Externalize

Metadata Strings. To determine which table and column names are localized, see the topic Using the Externalize Strings Utility for Localization.

Localized Oracle BI Components

 Web interface

 Oracle BI Presentation Services messages:  error

 warning  information

 Oracle BI Server functions:  DayName

 MonthName

NOTE: If a query is issued using the DayName or MonthName function, but the function is not shipped to a back-end database, then the day or month name is returned in the localized language but the column name remains in English (or may be affected by other localization controls). As an example of this situation, if the LOCALE parameter is set for German, the MonthName query returns the string "Mai" but the column header remains "Name of Month."

(9)

 Oracle BI Server and Scheduler messages:  error

 warning  information  Log files:

 nQServer.log for Oracle BI Server  nqQuery.log for Oracle BI Server

 If Clustering is enabled, nQCluster.log for Oracle BI Ser ver Cluster  Metadata:

 Oracle BI Presentation Services dashboards and reports (Oracle BI Catalog)  Presentation table and column names (.rpd file)

 Informatica (ENU and JPN locales only)  Disconnected Client interface

Unlocalized Oracle BI Components

 Server Administration Tool interface  Scheduler Job Manager interface  ODBC client tools:

 nqcmd.exe (UNIX)  nQCmd.exe (Windows)  nQClient.exe (Windows)  Installer