Before building a model, it helps to know which attributes have anything to do with the target. DBMS_PREDICTIVE_ANALYTICS.EXPLAIN answers that in one call, without a model: it ranks every column of a table or view by how much it explains the target column, and writes the ranking to a result table.
Code for This Guide
The main examples are in the examples/machine-learning folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_predictive_analytics.explain( data_table_name => 'TABLE_OR_VIEW', explain_column_name => 'TARGET', result_table_name => 'RESULT_TABLE'); -- RESULT_TABLE: ATTRIBUTE_NAME, EXPLANATORY_VALUE, RANK
The result table must not exist yet. Explanatory values run from 0, no relation, to 1.
The example uses the ML_TICKETS view from How to Explain Predictions with PREDICTION_DETAILS in Oracle.
What Explains the Cabin?
Example:
create or replace view ml_cabin_factors as
select cabin, tier, points, days_ahead, distance_km, legs, age from ml_tickets;
begin
dbms_predictive_analytics.explain(
data_table_name => 'ML_CABIN_FACTORS',
explain_column_name => 'CABIN',
result_table_name => 'CABIN_EXPLAINED');
end;
/
select attribute_name, round(explanatory_value, 4) as explanatory_value, rank
from cabin_explained
order by rank;Output:
View ML_CABIN_FACTORS created. PL/SQL procedure successfully completed. ATTRIBUTE_NAME EXPLANATORY_VALUE RANK _________________ ____________________ _______ DAYS_AHEAD 0.3411 1 POINTS 0.2219 2 TIER 0.2163 3 LEGS 0 4 AGE 0 4 DISTANCE_KM 0 4 6 rows selected.
Days booked ahead explains the cabin best, then loyalty points and tier. Legs, age, and distance explain nothing on their own. This matches what the decision tree found: its predictions rest on days ahead and points. The example drops the result table afterward.
Things to Know
- EXPLAIN looks at one attribute at a time, so attributes that matter only in combination can rank low.
- Use it to choose attributes before training, and to check that a model relies on sensible ones.
- Drop the ML_CABIN_FACTORS view when done.
Related Guides
- How to Explain Predictions with PREDICTION_DETAILS in Oracle
- How to Evaluate Models with COMPUTE_CONFUSION_MATRIX in Oracle
Conclusion
DBMS_PREDICTIVE_ANALYTICS.EXPLAIN ranks a table's columns by how much each explains a target, without building a model. Use it to pick attributes and to sanity-check what your models rely on.
