-->

Pages

Thursday, 8 January 2015

Configuring Pentaho CE 5.2 to work with Oracle 12c

Pentaho is just awesome. The fact that it is open source make it really useful for SME that do not have a massive budget. Although its UI may not be flashy and polished, it comes with a suite of powerful tools that can get the job done really well. During recent work on mining data from various database, Oracle 12c seems to be the most problematic to get it working. After experimenting with various configuration, finally got them to talk using JNDI setup.

1. Oracle 12c Configuration
On the Oracle 12c server, the tnsname is configured as follows:
tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JUPITER =

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jupiter)
    )
  )

PDBJUPITER =

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbjupiter)
    )
  )

* JUPITER         - CDB database
* PDBJUPITER   - PDB database

2. Installing Oracle JDBC Driver in Pentaho
Copy the Oracle JDBC driver, ojdbc7.jar from
       <ORACLE_INSTALLATION_PATH>/product/12.1.0/dbhome_1/jdbc/lib/
to
       <PENTAHO_INSTALLATION_PATH>/lib/

Note: Copy the ojdbc6.jar instead if using JDK 1.6

3. Pentaho DI 5.2 - Spoon Configuration
Locate jdbc.properties file at data_integration/simple_jndi folder. After adding the configuration for Oracle 12c, it becomes as follows:
jdbc.properties
SampleData/type=javax.sql.DataSource
SampleData/driver=org.h2.Driver
SampleData/url=jdbc:h2:file:samples/db/sampledb;IFEXISTS=TRUE
SampleData/user=PENTAHO_USER
SampleData/password=PASSWORD
Quartz/type=javax.sql.DataSource
Quartz/driver=org.hsqldb.jdbcDriver
Quartz/url=jdbc:hsqldb:hsql://localhost/quartz
Quartz/user=pentaho_user
Quartz/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.hsqldb.jdbcDriver
Hibernate/url=jdbc:hsqldb:hsql://localhost/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Shark/type=javax.sql.DataSource
Shark/driver=org.hsqldb.jdbcDriver
Shark/url=jdbc:hsqldb:hsql://localhost/shark
Shark/user=sa
Shark/password=

PdbJupiter/type=javax.sql.DataSource
PdbJupiter/driver=oracle.jdbc.pool.OracleDataSource
PdbJupiter/url=jdbc:oracle:thin:@192.168.56.11:1521/pdbjupiter
PdbJupiter/user=biadm
PdbJupiter/password=password

* PdbJupiter    - JNDI name
* pdbjupiter    -  PDB database 
* biadm           - A normal user created for PDB

Launch Spoon, go to Tools -> Repository -> Explore -> Connections and add the connection to Oracle 12c as shown in the screen below.



Spoon should now be able to extract and load data from Oracle 12c.

Note: For Oracle 12c, the database url connection has been changed from
            jdbc:oracle:thin:@192.168.56.11:1521:<SID>
to
            jdbc:oracle:thin:@192.168.56.11:1521/<SERVICE NAME>
Hence, using Native(JDBC) method to connect to Oracle 12c database is not working as it only regconise the first format.


24 comments:

  1. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me..
    Android Training in Chennai

    ReplyDelete
    Replies
    1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Java developer learn from Java Training in Chennai. or learn thru Java Online Training in India . Nowadays Java has tons of job opportunities on various vertical industry.


      or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

      Delete
  2. Thank you for sharing such a usefull information on your blog, I am inspired with your post writing style & how continuously you describe this topic.

    Hadoop Online Training | Qlikview Online Training | Tableau Online Training | SAS Online Training | Android Online Training | Business Analyst Online Training

    ReplyDelete
  3. In near future, big data handling and processing is going to the future of IT industry. Thus taking Hadoop Training in Chennai | Big Data Training in Chennai will prove beneficial for talented professionals.

    ReplyDelete
  4. Really awesome blog. Your blog is really useful for me.
    Thanks for sharing this informative blog. Keep update your blog.
    Oracle Training In Chennai

    ReplyDelete
  5. Nice blog.
    I really want to configure JDBC.
    Please mail me if you can help me thank you. (phaneendhrav@gmail.com)

    ReplyDelete
  6. Bed Bug Exterminator Leesburg VA Just stumbled across your blog and was instantly amazed with all the useful information that is on it. Great post, just what i was looking for and i am looking forward to reading your other posts soon!

    ReplyDelete
  7. Bed Bug Exterminator Leesburg VA I am really impressed with your efforts and really pleased to visit this post.

    ReplyDelete
  8. Rolex Watches Authentic Mens & Ladies Rolex Datejust, President Watches for Sale at JavyEstrella.com.

    ReplyDelete
  9. Awesome post and i learn lots of new information from your post. keep sharing. Software Testing Training in Chennai

    ReplyDelete
  10. These servers are manged in pools either from Oracle VM Management Pack or the Oracle VM Manager, but not from both the things.
    salesforce datawarehouse

    ReplyDelete
  11. That would really be a great help. Thanks for the post.

    Advanced Excel classes in Bangalore

    ReplyDelete
  12. Good post and I like it very much. By the way, anybody try this app development company for iOS and Android? I find it is so professional to help me boost app ranking and increase app downloads.

    ReplyDelete
  13. Good post.I really inspiring your post..Thanks for sharing.


    Weblogic Server 12cR2 Training

    ReplyDelete
  14. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts, have a nice weekend!
    big-data-hadoop-training-institute-in-bangalore

    ReplyDelete
  15. Thanks For Posting Such an Informative Article.........

    67500/12

    ReplyDelete
  16. Really It's A Great Pleasure reading your Article,learned a lot of new things,we have to keep on updating it,Chicago Immediate care in Chicago.By getting them into one place.Really thanks for posting.Very Thankful for the Informative Post.Really Thanks For Posting.Thanks For Sharing Such an Valuable Info.

    ReplyDelete
  17. Excellent article, Cool, Looking ahead to reading a lot. Sensible article USMLE Thanks for posting.

    ReplyDelete
  18. This Blog Provides Very Useful and Important Information. I just Want to share this blog with my friends and family members. digital transformation consulting Thanks for posting.

    ReplyDelete