Oracle BI with multiple Repositories and MySQL Datasource

A probably well known fact is that Oracle BI server can serve multiple repositories at the same time.  Equally well known is that MySQL (or any ODBC source) can be accessed by Oracle BI. At least theoretically.  In fact, it works, but there are some pitfalls, which one should be aware of. Ok, it sounds a little unrelated to each other and one can ask – why bother, in most cases it is still Oracle, oc4j and only one instance per machine.  Right. The problem with this standard configuration (of course, it is only my personal opinion) – it is delivered and works out of the box.  It is not bad at all, but most part of configuration is done in background and the person who implement oracle bi doesn’t get a chance to understand configuration in such default environment.  Choosing nonstandard components ( and it is often a requirement in a custom project) provides better understanding how single components work together.

OBIEE Architecture

OBIEE Architecture

In general it isn’t at all the most terrible idea to have a look into documentation and try to understand architecture of the software product before start to install or configure it.  From the picture (borrowed from the oracle documentation) it is clear that the 3 components (marked red on the picture) are the most important in this architecture:

  • j2ee server (web application containter) which communicates with presentation services and brings the data to the client (web browser)
  • presentation services which communicates with web tier and with oracle bi server (over odbc)
  • oracle bi server which performs the actual bi queries and communicate with data source (rdbms or odbc source or xml file)

To serve multiple repositories only one instance of bi server is needed, corresponding rpd files should be placed into OracleBI_HOME/server/Repository directory:

tree  ../server/Repository/ -P "*.rpd"
../server/Repository/
|-- paint.rpd
|-- sakila.rpd
|-- samplesales.rpd
`-- sh.rpd

and every repository should be referenced in oracle bi config file (NQSConfig.INI):

[ REPOSITORY ]

// Star = samplesales.rpd, DEFAULT;
samplesales = samplesales.rpd, DEFAULT;
sh = sh.rpd;
sakila = sakila.rpd;

That’s all regarding bi server, for presentation service a little bit more work is required, but first i would like to show how MySQL database and odbc DSN should be configured (because the blog title implies, not only multiple repositories, but one of them with MySQL as datasource). To create a database (please, don’t blame me for absolutely no security consideration, it’s only an example) – install mysql with any suitable tool – for example with yum. My test system is Cent OS 5.3 x86-64 machine, on the RHEL or OEL setup is absolutely the same, 32bit platform don’t differ much ( but there are some differences, which i’ll  mention later). After that, assuming mysql is started:

 mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database sakila;
Query OK, 1 row affected (0.02 sec)
mysql> grant all privileges on sakila.* to biee@'localhost' identified by 'biee' with  grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on sakila.* to biee@'%' identified by 'biee' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> Bye
mysql -u root sakila < sakila-schema.sql
mysql -u root sakila < sakila-data.sql

The demo database sakila can be downloaded from MySQL documentation page. Next step is to configure and test an odbc connection for this datasource. To do that, add to the system odbc config file ( /etc/odbc.ini) following section:

[sakila]
Driver       = /usr/lib64/libmyodbc3.so
Description  = Connector/ODBC 3.51 Driver DSN
SERVER       = localhost
PORT         = 3306
USER         =
Password     =
Database     = sakila
OPTION       =
SOCKET       = /var/lib/mysql/mysql.sock

and test the connection with the ODBC client isql

isql sakila biee biee
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select table_name,engine FROM information_schema.tables where table_schema='sakila';
+-----------------------------------------------------------------+-----------------------------------------------------------------+
| table_name                                                      | engine                                                          |
+-----------------------------------------------------------------+-----------------------------------------------------------------+
| actor                                                           | InnoDB                                                          |
| actor_info                                                      |                                                                 |
| address                                                         | InnoDB                                                          |
| category                                                        | InnoDB                                                          |
| city                                                            | InnoDB                                                          |
| country                                                         | InnoDB                                                          |
| customer                                                        | InnoDB                                                          |
| customer_list                                                   |                                                                 |
| film                                                            | InnoDB                                                          |
| film_actor                                                      | InnoDB                                                          |
| film_category                                                   | InnoDB                                                          |
| film_list                                                       |                                                                 |
| film_text                                                       | MyISAM                                                          |
| inventory                                                       | InnoDB                                                          |
| language                                                        | InnoDB                                                          |
| nicer_but_slower_film_list                                      |                                                                 |
| payment                                                         | InnoDB                                                          |
| rental                                                          | InnoDB                                                          |
| sales_by_film_category                                          |                                                                 |
| sales_by_store                                                  |                                                                 |
| staff                                                           | InnoDB                                                          |
| staff_list                                                      |                                                                 |
| store                                                           | InnoDB                                                          |
+-----------------------------------------------------------------+-----------------------------------------------------------------+
SQLRowCount returns 23
23 rows fetched
SQL> quit

For odbc connections oracle bi uses its own odbc. ini , which is located in OracleBI_HOME/setup/odbc.ini. After being ensured odbc connection works, the simplest step is to copy the odbc.ini entry for this database. Indeed, it is enough on 32bit platform, but doesn’t work on x86-64 – oracle bi couldn’t connect to my database. For troubleshouting, the usual suspicious were very useful (as expected):

  • bi server log (Oracle_BI_HOME/Log/NQServer.log)
  • odbc trace file (activated by following section in oracle bi  odbc.ini file)
  • [ODBC]
    # Trace=0
    Trace=1
    TraceFile=/tmp/odbctrace.out
    TraceDll=/opt/biee/OracleBI/odbc/lib/odbctrac.so
    InstallDir=/opt/biee/OracleBI/odbc
    UseCursorLib=0
    IANAAppCodePage=4
    
  • system calls trace ( strace -f -o /tmp/nqsserver.trc -p process_id_of_bi_server)

In short, the issue is:  oracle bi on linux exists only as 32 bit software. That is not a problem to run it on x64 bit system, oracle software itself works fine. But supplied odbc driver manager ( from data direct) is in 32bit version as well ( probably 32bit nqsserver can only be accessed over 32bit odbc driver) and it’s unable to load 64bit linux library for mysql odbc driver. Well, but it should be possible to access 64bit database with 32bit odbc client – was my first thought. Indeed, it works, but odbc driver in 32bit version  doesn’t exist for x64 CentOS ( and i assume – RHEL and OEL ) distribution. I don’t think, it’s a bug, in opposite, i tend to assume – there are some conflicts in running 32bit odbc client on 64bit system, however, i could not find any issue in internet or on my test system – and workaround is rather simple – download odbc driver from 32bit repository and use it for bi server. This don’t work out of the box – bi server driver manager can not load 32bit driver as well – but for another reason – it can’t find 32 bit mysql client libraries. Simple to solve – just extend LD_LIBRARY_PATH  appropriated (the same is very true regarding Oracle client on a 64bit Linux – oracle bi has to be pointed to 32bit libraries as well) , here is the final configuration for biee os user (first one for environment and second for OracleBI_HOME/setup/odbc.ini) :

echo $LD_LIBRARY_PATH
/opt/oracle/product/10.2.0.4/lib32:/usr/lib:/lib:/usr/lib/mysql:/usr/lib:
[sakila]
Driver = /usr/lib/libmyodbc3.so
DATABASE = sakila
DESCRIPTION = sakila
USER =
PWD =
PORT = 3306
SERVER = localhost
SOCKET = /var/lib/mysql/mysql.sock

[AnalyticsWeb01]
Driver=/opt/biee/OracleBI/server/Bin/libnqsodbc.so
Description=Oracle BI Server
ServerMachine=local
Repository=samplesales
Catalog=
UID=
PWD=
Port=9703

[AnalyticsWeb02]
Driver=/opt/biee/OracleBI/server/Bin/libnqsodbc.so
Description=Oracle BI Server
ServerMachine=local
Repository=sh
Catalog=
UID=
PWD=
Port=9703

[AnalyticsWeb03]
Driver=/opt/biee/OracleBI/server/Bin/libnqsodbc.so
Description=Oracle BI Server
ServerMachine=local
Repository=sakila
Catalog=
UID=
PWD=
Port=9703

The first DSN will be used by bi server itself to connect to datasource, the other 3 – by presentation services to connect to bi server.  It means, while bi server can serve 3 different repositories, at the same time, 3 instances of presentation services are needed (separate instance for each repository). All of them connect to the bi server on the port 9703 and to distinguish different repositories they use different DSN’s. They need as well a listening address ( to enable communication with web tier) and it should be different for each instance of course. To implement it – 3 different configuration files are required ( instead of  instanceconfig.xml in OracleBIData_HOME/web/config ) , which can be created as copies of instanceconfig.xml) . Default configuration uses 9710 as listening port, so, a smart idea could be to use 9711 ans 9712 for additional config files. For example, the relevant (changed)  section from instanceconfig02.xml and instanceconfig03.xml looks like:


 AnalyticsWeb02

/opt/biee/OracleBIData/web/catalog/sh

 AnalyticsWeb03

/opt/biee/OracleBIData/web/catalog/sakila

After that – 3 instances of presentation services can be started (the sawserver executables accept a -c commandline parameter to specify a configuration file). Unfortunately, delivered run-saw.sh doesn’t allow to start multiple instances, so i wrote a customized start script (i apologize for inconvinient media type – but the script is too long to post it here and i am really newby in regard to wordpress , i.e. i didn’t found a way to upload a shell script, that’s why i chose a pdf) , which does the same as supplied script, but additionally

  • allows start a single instance with specified config file ( either full path to instanceconfig.xml or only basename)
  • allows stop of a single instance with specified config file
  • checks existence of specified config file
  • takes care to write to separate log file for each instance
  • takes care to not start a javahost server only by startup of first instance presentation services and shut down only if the last instance is shutdown

I consider it as dirty hack, although, it works fine for me on Linux – but probably, there are some bugs , additionally i didn’t test it on solaris or hpux – so, please test it carefully if you decide to use it.  After all, the typical use case for it looks like:

./run-config-saw.sh -c instanceconfig02.xml start
Oracle BI Presentation Services with process id 27097 and config instanceconfig02.xml is already running.
Oracle BI Java Host with process id 26428 is already running
./run-config-saw.sh -c instanceconfig02.xml stop
Stopping Oracle BI Presentation Services with process id 27097 and config instanceconfig02.xml
./run-config-saw.sh -c instanceconfig02.xml start
Starting Oracle BI Presentation Services with config file instanceconfig02.xml
Oracle BI Presentation Services startup initiated.
./run-config-saw.sh -c whateveryoutypewrong start
Config File /opt/biee/OracleBIData/web/config/whateveryoutypewrong doesn't exist
Exit now ...

Now, after bi server and presentation services are configured, the last tier – webserver deployment should be completed. In my setup i used WebLogic (i consider myself slightly biased in regard to it – i like, how this software is designed and implemented), but the process is very similar on JBoss, Tomcat,OC4J or whatever you choose. To deploy the analytics.war under bea as 3 different applications – simply create 3 different directories under chosen deployment directory ( could be anywhere in system), extract analytics.war archive in each of them and modify web.xml configuration file to reflect the listening port of presentation services.

cd /opt/biee/Middleware
mkdir deployments
cd deployments
mkdir analytics0{1,2,3}
for dir in analytics*
do
 (cd $dir;jar -xf /opt/biee/OracleBI/web/analytics.war)
done

After modifiing web.xml for each application, it looks like

 cat analytics0{1..3}/WEB-INF/web.xml|grep -C 3 oracle.bi.presentation.sawserver.Port
localhost

oracle.bi.presentation.sawserver.Port
9710

--
localhost

oracle.bi.presentation.sawserver.Port
9711

--
localhost

oracle.bi.presentation.sawserver.Port
9712

The last step is to go to WebLogic Admin console and deploy these web applications ( it can be done with wlst as well)  – some screenshots  follows

After deployment it should be possible to access every single Oracle BI presentation service under its url (i configured WebLogic server to listen on Port 7003 – like in my previous blog post) :

Last thing to be mentioned – the configuration with multiple presentation services on the same host is not officially supported by oracle. But for development purposes it may be fully acceptable  – as long as it works.

Deployment Step 1

Deployment Step 1

Deployment Step 2

Deployment Step 2

Deployment Step 3

Deployment Step 3

Deployment Step 4

Deployment Step 4

Deployment Step 5

Deployment Step 5

Deployment Step 6

Deployment Step 6

Advertisements

3 thoughts on “Oracle BI with multiple Repositories and MySQL Datasource

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s