Oracle BI Enterprise Edition (OBIEE) Configuration and Integration with Teradata QueryGrid

Applications
Applications covers the world of Teradata apps, including apps offered by Teradata (such as TRM or DCM), as well as best practices and methodologies for building your own Teradata-connected apps.
Teradata Employee

Oracle BI Enterprise Edition (OBIEE) Configuration and Integration with Teradata QueryGrid

Teradata QueryGrid provides a means to access Hortonworks Hadoop® data from a Teradata system.  The QueryGrid uses SQL to access and join other system’s data with the enterprise data.  QueryGrid is flexible in that Hadoop, Aster or other databases can be accessed from the integrated data warehouse.

This article describes how to configure and integrate Oracle BI Enterprise Edition (OBIEE) 11g with Teradata QueryGrid to enable transparent OBIEE end user access to data stored within Hadoop. 

 Although Teradata QueryGrid is flexible and can deal with many types of systems (e.g. Teradata DBS, Aster, Hadoop, Oracle), this article specifically focuses on the Teradata Unified Data Architecture and the access of Hadoop data stored within the Teradata Appliance for Hadoop.

Overview – OBIEE and QueryGrid Integration

 Oracle BI Enterprise Edition can take advantage of the Teradata QueryGrid functionality to expand its compliment of data sources to include the Teradata Appliance for Hadoop.  It is likely integration and deployment of QueryGrid with OBIEE will require a collaboration of subject matter experts – a developer (or data scientist) who can define/test/validate the Hadoop data to fulfill the end user requirement, the Teradata DBA to identify Hadoop to the Teradata system, and the OBIEE Admin to work with the developer to integrate and deploy the analysis within OBIEE to the broader BI user community.

There are three main use cases for Teradata QueryGrid:

  1. Import data into a temporary or permanent table in the integrated data warehouse
    1. INSERT or SELECT or CREATE TABLE AS … WITH DATA
  2. Use Hadoop data interactively with a BI tool
  3. Select/join Hadoop data with IDW (Teradata Integrated Data Warehouse) for analytics

This article pertains to the second and third use cases as the first case is just a standard table for OBIEE to consume.

Since QueryGrid allows access to data that doesn’t reside within Teradata, the configuration of OBIEE is different than if the data did reside in Teradata.  Within OBIEE there are three ways to utilize the Teradata QueryGrid with the Teradata UDA and access the Hadoop data:

  1. Access a view created on Teradata that defines the Hadoop data source
  2. Use OBIEE dynamic table names to accommodate the TableName@ForeignServer syntax
  3. Use OBIEE opaque views to accommodate the “Foreign Table” table operator syntax

This article describes techniques for configuring OBIEE and Teradata for each of the above methodologies. 

Configuration for Teradata “View” Object

OBIEE can utilize Teradata QueryGrid via “views” created on the Teradata database.  Creating a view on the Teradata database that incorporates the foreign data essentially hides from OBIEE the fact that the data is on another system.  OBIEE treats the view object in the same manner as any other Teradata table or view.  This method has the advantage of allowing the OBIEE Administrator to import the view definition into the OBIEE metadata (RPD).

Configuration for dynamic table name

By utilizing the “dynamic name” feature OBIEE can access Teradata QueryGrid objects.  The OBIEE “dynamic name” feature is mostly used when a table name changes for one reason or another (separate physical table name for each customer or switching between primary and shadow tables that are valid at different times in the ETL process).  This feature can also be used to point OBIEE to the QueryGrid object. 

The dynamic table name feature does not have strict SQL syntax checking thus allowing the foreign table syntax (TableName@ForeignServer) to be used in queries.  Since the QueryGrid name isn’t going to change, the “dynamic” piece of the OBIEE feature is not going to be used.  This method doesn’t allow the OBIEE Administrator to import the foreign table definition into the OBIEE metadata (RPD).

Configuration for opaque view

By utilizing the “opaque views” feature OBIEE can access Teradata QueryGrid objects.  An opaque view in OBIEE is a physical table definition that has a table type of “select”.  The screen shot below shows the drop down selection for table type:

An opaque view can also be thought of as a “view definition” within OBIEE.  So rather than creating a view definition in the database the definition can be made within OBIEE.  This method also has loose SQL syntax checking and allows the foreign table syntax.  This method doesn’t allow the OBIEE Administrator to import the foreign table definition into the OBIEE metadata (RPD).  Below is an example of the foreign table syntax included in an opaque view:

Summary

This article has described how to integrate Oracle BI Enterprise Edition (OBIEE) with Teradata QueryGrid.  Three integration methods were described.  Based on the amount of effort required to implement the 3 methods the first method (Access a view created on Teradata that defines the Hadoop data source) is the easiest.  Creating a view is easy and then OBIEE can import the metadata – also easy.  This method had the added advantage that other tools can access the data as well.

More information on Teradata QueryGrid can be found on the Teradata Developers Exchange website.  There is also a companion document to this article that includes “step-by-step” detailed examples on each of the integration methods.   To obtain this document please contact Todd.Cannaday@teradata.com.