DBMS support of the Data Mining Advisor

DBMS support of the Data Mining Advisor : S. -Y. Hwang Ph. D D 954020005 Tsung-Hsien Yang D 954020006 Shi-Hwao Wang 1/22/2008

Agenda o o o Introduction to Data Mining The Promise of Data Mining KDD Process Data Mining Algorithms Data Mining Modeling and Language Conclusion

Introduction to Data Mining o The Explosive Growth of Data: from terabytes to petabytes n Major sources of abundant data o Business: Web, e-commerce, transactions, stocks, … o Science: Remote sensing, bioinformatics, scientific simulation, … o n Society and everyone: news, digital cameras, You. Tube Data collection and data availability o Automated data collection tools, database systems, Web, computerized society

What Is Data Mining? o Data mining: Discovering interesting patterns from large amounts of data o Data mining (knowledge discovery from data) n o Alternative names n o Extraction of interesting (non-trivial, implicit, previously unknown and potentially useful) patterns or knowledge from huge amount of data Knowledge discovery (mining) in databases (KDD), knowledge extraction, data/pattern analysis, data archeology, data dredging, information harvesting, business intelligence, etc. Watch out: Is everything “data mining”? n Simple search and query processing n (Deductive) expert systems

The Promise of Data Mining o Database analysis and decision support n Market analysis and management o n Risk analysis and management o n o target marketing, customer relation management, market basket analysis, cross selling, market segmentation Forecasting, customer retention, improved underwriting, quality control, competitive analysis Fraud detection and management Other Applications n Text mining (news group, email, documents) and Web analysis.

Knowledge Discovery (KDD) Process n Data mining—core of knowledge discovery process Pattern Evaluation Data Mining Task-relevant Data Warehouse Data Cleaning Data Integration Databases Selection

Data preprocessing Define a model Train the model Test the model Training Data Mining Management System (DMMS) Test Data Mining Model Prediction using the model Prediction Input Data

Data Mining Algorithms o o o o o Decision Trees Naïve Bayesian Clustering Sequence Clustering Association Rules Neural Network Time Series Support Vector Machines ….

Data Mining Function o o o Classification (attribute) Estimation (regression) Prediction (time series) Association (cross selling) Clustering (segmentation)

√ - first choice Data Mining Algorithms k or et w N Classification √ √ Regression √ √ Segmentaion √ √ √ Assoc. Analysis √ √ √ Anomaly Detect. Ti √ C D N √ √ A eu ra ia oc √ ss √ m e √ tio n s rie Se lu C √ q. Se √ l. N g st g er √ aï lu st ve in B ay es er in s ee Tr n io is √ ec ru le s √ - second choice √ √ Seq. Analysis √ Time series

Data Mining Language o o è è New challenges in data mining API n Large spectrum of applications: embedded to interactive BI n Interoperability between different DM providers (engine) and DM consumers (tools) n Data independence between content representation (trees, attributes, networks, etc) and data mining task (prediction, scoring, etc) Requirements: n Algorithm-neutral n Task-oriented (specification of what we need, rather than how to) n Vendor-neutral n Flexible, extensible, declarative/self-contained Sound familiar? Yes, SQL

DMX Approach o o Data Mining Extensions (DMX) to SQL Table vs. Mining Model TABLE MINING MODEL schema Column definition Attribute (variable) definition contains Rows Patterns, knowledge, cases DDL Create/drop/alter a model operatio (create, drop, alter) ns DML (insert, delete) Train (populate) a model Query (select) Prediction/browsing a model

Typical DM Process Using DMX Define a model: CREATE MINING MODEL …. Train a model: INSERT INTO dmm …. Data Mining Management System (DMMS) Training Data Prediction using a model: SELECT … FROM dmm PREDICTION JOIN … Prediction Input Data Mining Model

Defining a DM Model o Defines n n n o Shape of “training cases” (top-level entity being modeled) Input/output attributes (variables): type, distribution Algorithms and parameters Example CREATE MINING MODEL College. Plan. Model ( Student. ID Gender Parent. Income Encouragement College. Plans LONG TEXT KEY, DISCRETE, NORMAL CONTINUOUS, DISCRETE PREDICT ) USING Microsoft_Decision_Trees (complexity_penalty = 0. 5)

Training a DM Model: Simple INSERT INTO College. Plan. Model (Student. ID, Gender, Parent. Income, Encouragement, College. Plans) OPENROWSET(‘<provider>’, ‘<connection>’, ‘SELECT Student. ID, Gender, Parent. Income, Encouragement, College. Plans FROM College. Plans. Train. Data’)

Prediction Using a DM Model o PREDICTION JOIN SELECT t. ID, CPModel. Plan FROM CPModel PREDICTION JOIN OPENQUERY(…, ‘SELECT * FROM New. Students’) AS t ON CPModel. Gender = t. Gender AND CPModel. IQ = t. IQ CPModel ID Gender IQ Plan ID Gender IQ New. Students

Classification o Model Definition CREATE MINING MODEL CPClass ( Student. ID LONG KEY, Gender TEXT DISCRETE, Parent. Income LONG CONTINUOUS, Encouragement TEXT DISCRETE, College. Plans TEXT DISCRETE PREDICT ) USING Microsoft_Decision_Trees

Classification (cont) o Find the new students whose predicted class (College. Plan) is ‘Yes’ with confidence > 0. 8 SELECT Student. ID, Predict. Probability(CPClass. College. Plan) FROM CPClass PREDICTION JOIN OPENROWSET (’<provider>’, ’<connection>’, ’SELECT * FROM New. Students’) AS t ON t. Gender = CPClass. Gender AND t. Parent. Income = CPClass. Parent. Income AND t. Encouragement = CPClass. Encouragement WHERE CPClass. College. Plan = ‘Yes’ AND Predict. Probability(CPClass. College. Plan) > 0. 8

Regression o Model Definition CREATE MINING MODEL Cust. Credit ( Cust. ID LONG KEY, Gender TEXT DISCRETE, Age TEXT CONTINUOUS REGRESSOR, Income LONG CONTINUOUS REGRESSOR, Credit DOUBLE CONTINUOUS PREDICT ) USING Microsoft_Decision_Trees

Regression (cont) o Predict Credit score (and stdev) for the new customer data entered from the web form. SELECT Cust. Credit, Predict. Stdev(Cust. Credit) FROM Cust. Credit PREDICTION JOIN (SELECT ’Female’ AS Gender, 30 AS Age, 50000 AS Income) AS t ON t. Gender = Cust. Credit. Gender AND t. Age = Cust. Credit. Age AND t. Income = Cust. Credit. Income

Segmentation o Model Definition CREATE MINING MODEL CPCluster ( Student. ID LONG KEY, Gender TEXT DISCRETE, Parent. Income LONG CONTINUOUS, Encouragement TEXT DISCRETE, College. Plans TEXT DISCRETE ) USING Microsoft_Clustering

Segmentation (cont. ) o Find cluster and its probability for each student SELECT Student. ID, $Cluster, Cluster. Probability() FROM CPCluster PREDICTION JOIN OPENROWSET (’<provider>’, ’<connection>’, ’SELECT * FROM New. Students’) AS t ON t. Gender = CPCluster. Gender AND t. Parent. Income = CPCluster. Parent. Income AND t. Encouragement = CPCluster. Encouragement AND t. College. Plans = CPCluster. College. Plans

Association Prediction o Model Definition CREATE MINING MODEL Fav. Movie. Model ( ID LONG KEY, Marital. Status TEXT DISCRETE, Fav. Movies TABLE PREDICT ( Title TEXT KEY ) ) USING Microsoft_Decision_Trees

Association Prediction (cont) o As a web application, find 5 best recommendations for a customer whose shopping cart contains ‘Star Wars’ and ‘Matrix’. SELECT FLATTENED Predict. Association(Fav. Movie. Model. Fav. Movies, INCLUDE_STATISTICS, 5) FROM Fav. Movie. Model NATURAL PREDICTION JOIN (SELECT ’Single’ AS Marital. Status, (SELECT ’Star Wars’ AS Title UNION SELECT ’Matrix’ AS Title) AS Fav. Movies) AS t

Sequence Prediction o Model Definition CREATE MINING MODEL Web. Seq. Model ( Session. LONG KEY, Page. Seq. ID Page TABLE PREDICT ( LONG KEY SEQUENCE, TEXT DISCRETE ) ) USING Microsoft_Sequence_Clustering

Sequence Prediction (cont) o Show the next 2 steps that a web visitor who visited ‘home’ ‘news’ is going to take. For each step, it has to show top 5 candidate pages with the highest probability. SELECT FLATTENED ( SELECT $Sequence, Top. Count(Predict. Histogram(Page), $Probability, 5) FROM Predict. Sequence(Web. Seq. Model. Page. Seq, 2) ) FROM Web. Seq. Model NATURAL PREDICTION JOIN (SELECT 1 AS Seq. ID, ’home’ AS Page UNION SELECT 2 AS Seq. ID, ’news’ AS Page) AS Page. Seq ) AS t

Time-Series Prediction o Model Definition CREATE MINING MODEL Stock. Model ( Symbol LONG KEY, Date. Recorded DATE KEY TIME, Opening. Quote DOUBLE CONTINUOUS, Closing. Quote DOUBLE CONTINUOUS ) USING Microsoft_Time_Series

Time-Series Prediction (cont) o Predict next five days of MSFT stock closing quotes. SELECT FLATTENED Predict. Time. Series(Stock. Model. Closing. Quote, 5) FROM Fav. Movie. Model WHERE Stock. Model. Symbol = ’MSFT’

Major Issues in Data Mining o Mining methodology n Mining different kinds of knowledge from diverse data types, e. g. , bio, stream, Web n Performance: efficiency, effectiveness, and scalability n Pattern evaluation: the interestingness problem n Incorporation of background knowledge n Handling noise and incomplete data n Parallel, distributed and incremental mining methods Integration of the discovered knowledge with existing one: knowledge fusion n o User interaction n n Expression and visualization of data mining results n o Data mining query languages and ad-hoc mining Interactive mining of knowledge at multiple levels of abstraction Applications and social impacts n n Domain-specific data mining & invisible data mining Protection of data security, integrity, and privacy

Data Mining Vendors o o o SAS (Enterprise Miner) IBM (DB 2 Intelligent Miner) Oracle (ODM option to Oracle 10 g) SPSS (Clementine) Insightsful (Insightful Miner) KXEN (Analytic Framework) Prudsys (Discoverer and its family) Microsoft (SQL Server 2005) Angoss (Knowledge. Server and its family) DBMiner (DBMiner) Many others

Data Mining and Business Intelligence Increasing potential to support business decisions Making Decisions Data Presentation Visualization Techniques Data Mining Information Discovery End User Business Analyst Data Exploration Statistical Analysis, Querying and Reporting Data Warehouses / Data Marts OLAP, MDA Data Sources Paper, Files, Information Providers, Database Systems, OLTP DBA

Data Mining Modeling and Language o Problem Description n two powerful tools o o n Database management systems Efficient and effective data mining algorithms and frameworks Generally, this work asks: o o “How can we merge the two? ” “How can we integrate data mining more closely with traditional database systems, particularly querying? ”

Three Different Answers o o o MSQL: A Query Language for Database Mining (Imielinski & Virmani, Rutgers University) DMQL: A Data Mining Query Language for Relational Databases (Han et al, Simon Fraser University) Integrating Data Mining with SQL Databases: OLE DB for Data Mining (Netz et al, Microsoft)

MSQL o o o Focus on Association Rules Seeks to provide a language both to selectively generate rules, and separately to query the rule base Expressive rule generation language, and techniques for optimizing some commands

MSQL o Get-Rules and Select-Rules Queries Get-Rules operator generates rules over elements of argument class C, which satisfy conditions described in the “where” clause [Project Body, Consequent, confidence, support] Get. Rules(C) [as R 1] [into <rulebase_name>] [where <conds>] [sql-group-by clause] [using-clause] n

MSQL o <conds> may contain a number of conditions, including: n restrictions on the attributes in the body or consequent o “rule. body HAS {(Job = ‘Doctor’}” “rule 1. consequent IN rule 2. body” “rule. consequent IS {Age = *}” pruning conditions (restrict by support, confidence, or size) Stratified or correlated subqueries o in, has, and is are rule subset, superset, o and equality respectively n n

MSQL Get. Rules(Patients) where Body has {Age = *} and Support >. 05 and Confidence >. 7 and not exists ( Get. Rules(Patients) Support >. 05 and Confidence >. 7 and R 2. Body HAS R 1. Body) Retrieve all rules with descriptors of the form “Age = *” in the body, except when there is a rule with equal or greater support and confidence with a rule containing a superset of the descriptors in the body

MSQL correlated stratified Get. Rules(C) R 1 where <pruning-conds> and not exists ( Get. Rules(C) R 2 where <same pruning-conds> and R 2. Body HAS R 1. Body) Get. Rules(C) R 1 where <pruning-conds> and consequent is {(X=*)} and consequent in (Select. Rules(R 2) where consequent is {(X=*)}

MSQL o Nested Get-Rules Queries and their optimization n n Stratified (non-corrolated) queries are evaluated “bottom-up. ” The subquery is evaluated first, and replaced with its results in the outer query. Correlated queries are evaluated either top-down or bottomup (like “loop-unfolding”), and there are rules for choosing between the two options

MSQL Top-Down Evaluation Get. Rules(Patients) where Body has {Age = *} and Support >. 05 and Confidence >. 7 For each rule produced by the outer, evaluate the inner not exists ( Get. Rules(Patients) Support >. 05 and Confidence >. 7 and R 2. Body HAS R 1. Body)

MSQL Bottom-Up Evaluation not exists ( Get. Rules(Patients) Support >. 05 and Confidence >. 7 and R 2. Body HAS R 1. Body) For each rule produced by the inner, evaluate the outer Get. Rules(Patients) where Body has {Age = *} and Support >. 05 and Confidence >. 7

DMQL o Commands specify the following: n n The set of data relevant to the data mining task (the training set) The kinds of knowledge to be discovered o Generalized relation o o Characteristic rules Discriminant rules Classification rules Association rules

DMQL o Commands Specify the following: n Background knowledge o n Concept hierarchies based on attribute relationships, etc. Various thresholds o Minimum support, confidence, etc.

DMQL o Specify background knowledge Specify rules to be discovered Relevant attributes or aggregations Collect the set of relevant data to mine Specify threshold parameters Syntax use database <database_name> {use hierarchy <hierarchy_name> for <attribute>} <rule_spec> related to <attr_or_agg_list> from <relation(s)> [where <conditions>] [order by <order list>] {with [<kinds of>] threshold = <threshold_value> [for <attribute(s)>]}

DMQL use database Hospital find association rules as Heart_Health related to Salary, Age, Smoker, Heart_Disease from Patient_Financial f, Patient_Medical m where f. ID = m. ID and m. age >= 18 with support threshold =. 05 with confidence threshold =. 7

DMQL o o DMQL provides a display in command to view resulting rules, but no advanced way to query them Suggests that a GUI interface might aid in the presentation of these results in different forms (charts, graphs, etc. )

OLE DB for DM o o An extension to the OLE DB interface for Microsoft SQL Server Seeks to support the following ideas: n n Define a model by specifying the set of attributes to be predicted, the attributes used for the prediction, and the algorithm Populate the model using the training data Predict attributes for new data using the populated model Browse the mining model (not fully addressed because it varies a lot by model type)

OLE DB for DM o Defining a Mining Model n o Identify the set of data attributes to be predicted, the set of attributes to be used for prediction, and the algorithm to be used for building the model Populating the Model n Pull the information into a single rowset using views, and train the model using the data and algorithm specified

OLE DB for DM o Using the mining model to predict n Defines a new operator prediction join. A model may be used to make predictions on datasets by taking the prediction join of the mining model and the data set.

OLE DB for DM CREATE MINING MODEL Heart_Health Prediction ( ID Int Key, Age Int, Smoker Int, Salary Double discretized, Heart. Attack Int PREDICT, %Prediction column ) USING Microsoft_Decision_Trees Identifies the source columns for the training data, the column to be predicted, and the data mining algorithm.

OLE DB for DM INSERT INTO Heart_Health Prediction (Age, Smoker, Salary, Heart. Attack ) OPENROWSET (’<provider>’, ’<connection>’, ’SELECT Age, Smoker, Salary, Heart. Attack FROM Patient_Medical M, Patient_Financial F WHERE M. ID = F. ID’) The INSERT represents using a tuple for training the model (not actually inserting it into the rowset).

OLE DB for DM SELECT T. ID, H. Heart. Attack FROM Heart_Health Prediction H PREDICTION JOIN ( OPENROWSET (’<provider>’, ’<connection>’, ’SELECT ID, Age, Smoker, Salary FROM Patient_Medical M, Patient_Financial F WHERE M. ID = F. ID’) as T ON H. Age = T. Age AND H. Smoker = T. Smoker AND H. Salary = T. Salary Prediction join connects the model and an actual data table to make predictions

Key Ideas o o o Important to have an API for creating and manipulating data mining models The data is already in the DBMS, so it makes sense to do the data mining where the data is Applications already use SQL, so a SQL extension seems logical

Key Ideas o o o Need a method for defining data mining models, including algorithm specification, specification of various parameters, and training set specification (DMQL, MSQL, ODBDM) Need a method of querying the models (MSQL) Need a way of using the data mining model to interact with other data in the database, for purposes such as prediction (ODBDM)