allan mitchell sql server mvp konesans limited ww.sqlis.com

Excel – Not a Bad Data Mining Client At All

Allan MitchellSQL Server MVP

Konesans Limitedww.SQLIS.com

Who am I

• SQL Server MVP• SQL Server Consultant• Joint author on Wrox Professional SSIS book• Worked with SQL Server since version 6.5• www.SQLDTS.com and www.SQLIS.com

Today’s Schedule

• Mostly Demos• Data Mining Add-In for Excel 2007– Added XL Functions– Visualisation Methods

Today’s Schedule

• Added XL Functions - Not a lot of people know these exist– DMPREDICT– DMPREDICTTABLEROW– DMCONTENTQUERY

– Only exist after add-in installed

Today’s Schedule

• Visualisation Methods– Accuracy Charts– Classification Matrix– Profit Charts– Folding (X-Validation)– Calculator (if we get time)

Excel Functions

• DMPREDICT• Can take a variable number of arguments, the minimum being 3.• The first parameter is the Analysis Services connection to be used.

An empty string refers to the current (active) connection.• The second parameter is the name of the mining model that will

execute the prediction• The third parameter, is the requested predicted entity (predictable

column, in general, but could also be any prediction function)• The function may also take up to 32 pairs of arguments. Each such

pair contains the value and the name of an input (in this order, i.e. value followed by name).

Excel Functions

• DMPREDICTTABLEROW• The first parameter is the Analysis Services connection to be

used. An empty string refers the current (active) connection.• The second parameter is the name of the mining model that will

execute the prediction• The third parameter, is the requested predicted entity

(predictable column, in general, but could also be any prediction function)

• The fourth parameter is a range of cells to be passed as inputs• The fifth parameter (optional) is a comma-separated list of

column names to be used as names for the inputs

Excel Functions

• DMPREDICTTABLEROW• If range of cells is form XL List Object• Column Headers taken from List• 5th Parameter not necessary– Unless Column Name != Model Column Name

Excel Functions

• DMCONTENTQUERY• The first parameter is the Analysis Services connection to

be used. An empty string refers to the current (active) connection.

• The second parameter is the name of the mining model that will execute the prediction

• The third parameter, is the requested content column• The fourth parameter is a WHERE clause to be appended

to the content query

DEMOData Mining Excel functions

Excel Add-In

• Great way of visualising Data Mining• Takes away some of the mystery• Easy to use• Some wizards• Freedom vs. flexibility

Accuracy Charts

• Compare 1-n models against– Another model– Best model– Thumb in the air model/no model/chance

Accuracy Charts

• Interpreting– How does a model compare with other models– What is the cumulative gain– Lift

• The real thing we want to see is.....– By how much do we beat the “chance” model

DEMOAccuracy Charts

Classification Matrix

• What are we interested in– How well did my model predict outcomes– False Positive– False Negative– True Positive– True Negative

Classification Matrix

Predicted TRUE FALSE

Actual

TRUE True Positive False Negative (type 2 error)

FALSE False Positive (type 1 error) True Negative

Classification Matrix

• A misclassification is not always a bad thing• Consider– Predicted possibility of disease– Extra care/treatment given– Real result is “No disease”– Example of false positive– Is it such a bad thing?

DEMOClassification Matrix

Profit Charts

• Closely follows lift/cumulative gain chart• Apply costs to efforts

Profit Charts

• Apply costs to– Initial/Fixed outlay– Cost per case– Return per case

• Target predictable column• Target Outcome• Count of cases to use

DEMOProfit Chart

X-Validation/Folding/Rotation Estimation

• Validates your model• Tests whether model generally applicable• Large variations in results between partitions– Model not generally applicable– May need tuning

X-Validation/Folding/Rotation Estimation

• Stratified K-Fold Cross Validation• Creates K folds– Representative partitions

• Holds one partition out• Trains model with others• Tests with holdout partition• Repeat (different holdout/test partition)* K

DEMOX-Validation/Folding/Rotation Estimation

Prediction Calculator

• Set costs and profits associated with– Getting the prediction right– Getting the prediction wrong

• See profit curves• See profit threshold scores• Pad for entering new data

Prediction Calculator

• Cloud Version available• Print version available for later data entry• Easy to use• Easy to understand

DEMOPrediction Calculator

Thank you…allan.mitchell@konesans.com