This article describes how to integrate Oracle BI Enterprise Edition (OBIEE) 11g with Fuzzy Logix DB Lytix™ 1.x on Teradata to enable transparent OBIEE end user access to the advanced analytic functions from Fuzzy Logix. Fuzzy Logix’s DB Lytix™ 1.x includes libraries of in-database models that run deep inside Teradata Systems. Use of these models allows execution of analytics within the database, leveraging the performance, parallelism and scalability of Teradata system while dramatically improving the performance of analytic results and simplifying the integration of analytics into existing reporting and analytic applications.
Oracle Business Intelligence Enterprise Edition 11g can take advantage of Fuzzy Logix on Teradata functionality to expand its compliment of functions available for analysis. It is likely integration and deployment of Fuzzy Logix with OBIEE will require a collaboration of subject matter experts – a developer (or data scientist) who can define/test/validate the analysis to fulfill the end user requirement, the Teradata DBA to implement any additional functionality needed in the Teradata system, and the OBIEE Admin to work with the developer to integrate and deploy, or operationalize the analysis within OBIEE to the broader BI user community.
As mentioned previously DB Lytix™ is a very rich library of quantitative methods implemented for Teradata. The quantitative methods have been implemented in several different ways on Teradata:
· Scalar functions
· Aggregate functions
· Stored procedures
Depending upon the DB Lytix™ method, some methods are standalone analyses whereas other methods require preprocessing or running multiple steps where one step feeds another.
The DB Lytix™ scalar functions are the easiest to integrate with OBIEE. The OBIEE “evaluate” function will be used to access the function. Evaluate functions can be configured in OBIEE Administrator or in an OBIEE analysis.
The DB Lytix™ Aggregate functions are also easy to integrate with OBIEE. The OBIEE “evaluate_aggr” function will be used to access the function. Evaluate functions can be configured in OBIEE Administrator or in an OBIEE analysis.
The DB Lytix™ External stored procedures are the hardest to integrate with OBIEE. OBIEE has the capability to call external stored procedures (XSP), but, handling the output of the Fuzzy Logix stored procedures is the hard part. The stored procedures use either regular “permanent” tables and/or volatile tables during processing. Fuzzy Logix External Stored Procedures return results as follows:
· External Stored Procedure saves results to predefined “permanent” table(s) which reside in the database where DB Lytix™ is installed. Fuzzy Logix refer to these tables as their “System Tables”. All System Tables are defined within the Fuzzy Logix DB Lytix™ User Guide for each function available as an XSP. Using the Fuzzy Logix Linear and Logistic Regression functions FLLinRegr and FLLogRegr as an example, their System Tables store information such as model statistics and coefficient values. System tables are indexed by an Analysis Identifier, which uniquely identifies the execution of all DB Lytix functions. Analysis Identifiers are returned in the XSP’s OUT Argument.
· External Stored Procedure saves results to a new table in any database. Creating new tables is a special case of the External Stored Procedure FLRegrDataPrep, which prepares the input data for the Fuzzy Logix data mining algorithms. This XSP also has a System Table that is updated with information required to return the input tables to their previous state, or describe the contents of the prepared data. As such, an Analysis Identifier is also returned and used as an index in that System Table.
· External Stored procedure either returns the results to STDOUT or writes results to a volatile table which can be used for downstream processing. In this case, no Analysis Identifer is returned because no System Tables exist for the functions. Examples of this type of stored procedure are the statistical test FLADTest and all of the scoring procedures, such as FLLogRegrScore.
The output method is very important to OBIEE modeling. OBIEE can deal with some of the methods.
This is a very easy output method to deal with – this is standard OBIEE reporting. The results tables exist in the database and can be easily mapped into the OBIEE physical model using standard techniques. Since the tables exist the OBIEE import functionality can be used to grab the column definitions.
Some stored procedure save results to a new table. This table is created in the database where DB Lytix™ is installed. An example of this type of Stored Procedure is FLRegrDataPrep. Since there is a new table generated each time this type of procedure is run it cannot be handled via standard OBIEE modeling. But, if you reuse the same table name each time the XSP is run then OBIEE standard reporting can be used. The only trick is to drop the table before the Fuzzy XSP tries to create it. This can be handled with a custom stored procedure or other methods.
Some of the DB Lytix™ stored procedures have the capability to dynamically return a table (also known as sending output to the screen). Dynamically returning results is controlled by an argument in the stored procedure call. Testing of stored procedures returning a table was not successful. Not all of the stored procedures that fit into this category were tested, but the ones that were tested caused errors in OBIEE. It should be noted that typically OBIEE can handle a table returned as output from stored procedures. There are “how to” documents available on the web that show the integration. While this will work for some XSPs it did not work for the Fuzzy Logix XSPs that were tested. The Fuzzy Logix stored procedures would run, but the output would not return to OBIEE. Mostly, errors like the following occurred:
Some of the DB Lytix™ stored procedures create result sets as volatile tables. Volatile tables are only available to the session that created it. Because OBIEE uses connection pools the session that created the volatile table probably isn’t the same session that is going to read the table. Therefore XSPs don’t work without ensuring session consistency. One method to ensure session consistency is to create a special connection pool for use with Fuzzy Logix stored procedures. If a connection pool only has one connection (or session) then the consistency should be intact. Typically however, the volatile table name changes with each invocation of the Fuzzy Logix stored procedure. Getting OBIEE to handle the changing table name is tricky, but possible.
One method to access the changing table name could have been to use OBIEE “dynamic table names”, init blocks and session variables. The stored procedure would be called by an init block and return the table name to a session variable. The session variable would then be used as a dynamic table name in the OBIEE physical model.
Unfortunately this method will not work. The problem is the connection pool. Init Blocks will use one connection pool and the query of the data will use a different connection pool. Being a volatile table the different connection pools can’t share the data.
A method that does handle the changing table name would be to create a custom stored procedure (SP) that calls the Fuzzy XSP. The custom SP would be written such that it would grab the volatile table and put the results into a predefined regular table. Since the creation and read of the volatile table data is done via the custom procedure there are no connection pool or session issues. Also, using predefined table names allows for straightforward OBIEE modeling.
The custom stored procedure (below) not only calls the Fuzzy SP, but it also copies the volatile table data to a regular table.
create procedure callsp (IN TableToScore char(50),IN RegrAnalysisID char(50),OUT newT char(50))
declare OutTable char(50);
declare SqlString char(500);
CALL fuzzy.FLLogRegrScore(TableToScore, 'OBSID', 'VARID', 'Num_Val', NULL, RegrAnalysisID,1, OutTable);
set newT= substr(OutTable,1,instr(OutTable,'_')-1);
set SqlString = 'insert into ResultsOverview_LogRegrScore (InputTable,RegrAnalysisID,ScoreAnalysisID) values('''||TableToScore||''','''||RegrAnalysisID||''','''||substr(OutTable,1,instr(OutTable,'_')-1)||''')';
EXECUTE IMMEDIATE sqlstring;
set SqlString = 'insert into Results_LogRegrScore select '''||substr(OutTable,1,instr(OutTable,'_')-1)||''',ObsID,Y from '|| OutTable;
EXECUTE IMMEDIATE sqlstring;
Notice that this custom SP wrapper above is inserting rows into 2 tables. One table is an overview table and tracks when the scoring stored procedure was run. The other table is a detail table for the scoring results. OBIEE can then use these 2 tables to report results to the end user. This becomes standard OBIEE reporting. Once these 2 tables exist in the database and they can be easily mapped into the OBIEE physical model using standard techniques. Since the tables exist the OBIEE import functionality can be used to grab the column definitions.
This article has described how to configure and integrate Oracle BI Enterprise Edition (OBIEE) with Fuzzy Logix DB Lytix™ on Teradata. Many integration methods were described. Some methods are straight-forward and easy to implement. Others are complex and require special handling to integrate with OBIEE. The output method is very important to OBIEE modeling. OBIEE 12c will be reviewed for alternative integration methods.
There is a companion document that includes “step-by-step” detailed examples on each of the integration methods. To obtain this document please contact Todd.Cannaday@teradata.com.