Oracle Business Intelligence Enterprise Edition (OBIEE) 11g Integration Possibilities with Oracle Data Mining (ODM) - Part A - Direct Database Request (Direct SQL)

Oracle Data Mining (ODM), is a component of the Oracle Advanced Analytics Option for Oracle Database 11g Enterprise Edition. ODM provides a collection of in-database data mining algorithms that solve a wide range of business problems. It provides the necessary mining model building, testing and scoring capabilities. Because of data, models, and results remain in Oracle Database, data movement is eliminated, security is maximized, and information latency is minimized.

The purpose of this blog is to introduce oracle data miner, list down the different data mining algorithm supported by oracle data miner and possibilities of combining OBIEE and ODM. The focus of the first part is to explain the integration of ODM by using Direct Database Request option of OBIEE.

Oracle Data Mining (ODM) Architecture 

Oracle Data Mining (ODM) has two components a server and one or more clients.

·         Oracle Database 11g (11.2) Enterprise Edition Server

·         Oracle Data Miner, the client, is an integrated feature of Oracle SQL Developer

In addition to the database, Data Miner requires the installation of a Data Miner Repository account. The repository is a separate account in the database named ODMRSYS. This repository is shared by all user accounts in the database that have been granted the appropriate privileges to use the Data Miner Repository.

Full Set of Mining Algorithms

Oracle Data Mining provides support for a wide range of data mining functionality.

OBIEE Integration Opportunities with ODM

Executing the algorithm/data mining model is just beginning. Integration of data mining model execution result with the business data is vital.

As the data mining model execution results are immediately available through SQL and integrated in Oracle Database, data mining model execution results can be immediately used across the Oracle stack - unleashing many more opportunities to leverage your results in spontaneous and unexpected ways.

There are number of options by which we can integrate the data mining model execution results into the business. I would like to focus on its integration with Oracle Business Intelligence in general and Direct SQL in particular within this blog entry. Oracle Business Intelligence can leverage data results, combining the data mining model execution results with other data.

The following are four possibilities by which Oracle Statistical Functions can be integrated with OBIEE.

Direct Database Request ( Direct SQL)  

The OBIEE Direct Database Request option allows executing SQL statement which uses the oracle data mining algorithm directly against a data source. The Oracle BI Server sends unprocessed, user-entered, physical SQL directly to chosen database. The returned results can be rendered in Oracle Presentation Services, which can be treated as an Oracle BI Analyze.

Use Evaluate Function

The EVALUATE function within the report definition allows calling the database analytic/data mining functions, result tables by passing the necessary parameters. The data mining PL/SQL’s and functions in Oracle database can be invoked from OBIEE using the EVALUATE function. The function would be executed as part of the report generation SQL against the chosen database.

Integrate in Common Enterprise Information Model

This is the standard metadata design method by which all required database objects and data mining result tables and output columns are exposed to physical layer, then modeled within logical layer of OBIEE and then exposed to presentation layer.

Direct Interactions with Opaque Views by Using Session Variables

This is an option where one can define Opaque View (view within the OBIEE physical layer) which uses the data mining function and result tables within the SQL definition of opaque view. The session variables are used as substitute variables within the SQL definition. The value of session variables can be set by user selected prompt to drive the opaque view output. This allows direct interactions with the statistical function and open opportunities to carry out what if analysis.

ODM- OBIEE Integration Example – Database Direct Request ( Direct SQL)

This section attempts to demonstrate oracle data mining integration with OBIEE which uses the direct database request feature of OBIEE 10g/11g.

SQL with Prediction Verbs

Oracle Data mining supports verbs via SQL such as PREDICT, DETECT, CLUSTER, CLASSIFY, REGRESS, PROFILE, IDENTIFY FACTORS and ASSOCIATE.

SQL which uses the prediction verb is as follows

The SQL output comprises of the customer id, country name, customer marital status and prediction based on sample regression model.

Direct Database Request

Choose New > Analysis > Choose Create Direct Database Request.

Enter Connection Pool – Sample Relational Connection, this is connection pool defined within repository. Enter the SQL statement with prediction clause as shown below.

Analysis Output

The analysis output for the entered SQL statement with column outputs as customer id, country name, customer marital status and prediction is shown below.

Conclusion

The data mining results can be immediately used by database direct request option of OBIEE which can combine these outputs in business data with ease.

The returned results can be rendered in Oracle Presentation Services, which can be treated as an Oracle BI Analyze. We can leverage on OBIEE visuals such as graph, geo, OSSM, Google etc to render the data mining output.

The Oracle Advanced Analytics Option, a combination of Oracle Data Mining and Oracle R Enterprise, delivers predictive analytics, data mining, text mining, statistical analysis, advanced numerical computations and interactive graphics inside the database. This brings powerful computations to the database resulting in dramatic improvements in information discovery, scalability, security, and savings.