How to connect to Oracle BI server using ODBC

Version 4

    Introduction

    Before you make any Oracle BI Server ODBC connections, review the following information to determine whether the Oracle BI Server ODBC enhancements are right for you. Make sure that you are familiar with the information in the Tableau and ODBC article, which discusses Tableau's requirements and level of support for ODBC data sources. For more information about ODBC connections, see Customizing and Tuning ODBC Connections.

     

    For a different method to connect to Oracle BI server, without using the ODBC connection, see How to Connect to Oracle BI Server with Tableau without the shortcomings of Oracle ODBC driver.

     

    Important: Tableau does not support or test third-party ODBC data sources like Oracle BI Server ODBC. The following information provides general guidance on certain configurations that may allow Tableau to connect to data sources that use the general purpose ODBC standard.

     

    Design OBIEE data models to make joins in Tableau

    Before you connect Tableau to your Oracle BI Server using ODBC, there are a few steps you need to take in the OBIEE Administration Tool to make sure that your model is designed for making joins in Tableau. The following screenshot shows an OBIEE sample data model from the OBIEE Administration Tool.

    Oracle-BI-1.png

    There are three layers in OBIEE data models:

    • Presentation Layer (left pane)—The layer that is exposed to the business through “subject areas.” These subject areas are neatly modeled to expose data in an easy to understand format.
    • Business Layer (middle pane)—The layer where data modeling and logic—such as relative time and hierarchy creation—are done.
    • Physical Layer (right pane)—The layer where the connection to each data source is made and the raw tables are exposed. Joins across tables are done at this layer.

     

    In Tableau, you will connect to the tables in the presentation layer.

     

    Expose key fields to Tableau

    Suppose you want to connect Tableau to the OBIEE “Sample Sales Lite” subject area and look at sales by product and by office. For this analysis in Tableau, you will need to connect to the Sales Fact Table, the Products Table, and the Offices Table; and then make joins from the Products Table and Offices Table to the Sales Fact Table. To make these joins, the key fields must exist in all three tables and be exposed at the business layer and the presentation layer in OBIEE.

     

    1. Expose key fields in the business layer.

      The screenshot below shows the business layer; the OBIEE administrator has added the key fields to the Sales Fact Table, the Products Table, and the Offices Table.

      Oracle-BI-2.png

      The Sales Fact Table contains the key fields for the Products Table and Office Table, and each of the dimension tbales has the key field for that table only (Product Number and Office Key). After these keys are added to the business layer in OBIEE, you can add the fields to the presentation layer.

       

    2. Expose key fields in the presentation layer.

       

      The OBIEE administrator adds fields from the consolidated business layer to specific subject areas in OBIEE. These subject areas act as groupings of data for an area of the business. For example, you may have a subject area for finance, one for supply chain, and one for sales. In the screenshot below, the appropriate key fields have been added to the Sample Sales Lite subject area.

      Oracle-BI-3.png

      This is the final step in the OBIEE model that needs to be completed before connecting to it from Tableau.

       

    Create a DSN to use with Tableau

    To establish an ODBC connection to the Oracle BI Server, you must install a copy of the Oracle Business Intelligence Developer Client Tools (available from the Oracle website).

     

    After installing the client, follow the steps below to create a database source name (DSN) to use with Tableau connections.

     

    1. In Windows, open the Run dialog box.
    2. Start the Windows ODBC Administrator tool by entering one of the following commands in the Run dialog box:

      • 32-bit operating system: C:\Windows\system32\odbcad32.exe
      • 64-bit operating system: C:\Windows\SysWOW64\odbcad32.exe


    3. In the ODBC Administrator tool, add a new System DSN for Oracle BI Server ODBC by following these steps:

      1. On the System DSN tab, click Add.
      2. Select the Oracle BI Server DSN from the list of available drivers.
      3. Enter a unique name for the connection for use in Tableau.
      4. Select Clustered DSN.
      5. For Primary Controller, enter the IP address of the server hosting the Oracle BI Server.
      6. For Port, enter the port number to connect to Oracle BI Server.
      7. Click Test Cluster Connect to test your connection.
      8. Click Next twice, and then click Finish to complete the connection setup process.

    Note: You must select the subject area that you want to connect to in each ODBC connection you create. For example, if you want to connect to four subject areas, you need to create one ODBC connection to each subject area.

     

    Create a .tdc File

    Create a Tableau data source customization (.tdc) file to connect Tableau Desktop to an Oracle BI server. To do this, follow these steps:

    1. Open a text editor and copy and paste the following code into it:
      <connection-customization class='genericodbc' enabled='true' version='8.3'>
        <vendor name='Oracle Business Intelligence' />
        <driver name='Oracle BI Server 1' />
        <customizations>
          <customization name='CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API' value='no' />
          <customization name='CAP_SUPPRESS_DISCOVERY_QUERIES' value='no' />
          <customization name='SQL_CATALOG_USAGE' value='0' />
          <customization name='SQL_SCHEMA_USAGE' value='0' />
          <customization name='CAP_FAST_METADATA' value='yes' />
        </customizations>
      </connection-customization>
    2. Save the .tdc file to the correct folder:        
      • For Tableau Desktop on the Mac: /Users/<user>/Documents/My Tableau Repository/Mapsources

      • For Tableau Desktop on Windows: My Tableau Repository\Datasources

      • For Tableau Server: Program Files\Tableau\Tableau Server\<version>\bin


    Important:
    Tableau does not test or support .tdc files. Use a .tdc file only to explore or occasionally address issues with your data connection. Creating and maintaining .tdc files requires careful manual editing, and there is no support for sharing these files.

     

    Connect to Oracle BI Server ODBC from Tableau Desktop

    In Tableau Desktop, follow these steps to make your connection.

    1. Click Connect to Data.
    2. On the Connect page, click Other Databases (ODBC).
    3. In the DSN drop-down list, select the name that you gave to your Oracle BI Server ODBC connection, and then click Connect.

      Oracle-BI-4.png

    4. Enter your Oracle BI Server credentials, and then click OK to connect to the Oracle BI Server.
    5. Select and join the tables in the join area on the Tableau data source connection screen. Below is an example of a connection to the Sample Sales Lite subject area on the Oracle BI Server:

      Oracle-BI-5.png

    6. Click Go To Worksheet to open your workbook and start your analysis in Tableau Desktop.       

      Note: In the Tableau workbook, the key fields do not appear in the Data window under Dimensions, as shown in the following graphic. Using a key field dimension will cause an error, so hiding these fields in the Dimensions pane is recommended.

      Oracle-BI-6.png

    Now you can take advantage of all the things a business user in Tableau is accustomed to—renaming fields, creating hierarchies, creating calculations, saving the data source, and publishing the data source to Tableau Server.

     

    Note: Some capabilities (for example, Top N filtering) may not work with an ODBC connection. If you discover a limitation, use an extract to make sure that you can use Tableau’s full capabilities when connecting to an Oracle BI server.