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.


43 comments:

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

    ReplyDelete
  2. 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
  3. Awesome post and i learn lots of new information from your post. keep sharing. Software Testing Training in Chennai

    ReplyDelete
  4. 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
  5. Good post.I really inspiring your post..Thanks for sharing.


    Weblogic Server 12cR2 Training

    ReplyDelete
  6. 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
  7. Thanks For Posting Such an Informative Article.........

    67500/12

    ReplyDelete
  8. 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
  9. Excellent article, Cool, Looking ahead to reading a lot. Sensible article USMLE Thanks for posting.

    ReplyDelete
  10. 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
  11. thanks for the informative and useful stuff........

    Just News To U 99

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

    ReplyDelete
  13. Great explanation to given on this post and i read our full content was really amazing,then the this more important in my part of life. The given information very impressed for me really so nice content.
    AWS training in chennai | AWS training in anna nagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

    ReplyDelete
  14. Very informative post. Social media will give the loads of opportunities to grow your business and helps you attract the targeted audiences (both B2B and B2C). Sharing more infographic content on social media will give more visibility.Thanks for sharing...keep update...
    i need some more infomation
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai

    ReplyDelete
  15. Very Informative blog thank you for sharing. Keep sharing.

    Best software training institute in Chennai. Make your career development the best by learning software courses.

    Docker Training institute in Chennai
    android course in chennai
    power bi training in chennai

    ReplyDelete
  16. Those guidelines additionally worked to become a good way to
    recognize that other people online have the identical fervor like mine
    to grasp great deal more around this condition..
    dba course in chennai
    java training institute in chennai
    node js course in chennai

    ReplyDelete
  17. Western Region, which introduced with it her relocation to the town of Las Vegas, where she at present resides. In 2020, Solsiree accepted the supply to be Director of Product Compliance for the massive slot manufacturer Light & Wonder . Currently, she works there with a wonderful staff of pros that gives services across the 빅카지노 globe.

    ReplyDelete