Oracle BI Server Connectivity with Tableau (Part 1)

by Rushendra Prasad

This blog post discusses how to integrate the Oracle BI server and make use of the existing Subject Areas built on RPD in the Tableau desktop workbook as a Data Source.

This is applicable for 8.2, 8.3, 9.0, and later versions.

Important: Tableau does not support or test third-party ODBC data sources like Oracle BI Server ODBC. 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.

Prerequisites

OBIEE client to be installed to Open RPD using Admin Tool and make necessary changes to support this integration.
Make sure you have permissions to create DSN for OBIEE server and server is reachable from the machine if connecting to remote machine.
Tableau Desktop to be installed to connect to OBIEE Server DSN to make use of SAs as data source

Why do I need integration between Tableau and OBIEE?

The obvious question at this point is do we really need integration between OBIEE and Tableau? BI users of sales, marketing, customer support etc. adopted user friendly self-service BI tools such as Tableau Desktop in a big way. The reason is simple Tableau is easy to use and has powerful visualization to bring insights to data. Suppose the business users of sales department who have knowledge of their systems and data flow can easily create Sales POV reports by just feeding Excel data into Tableau. But these analysts are usually kept waiting for a chance to make use of corporate data.

In enterprises using Oracle E-Business Suite (EBS) and Salesforce as source systems, business users from the sales department would like to know what happens after an Opportunity is converted to an Order and then entered into Oracle EBS (end-to-end reporting). End of Quarter (EOQ) Operations reports are a classic example for this case. These analyses time taken for a Sales Order from the time it has been generated from a source to the time the Order is shipped and closed. However, they hit an insurmountable wall when trying to access the corporate data securely managed in traditional BI platforms such as OBIEE. The inability to connect these two worlds is a great barrier not just for the business users, but also BI teams. Most financial organizations are worried about the increasing data security risk from proliferation of Excel dumps.

Benefits of OBIEE Integration with Tableau

Analysts viewing a report in OBIEE do not have the capabilities to visualize the data the way they want or to blend data between different sources due to the limitations of the OBIEE tool. One great advantage of OBIEE is that its a common enterprise model with a semantic layer of many logical columns and calculations which have been added over the years. These calculations will take a long time to recreate in Tableau.

OBIEE has a well-defined security model
Traditional waterfall model Design, Develop, UAT and Deployment lead to tested and accurate visualizations leaving no room for bad data.
There is no need to download data from OBIEE and load into Tableau. This reduces the security risk and the possibility of stale and wrong data.
Blending of local data with OBIEE data is now possible. Lack of blending is a big drawback of OBIEE.
Leverage Tableau's vast array of features and beautiful visualizations with a backed up OBIEE sturdy design model.
Ad hoc reporting by business users without relying on the IT team for development of visualizations that they need.

Both the business users and IT teams are desperately looking for a solution that will bridge this great divide of Tableau plug-in play and OBIEE Enterprise solution method.

Integrating OBIEE and Tableau

Designing OBIEE RPD Layers to Support Joins in Tableau
The whole point of making the changes as described below is to allow Tableau to make joins between tables as we do in physical layer. Before accessing subject areas built on RPD in Tableau using ODBC, there are few steps need to be taken care in RPD using OBIEE Admin tool. Open RPD either offline or online. If changes are offline, deploy the RPD to make sure the changes are available for Tableau when accessing using ODBC connection.

Create a System DSN to connect to BI Server

To establish an ODBC connection with the Oracle BI Server for accessing RPD Online, you must install a copy of the Oracle Business Intelligence Developer Client Tools (available from the Oracle website). We can use the same DSN even for connecting to Tableau using ODBC connections. After installing the client, follow the steps below to create a database source name (DSN).

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

On the System DSN tab, click Add
Select the Oracle BI Server DSN from the list of available drivers

2. Provide proper connection details to connect to BI server

3. Save the System DSN

About OBIEE RPD
There are three layers in RPD, as explained below briefly:
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.
Business Layer (middle pane) the layer where data modeling and logical relations and hierarchy creation are done.
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.

In Tableau, tables in the presentation layer are connected only as data sources.

As shown in the screenshot above, if we want to create reports in Tableau using the Sample Sales Lite Subject Area exposed in Presentation Layer.

Identify the Key Fields in Dimensions and Fact Table in Physical Layer of RPD and expose the same to respective Presentation tables in presentation layer of Sample Sales Lite 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:

4. Open a text editor and copy and paste the following code into it:
<connection-customization class='genericodbc' enabled='true' version='9.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>

In the above code, version is very important, change the version as your Tableau desktop version. In my case it is 9.3.

1. 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 file 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

Open Tableau Desktop, click connect to Data Sources and select Other Data Sources (ODBC)

In the DSN tab, select the BI_Server_Tableau DSN that we have created.

Once connection made, we can see the list of tables available in Sample Sales Lite presentation layer in Alphabetical order.

We are good to go and create reports based on the available joins. Click on Sheet1 in Tableau.

Note: Most of the features can be availed Tableau does not support any issues with this connectivity.

Source: Tableau KB and Real-Time Implementation

To read part 2 of this blog post, please click here.

Check out our case studies to see how we've helped our clients:
Networking Hardware Company
Williams Sonama
Masterlock
Twist BioScience

Do you enjoy solving technology problems and helping people meet their data analytics challenges? Maybe you would be a good fit for our team. See our job openings.

Rushendra is a Lead Consultant at KPI Partners' Offshore Technology Center. He is a certified Tableau Desktop Associate with expertise in OBIEE and several modules of Oracle BI Applications. He has also worked on various Salesforce integration and analytics projects and has hands-on experience in Hyperion, QlikView, and Power BI.