-->

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.


63 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
  19. thanks for the informative and useful stuff........

    Just News To U 99

    ReplyDelete
  20. I have read your blog its very attractive and impressive. I like it your blog.

    appvn app

    ReplyDelete
  21. Have questions to enter the online casino? Come to our section of rock BGAOC. best real online gambling slots Come and do not get lost. win with us.

    ReplyDelete
  22. I enjoy what you guys are usually up too. This sort of clever work and coverage! Keep up the wonderful works guys I’ve added you guys to my blog roll.
    iosh safety course in chennai

    ReplyDelete
  23. Разноцветная светодиодная лента или как ее еще называют светодиодная лента rgb наверое одно из лучших изобретений которое придумали люди, очень забавная штука.

    ReplyDelete
  24. Hello, I read your blog occasionally, and I own a similar one, and I was just wondering if you get a lot of spam remarks? If so how do you stop it, any plugin or anything you can advise? I get so much lately it’s driving me insane, so any assistance is very much appreciated.
    Android Course Training in Chennai | No.1 Android Training in Chennai
    Data Science Course Training in Chennai | Best Data Science Training in Chennai
    Matlab Training in Chennai | Best Matlab Course Training in Chennai
    AWS Training in Chennai | No.1 AWS Training in Chennai

    ReplyDelete
  25. Interesting information and attractive.This blog is really rocking... Yes, the post is very interesting and I really like it.I never seen articles like this. I meant it's so knowledgeable, informative, and good looking site. I appreciate your hard work. Good job.
    Kindly visit us @
    Sathya Online Shopping
    Online AC Price | Air Conditioner Online | AC Offers Online | AC Online Shopping
    Inverter AC | Best Inverter AC | Inverter Split AC
    Buy Split AC Online | Best Split AC | Split AC Online
    LED TV Sale | Buy LED TV Online | Smart LED TV | LED TV Price
    Laptop Price | Laptops for Sale | Buy Laptop | Buy Laptop Online
    Full HD TV Price | LED HD TV Price
    Buy Ultra HD TV | Buy Ultra HD TV Online
    Buy Mobile Online | Buy Smartphone Online in India

    ReplyDelete
  26. And indeed, I’m just always astounded concerning the remarkable things served by you. Some four facts on this page are undeniably the most effective I’ve had.
    SOFTWARE TRAINING IN CHENNAI
    POWERBI TRAINING IN CHENNAI
    CCNA TRAINING IN CHENNAI
    ANDROID TRAINING IN CHENNAI

    ReplyDelete
  27. The article is very interesting and very understood to be read, may be useful for the people. I wanted to thank you for this great read!! I definitely enjoyed every little bit of it. I have to bookmarked to check out new stuff on your post. Thanks for sharing the information keep updating, looking forward for more posts..
    Kindly visit us @
    Madurai Travels
    Best Travels in Madurai
    Cabs in Madurai
    Tours and Travels in Madurai

    ReplyDelete
  28. Excellent Blog. I really want to admire the quality of this post. I like the way of your presentation of ideas, views and valuable content. No doubt you are doing great work. I’ll be waiting for your next post. Thanks .Keep it up!
    Kindly visit us @
    Luxury Packaging Box
    Wallet Box
    Perfume Box Manufacturer
    Candle Packaging Boxes
    Luxury Leather Box
    Luxury Clothes Box
    Luxury Cosmetics Box
    Shoe Box Manufacturer
    Luxury Watch Box

    ReplyDelete
  29. Home Mart is a site about Home Improvement, Furniture, Home Appliances and many more.
    Check out the best
    bookshelves
    balance bike

    ReplyDelete