Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Data Quality Overview | Main | Managing big teams :24X7 support »

OBIEE Integration with Tableau

         In this  blog I will discuss and show how Tableau - OBIEE integration can be achieved and  options to connect to several data sources from Tableau.  It's a frequent ask from clients where OBIEE and Tableau present in reporting area.

OBIEE and Tableau are known as Business Intelligence Reporting tools. It is possible from Tableau to connect to OBIEE metadata file, build reports and provide advance data visualizations using Tableau.

What it Needs?

OBIEE Client tool installation in Tableau Desktop System

OBIEE application details to connect from Tableau

How to achieve?

  • Create a ODBC DSN in Tableau desktop system using driver: Oracle BI server.  Provide OBIEE server name and port details and test Cluster connectivity

Hari1.jpg

  • Create a Tableau Data Source connector(Tableau.tdc) file with the specifics as provided below

HariTdc.jpgReporting from Tableau by using ODBC Data Source

Tableau allows to use multiple data sources for a single worksheet. The below provided are the section steps for connecting to ODBC data source from Tableau; to be followed creating ODBC DSN.

Step1: Launch Tableau Desktop

Step2: In Tableau Desktop, click on Other Databases (ODBC)

Hari2.jpg

Step3: In DSN drop down,you will find the DSN you already created. Select the DSN you just created. Click Connect

Step4: There will be a popup screen appear. In Login window, provide credentials to connect to. Click OK Button

Hari3.jpg

Step5: On providing correct credentials, It shows window with message: Connection has been established successfully with ODBC Data source

Step6: You can create data model in Tableau Book by selecting the required OBIEE Presentation Tables that appear on left navigator. And you continue to build the report

Reporting from Tableau using Multiple Data sources

Tableau allows to use multiple data sources for a single worksheet. One data source is primary; all others are secondary. 

The below are the Key Data sources:

  • File-based data sources

  • Relational data sources

  • OLAP data sources

  • Cloud-based data sources

    In this blog, I'm discussing how we can use File/Relations data source in Tableau

  1. Creating a Data source to connect to a File: 

    The below steps are to be followed to connect Tableau to a file data and set up the data source.  The below steps help to establish a connection to text file. 

  • Launch Tableau Desktop

    Hari4.jpg

  • As shown in the below screenshot, select Text File to connect to a text data file,  In case you want to connect to Excel file, select Other Option : Microsoft Excel

    Hari5.jpg

  • In the next screen, select the file path from network

    Hari6.jpg
  • In the next screen, select the Connection Type: Live /Extract
  • When Live connection is selected, a direct connection to the given file path will be established

    Then select Goto Worksheet to go to the work book to do Reporting by selecting the fields from the connected file. Any update in the source file reflects in the Reports accessing this Live connection.

Hari7.jpg

      When Extract Connection is selected, it prompts to save the file in a local location, So that the extracted data will be stored in .tde file.

    The above .tde file can be used in any Workbook to work on the data available in the local Tableau data extract.  This step needs be repeated if there is any data refresh in the source file.

2.    Data Source creation to connect to a Database Table:

The below steps are to be performed to create a data source to connect to database tables. The given example illustrates steps to connect to Oracle Database.

  • Launch Tableau Desktop

  • Select Oracle to create Oracle Database connection

Hari9.jpg

  • Provide the Connection details to connect to the Oracle database

Hari10.jpg

  • Click connect, Select the Schema Name, Table Names. When Multiple Tables are selected, provide the join among the Tables.  Click Go to Worksheet, where the Report can be built using the fields selected out of the Tables 

    Hari11.jpg

Data Blending using Multiple Data Sources

Tableau features to connect to multiple data sources to enable Data Blending for combining data from multiple data source types in a single worksheet.

Once multiple data sources are connected in worksheet, the Relationship between the data sources has to be defined on the Joining columns as shown below.

Hari12.jpg

Once the Relationship is defined between data sources on joining columns, the Report can be created by dragging the fields from the defined data sources as shown in the below screenshot.

Fields highlighted with Blue border are dragged from a Text File Data source.

Fields highlighted in Green border are dragged from a ODBC data source created to fetch the data from OBIEE Subject Area.

Hari13.jpg

Reuse of Data Source Creation:

Once a Data source of any type( File/Excel/Database Table/ODBC ) is created in Tableau, this Data source can be saved by using the option : Add to Saved Data Sources in Tableau as given below. This Data source can be reused in other workbooks if you want to reuse the same data source (same file/Table/model) again.

Hari14.jpg

Save the Data source in a location. This Data source can be opened in other workbooks so that this can be reused.

Hari15.jpg

Caching

  • OBIEE "nqquery.log"/OBIEE Cache doesn't capture the log for the reports you create from Tableau

  • Caching (Browser Cache) can be managed at Tableau level as all the reporting happens on Tableau

Security

  • OBIEE and Tableau must have a common Authentication mechanism to pass the user credentials from OBIEE to Tableau (enabling Single Sign On).

  • Authentication is done at OBIEE. Data Level Security is managed/controlled within Tableau.

Key Features:

  • Amazing Data Visualization

  • Direct Database connection and report creation

  • Easy Dashboard Publishing and sharing

  • All general configurations like deriving calculative fields, report drill capabilities, hierarchies supported

  • Dashboard Export functionality for the selected worksheets

Key Considerations & Limitations:

  • OBIEE is not a trusted data source in Tableau Desktop. As a result, when using the generic ODBC data connection with an unsupported data source, success often varies and full compatibility with Tableau features cannot be guaranteed.

  • All the tables across OBIEE subject areas presented to the user on Tableau data sources tab.

  •Its recommended to expose the foreign keys in OBIEE RPD in Presentation layer in order to frame the joins between the selected presentation tables in Tableau. 

  • Select the subject area for which ODBC connection needs to be created.

   All the tables across OBIEE subject areas presented to the user on Tableau data sources tab. Key attributes of all the Dimension tables need to be exposed in the Fact Table in OBIEE RPD to configure joins in Tableau Data Sources. At the same time, Exposing Foreign Keys in the Presentation layer is not an OBIEE best practice. It's suggested to restrict the foreign keys in OBIEE RPD to Administrator, so that the Keys gets exposed in OBIEE RPD for usage in Tableau and these will not be exposed to OBIEE end user/Business users while performing adhoc reports.

  • Data Sources from External source (Files/Tables) can be saved in Tableau, so that it can be reused if the same data model is needed in the other workbook. In case it needs a data model other than that present in the saved data sources, then new connection has to be recreated every time.


Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles