Oacle 11g express edition database connection and sample query execution example in Pentaho CDE

Hi,
In this post you will see how to connect to Oracle 11g Express edition and how fire a query against the connected data-source in Pentaho CDE

Software used to test this example : 
1) Pentaho BA Server 6.1
2) Pentaho Ctools (6.1- CDE,CDF,CDA,CGG)


Steps : 
1) Download and install "ojdbc6" in "lib" folder of pentaho installatio.
 http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html

 Driver installation location : 
(E:\2_Archive_Installed\Pentaho\6.1\biserver-ce-6.1.0.1-196\biserver-ce\tomcat\lib)

2) Start the pentaho BA server using "start-pentaho.bat" . (Double click on it)

3) Create a new dashboard (Home -> Create New -> Dashboard) and save the dashboard with the name of your choice ( In my case it is "Oracle 11g Connection")

4) Design the layout for a table component ( row -> Column).

5) Navigate to Data source section and click on " SQL Queries" from the bottom and then click on "sql over sqljdbc"

6) In its properties, fill the query name, username, password, URL as shown in below image.
Name= Oracle11g_Query
Driver = oracle.jdbc.OracleDriver
User Name = sadakar
Password = sadakar
URL =  jdbc:oracle:thin:@//localhost:1521/XE ( I used default SID as it is my local instance) 
Query = SELECT * FROM employee

 

7) Test whether the connection is successful or not and preview the query output.

#) Save the dashboard, unless you save it, the CDF will not generate .cda file to test the connection
    (Of course, you can write your own CDA file, but in this test I am not going to talk about it).
#) Go the location where the .cda generated (its the location where you saved your dashboard).
#) Open the CDA file in Edit mode and click on "Preview" button.

#) It will open a new tab in the browser

Sample link for CDA 
(http://localhost:9090/pentaho/plugin/cda/api/previewQuery?path=/public/D3%20Calendar%20View%20Example/Oracle%2011g%20Connection.cda)

#) Select the "Data Access ID" and see the result set. If you wont see the preview you must check the  database connection properties. (You can use any other client tool that takes SQL url, username, password and driver class name).

8) Now, take a component and populate this result set ( In my case, I took "Table" component from Components section tested it).
Sample output of table component on dashboard: 

This way one can connect to "Oracle 11g Express edition". I hope it helps someone in the community.

References : 
URL I have taken from : https://razorsql.com/docs/help_oracle.html
Driver class & Other formats of URL : https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html

- Sadakar Pochampalli