Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
ISO/IEC 13249-6:2007 (CAN/CSA-ISO/IEC 13249-6-07): SQL/MM Data Mining — Technical Requirements and Compliance Guide
A comprehensive overview of the international standard for integrating data mining models and predictive analytics directly within SQL database environments through user-defined types.
The ISO/IEC 13249 family of standards, commonly known as SQL/MM (SQL Multimedia and Application Packages), extends the capabilities of the SQL database language to handle complex domain-specific data. Part 6 of this series, formally designated ISO/IEC 13249-6:2007 (adopted in Canada as CAN/CSA-ISO/IEC 13249-6-07), specifically addresses the domain of Data Mining.
This standard provides a rigorous framework for storing, managing, retrieving, and applying data mining models directly within an SQL database management system (DBMS). By defining abstract data types (ADTs) and associated routines, ISO/IEC 13249-6 bridges the gap between traditional transactional data processing and advanced analytic operations, enabling the execution of mining tasks—such as clustering, regression, and association rule discovery—natively in the database layer.
Tip: For most modern relational database vendors supporting OLAP and advanced analytics, ISO/IEC 13249-6 serves as the key blueprint for implementing in-database mining algorithms, reducing data movement and latency while maintaining transactional integrity.
Scope and Objectives of ISO/IEC 13249-6
The primary objective of ISO/IEC 13249-6 is to define a set of SQL-based user-defined types (UDTs) and their associated methods that can represent and operate upon data mining models and tasks. The standard does not dictate specific algorithms for regression, classification, or clustering. Instead, it standardizes the interface through which these models are created, deployed, and queried within the SQL environment, ensuring schema-level portability.
Key Areas Covered by the Standard
- Data Mining Model Object: The abstract representation of a mining model (e.g., a decision tree, a neural network, a cluster set).
- Data Mining Settings Object: The configuration parameters governing algorithm behavior and model building.
- Data Mining Functions: Standard routines for applying models to new data (e.g., DM_Predict, DM_Cluster, DM_Sequence).
- Management of Test Results: Standard interfaces for storing and querying the results of model testing, including confusion matrices and lift charts.
Important: While Part 6 provides the interface, the specific performance characteristics and underlying statistical algorithms are implementation-defined. The standard ensures portability of the schema and basic operations, not identical statistical output across different vendor platforms.
Core Technical Requirements and Data Mining Model Components
The standard defines three core logical data structures, realized in SQL as structured types which can be instantiated and manipulated. These types are the foundation upon which all standard-compliant data mining functionality is built.
| UDT Name | Role / Purpose | Key Methods |
|---|---|---|
DM_Model | Encapsulates a trained data mining model. Contains the logic for applying the model to new data for scoring or prediction. | DM_Predict(), DM_Cluster(), DM_Prediction_Details(), model_cost() |
DM_Settings | Configures the model building process. Specifies the mining function type (e.g., classification, clustering) and associated hyperparameters. | DM_getSettings(), setAlgorithm(), setMaxRunTime() |
DM_TestResult | Stores the outcome of supervised model testing, including accuracy, precision, recall, and cost matrices for evaluation purposes. | testAccuracy(), testCostMatrix(), testLift() |
DM_Model: The Central Abstraction
The DM_Model type is the cornerstone of the standard. An instance of this UDT contains all the rules or patterns learned from historical data during the training phase. The standard specifies the methods that must be available to apply this model:
- DM_Predict: Returns a predicted value (categorical or numeric) for a given input case. This is the primary method for classification and regression tasks.
- DM_Cluster: Returns the cluster membership and affinity scores for a given input case in unsupervised learning scenarios.
- DM_Prediction_Details: Provides a structured breakdown of why a specific prediction was made (e.g., the rules fired, attribute contributions), supporting explainability.
- Information Functions: Methods to retrieve metadata about the model such as creation date, training algorithm used, and source data lineage.
DM_Settings: Configuring the Mining Task
Before a DM_Model is built, a DM_Settings object must be configured. This object specifies the target field, the mining function type (classification, clustering, regression, association rules), and the specific algorithm settings. The standard provides a generic mechanism for passing algorithm-specific parameters as key-value pairs, ensuring extensibility as new algorithms emerge without requiring changes to the core UDT interfaces.
Implementation Highlights and Developer Considerations
Implementing SQL/MM Data Mining requires a careful mapping of these abstract types to a specific database architecture. The key implementation challenge is integrating the computational complexity of model training—often requiring linear algebra and tree building—within the database kernel while ensuring transactional consistency.
Implementation Strategy: When a customer demands an environment where data does not leave the database system for privacy, security, or governance reasons, compliance with ISO/IEC 13249-6 allows the development of portable analytical applications. A typical workflow involves:
- Creating Settings: Inserting a
DM_Settingsinstance into a standard table, specifying the algorithm and target field parameters. - Building the Model: Executing a routine such as
DM_ModelBuilder(DM_Model, DM_Settings, TrainingData)which analyzes the training data and populates the DM_Model instance. - Scoring: Applying the model to new data using a query like
SELECT DM_Predict(model_ref, attributes) FROM NewDatato generate predictions inline.
For developers, the standard implies that complex data mining operations can be invoked through standard SQL queries. Instead of exporting data to a separate statistical package or machine learning environment, a simple SQL SELECT or INSERT statement can trigger model generation or scoring. This significantly streamlines MLOps and reduces the friction between operational databases and analytical systems.
Compatibility Check: Not all databases implement the full standard. Many use proprietary extensions for model training (e.g., Oracle Data Mining, IBM Db2 Intelligent Miner). Version 2007 of the standard, adopted as CAN/CSA-ISO/IEC 13249-6-07, remains a key benchmark for assessing the standards-readiness of a vendor in-database analytics offering.
Compliance and Certification Notes
Compliance with ISO/IEC 13249-6 is assessed primarily through conformance testing of the user-defined types, routines, and specified SQL schemas declared in the standard. As part of the ISO/IEC 9075 (SQL/Foundation) framework, it inherits rigorous conformance requirements for data definition language (DDL) and data manipulation language (DML) operations on these specialized types.
Conformance Levels
The 2007 standard distinguishes between Core and Full conformance. Core conformance generally covers the basic structure of the Data Mining UDTs, the ability to define and drop them within schemas, and implement the basic metadata functions. Full conformance requires the implementation of all mining functions (predict, cluster, test) and the detailed manipulation capabilities for test results and prediction details.
Key Compliance Points for Procurement
- UDT Instantiation: The database system must support the creation of
DM_Model,DM_Settings, andDM_TestResulttypes without significant vendor-specific deviation. - Method Invocation: The standard methods (e.g.,
DM_Predict) must be callable within standard SQL queries on the respective UDT instances. - Data Model Integrity: The internal structure for representing test results and training data must conform to the standard schema to ensure portability of analytical workflows.
- Transparent Documentation: A compliant implementation must clearly document the supported algorithms, hyperparameters, and any implementation-defined behaviors that deviate from the main clauses of the standard.
Risk of Non-Compliance: Relying purely on proprietary data mining extensions creates significant vendor lock-in and can hinder data portability. Organizations prioritizing long-term data governance and adherence to government procurement standards (in Canada, the CAN/CSA adoption makes this a recognized national standard) should demand explicit conformance matrices from their software vendors regarding ISO/IEC 13249-6.
While the standard has been superseded by later editions (e.g., ISO/IEC 13249-6:2009, 2016), the 2007 edition (CAN/CSA-ISO/IEC 13249-6-07) remains widely referenced in established production database systems and forms the foundational basis for many of the SQL data mining extensions currently in use across enterprise environments.
Frequently Asked Questions
Q: What is the main difference between ISO/IEC 13249-6 data mining functions and standard statistical SQL aggregate functions?
A: Standard SQL functions (e.g., AVG, STDDEV, CORR) calculate values based on the current data set. ISO/IEC 13249-6 introduces stateful objects (UDTs) that encapsulate a pre-trained model. This allows for advanced operations like predictive scoring and clustering, which require an immutable model artifact versus simple linear calculations on existing rows.
A: Standard SQL functions (e.g., AVG, STDDEV, CORR) calculate values based on the current data set. ISO/IEC 13249-6 introduces stateful objects (UDTs) that encapsulate a pre-trained model. This allows for advanced operations like predictive scoring and clustering, which require an immutable model artifact versus simple linear calculations on existing rows.
Q: Does this standard specify which data mining algorithms vendors must implement?
A: No. The standard focuses strictly on the interface and data structures for data mining, not the specific algorithms. A compliant implementation can use decision trees, neural networks, Bayesian networks, or support vector machines. The algorithm selection is an implementation detail managed through the generic
A: No. The standard focuses strictly on the interface and data structures for data mining, not the specific algorithms. A compliant implementation can use decision trees, neural networks, Bayesian networks, or support vector machines. The algorithm selection is an implementation detail managed through the generic
DM_Settings configuration parameters. Q: How does the CAN/CSA adoption (CAN/CSA-ISO/IEC 13249-6-07) affect organizations operating in Canada?
A: The CAN/CSA designation gives the standard the full authority of a recognized National Standard of Canada by the Standards Council of Canada (SCC). For government IT procurement and regulated industries (finance, telecommunications), adopting database software that explicitly complies with this standard helps satisfy regulatory and legal requirements for the use of recognized international standards in data processing and information management.
A: The CAN/CSA designation gives the standard the full authority of a recognized National Standard of Canada by the Standards Council of Canada (SCC). For government IT procurement and regulated industries (finance, telecommunications), adopting database software that explicitly complies with this standard helps satisfy regulatory and legal requirements for the use of recognized international standards in data processing and information management.
Q: Is ISO/IEC 13249-6 compatible with modern big data platforms like Hadoop and Spark?
A: While originally designed for classical SQL DBMS architectures, the concepts in ISO/IEC 13249-6 heavily influence modern SQL-on-Hadoop systems and Spark SQL MLlib interfaces. Systems that support model management and SQL-based ML scoring often borrow the UDT architecture outlined in this standard. However, explicit formal certification of these newer distributed platforms against the 2007 edition of the standard remains uncommon compared to traditional enterprise database vendors.
A: While originally designed for classical SQL DBMS architectures, the concepts in ISO/IEC 13249-6 heavily influence modern SQL-on-Hadoop systems and Spark SQL MLlib interfaces. Systems that support model management and SQL-based ML scoring often borrow the UDT architecture outlined in this standard. However, explicit formal certification of these newer distributed platforms against the 2007 edition of the standard remains uncommon compared to traditional enterprise database vendors.