comsysto
DamirAbdic
MaximDemenko
Tomislav Zorc
Daniel Bartl
Peter Dmytrasz
Oracle BI with multiple Repositories and MySQL Datasource
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. Chosing nonstandard components ( and it is often a requirement in a custom project) provides better understanding how single components work together.
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:
<WebConfig> <ServerInstance> <DSN>AnalyticsWeb02</DSN> <Listener port="9711" /> <CatalogPath>/opt/biee/OracleBIData/web/catalog/sh</CatalogPath>
<WebConfig> <ServerInstance> <DSN>AnalyticsWeb03</DSN> <Listener port="9712" /> <CatalogPath>/opt/biee/OracleBIData/web/catalog/sakila</CatalogPath>
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
<param-value>localhost</param-value>
</init-param>
<init-param>
<param-name>oracle.bi.presentation.sawserver.Port</param-name>
<param-value>9710</param-value>
</init-param>
</servlet>
--
<param-value>localhost</param-value>
</init-param>
<init-param>
<param-name>oracle.bi.presentation.sawserver.Port</param-name>
<param-value>9711</param-value>
</init-param>
</servlet>
--
<param-value>localhost</param-value>
</init-param>
<init-param>
<param-name>oracle.bi.presentation.sawserver.Port</param-name>
<param-value>9712</param-value>
</init-param>
</servlet>
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 2
Deployment Step 3
Deployment Step 4
Deployment Step 5
Deployment Step 6
rnm1978 10:45 on 10 September, 2009 Permalink |
Excellent post, and interesting comments around getting ODBC 32/64 bit working.
MaximDemenko 09:02 on 11 September, 2009 Permalink |
Thank you for kind words
Calista Gelbach 06:05 on 19 March, 2010 Permalink |
Ich hab darueber erst mal wo gelesen, extrem gutes Thema. Ich komme wieder.