Tuesday, June 22, 2010

How to Config SpagoBI with Oracle database?

SpagoBI is an open source business intelligence (BI) platform developed following FOSS philosophy. It covers all the BI areas (reporting tool, data mining, ETL tool, OLAP analysis, and BI dashboard).

This document records the steps to install and config the Oracle database connection as reporting data source in SpagoBI.

You can config the database connection using JNDI resource that is shared between reporting applications; or you can config JDBC data source connection, so, a new JDBC connection (non-shareable) will be created for each of reporting applications (document) execution.


Environment
============
SpagoBI: version 2.5
OS Server: Windows server 2008 R2 (x64)
App Server: Tomcat 6.0.18 (in the SpagoBI demo package)
Oracle database: 11g R2 (for windows x64)
JDBC driver: ojdbc5.jar (for JDK 1.5 or up)
Java: JDK 1.6.0_20

1. Create JNDI Data Source Connection

1) Download and unzip the file "Demo-SpagoBI-2.5.0-apache-tomcat-6.0.18-04092010.zip" to your local folder (like tomcathome\);
2) Copy the Oracle jdbc driver file "ojdbc5.jar" to your tomcathome\lib folder;
3) Edit the file "server.xml" at the folder "tomcathome\conf" and add / edit the following items:


4) Edit the file "context.xml" at the folder "tomcathome\webapps\SpagoBI\META-INF" as showing below:


5) Edit the file "context.xml" at the folder "tomcathome\webapps\SpagoBIJasperReportEngine\META-INF" as showing below:


6) Start Tomcat server to enable the above changes;

7) Log on to SpagoBI at http://localhost:8080/SpagoBI to create a data source connection:

Click "Resources" -> "Data Source"


Click Insert button (icon) to add a new data source


Enter the settings for the new data source


Test the new connection before you save and exit.



2. Create JDBC Connection

1) Download and unzip the file "Demo-SpagoBI-2.5.0-apache-tomcat-6.0.18-04092010.zip" to your local folder (like tomcathome\);
2) Copy the Oracle jdbc driver file "ojdbc5.jar" to your tomcathome\lib folder;
3) Start Tomcat server to enable the above changes;
4) Log on to SpagoBI at http://localhost:8080/SpagoBI to create a data source connection:

Click "Resources" -> "Data Source"


Click Insert button (icon) to add a new data source


Enter the settings for the new data source


Test the new connection before you save and exit.

4 comments:

  1. Very informative and clear , thanks a lot.

    ReplyDelete
  2. Great advise! Useful for configuration of PostgreSql as well - thanks!

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete