Beginning with version 8.2.3, Tableau has made some important stability enhancements for unsupported third-party ODBC data sources such as Oracle Business Intelligence (BI) Server or Oracle Business Intelligence Enterprise Edition (OBIEE). Connecting to data sources by using the Other Database (ODBC) option now provides a more capable and reliable connection.
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.
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.
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.
- 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.
The Sales Fact Table contains the key fields for the Products Table and Office Table, and each of the dimension tables 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. - 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.
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.
- In Windows, open the Run dialog box.
- 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 - In the ODBC Administrator tool, add a new System DSN for Oracle BI Server ODBC by following these steps:
- On the System DSN tab, click Add.
- Select the Oracle BI Server DSN from the list of available drivers.
- Enter a unique name for the connection for use in Tableau.
- Select Clustered DSN.
- For Primary Controller, enter the IP address of the server hosting the Oracle BI Server.
- For Port, enter the port number to connect to Oracle BI Server.
- Click Test Cluster Connect to test your connection.
- Click Next twice, and then click Finish to complete the connection setup process.
Connect to Oracle BI Server ODBC from Tableau Desktop
In Tableau Desktop, follow these steps to make your connection.
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.
- Click Connect to Data.
- On the Connect page, click Other Databases (ODBC).
- In the DSN drop-down list, select the name that you gave to your Oracle BI Server ODBC connection, and then clickConnect.
- Enter your Oracle BI Server credentials, and then click OK to connect to the Oracle BI Server.
- 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:
- Click Go To Worksheet to open your workbook and start your analysis in Tableau Desktop.
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.