2 Replies Latest reply on Feb 24, 2015 3:53 PM by Robert Lingard

    Connect Tableau 8 to Oracle 11g Release 2

    Robert Lingard

      Connect Tableau 8 to Oracle 11g Release 2

      I started using Oracle 11g Release 2 in order to practice SQL.  I used Oracle’s applications SQLPlus and SQL Developer to practice SQL.  After practicing SQL for a while and getting to know the data, it occurred to me to connect Tableau 8 to the data. I went to the Tableau 8 Connect Page and connected to the Oracle database successfully, but I encountered some naming inconsistencies between the Tableau Connect Page and Oracle.  As a result, I had to go through a translation exercise in order to determine what information is required and where to find it in Oracle.  Some is derived from the Oracle 11g Release 2 installation process, some isn’t.

       

      Required on Tableau Connect Page

      Corresponding Name in Oracle Application

      Source

      Value

      Service

      Global Database Name

      Automatically generated during 11g Installation

      orcl

      Server

      Hostname

      Default value in SQL Developer New Connection screen

      localhost

      Port

      Port

      Default value in SQL Developer New Connection screen

      1521

      Username

      Account

      Admin Accounts are Automatically setup during 11g Installation

      SYSTEM

      Password

      Password

      The Admin password is created by the user during 11g installation

      *********


      Be sure to use the information from your own installation of Oracle 11g Release 2 and SQL Developer, as it might not be the same as the information in this example.

       

      Note: In a browser, the full URL would look like http://<HOSTNAME>:<PORT>/<SERVICE>

       

      Here is a screenshot of the Tableau 8 Connect Page with the required information:

      Tableau Connect Page with Required Information.png

      Here is a screenshot from the 11g installation showing the Global Database Name, “orcl.”

      Global Database Name.png

      Here is a screenshot from the 11g installation showing the Admin Account SYSTEM:

      Admin Account SYSTEM.png

      Here is a screenshot of the SQL Developer New Connection screen, showing the Hostname, which means the same thing as Server, “localhost;” and the Port, 1521.

      New Connection Screen.png

      Technical notes:

       

      Here is what the screen looks like after you click the orange box that says “Connect.”  I changed the schema to HR to display the tables I’m using:

      Successful Connection.png

      Now I’m ready to get to work joining tables in Tableau and designing visualizations and other features. I drag the tables into the box as shown and Tableau automatically sets up joins.  I have to know my data to evaluate whether Tableau set up the joins correctly.  I can edit the joins directly on the screen, and then go to the Worksheet to work with the data.

       

      Here’s a bar chart that I made in Tableau after joining two of the tables in my 11g database, Employees and Departments.  I had to edit the joins that Tableau automatically created, because there is one employee who has no department ID, so I changed the type of join from an inner join to a left join.  Now there is one department, Null, with one employee, for the employee who has no department.  Also, Tableau created two joins, but one of them would have restricted the chart to managers only, so I deleted it.  I’ve attached a Tableau packaged workbook with a Tableau extract of the joined data and an identical bar chart that uses the Tableau extract. Users can’t see all the tables in the database, only the data in the Tableau Extract.

      11g Bar Chart.jpg

      Best regards,

      Bob Lingard