Introduction to Oracle Machine Learning User Interfaces and User Interfaces

Lesson 3: Introduction to Oracle Machine Learning UIs

Overview of the Oracle Machine Learning (OML) Ecosystem

• Rebranding and Evolution:

  • Oracle Database previously featured the Oracle Advanced Analytics (OAA) option.

  • OAA provided access to widely used machine learning algorithms without requiring data movement away from the database.

  • OAA and related components have been rebranded under the Oracle Machine Learning (OML) umbrella.

  • Reference for rebranding details: blogs.oracle.com/machinelearning/oracle-machine-learning-family-of-products.

• Core Algorithms:

  • The Oracle Database kernel contains over 30 different machine learning algorithms.

  • These algorithms have existed for many years and are optimized for scalability and high performance within the database platform.

Oracle Machine Learning for SQL (OML4SQL)

• Definition: An API consisting of extensions to Oracle SQL that allows for powerful machine learning capabilities within the Oracle Database kernel.

• Key Advantages:

  • Scoring in SQL Queries: Scoring can be performed dynamically or by applying existing machine learning models.

  • Automatic Data Preparation (ADP): Includes embedded transformations for streamlined model building.

  • Model Transparency: Algorithm-specific queries return metadata about the attributes used to create the model.

  • Scoring Transparency: Provides details about predictions, clustering, or feature extraction operations along with the calculated score.

  • Predictive Analytics: Simple routines for generating predictive outputs.

  • Database Objects: OML4SQL models are treated as first-class database objects.

• Supported Data Types and Sources:

  • Standard data tables and views.

  • Star schema data, including transactional data and aggregations.

  • Unstructured data: Utilization of the Character Large Object (CLOB) data type and Oracle Text to extract tokens.

  • Spatial data.

• Technical Performance:

  • Leverages database parallelism for model building and model application.

  • Honors user data access privileges and security schemes inherent to the Oracle Database.

Oracle Machine Learning Development and Model Creation

• Process for Model Creation:

  1. Decide the model's intended function.

  2. Identify training data and determine required transformations.

  3. Specify model settings to influence behavior.

  4. Choose the machine learning function and algorithm.

  5. Identify the build (training) data and, for classification, specify test data.

  6. Create and populate a settings table if necessary.

• PL/SQL API Example:

  • Scenario: Predicting customer response to an affinity card program using demographic data.

  • Model name: $DT_SH_Clas_sample$.

  • Data source: $mining_data_build_parallel_v$ view.

  • Target column: $affinity_card$.

  • Unique Identifier (UID): $cust_id$.

• Executing Models via SQL Functions:

  • Example query for identifying customers in Brazil likely to respond to a program:

    • SELECT cust_id FROM mining_data_apply_v WHERE PREDICTION(dt_sh_clas_sample USING customer_marital_status, education, cust_income_level) = 1 AND country = 'Brazil';.

Oracle Data Miner (ODM)

• Role: A drag-and-drop graphical user interface (GUI) extension to Oracle SQL Developer.

• Target Audience: Targeted toward "Citizen Data Scientists."

• Supported Platforms: On-premise Oracle installations and Oracle Database Cloud Service (DBCS). It does not support Oracle Autonomous Database.

• Architectural Components:

  • Client: Integrated feature of Oracle SQL Developer (Free of charge).

  • Server: Oracle Enterprise Edition 12c (or later) or Standard Edition 2 (12c or later).

  • Repository: A separate database account named $ODMRSYS$ which stores metadata about workflows. It is shared by all users with appropriate privileges.

  • Supporting Technologies:

    • Oracle Machine Learning for SQL (formerly ODM): Mining, building, testing, and scoring.

    • Oracle Text: Supports text mining.

    • Oracle XML DB: Manages repository metadata.

    • Oracle Scheduler: Engine for scheduling and execution of workflows.

    • Oracle Machine Learning for R (formerly Oracle R Enterprise): Executes embedded R scripts.

Setting up Oracle Data Miner

1. Install SQL Developer: Version 19.4 or later (Data Miner is included natively).

2. Create a Connection:

   • Establish a SQL Developer connection to an Oracle Database (11g R2 or later).

   • Account must have all required OML grants.

   • Example user: $dmuser$.

3. Enable GUI: Navigate to View -> Data Miner -> Data Miner Connections.

4. Install Repository:

   • Triggered automatically when activating a connection from the Data Miner tab for the first time.

   • Requires DBA authentication.

   • User must provide a default tablespace and a temporary tablespace.

   • Optionally install demonstration data.

Oracle Data Miner UI Elements

1. Connections Panel: Displays available database connections (e.g., $dmuser$).

2. Workflow Jobs Panel: Status of completed and running jobs.

3. Structure Panel: Tree structure for workflow navigation (supports visually impaired users).

4. Workflow Canvas: The main window for graphically building machine learning processes.

5. Messages Panel: Log of events and system messages.

6. Property Inspector: Displays property values of selected items in the workflow.

7. Component Palette: Contains graphical icons (nodes) organized into six categories.

8. Thumbnail: Zoomed-out display for navigating large workflows.

Projects and Workflows in ODM

• Projects: Reside in a database connection; a project must be created before a workflow can exist.

• Workflows: Mechanisms to define operations like model building, testing, and applying.

  • A workflow consists of connected nodes (linked objects).

  • Must contain one or more data sources (tables or models).

• Building a Workflow Sequence:

  1. Create a blank workflow under a project.

  2. Add nodes and connect them (Right-click node -> Connect -> Drag to target node).

  3. Run nodes (individually or the entire workflow).

  4. Examine results and repeat as needed.

Oracle Data Miner Nodes and Categories

• Data Understanding (CRISP-DM Phase 2):

  • Data Source Node: Defines source data (e.g., $CUST_INSUR_LTV$ table).

  • Graph Node: Creates 2D graphs of numeric data.

  • Explore Data Node: Provides profiles, statistics, and histograms for all selected columns.

• Data Preparation (CRISP-DM Phase 3):

  • Transforms Category: Aggregate, Filter Columns, Filter Rows, Join, JSON, Sample, and Transform.

  • Filter Columns Node: Excludes specific columns from subsequent calculations.

• Data Modeling (CRISP-DM Phase 4):

  • Classification Node: Defines models to build and test categorization.

• Evaluation (CRISP-DM Phase 5):

  • Nodes evaluate if models meet business goals.

• Deployment/Apply (CRISP-DM Phase 6):

  • Apply Node: Takes models and returns a single score as a query result.

• Additional Specialist Nodes:

  • Anomaly Detection, Clustering Query, Feature Extraction, Prediction Query.

  • Text Category: Apply Text, Build Text, and Text Reference.

Deployment Use Cases

• Code Invocation: Once a model is built, it can be invoked via SQL queries in SQL*Plus, SQL Developer, or any interface connecting to Oracle SQL.

• Programmatic Access: Invoke functions from PL/SQL blocks, stored programs, or packages.

• Oracle APEX: Integrate machine learning functions directly into web applications.

• RESTful Web Services: Expose functions via Oracle REST Data Services (ORDS) for consumption by cURL, Postman, Java, or Python.

• Scoring Types:

  • Batch Scoring.

  • Single-Record Scoring.

  • Scoring using bind variables or supplied values.

Oracle Machine Learning Notebooks

• Role: Web-based, collaborative UI based on Apache Zeppelin.

• Target Audience: Data Scientists, analysts, and DBAs.

• Platform: Available only for Oracle Autonomous Database (Autonomous Data Warehouse and Autonomous Transaction Processing).

• Features:

  • Automated provisioning, management, and backup.

  • Supports SQL, PL/SQL, and markdown for explanatory text.

  • Easy sharing, versioning, and execution scheduling.

  • Built-in visualizations.

  • Future support planned for Python and R.

Comparison: Oracle Data Miner vs. OML Notebooks

Machine Learning Task Cheat Sheet

• Clustering: Group or segment cases into clusters based on input variables.

• Attribute Importance: Rank input variables by their importance in predicting a target.

• Feature Extraction: Generate a reduced set of new variables derived from all input variables.

• Time Series: Predict patterns and trends based on a specific time sequence.

• Classification (Predict Categories): Target variable contains distinct category values (e.g., Yes/No).

• Regression (Predict Numeric values): Target variable contains numeric values.

• Anomaly Detection: Identify unusual cases, flagging them as normal or anomalous.

• Association Rules: Find interesting relationships or patterns that frequently occur together.