Connect Tableau 8 to Oracle 11g Release 2

Version 4

    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 before I could determine what is required and where to find this information 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

    *********

     

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

    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.

     

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

    Tableau Connect Page with Required Information.png

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

    Global Database Name.png

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

    Admin Account SYSTEM.png

    Here is a screenshot of my 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:

    My version of Tableau is 8.2.2 (8200.140819.2015) 64 bit

    I obtained Oracle 11g Release 2 for my Windows 64 bit system at http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

    I obtained SQL Developer at http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

    SQL Developer requires Java Developer Kit 7 (JDK 7) which is also available at that site.

    Here is what the screen looked like after I clicked 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 simply 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 simple 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