To read part 1 of this blog post, please click here.
This blog post discusses how to integrate Oracle BI server and make use of the existing Subject Areas built on RPD in Tableau desktop workbook as 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).The whole point of making the changes as described below is to allow Tableau to make joins between tables as we do in the physical layer.
Before accessing subject areas built on RPD in Tableau using ODBC, a few steps need to be taken in RPD using the OBIEE admin tool.
Open RPD Offline or Online. If the changes are offline, deploy the RPD to make sure the changes are available for Tableau when accessing using ODBC connection.
The following changes are done online to the RPD by connecting to the BI Server using OOTB Sample App Lite RPD
To establish an ODBC connection to the Oracle BI Server for accessing RPD Online, you must install a copy of the Oracle Business Intelligence Developer Client Tools (available on the Oracle website).
After installing the client, follow the steps below to create a database source name (DSN).
32-bit operating system: C:\Windows\system32\odbcad32.exe
64-bit operating system: C:\Windows\SysWOW64\odbcad32.exe
We can also use it without checking Clustered DSN as it enables Server Tab and we can enter the server name hosting OBIEE
Important: 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 for Tableau.
Because all presentation tables in the SA will be listed as a set of tables in alphabetical order instead of grouping them into SAs.
Below is a screenshot of the same in case of multiple SAs in RPD. To avoid this, we have to create individual DSN's for individual SAs.
There are three layers in RPD as explained below:
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 will be 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, you will connect to the tables in the presentation layer only as data sources.
As shown in above screenshot, if we want to create reports in Tableau using the Sample Sales Lite Subject Area exposed in Presentation Layer.
Suppose we need a report Total Sales by Product and by Time and By Office. We need to make a join with Time, Products, Office & Sales Fact Table in Tableau. To make a join between tables, we need Key columns (Primary Keys in Dimensions and Foreign Keys in Fact) also be exposed in BMM and Presentation Layer.
Step 1
Identify the Key Fields in Dimensions and Fact Table in Physical Layer of RPD. In our case it is as below
Time - Calendar_Date (Joined with Fact at Physical Layer with Bill_Day_Dt)
Products - Prod_Key
Office - Office_Key
F10 Revenue Facts (Order grain) - Prod_Key, Office_Key, Order Number & Bill_Day_Dt
Step 2
Expose the above-mentioned Key columns to respective logical tables in the BMM layer. Do not expose if any of them are already exposed, as Primary Keys will be exposed to Logical Tables as part of Design. Need to take care of exposing foreign keys in the fact table.
Step 3
Expose the same keys to respective Presentation tables in presentation layer of Sample Sales Lite Subject Area.
Step 4
Save the RPD
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:
<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.
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.
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
Check out our case studies to see how we've helped our clients:
Networking Hardware Company
Williams Sonama
Masterlock
Twist BioScience
Do you enjoy the 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.