Detect Anomalies in Excel Spreadsheets

12 Pages·0.188 MB·English

by  Crivat B., MacLennan J.

Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 1
AskAdvisor
Experts
Username Submit GotoArticle Doc # Go
Password AShdovipsorTips AdvancedSearch Search All PublicationsGo
EXPERTADVICE&KNOW-HOW Join:MemberCenter Login Advisor Jump to . . .
Customer
Help
ADVISORHome AdvisorZones AdvisorMagazines AdvisorLiveEvents CDs/DVDs AdvisorForums
ARTICLEACCESS = Youarehere: Advisor.com>Magazines>AccessAdvisor >DetectAnomaliesinExcelSpreadsheets
freeaccess
=subscriber-only
=hasdownload
ARTICLEINFO
ACCESSADVISOR
Webissue2004week
36
PrintissueOctober
2004 SQLSERVERDEVELOPMENT
Length6.25pages DetectAnomaliesin
Doc#14413
ExcelSpreadsheets
Filesforthisarticle
areonthisissue's UseSQLServer2005DataMining
ProfessionalResource insideExcel.
CD.
ByBogdanCrivatandJamieMacLennan,
FileDescription:
Exampletofind MicrosoftSQLServerDataMining
anomaliesin
spreadsheets. Noreadercommentsyet.
Clicktodownload
file
763,329bytes MicrosoftExcelbecomesmoreandmoreversatilewitheachreleaseand
solvesawidervarietyofbusinessneeds.Itsflexibilityand
programmabilityletyouintegratedifferenttechnologiestobetter
understandandprocessthedatainyourspreadsheets.Fromitsinception
ADVISORARTICLES inSQLServer2000,Microsoft'sdataminingsolutionhasprovideda
programmingmodeltoaccessdataminingtechnologies,whichhas
MoreonMicrosoftExcel
expandedwithSQLServer2005.Thisarticleshowsyouhowthesetwo
MoreonMicrosoftSQL technologiescanworktogetherseamlessly.
Server
MoreonDataIntegration Acommonproblemthat ACCESSADVISORAdvertisers
requiresadatamining FMS-SuiteSavingswithTotal.NETDeveloperSuite,
MoreonDatabase solutionisanomaly TanotdaTloVtisaulAaclDceesvseUloltpimeraStueiSteu,iTteotalAccessDeveloperSuite,
MoreonDatabase detection--thatis, FMS-TotalAccessEmailer--generatepersonalizedemail
Development findingthosevaluesthat foreveryoneinyourtableorquery.FreeTrial.
"donotfit"basedonthe FMS-TotalAccessMemo;TotalAccessSpeller;Total
MoreonDataMining patternspresentinthe AccFeMssSCo-TmoptoalnAecnctess.sAAllntharlyezeesru-p-tpaokretAycocuerssso2lu0t0io3n.stothe
restofthedata.You'll nextlevel;over200BestPracticesforcreatingAccess Compliance
seehowtosolvethis applications. SolutionsAdvisor
problemusingSQL FMS-TotalVisualCodeTools--simplifyVB/VBAcode Magazine
ADVISORNETWORK Server2005DataMining wriMtinicgr;oensfoofrtce-VaicsounasliSsttuednitos.eNtoEfTc2o0d0in3g--sbtaunilddaerndtse.rprise CSaormbpalniaensc-Oeixslneoyt,oPpattiroiontaAl!ct,
ACCESS ADVISOR fTohriasnsoElxucteiolwndoorkesshne'tet. Winteobraepaplitliycationswithlesscode,socanyouturnbigideas HWIhPaAtrAeaanlldymwourckhsm?ore...
Subscribetothemanager's
requireanyseparate ExtendedSystems -AdvantageDatabaseServer-- guidetocorporate
ATEDCVHISAORRCHITECT treatmentofthedata;it scaBlalbalcek,rMeloiasbhlea,nannodbnuSiyltsfotrepmersform-SaPnEcEeDFerret--aglobal csoomlutpioliannsc.estrategiesand
worksdirectlyinyour find-and-replaceutility ComplianceAdvisor.com
workbookafterafew SageKey-Buildandtroubleshootinstallations
DATABASED ADVISOR mouseclicks.The
codethat
DADEVVEISLOORPMENT aacrtcioclmeipsaanniEexsctehliasdd- ADVERTISEMENT SEexpcreerttssO--NfTohwe!Top
inyoucaninstallinMicrosoftExcelandusefordetectinganomaliesinany Seeexactlyhowtodoit,step-
MICROSOFT EXCHANGE Excelworksheet. by-step,inAdvisorAcademy
&OUTLOOKADVISOR CexDpsecrrtse.aCtelicdkbtyotsheeetowphatyou
canlearnrightnow.
Youdon'tneedanypreviousknowledgeofdataminingorprogrammingto AdvisorAcademy.com
MICROSOFT.NET usethesolutioninthisarticle.However,thelastpartofthearticle(the
ADVISOR "Add-indetails"section)containsdetailsaboutthesolution
implementation.TheExceladd-inisdevelopedwithVisualBasicfor
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 2
Applications(VBA)andusesDataMiningeXtensions(DMX)forSQL JointheMobile&
MICROSOFT OFFICE statementsinmodelingthedataanddetectingtheanomalies.Some WirelessRevolution
SYSTEMADVISOR knowledgeofthesetechnologiesmighthelpyouunderstandandfurther Readtheofficialguidetothe
nextwaveofbusinessand
extendthesolution.The"Add-indetails"sectionalsocontainsabrief lifestyle.Subscribenowto
MICROSOFT ADVISOR descriptionoftheDMXlanguageandthelocationofthespecification. kaercehpivueps,atoncdastccahnutph.e
MobileBusinessAdvisor.com
Requirements
MICROSOFT SQL
SERVERADVISOR
WetestedtheExceladd-inpresentedherewiththeSQLServer2005Beta NeedKnow-How?
CUSTOMERSERVICE 2release,whichMicrosoftshippedtomorethan200,000Microsoft As2005cranksup,what
directionareyougoing?
ShopAdvisor DeveloperNetwork(MSDN)subscribers.TheMicrosoftAnalysisServices Advisormagazinesare
AdvisorFAQ 2005server,partofwhichisMicrosoftDataMining,isincludedinthisbeta pnaecekdetodwwoitrhktshmeaanrtsewr.eCrasynou
Writing&Speaking release.Fortheadd-intowork,youhavetoinstalltheConnectivity youaffordtofallbehind?
ComponentsincludedwithSQLServer2005Beta2onthemachine. AdvisorStore.com
Theadd-inalsorequiresthatyouhavepermissionstocreateatemporary
fileontheC:\drive,althoughyoucanchangethecodetouseanyfolder FreeE-Newsletters
Keepup!HotNews,How-To,
nmlkjiADVISOR.com wheretheusershavepermissions. Tips&Tricks,ExpertAdvice,
andmore.Clicktorequest
nmlkjWeb your'sfree.
AdvisorUpdate.info
Search Theproblem
Real,meaningfuldatausuallycontainspatterns.Youcandescribethese InternetDomain
patternsintermsofrelationsbetweenvariouscolumnvalues.Anexample Management
is"IFthevalueoftheAgecolumnissmallerthan18THENthevalueof GettotalcontrolofyourWeb
ande-maildomainswitha
theOccupationcolumnISLIKELYTOBEStudent."Sometimesyoucan powerfulbrowsercontrol
detectasimpleruleeasilyjustbyvisuallyinspectingthedataorbyusing panel--andsavemoney!
Registeryourdomainswith
commonsense.Aspreadsheetentrywith10asthevalueoftheAge AdvisorDomains.com
columnandLawyerasthevalueoftheOccupationcolumnusuallyraises
eyebrowsandwillmostlikelybetreatedasananomaly.
ShowcaseYour
Ifthespreadsheethasasmallnumberofcolumns(wealsousetheterm Smarts
"attributes"forcolumns),datavisualizationtools(suchasthegraph Submityourtips,techniques
andadviceandletAdvisor
componentofExcel)arehelpful.Withscatterplots(suchastheX-Y promoteyourbusinessand
graphsinExcel),youcanusuallydetectsimplerelationsbetweentwoor buildyourcareer.Showthe
worldwhatyouknow!
threecolumnsjustbyvisuallyinspectingthespreadsheet. AdvisorTips.com
However,thecomplexityofthesepatternsgrowswiththedimensionality
ofthedata.Thatis,themorecolumnsaspreadsheethas,themore
complextherulesdescribingthepatterns.Intuitively,morecolumnsmean
therulestendtobemorecomplicated.Forexample:
"IF
thevalueoftheAgecolumnisgreaterthan21
BUTsmallerthan25AND
thevalueoftheCreditScorecolumnissmallerthan720
AND
thevalueoftheIncomecolumnisgreaterthan50000BUT
thevalueoftheNumberofChildrencolumnISNOT 0
THEN
thevalueoftheHomeOwnershipcolumnISLIKELYTOBE
Rent"
Now,such aruleisn'teasytofind.Agoodunderstandingofthedata
alwayshelps,butvisuallyinspectingafewthousandrowsin a
spreadsheetisadauntingtaskevenforapersonfamiliarwiththe
columns.
Theproblemisevenmorecomplexbecausetherelationsbetweentwo
columnsmaychangecompletelyfordifferentvaluesinathirdcolumn.For
example,therelationsbetweenthenumberofchildrenandthehome
ownershipstatuschangealotwithage.Forinstance,regardlessofthe
numberofchildren,peopleatthebeginningoftheircareersarelesslikely
toown ahomethanaseasonedprofessional.Therefore,youmust
considerallthepossiblevaluesof acolumnbeforeattemptingtousethat
columninarule.
Theanomalydetectionprocesscanonlystartwhenyou'vecompletely
determinedthesetofrules,anditrequiresreadingallthedataagainand
verifying,foreachrow,anyrulethatmightapply.
Theproblemtreatedinthisarticleisfindingthevaluesthatareanomalies
foraspecifiedcolumn(werefertothiscolumnas"thetargetcolumn")-
thatis,thosespreadsheetentriesthatdon'tabidebytherulesthat,in
general,relatethevaluesinthetargetcolumntothevaluesintheother
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 3
columns.Afteryouresolvethisproblem,thespreadsheetusercantake
actionandcleanthespreadsheetinvariousways,dependingonhis
purpose.Forexample,hecan:
Recheckthedatafortheentriesthatcontainanomalies
Eliminateentriesconsideredabnormal
Correcttheabnormalentriesbychangingtheanomalyvaluestothe
onessuggestedbytherules
Forthisarticle,thespreadsheetofinterestlookslikefigure1.The
spreadsheethassixcolumns:StudentID,Gender,ParentIncome,IQ
(intelligencequotient),ParentEncouragement,andCollegePlans.These
arethevaluesineachcolumn:
StudentIDcontainsauniqueidentifier(anumber)foreachentryin
thespreadsheet.Intermsofrelationaldatabases,thiscolumnisa
key:Itcanhelpyouconnectthisspreadsheettoanotherone
containingthenameandaddressforeachstudent,ortoone
containingtheexamgradesforeachcourseastudenthastaken.
Thevaluesinthiscolumndependonlyontheorderinwhichthe
studentinformationwasenteredinthespreadsheet.They'reinno
wayrelatedtotheothercolumns,soyoucanignorethiscolumn.
TheGendercolumncontainsdemographicinformationabouteach
student.ThevaluesareMaleandFemale.
TheParentIncomecolumncontainstheincomeinformationforthe
parentsofeachstudent.Itcontainsvaluesbetween5,000and
74,900.
TheIQcolumncontainstheintelligencequotientforeachstudent.
Thevaluesarebetween60and140.
TheParentEncouragementcolumndescribeswhethertheparents
encourageastudenttocontinuehiseducationthroughcollege.It
containsvaluesofEncouragedandNotEncouraged.
TheCollegePlanscolumnshowswhetherastudentintendstogoto
college.
Here'stheproblemwewanttosolve:Whoarethestudentswhosecollege
plansdon'tfittheirpotential?
Afirstguesswouldindicatethatstudentswith:
highIQ
parentalencouragement
andhigh-incomeparents
wouldplantotogotocollege.Thereforeanystudentwhodidn'tfollowthis
patternwouldbeananomaly.Butarethesetheonlyones?Arethere
othercasesthatdon'tfitthegeneralpattern?
Theproblembecomesevenmoredifficultwhentheuserofthe
spreadsheetdoesn'tfullyunderstandthedata.
HowDataMiningcanhelp
YoucanthinkofSQLServer2005DataMiningasasetoftechnologies
thatdealwithautomaticallydiscoveringmeaningindata,asopposedto
imperativetechnologiessuchasquerylanguages,wheretheuserexplicitly
asksforcertainpropertiesofthedata.Thisisn'treallyadefinition,but
thisexplanationdescribeshowweusedatamininginthisarticle.You'll
seehowdataminingcanhelpinfindingrulesandanomaliesinyour
spreadsheet.
Howcandataminingfindrulesinmy
spreadsheet?
ForMicrosoftSQLServer2005DataMining,dataisalwaysrepresentedas
asetofinputcases.Theseinputcasesshareasetofattributes.Generally,
eachcasehasavalueforeachattribute.However,forsomecases,certain
attributesmaybemissing.FortheExcelspreadsheet,eachrowisaninput
case,withcolumnvaluesactingasattributes.
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 4
In atypicalusagescenario,youfirsttrain aDataMiningenginefromthe
wholedataorjustasubset.Duringtraining,theenginelearnstherules
andpatternsinthesubset.Inasecondphase,youapplytherulesfor
variouspurposes,suchasdetectinganomaliesinyourspreadsheet.
Youcanreformulatetheproblemofdetectinganomaliesintermsofdata
miningtechnology:Youhavetofindtherulesandpatternsbehindthe
columnsinthespreadsheet.
Withtheserulesathand,foreachrowinthespreadsheet,youdecide
what'sthemostlikelyvaluefortheCollegePlanscolumnbasedonthe
valuesintheothercolumns.Inotherwords,foreachstudentwhoseIQ,
ParentIncome,andParentEncouragementareknown,youhavetodecide
whetherhe'slikelytocontinuewithcollegeeducation.Then,ifthelikely
collegeplansdon'tmatchtheactualcollegeplans,youtreatthatstudent
asananomalyfromthesetofdiscoveredrules.
Thiskindofproblem,intermsofdatamining,isaclassificationproblem.
You'reclassifyingeachrowbasedonthestudent'splanstogotocollege.
SQLServer2005DataMiningprovidesafewalgorithmsforsolving
classificationproblems.TheMicrosoft_Decision_Treesalgorithmisa
particularlygoodfitforyourspreadsheetproblembecauseit'sprovento
findgoodruleswithhighaccuracy,it'shighlyoptimizedforperformance,
anditdescribestherulesinanintuitiveform.
Foraspecificattribute,theMicrosoft_Decision_Treesalgorithmcan
determinethefactorsthatmostinfluencethevalueofthatcolumn.
Furthermore,it'sabletoclearlydescribetherelativeimportanceofthese
factors.
Let'sassume,fornow,thefactorsaffectingastudent'scollegeplansare
(inthedescendingorderofimportance)IQandtheparents'income.The
Microsoft_Decision_Treesalgorithmwillfindandorganizeruleslikethose
shownintable1.
Youcanthinkofthisstructureofrulesasatreebecauseallthestudents
arefirstdividedbasedonthemostimportantattribute(here,IQ).Then,
eachbranchisdividedagainbasedonthemostimportantfactorforthat
subsetofdata.Intable1,weassumedtheparents'incometobethe
mostimportantfactorforthosestudentswithhighIQ.
Here'sanexampleoftheformatof aruletheMicrosoft_Decision_Trees
algorithmdiscovers:
IF"IQ>=100"AND"Parent'sincome >20000"THEN(thestudent)
MOSTLIKELY"Planstoattend"
Howlikelyis"mostlikely"?
Beforemovingfurther,let'sseehowmuchyoucantrusttheserules.The
Microsoft_Decision_Treesalgorithmnevergeneratesrulesthataren't
reflectedinthedata.However,somerulesaremoreimportantthan
others,andsomerulesaretobetrustedmorethanothers.
Clearly,arulethatappliesto1,000studentsdeservesmoreconsideration
thanarulethatappliestoonlytwostudents.So,afirstmeasureof
confidenceisthesupport:thenumberofstudents(spreadsheetrows)for
whomtheruleapplies,or,indataminingterminology,thenumberof
casesthatsupportthisrule.
Now,let'stakeanotherlookattheruleabove.Whatdoesitmeanby
"MOSTLIKELYPlanstoattend"?Howlikelyis"MOSTLIKELY"?Let's
assume,inthecontextoftheruleabove,thatthesupportis100.This
meansthat,inthewholespreadsheet,wefound100studentswhohave
anIQgreaterthanorequalto100andparentswithincomegreaterthan
$20,000.Now,ifallthesestudentsplantoattendcollege,thisisastrong
rule;thereseemstobenoexception.However,thishardlyhappensinreal
life.Youusuallyendupwithsomethinglikethis:82ofthestudentsplan
toattendcollege,but17don'tplantoattendcollege,andonedidn't
mentionanyplans.
Now,youcandefinemostlikelycollegeplansasthoseplanssharedby
mostofthestudentswhomatchthisrule.Thisis"Planstoattend",with
82votes.Thelikelihoodofsuchplansis82outof100-82percent(or
0.82).Thisvalueistheconfidence(orprobability)oftherule.
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 5
Microsoft_Decision_Treesfindsboththesupportandtheconfidencefor
eachruleandprovidesaccesstothisinformationthroughtheSQL-like
DMXlanguage.
IntegrationwithExcel
MicrosoftDataMiningprovidesanenvironmentfordescribingthedatato
beminedandforintuitivelydisplayingtherulesandpatternsyoufound.
However,wewanttosolvethisproblemcompletelyinsideExcel.Microsoft
DataMiningalsocomeswithanextensiveprogrammabilitysolution,aset
oflibrariesthatsimplifythetaskofintegrationinvariousapplications.It's
thissetoflibrariesthatallowsseamlessintegrationwithExcelandother
applications.
Excelprovidesahandyfeaturecalledadd-ins.Anadd-inisalibrarythat
canextendtheworkbookfunctionality.TheDataMiningAnomaly
Detection.xlafileprovidedwiththisarticleissuchanadd-in.Toinstallit,
performthesesteps:
1.Open aworkbookinExcel(forexample,opentheCollegePlans.xlsfile
thatcomeswiththisarticle).
2.IntheToolsmenu,selectthe"Add-Ins…"menuitem.Aboxlabeled
Add-Insappears.
3.Inthatbox,lookforthe"Browse…"button.Afteryouclickonit,the
usualWindowsfileselectionboxcomesup.SelecttheDataMiningAnomaly
Detection.xlafilefromthelocationwhereyousavedit.
4.Anewboxmightshowup,withamessagelikethis:
"CopyDataMiningAnomalyDetection.xlatotheAdd-Insfolder
for<yourusername>?"
IfyouselectYes,Excelsavesacopyofthisadd-inintoaspecialAdd-Ins
folder.Otherwise,theadd-inonlyworksaslongasitsoriginallocationis
stillvalid.
5.Anewentryshowsupinthelistofadd-ins:DataMiningAnomaly
Detection.
6.AfteryouclosetheAdd-Insbox,ExceladdsanewentrytotheTools
menu,DataMiningAnomalyDetection.
Pleasenotethat,dependingonyourcurrentExcelsecuritysettings,this
proceduremightnotworkifyou'vedisabledmacros.Usually,anerror
messageindicatesthisproblem.YoucansolveitbyselectingTools>
Optionsand,intheSecuritypaneloftheresultingdialog,clickingonthe
"MacroSecurity…"button.Youseeanewdialogthatletsyouselectthe
securitylevelforrunningmacros.TheMediumlevelletsyouchoose
whethertoallowmacros;inparticular,youcanselectwhetheryouwantto
allowtheAnomalyDetectionadd-intorun.Afteradjustingsecurity
settings,youhavetore-addthedataminingadd-in.
Now,youcanusetheAnomalyDetectionadd-intofindproblemsinthe
CollegePlans.xlsspreadsheet.Youcanapplythesestepstoany
spreadsheet:
1.Selecttherangeofdatayouwanttoanalyze.
Inthespreadsheet,selectthecolumnsandrowsthatcontainthedatayou
wanttoanalyze.TheselectionmustincludethenamesoftheExcel
columnsinthefirstrow,anditmusthaveatleasttworows(atleastone
datarow,besidesthecolumnnames).Youdon'thavetoselectallthe
datainthespreadsheet,butthisishowIgottheresultsIdescribebelow.
Afteryouselecttherangeofdata,selectthenewentrybychoosingData
MiningAnomalyDetectionfromtheToolsmenu.Adialoglikefigure 2
displays.
2.Telltheadd-inwhattodo.
Thefirstinputofthisdialog(theonecontainingtherange"$A$1:$F$9001"
infigure2)letsyouchangeormakeaselection.Afteryoumakethe
selection,theadd-inpopulatesthetwodrop-downlistsforthekeycolumn
andanomalydetectioncolumn.
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 6
Ifyourspreadsheetcontainsakeycolumn,suchasarowidentifier,
indicatethisinthe"Selectthekeycolumn,ifany"fieldtoinstructthe
AnomalyDetectionadd-inthatnosignificantinformationisinthatcolumn.
Or,simplydon'tselectthatcolumnatallandleavethekeycolumnto
"<none>".Then,selectacolumntosearchforanomalies.Forthedataset
describedinthisarticle,selecttheCollegePlanscolumn.
MicrosoftDataMiningalsoletsyouseethereasonsbehindtheanomalies.
Ifyoudon'twantthis,deselecttheboxmarked"Clickonanomaliesto
showtheruletheybreak."Whenenabled,thisoptioncreatesahyperlink
insideeachExcelcellcontainingananomaly.Byclickingonthatcell,you'll
beabletoseetherulethatidentifiedthecellasananomaly.However,if
thecolumntosearchforanomaliesinyourparticularspreadsheetalready
containshyperlinks,leavethisboxunchecked,asthenewhyperlinkswill
removetheexistingones.
IfyouhaveMicrosoftAnalysisServices2005installedonyourmachine,
youcanexportthesetofrulesforyourspreadsheettoafile.Thisletsyou
importtherulesintoAnalysisServices2005andfurtherexplorethem.The
"WhatelsecanDataMiningdowithmydata?"sectiondescribesthe
proceduretofollow.Ifyoudon'thaveMicrosoftAnalysisServicesinstalled
ordon'twanttoexportyourdata,justleavethefilenameempty.
Afteryou'vemadealltheselections,clickontheOKbuttontoinstructthe
add-intostartlookingforrulesandanomalies.Thisprocesstookusabout
2to 3minutesforall9,000rowsoftheCollegePlans.xlsspreadsheet. A
smalldialogappearsandinformsyouofthestatusoftheoperation.
Aftertheanomaliesaredetected,youcanmoveontoinspecttheresults.
3.Inspecttheresults.
AfterExcelcompletestheanalysisanddetectstheanomalies,ithighlights
themonthespreadsheet.Thecellsdetectedasanomaliesareredand
havecommentsassociatedwiththem.Ifyouselectedthe"Clickon
anomaliesshowstheruletheybreak"option,thesecellsarealso
hyperlinks(thatis,clickingonthecellsshowstheruletheybreak).
Figure3showshowthespreadsheetlooksafteryouruntheAnomaly
Detectionadd-in.
Asyoucansee,eachanomalycellhasacommentnow,describingthe
expectedcollegeplansforthatparticularstudentandtheprobability
(confidence)oftherulethatfitsthestudent.
Finally,theadd-inaddsanewworksheettotheExcelworkbook
containingthoserulestheDataMiningadd-infoundwererelevantin
detectingtheanomalies.
Thenewlycreated"RulesfoundbyDataMining"worksheetlookslike
figure4.
Foreachrule,thefollowingcolumnsarepresent:
RuleDescription --Containstheverbosedescriptionoftherule.Aswe
discussedinthe"Howdataminingcanhelp"section,theconditionsin a
ruledescriptionareorderedbasedontheimportance.Noticethemost
importantfactorfordeterminingthecollegeplansof astudentisthe
encouragementhereceivesfromtheparents.
ConfidenceandSupport --Thesearemeasuresforthequalityofthe
rule,asmentionedbeforeinthe"Howlikelyis'mostlikely'?"section.
LikelyvalueforCollegePlans --Thisisthemostlikelyvalueforthe
CollegePlansofstudentsthatfitintothisrule.
Notethattherulesmaydifferalotdependingonthespreadsheetdata
you'reanalyzing.Forexample,ifyouonlyselectthefirstfewrows,you'll
likelyfindfewerrulesandeachrulewillhavefewerconditions.
WhatelsecanDataMiningdowithmydata?
MicrosoftDataMiningcandoalotwithyourdata.Ratherthandetecting
anomalies,thisadd-incansuggestthemostlikelyvaluewhenthe
informationismissing.Totrythis,justemptyafewcellsintheCollege
PlanscolumnandruntheAnomalyDetectionadd-inagain.Theadd-inwill
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 7
filltheemptycellswiththe"[Missingdata]"string,andthey'llhavea
commentwiththeirmostlikelyvalueandalinktoarulethatjustifiesthe
comment.
Withsmallchanges,youcanusetheadd-inforapurposeotherthan
detectinganomalies.Forinstance,youcanuseittopartitionthe
spreadsheetingroupsofrowswithcommoncharacteristics(aproblem
knowninDataMiningasclustering,orsegmentation).
Whilerunningtheadd-in,ifyouindicateafiletoexporttherulesto,you
canloadthesetofrulesonMicrosoftAnalysisServices2005.Todothis,
opentheSQLServerManagementStudioandconnecttoarunning
instanceofMicrosoftAnalysisServices2005.Inthecontextmenu
associatedwiththeDatabasesnodeoftheObjectExplorer,selectRestore
andindicatethenameofthefiletowhichyouexportedtherules.Also,
enteranameforthedatabasetocontainthelocalminingmodelyou're
restoringandgoaheadwiththeRestoreoperation.Asaresult,youcreate
anewdatabaseontheAnalysisServicesservercontainingthelocal
miningmodelbuiltonyourspreadsheet.Thismodeldoesn'tcontainthe
actualdata,onlythesetofrulesDataMiningdiscoveredwhileprocessing
theExcelspreadsheet.
Afterthedataisloadedontheserver,arichsetoftoolsisavailablefor
graphicallydisplayingtherules.
Figure5showshowtherulesdiscoveredinsidetheCollegePlans
spreadsheetdisplayinsidetheMicrosoftDecisionTreeViewer.Youcan
easilyfollowthewayDataMiningdiscoversrulesaswellasthe
importanceofvariousattributes(spreadsheetcolumns)indeterminingthe
outcome.Youcanalsoeasilyunderstandtheconfidenceandsupportfor
eachrule.
Ontheserverside,DataMininganalysiscanhandlelargevolumesofdata
bytakingadvantageofmultipleprocessors.Also,acollectionofvarious
DataMiningalgorithmscanhelpwithvariousbusinessproblems.
MicrosoftDataMiningcomeswithaquerylanguagesimilartoSQL.The
DMXquerylanguageletsyoumodeldata,trainalgorithms,andexecute
businessintelligenceoperations,suchasretrievingtherulesforthe
spreadsheetordeterminingthemostlikelyvaluesforvariousattributes.
ThenextsectionincludesafewexamplesoftheDMXsyntax.Italso
describeshowtheadd-inworksandshowsyoutheimplementation
details.
Add-indetails
MicrosoftDataMiningisdesignedasaplatformfordevelopingthevarious
applicationsthatcantakeadvantageoftheDataMiningtechnology.For
datawarehouseapplications,itcontainsapowerful,scalableserverthat
canhandlelargevolumesofdataandhelpmanyusers.Italsocontainsa
solutionforlightweight,embeddedDataMiningusage,suchasfinding
patternsinExcelspreadsheets.
Thisembeddedsolutioniscalled"localminingmodels"andisalibrarywith
manyofthemostcommonlyusedfunctionalitiesoftheDataMining
server.
Fortheserverandthelocalminingmodels,communicationwiththeData
MiningframeworkoccursviaanOLEDBproviderthatletsyousend
commandsintheSQL-likeDMXlanguageandreadresults.TheExceladd-
inusesthislocalservertoperformdataminingonthespreadsheetdata.
First,youhavetoinitializeaconnectiontotheOLEDBproviderfor
AnalysisServices.Intheconnectionstring,youcansubstitutetheData
Sourceproperty,whichisusuallyaservername,forafilename.When a
filenameisdetected,theproviderunderstandsthatit'ssupposedtoload
thelocalserver.InVBA,theADODBlibraryisagreatinstrumentfor
dealingwithOLEDBproviders.Theadd-inusesADODBforsendingDMX
requeststothelocalminingmodel.ThisistheVBAcodesnippetthat
opensanADODBconnectiontoalocalminingmodel,hostedin a
temporaryfileontherootdrive:
Privatem_cnASAsADODB.Connection
Setm_cnAS =NewADODB.Connection
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 8
m_cnAS.Open"Provider=MSOLAP.3;DataSource=c:\ExcelAddIn.cub"
NotethefollowingelementsspecifictotheOLEDBproviderforAnalysis
Services:
Theprovidersignatureis"MSOLAP.3".
Thedatasourceisafilenamebecausetheconnectioniscreated
againstalocalminingmodel.
Toperformtheanalysis,DataMiningmustfirstmodelthedata.Forthisto
happen,youmustcreateaminingmodelobject.Thisobjectwillbethe
containerofalltherulesandpatternsinthedatayouanalyze.Creating a
miningmodelissimilartocreatingatableinSQL.Here'stheDMX
statementthatcreatesthemodelassociatedwiththespreadsheet:
CREATEMININGMODEL__ExcelTemp(
[StudentID]TEXTKEY,
[Gender]TEXTDISCRETE,
[ParentIncome]DOUBLECONTINUOUS,
[IQ]DOUBLECONTINUOUS,
[ParentEncouragement]TEXTDISCRETE,
[CollegePlans]TEXTDISCRETEPREDICT
)USINGMicrosoft_Decision_Trees
Youobtainthenameofthemodelcolumnsfromthefirstlineofthe
selectiontowhichyouapplytheadd-in.Thetypeofthecolumnsis
inferredfromthevaluesinthesecondrowoftheselection(thefirstrowof
data).NotethePREDICTkeywordthatmarkstheCollegePlanscolumn;it
signifiesthemodelissupposedtofindrulesforthatcolumn.
Afteryoucreatethemodel,youhavetotrainit-thatis,feeditwithdata
tofindrules.Here'stheDMXstatement(again,similartotheSQLINSERT
statement)todothis:
INSERTINTO__ExcelTemp (
[Gender],
[ParentIncome],
[IQ],
[ParentEncouragement],
[CollegePlans])
@MySpreadsheet
TheAnalysisServicesOLEDBprovidersupportsparameters,suchas
@MySpreadsheet.Tobemorespecific,it'saparameterthat'sasetof
datarows.TheOLEDBprovidersupportsadatatableparameter.You
passthistypeofparameterintheformatdescribedbytheXMLfor
Analysis(XMLA)1.1specification,whichisavailableat http://
www.xmla.org.
Theadd-incontainssomecodethatreadsalltheselectionsandpacksit
intotheXMLAformat.ThiscodeisincludedintheXMLARowsetGenclass
module,whichisincludedintheplug-in.TheXMLARowsetGenclasssimply
serializeseachrowintheXMLformatdescribedbytheXMLA1.1
specification.TheXMLARowsetGenobjectthenreadstherowsonebyone.
TheGenerateRowsetmethodofthisclassmodulereturnsastring,which
containstheXMLserializationofalltherowsaddedsofar.
Here'sthecodethatattachesanXMLA1.1rowsetasaparametertothe
ADODBcommand:
'ExecuteTrainingcommand
DimcmdAsNewADODB.Command
cmd.ActiveConnection =m_cnAS
'TheINSERTINTODMXStatement
cmd.CommandText =strInsert
cmd.NamedParameters =True

DimparamAsADODB.Parameter
Setparam =cmd.CreateParameter

param.Name ="MySpreadsheet"
param.Type =adBSTR
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database Da... Page 9
param.Direction =adParamInput
param.Attributes =adParamLong
'TheXMLA11serializedrowset
param.value =m_xmla.GenerateRowset
Noteyouhavetonametheparameter.ThisisarequirementfortheOLE
DBforAnalysisServicesprovider.Also,theparameterisoftypeadBSTR
Then,thisstatementissenttothelocalDataMiningserver,which
processestheminingmodel.
Withtheprocessedmodel,youcanstartpredictingthemostlikelyvalue
fortheCollegePlanscolumn.YoucanuseastatementsimilartoSQL
SELECTwithJOIN:
SELECT
Predict(__ExcelTemp.[CollegePlans],EXCLUDE_NULL),
PredictProbability(__ExcelTemp.[CollegePlans]),
PredictNodeId(__ExcelTemp.[CollegePlans])
FROM
__ExcelTemp
NATURALPREDICTIONJOIN
@MySpreadsheetas__Input
MySpreadsheethasthesamemeaningasabove-i.e.,aparameterthat
containstheselectioninwhichyou'relookingforanomalies.
We'llanalyzethesemanticsofthispredictionstatement,asit'simportant
forunderstandinghowMicrosoftDataMiningworks.
The__ExcelTemplocalminingmodel,createdwiththeCREATEMINING
MODEL…DMXstatement,containsanumberofcolumns,matchingthe
columnsinthespreadsheet.The@MySpreadsheettableinputparameter
alsocontainsthecolumnsinthespreadsheet.
TheNATURALPREDICTIONJOINpartofthepredictionstatementindicates
thelocalminingmodeltomapeachcaseintheinputtabletothecolumns
ofthelocalminingmodelbasedonthenamesofthecolumns.Ifthe
columnsintheinputtablehavedifferentnamesfromtheonesinthe
miningmodel,youhavetospecifythemappingsexplicitlywithasyntax
similartoSQLJOIN:
ON
[MiningModelColumn] =[InputColumn]
Thestatement,translatedtoplainEnglish,is:
"Foreachrowinthe@MySpreadsheetinputtable,usingtherules
detectedwhileyoutrainedtheExcelTempminingmodelcompute:
Thepredictedvalueforthe[CollegePlans]column,excludingnull
values
Theprobabilityofthisprediction
Thenodeidentifieroftherulethatgovernsthisprediction
When:
The[StudentId]columnoftheminingmodeltakesthevalueofthe
[StudentId]columnoftheinputtable
The[Gender]columnoftheminingmodeltakesthevalueofthe
[Gender]columnoftheinputtable
The[ParentIncome]columnoftheminingmodeltakesthevalueof
the[ParentIncome]columnoftheinputtable
Andsoon"
Thepredictedvalueisthemostlikelyvalueforthe[CollegePlans]
column,accordingtotherulesthatapplytothecurrentrowintheinput
table.Theprobabilityofthepredictionistheconfidenceoftherule.The
add-inusesthenodeidentifieroftherulelatertofetchthedescription,
support,andconfidencefortherespectiverulefromtheDataMininglocal
server.
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34
Access Advisor :: Detect Anomalies in Excel Spreadsheets -- Microsoft Excel Microsoft SQL Server Data Integration Database D... Page 10
Foreachlineintheinputtable,thestatementcomputesthreevalues;
therefore,theresultisatablewiththreecolumnsforeachrowinthe
inputtable.
ButMySpreadsheetcontainsallthecurrentselection.Thismeanseachrow
intheresponsecontainsthethreenamedcolumnsforonerowinthe
selection.
Aftertheadd-infetchesthisresult,itwalkstheselectionrowbyrow.If
thevalueoftheCollegePlanscolumndiffersfromthemostlikelyvalue
theDataMiningserverreturned,therespectiverowismarkedas
containingananomaly.
ThelastDMXstatementtheadd-inissuesexportsthelocalminingmodel
intoafileyoucanreuseinAnalysisServices2005.Thestatementis:
EXPORTMININGMODEL__ExcelTempTO
'c:\MySpreadsheetRulesFile.abf'
Thisstatementcreatesanewfileontheharddisk,named
c:\MySpreadsheetRulesFile.abf,whichcontainsanAnalysisServices2005
databasewithasingleminingmodel.Youcanlaterrestorethisfileas a
databaseorimportitintoanexistingdatabasewithanIMPORT
statement:
IMPORTFROM'c:\MySpreadSheetRulesFile.abf'
TheDMXlanguageisprettypowerfulandnothardtocomprehendfor
someonefamiliarwithSQLsyntax.ThedetailedspecificationforDMXis
alsoincludedinthe"OLEDBforDataMining"specification,whichyoucan
findontheMicrosoftWebsiteat http://www.microsoft.com/downloads/
details.aspx?FamilyID=c66af00d-51be-4d8d-9056-
82cb2410ae3f&displaylang=en .
SimplybyusingadifferentalgorithmnameintheCREATEMININGMODEL
statementandchangingsomeofthefunctionsintheprediction
statement,youcanmodifytheadd-intosolveotherbusinessproblems.
Ifthedatasourceisn'tanExcelspreadsheetanddoesn'tsupportanadd-
indevelopmentlanguagesuchasVBA,youcansolvetheanomaly
detectionprobleminsidetheAnalysisServices2005server(althoughwith
afewmoreclicksthanrequiredtoruntheadd-in).
Poweratyourfingertips
MicrosoftSQLServer2005DataMininggivesyoualotofversatilityin
findingmeaninginyourdata.Usethisadd-inwithyourspreadsheetsor,
evenbetter,playwiththeadd-incodetofurtherexplorewhatlocalmining
modelscandoforyou.Youcanchangethealgorithmfrom
Microsoft_Decision_TreestoMicrosoft_Clustering.Theadd-incontinuesto
workasitdidbefore,butbyaddingafewmoreDMXstatements,youcan
seehowyourdataispartitionedandareceiveadetaileddescriptionfor
eachofthesepartitions.
Inthepast,datamininghasbeen afieldofacademicsandhigh-end
researchersandanalysts.Thesimplicityandeaseofuseofthenew
MicrosoftSQLServer2005DataMiningplatformbringthispowerful
technologytoeverybody'sfingertips.
Figure1: Example --TheCollegePlans
spreadsheet.
http://accessvbsqladvisor.com/doc/14413 09/03/2005 17:24:34