Oracle BI Enterprise Edition (OBIEE) Integration with Teradata Aster SQL/MR

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) Integration with Teradata Aster SQL/MR

Beyond the typical database functions Aster has advanced analytical functionality.  For instance, the Aster nPath function allows regular pattern matching over a sequence of rows. With it, you can find sequences of rows that match a pattern you’ve specified and easily extract information from these matched patterns using symbols that represent the matched rows in the pattern.

OBIEE doesn’t have native support for the nPath function, but with a little additional configuration OBIEE can expose nPath (and other SQL/MR functions) to the end user.  Opaque views in OBIEE can be configured for nPath analysis.  An example of nPath usage in OBIEE will analyze time between orders.  We have an “orders” application that shows when customers ordered products.  Time between orders can be found by comparing the order dates in the orders table.  By analyzing time between orders we can look for shipping efficiencies.  Opaque views will be used to execute the nPath statement.

Note:  It is likely integration and deployment of SQL-MR with OBIEE will require a collaboration of subject matter experts – an Aster developer (or data scientist) who can define/test/validate the SQL-MR syntax to fulfill the end user requirement, and the OBIEE Admin to work with the Aster developer to integrate the SQL-MR syntax within OBIEE.

Configuration Overview  

The NPATH function fits into the “from” section of a sql statement.  For example:

select * from NPATH (on …);

Due to the placement in this section of the SQL statement the various OBIEE “evaluate” functions cannot be used.  For this example OBIEE opaque views will be used.  We will use a combination of opaque views and session variables to allow the end-user to execute and manipulate SQL/MR statements.  The high level overview of this is as follows:

  1. Craft the SQL/MR statement
  2. Determine which parts of the SQL/MR statement are to be manipulated by the end-user
  3. Add session variables for the items in step 2
  4. Prepare the physical layer and connectivity for Aster
  5. Add a table in the physical layer for the SQL/MR statement
  6. Add the columns for the SQL/MR results to the table created in step 5
  7. Add appropriate dimensions
  8. Build the logical and presentation layers
  9. Build a dashboard using drop-downs for the variables in step 3 and the SQL/MR table
  10. Save and run

The details for each of the steps follow.

Step-by-step guide to putting together the OBIEE metadata

Craft the SQL/MR statement

For our example we have an “orders” table that shows when customers ordered items and an “orderdetails” table that shows which products.  Here are the definitions of the tables:

The SQL/MR statement that will find the time between orders for a particular product is:

select CustomerID,productname,purchase_date1,purchase_date2,purchase_date2-purchase_date1 as elapsed_days,quantity

from npath (on (select customerid,productname,orderdate,products.productid,quantity from orders,orderdetails,products

where orders.orderid=orderdetails.orderid

and orderdetails.productid=products.productid)

partition by customerid,productid

order by orderdate

mode ( OVERLAPPING )

pattern ('A.B')

symbols (productname= 'Chai' as A, productname= 'Chai' as B)

result (first (customerid of A) as CustomerID,first(productname of A) as productname,

first(orderdate of A) as purchase_date1,first(orderdate of B) as  purchase_date2, first(quantity of A) as quantity))

 

A couple of points about the above SQL:

  1. The columns listed in the “select” part of the statement will be what data is returned to OBIEE.  These columns are derived from the “result” line.
  2. The “symbols” line above shows that “productname” is hard coded to ‘Chai’. 

Determine which parts of the SQL/MR statement are to be manipulated by the end-user

The hard coded product name value in the above SQL was just for testing.  The end-user actually wants to be able to choose the product.  On the end-user dashboard a drop down prompt will be added that allows them to select which product they want to analyze.

Add OBIEE session variables for the items the end-user wants to manipulate

Session variables will be used to transfer the product selected in the dashboard drop down to the nPath statement.  A session variable called “prod” is added in “variable management”.  “Enable any user to set the value” must be enabled.  The “default initializer” is set to “Chai”.  With the default initializer set to Chai when the end-user brings up their dashboard it will automatically display the results for product name “Chai”.  They can then change it for any product.  If there were no default the dashboard would error out. 

Prepare the OBIEE Physical layer for Aster

The first step in preparing the physical layer is to create a database for Aster data in OBIEE (if one does not already exist).  Set the feature table as described earlier in this document.

Next create a connection pool and set the isolation level to “serializable” (also described earlier in this document).

Add a table in the OBIEE physical layer for the SQL/MR statement

Create a table in the OBIEE database for the SQL/MR statement.  The table will be used to execute the nPath statement.  This will actually be an opaque view so set the table type to “select”.  For this example the table name is npath_analysis.

The SQL/MR statement goes in the text box for the default initialization string.  Notice in the screen shot that the hard coded productname=’Chai’ is replaced with the string “valueof(NQ_SESSION.prod)”.  This will enable a selection on a dashboard to be passed to the Aster database.  In this example the end-user will select a productname and that value will replace the valueof function.  Here is the new SQL/MR statement:

select CustomerID,productname,purchase_date1,purchase_date2,purchase_date2-purchase_date1 as elapsed_days,quantity

from npath (on (select customerid,productname,orderdate,products.productid,quantity from orders,orderdetails,products

where orders.orderid=orderdetails.orderid

and orderdetails.productid=products.productid)

partition by customerid,productid

order by orderdate

mode ( OVERLAPPING )

pattern ('A.B')

symbols (productname= 'valueof(NQ_SESSION.prod)' as A, productname= 'valueof(NQ_SESSION.prod)' as B)

result (first (customerid of A) as CustomerID,first(productname of A) as productname,

first(orderdate of A) as purchase_date1,first(orderdate of B) as  purchase_date2, first(quantity of A) as quantity))

 

Note that the case of the NQ_SESSION.prod variable is important.  The case of the defined variable must match the case in the opaque view.

Add the columns for the SQL/MR results to the table

In the example SQL/MR statement we are selecting 6 columns to return to OBIEE; CustomerID, productID, purchase_date1, purchase_date2, quantity plus a derived column called elapsed_days.  These columns must be defined in the OBIEE physical model with appropriate data types.  Once the columns are added to the table the OBIEE physical layer looks like this:

Note that the case doesn’t need to match the column name vs. what was coded in the SQL/MR.  But, the spelling of the name must match.

At this point the physical model setup can be tested with the “view data” option from a right click on the table.  A grid with the results of the SQL/MR statement is displayed.

Add appropriate dimensions

For this example there are 4 dimensions to join to the npath_analysis fact table.  The table definitions are added to the physical model like any normal table.  The joins are then done in the physical diagram.

Build the logical and presentation layers

The logical and presentation models are built in the standard manner.  There is nothing special to do to deal with the SQL/MR.

This completes the configuration in OBIEE Administrator.  Now the end-user dashboard is built.

Build a dashboard for the SQL/MR table

In OBI Answers, configure and save a query on the SQL/MR table.  This is a standard dashboard query – there is nothing unique about it.  For this example Customers.CompanyName and product1.ProductName are selected for the dimensions.  The facts that were chosen are total_elapsed_days and avg_elapsed_days.  The query is then saved.

Next, in OBI Answers add a new Dashboard prompt. 

On the dashboard prompt page, add a new column prompt.

For the example, the end-user wanted to be able to manipulate the product name.  For the column prompt the product1.ProductName column is appropriate.  The key to the column prompt creation is in the “options” section.  The “set a variable” drop-down must be set to “request variable”.  Then the name of the variable that was used in the SQL/MR statement is filled into the text box.  In this example the session variable was called “prod”. 

Next create and save a dashboard with the previously defined query and prompt

When the dashboard is run, the data from the prod variable defaults to the value set in the “default initializer” in the session variable.  For this example, the default is set to “Chai”.

The product name drop-down is now available for the end-user.  They can select different products and run the SQL/MR statement.

Further Manipulation of the SQL/MR Statement

The SQL/MR example can be further tailored to include some SQL/MR specific user interaction.  For instance the user may want to be able to set the “overlapping” mode to “non-overlapping.  To accommodate this, a lookup table is added to the database with the different modes inserted as rows in the table. 

Create Dimension Table lookups (

   category varchar(20),

 value varchar(20));

INSERT INTO lookups (category, value) VALUES ('lapping','OVERLAPPING');

INSERT INTO lookups (category, value) VALUES ('lapping','NONOVERLAPPING');

The “lookups” table is then added to the OBIEE model.  A physical table alias called “lapping” is created for ease of future expansion.  Note the filter on the Logical Table Source.  This is done so other lookup types or categories can be added to the table. 

A variable is added for “lapping” so the user selected value can be passed to the opaque view.  The opaque view is then modified for the variable substitution (highlighted in red, below). 

select CustomerID,productname,purchase_date1,purchase_date2,purchase_date2-purchase_date1 as elapsed_days,quantity

from npath (on (select customerid,productname,orderdate,products.productid,quantity from orders,orderdetails,products

where orders.orderid=orderdetails.orderid

and orderdetails.productid=products.productid)

partition by customerid,productid

order by orderdate

mode ( valueof(NQ_SESSION.lapping) )

pattern (‘A.B')

symbols (productname= 'valueof(NQ_SESSION.prod)' as A, productname= 'valueof(NQ_SESSION.prod)' as B)

result (first (customerid of A) as CustomerID,first(productname of A) as productname,

first(orderdate of A) as purchase_date1,first(orderdate of B) as  purchase_date2, first(quantity of A) as quantity))

The dashboard can now be tweaked to include setting the “overlapping” mode.  The previously created prompt will be modified to include a new drop down.  It is tied to the “lookups” table on the “value” column.  The prompt sets the variable “lapping”.  

The dashboard is run and the new prompt appears.

The same technique can be applied to the “pattern” section of the SQL/MR statement.  These are the steps (screenshots are included in Appendix A):

  1. Insert additional rows in the “lookups” table for “pattern”:

INSERT INTO lookups (category, value) VALUES ('pattern','A.B');

INSERT INTO lookups (category, value) VALUES ('pattern','B.A');

INSERT INTO lookups (category, value) VALUES ('pattern','A.B.A');

INSERT INTO lookups (category, value) VALUES ('pattern','A.B.A.C');

  1. Create a new physical table alias called “pattern” based on the “lookups” table 
  2. Add table “pattern” to the logical model 
  3. In the logical model add the filter “category = ‘pattern’” to the logical table source for “pattern”
  4. Add the “pattern” table to the presentation model
  5. Add a session variable “pattern”.  Set the default to something appropriate
  6. Modify the opaque view table to include the variable substitution for “pattern”
  7. Modify the dashboard prompt to include a drop down for “pattern” based on the “value” column in the “pattern” table
  8. Ensure the prompt sets the variable “pattern”
  9. Save and run the dashboard

This completes the configuration for the SQL/MR example.  This technique can be applied in OBIEE with most of the SQL/MR functions that are relevant to BI.  The output, of course, can have graphs added and hierarchies and such to make analysis easier for end users.