数据挖掘教程

DataMiningTutorial

Author：SethPaulJamieMacLennanZhaohuiTangScottOveson

Abstract：MicrosoftSQLServer2005providesanintegratedenvironmentforcreating

andworkingwithdataminingmodels.Thistutorialusesfourscenarios,targetedmailing,forecasting,marketbasket,andsequenceclustering,todemonstratehowtousetheminingmodelalgorithms,miningmodelviewers,anddataminingtoolsthatareincludedinthisreleaseofSQLServer.

TheinformationcontainedinthisdocumentrepresentsthecurrentviewofMicrosoftCorporationontheissuesdiscussedasofthedateofpublication.BecauseMicrosoftmustrespondtochangingmarketconditions,itshouldnotbeinterpretedtobeacommitmentonthepartofMicrosoft,andMicrosoftcannotguaranteetheaccuracyofanyinformationpresentedafterthedateofpublication.

Thiswhitepaperisforinformationalpurposesonly.MICROSOFTMAKESNOWARRANTIES,EXPRESSORIMPLIED,ASTOTHEINFORMATIONINTHISDOCUMENT.

Complyingwithallapplicablecopyrightlawsistheresponsibilityoftheuser.Withoutlimitingtherightsundercopyright,nopartofthisdocumentmaybereproduced,storedinorintroducedintoaretrievalsystem,ortransmittedinanyformorbyanymeans(electronic,mechanical,photocopying,recording,orotherwise),orforanypurpose,withouttheexpresswrittenpermissionofMicrosoftCorporation.

Microsoftmayhavepatents,patentapplications,trademarks,copyrights,orotherintellectualpropertyrightscoveringsubjectmatterinthisdocument.ExceptasexpresslyprovidedinanywrittenlicenseagreementfromMicrosoft,thefurnishingofthisdocumentdoesnotgiveyouanylicensetothesepatents,trademarks,copyrights,orotherintellectualproperty.

2003MicrosoftCorporation.Allrightsreserved.

MicrosoftiseitheraregisteredtrademarkoratrademarkofMicrosoftCorporationintheUnitedStatesand/orothercountries.

Thenamesofactualcompaniesandproductsmentionedhereinmaybethetrademarksoftheirrespectiveowner

Introduction

ThedataminingtutorialisdesignedtowalkyouthroughtheprocessofcreatingdataminingmodelsinMicrosoftSQLServer2005.ThedataminingalgorithmsandtoolsinSQLServer2005makeiteasytobuildacomprehensivesolutionforavarietyofprojects,includingmarketbasketanalysis,forecastinganalysis,andtargetedmailinganalysis.Thescenariosforthesesolutionsareexplainedingreaterdetaillaterinthetutorial.

ThemostvisiblecomponentsinSQLServer2005aretheworkspacesthatyouusetocreateandworkwithdataminingmodels.Theonlineanalyticalprocessing(OLAP)anddataminingtoolsareconsolidatedintotwoworkingenvironments:BusinessIntelligenceDevelopmentStudioandSQLServerManagementStudio.UsingBusinessIntelligenceDevelopmentStudio,youcandevelopanAnalysisServicesprojectdisconnectedfromtheserver.Whentheprojectisready,youcandeployittotheserver.Youcanalsoworkdirectlyagainsttheserver.ThemainfunctionofSQLServerManagementStudioistomanagetheserver.Eachenvironmentisdescribedinmoredetaillaterinthisintroduction.Formoreinformationonchoosingbetweenthetwoenvironments,see"ChoosingBetweenSQLServerManagementStudioandBusinessIntelligenceDevelopmentStudio"inSQLServerBooksOnline.

Allofthedataminingtoolsexistinthedataminingeditor.Usingtheeditoryoucanmanageminingmodels,createnewmodels,viewmodels,comparemodels,andcreatepredictionsbasedonexistingmodels.

Afteryoubuildaminingmodel,youwillwanttoexploreit,lookingforinterestingpatternsandrules.Eachminingmodelviewerintheeditoriscustomizedtoexploremodelsbuiltwithaspecificalgorithm.Formoreinformationabouttheviewers,see"ViewingaDataMiningModel"inSQLServerBooksOnline.

Oftenyourprojectwillcontainseveralminingmodels,sobeforeyoucanuseamodeltocreatepredictions,youneedtobeabletodeterminewhichmodelisthemostaccurate.Forthisreason,theeditorcontainsamodelcomparisontoolcalledtheMiningAccuracyCharttab.Usingthistoolyoucancomparethepredictiveaccuracyofyourmodelsanddeterminethebestmodel.

Tocreatepredictions,youwillusetheDataMiningExtensions(DMX)language.DMXextendsSQL,containingcommandstocreate,modify,andpredictagainstminingmodels.FormoreinformationaboutDMX,see"DataMiningExtensions(DMX)Reference"inSQLServerBooksOnline.Becausecreatingapredictioncanbecomplicated,thedataminingeditorcontainsatoolcalledPredictionQueryBuilder,whichallowsyoutobuildqueriesusingagraphicalinterface.YoucanalsoviewtheDMXcodethatisgeneratedbythequerybuilder.

Justasimportantasthetoolsthatyouusetoworkwithandcreatedataminingmodelsarethemechanicsbywhichtheyarecreated.Thekeytocreatingaminingmodelisthedataminingalgorithm.Thealgorithmfindspatternsinthedatathatyoupassit,andittranslatesthemintoaminingmode—itistheenginebehindtheprocess.SQLServer2005includesninealgorithms:

MicrosoftDecisionTrees

MicrosoftClustering

MicrosoftNaiveBayes

MicrosoftSequenceClustering

MicrosoftTimeSeries

MicrosoftAssociation

MicrosoftNeuralNetwork

MicrosoftLinearRegression

MicrosoftLogisticRegression

Usingacombinationoftheseninealgorithms,youcancreatesolutionstocommonbusinessproblems.Thesealgorithmsaredescribedinmoredetaillaterinthistutorial.

Someofthemostimportantstepsincreatingadataminingsolutionareconsolidating,cleaning,andpreparingthedatatobeusedtocreatetheminingmodels.SQLServer2005includestheDataTransformationServices(DTS)workingenvironment,whichcontainstoolsthatyoucanusetoclean,validate,andprepareyourdata.FormoreinformationonusingDTSinconjunctionwithadataminingsolution,see"DTSDataMiningTasksandTransformations"inSQLServerBooksOnline.

InordertodemonstratetheSQLServerdataminingfeatures,thistutorialusesanewsampledatabasecalledAdventureWorksDW.ThedatabaseisincludedwithSQLServer2005,anditsupportsOLAPanddataminingfunctionality.Inordertomakethesampledatabaseavailable,youneedtoselectthesampledatabaseattheinstallationtimeinthe“Advanced”dialogforcomponentselection.

Theaudienceforthistutorialisbusinessanalysts,developers,anddatabaseadministratorswhohaveuseddataminingtoolsbeforeandarefamiliarwithdataminingconcepts.Ifyouarenewtodatamining,download"PreparingandMiningDatawithMicrosoftSQLServer2000andAnalysisServices"(/library/default.asp?url=/servers/books/sqlserver/mining.asp).

AdventureWorks

AdventureWorksDWisbasedonafictionalbicyclemanufacturingcompanynamedAdventureWorksCycles.AdventureWorksproducesanddistributesmetalandcompositebicyclestoNorthAmerican,European,andAsiancommercialmarkets.ThebaseofoperationsislocatedinBothell,Washingtonwith500employees,andseveralregionalsalesteamsarelocatedthroughouttheirmarketbase.

AdventureWorkssellsproductswholesaletospecialtyshopsandtoindividualsthroughtheInternet.Forthedataminingexercises,youwillworkwiththeAdventureWorksDWInternetsalestables,whichcontainrealisticpatternsthatworkwellfordataminingexercises.

FormoreinformationonAdventureWorksCyclessee"SampleDatabasesandBusinessScenarios"inSQLServerBooksOnline.

DatabaseDetails

TheInternetsalesschemacontainsinformationabout9,242customers.Thesecustomersliveinsixcountries,whicharecombinedintothreeregions:

NorthAmerica(83%)

Europe(12%)

Australia(7%)

Thedatabasecontainsdataforthreefiscalyears:2002,2003,and2004.

Theproductsinthedatabasearebrokendownbysubcategory,model,andproduct.

BusinessIntelligenceDevelopmentStudio

BusinessIntelligenceDevelopmentStudioisasetoftoolsdesignedforcreatingbusinessintelligenceprojects.BecauseBusinessIntelligenceDevelopmentStudiowascreatedasanIDEenvironmentinwhichyoucancreateacompletesolution,youworkdisconnectedfromtheserver.Youcanchangeyourdataminingobjectsasmuchasyouwant,butthechangesarenotreflectedontheserveruntilafteryoudeploytheproject.

WorkinginanIDEisbeneficialforthefollowingreasons:

YouhavepowerfulcustomizationtoolsavailabletoconfigureBusinessIntelligenceDevelopmentStudiotosuityourneeds.

YoucanintegrateyourAnalysisServicesprojectwithavarietyofotherbusinessintelligenceprojectsencapsulatingyourentiresolutionintoasingleview.

Fullsourcecontrolintegrationenablesyourentireteamtocollaborateincreatingacompletebusinessintelligencesolution.

TheAnalysisServicesprojectistheentrypointforabusinessintelligencesolution.AnAnalysisServicesprojectencapsulatesminingmodelsandOLAPcubes,alongwithsupplementalobjectsthatmakeuptheAnalysisServicesdatabase.FromBusinessIntelligenceDevelopmentStudio,youcancreateandeditAnalysisServicesobjectswithinaprojectanddeploytheprojecttotheappropriateAnalysisServicesserverorservers.

IfyouareworkingwithanexistingAnalysisServicesproject,youcanalsouseBusinessIntelligenceDevelopmentStudiotoworkconnectedtheserver.Inthisway,changesarereflecteddirectlyontheserverwithouthavingtodeploythesolution.

SQLServerManagementStudio

SQLServerManagementStudioisacollectionofadministrativeandscriptingtoolsforworkingwithMicrosoftSQLServercomponents.ThisworkspacediffersfromBusinessIntelligenceDevelopmentStudiointhatyouareworkinginaconnectedenvironmentwhereactionsarepropagatedtotheserverassoonasyousaveyourwork.

Afterthedatahasbeencleanedandpreparedfordatamining,mostofthetasksassociatedwithcreatingadataminingsolutionareperformedwithinBusinessIntelligenceDevelopmentStudio.UsingtheBusinessIntelligenceDevelopmentStudiotools,youdevelopandtestthedataminingsolution,usinganiterativeprocesstodeterminewhichmodelsworkbestforagivensituation.Whenthedeveloperissatisfiedwiththesolution,itisdeployedtoanAnalysisServicesserver.Fromthispoint,thefocusshiftsfromdevelopmenttomaintenanceanduse,andthusSQLServerManagementStudio.UsingSQLServerManagementStudio,youcanadministeryourdatabaseandperformsomeofthesamefunctionsasinBusinessIntelligenceDevelopmentStudio,suchasviewing,andcreatingpredictionsfromminingmodels.

DataTransformationServices

DataTransformationServices(DTS)comprisestheExtract,Transform,andLoad(ETL)toolsinSQLServer2005.Thesetoolscanbeusedtoperformsomeofthemostimportanttasksindatamining:cleaningandpreparingthedataformodelcreation.Indatamining,youtypicallyperformrepetitivedatatransformationstocleanthedatabeforeusingthedatatotrainaminingmodel.UsingthetasksandtransformationsinDTS,youcancombinedatapreparationandmodelcreationintoasingleDTSpackage.

DTSalsoprovidesDTSDesignertohelpyoueasilybuildandrunpackagescontainingallofthetasksandtransformations.UsingDTSDesigner,youcandeploythepackagestoaserverandrunthemonaregularlyscheduledbasis.Thisisusefulif,forexample,youcollectdataweeklydataandwanttoperformthesamecleaningtransformationseachtimeinanautomatedfashion.

YoucanworkwithaDataTransformationprojectandanAnalysisServicesprojecttogetheraspartofabusinessintelligencesolution,byaddingeachprojecttoasolutioninBusinessIntelligenceDevelopmentStudio.

MiningModelAlgorithms

Dataminingalgorithmsarethefoundationfromwhichminingmodelsarecreated.ThevarietyofalgorithmsincludedinSQLServer2005allowsyoutoperformmanytypesofanalysis.Formorespecificinformationaboutthealgorithmsandhowtheycanbeadjustedusingparameters,see"DataMiningAlgorithms"inSQLServerBooksOnline.

MicrosoftDecisionTrees

TheMicrosoftDecisionTreesalgorithmsupportsbothclassificationandregressionanditworkswellforpredictivemodeling.Usingthealgorithm,youcanpredictbothdiscreteandcontinuousattributes.

Inbuildingamodel,thealgorithmexamineshoweachinputattributeinthedatasetaffectstheresultofthepredictedattribute,andthenitusestheinputattributeswiththestrongestrelationshiptocreateaseriesofsplits,callednodes.Asnewnodesareaddedtothemodel,atreestructurebeginstoform.Thetopnodeofthetreedescribesthebreakdownofthepredictedattributeovertheoverallpopulation.Eachadditionalnodeiscreatedbasedonthedistributionofstatesofthepredictedattributeascomparedtotheinputattributes.Ifaninputattributeisseentocausethepredictedattributetofavoronestateoveranother,anewnodeisaddedtothemodel.Themodelcontinuestogrowuntilnoneoftheremainingattributescreateasplitthatprovidesanimprovedpredictionovertheexistingnode.Themodelseekstofindacombinationofattributesandtheirstatesthatcreatesadisproportionatedistributionofstatesinthepredictedattribute,thereforeallowingyoutopredicttheoutcomeofthepredictedattribute.

MicrosoftClustering

TheMicrosoftClusteringalgorithmusesiterativetechniquestogrouprecordsfromadatasetintoclusterscontainingsimilarcharacteristics.Usingtheseclusters,youcanexplorethedata,learningmoreabouttherelationshipsthatexist,whichmaynotbeeasytoderivelogicallythroughcasualobservation.Additionally,youcancreatepredictionsfromtheclusteringmodelcreatedbythealgorithm.Forexample,consideragroupofpeoplewholiveinthesameneighborhood,drivethesamekindofcar,eatthesamekindoffood,andbuyasimilarversionofaproduct.Thisisaclusterofdata.Anotherclustermayincludepeoplewhogotothesamerestaurants,havesimilarsalaries,andvacationtwiceayearoutsidethecountry.Observinghowtheseclustersaredistributed,youcanbetterunderstandhowtherecordsinadatasetinteract,aswellashowthatinteractionaffectstheoutcomeofapredictedattribute.

MicrosoftNaiveBayes

TheMicrosoftNaiveBayesalgorithmquicklybuildsminingmodelsthatcanbeusedforclassificationandprediction.Itcalculatesprobabilitiesforeachpossiblestateoftheinputattribute,giveneachstateofthepredictableattribute,whichcanlaterbeusedtopredictanoutcomeofthepredictedattributebasedontheknowninputattributes.Theprobabilitiesusedtogeneratethemodelarecalculatedandstoredduringtheprocessingofthecube.Thealgorithmsupportsonlydiscreteordiscretizedattributes,anditconsidersallinputattributestobeindependent.TheMicrosoftNaiveBayesalgorithmproducesasimpleminingmodelthatcanbeconsideredastartingpointinthedataminingprocess.Becausemostofthecalculationsusedincreatingthemodelaregeneratedduringcubeprocessing,resultsarereturnedquickly.Thismakesthemodelagoodoptionforexploringthedataandfordiscoveringhowvariousinputattributesaredistributedinthedifferentstatesofthepredictedattribute.

MicrosoftTimeSeries

TheMicrosoftTimeSeriesalgorithmcreatesmodelsthatcanbeusedtopredictcontinuousvariablesovertimefrombothOLAPandrelationaldatasources.Forexample,youcanusetheMicrosoftTimeSeriesalgorithmtopredictsalesandprofitsbasedonthehistoricaldatainacube.

Usingthealgorithm,youcanchooseoneormorevariablestopredict,buttheymustbecontinuous.Youcanhaveonlyonecaseseriesforeachmodel.Thecaseseriesidentifiesthelocationinaseries,suchasthedatewhenlookingatsalesoveralengthofseveralmonthsoryears.

Acasemaycontainasetofvariables(forexample,salesatdifferentstores).TheMicrosoftTimeSeriesalgorithmcanusecross-variablecorrelationsinitspredictions.Forexample,priorsalesatonestoremaybeusefulinpredictingcurrentsalesatanotherstore.

MicrosoftAssociation

TheMicrosoftAssociationalgorithmisspecificallydesignedforuseinmarketbasketanalyses.Thealgorithmconsiderseachattribute/valuepair(suchasproduct/bicycle)asanitem.Anitemsetisacombinationofitemsinasingletransaction.Thealgorithmscansthroughthedatasettryingtofinditemsetsthattendtoappearinmanytransactions.TheSUPPORTparameterdefineshowmanytransactionstheitemsetmustappearinbeforeitisconsideredsignificant.Forexample,afrequentitemsetmaycontain{Gender="Male”,MaritalStatus="Married",Age="30-35"}.Eachitemsethasasize,whichisnumberofitemsitcontains.Inthiscase,thesizeis3.

Oftenassociationmodelsworkagainstdatasetscontainingnestedtables,suchasacustomerlistfollowedbyanestedpurchasestable.Ifanestedtableexistsinthedataset,eachnestedkey(suchasaproductinthepurchasestable)isconsideredanitem.

TheMicrosoftAssociationalgorithmalsofindsrulesassociatedwithitemsets.AruleinanassociationmodellookslikeA,B=>C(associatedwithaprobabilityofoccurring),whereA,B,Careallfrequentitemsets.The'=>'impliesthatCispredictedbyAandB.Theprobabilitythresholdisaparameterthatdeterminestheminimumprobabilitybeforearulecanbeconsidered.Theprobabilityisalsocalled"confidence"indataminingliterature.

Associationmodelsarealsousefulforcrosssellorcollaborativefiltering.Forexample,youcanuseanassociationmodeltopredictitemsausermaywanttopurchasebasedonotheritemsintheirbasket.

MicrosoftSequenceClustering

TheMicrosoftSequenceClusteringalgorithmanalyzessequence-orienteddatathatcontainsdiscrete-valuedseries.Usuallythesequenceattributeintheseriesholdsasetofeventswithaspecificorder(suchasaclickpath).Byanalyzingthetransitionbetweenstatesofthesequence,thealgorithmcanpredictfuturestatesinrelatedsequences.

TheMicrosoftSequenceClusteringalgorithmisahybridofsequenceandclusteringalgorithms.Thealgorithmgroupsmultiplecaseswithsequenceattributesintosegmentsbasedonsimilaritiesofthesesequences.AtypicalusagescenarioforthisalgorithmisWebcustomeranalysisforaportalsite.AportalWebsitehasasetofaffiliateddomainssuchasNews,Weather,Money,Mail,andSport.EachWebcustomerisassociatedwithasequenceofWebclicksonthesedomains.TheMicrosoftSequenceClusteringalgorithmcangrouptheseWebcustomersintomore-or-lesshomogenousgroupsbasedontheirnavigationspatterns.Thesegroupscanthenbevisualized,providingadetailedunderstandingofhowcustomersareusingthesite.

MicrosoftNeuralNetwork

InMicrosoftSQLServer2005AnalysisServices,theMicrosoftNeuralNetworkalgorithmcreatesclassificationandregressionminingmodelsbyconstructingamultilayerperceptronnetworkofneurons.SimilartotheMicrosoftDecisionTreesalgorithmprovider,giveneachstateofthepredictableattribute,thealgorithmcalculatesprobabilitiesforeachpossiblestateoftheinputattribute.Thealgorithmproviderprocessestheentiresetofcases,iterativelycomparingthepredictedclassificationofthecaseswiththeknownactualclassificationofthecases.Theerrorsfromtheinitialclassificationofthefirstiterationoftheentiresetofcasesisfedbackintothenetwork,andusedtomodifythenetwork'sperformanceforthenextiteration,andsoon.Youcanlaterusetheseprobabilitiestopredictanoutcomeofthepredictedattribute,basedontheinputattributes.OneoftheprimarydifferencesbetweenthisalgorithmandtheMicrosoftDecisionTreesalgorithm,however,isthatitslearningprocessistooptimizenetworkparameterstowardminimizingtheerrorwhiletheMicrosoftDecisionTreesalgorithmsplitsrulesinordertomaximizeinformationgain.Thealgorithmsupportsthepredictionofbothdiscreteandcontinuousattributes.

MicrosoftLinearRegression

TheMicrosoftLinearRegressionalgorithmisaparticularconfigurationoftheMicrosoftDecisionTreesalgorithm,obtainedbydisablingsplits(thewholeregressionformulaisbuiltinasinglerootnode).Thealgorithmsupportsthepredictionofcontinuousattributes.

MicrosoftLogisticRegression

TheMicrosoftLogisticRegressionalgorithmisaparticularconfigurationoftheMicrosoftNeuralNetworkalgorithm,obtainedbyeliminatingthehiddenlayer.Thealgorithmsupportsthepredictionofbothdiscreteandcontinuousattributes.

WorkingThroughtheTutorial

ThroughoutthistutorialyouwillworkinBusinessIntelligenceDevelopmentStudio(asdepictedinFigure1).FormoreinformationaboutworkinginBusinessIntelligenceDevelopmentStudio,see"UsingSQLServerManagementStudio"inSQLServerBooksOnline.

Figure1BusinessIntelligenceStudio

Thetutorialisbrokenupintothreesections:PreparingtheSQLServerDatabase,PreparingtheAnalysisServicesDatabase,andBuildingandWorkingwiththeMiningModels.

PreparingtheSQLServerDatabase

TheAdventureWorksDWdatabase,whichisthebasisforthistu