会计数据分析 课件 第2章　数据的准备和清理

DataPreparation

andCleaningChapter2Wherewearenow1.DataAnalytics2.DataPreparationandCleaning3.ModelingandEvaluation4.Visualization5.TheModernAudit6.AuditAnalytics7.KeyPerformanceIndicators8.FinancialStatementAnalyticsObjectivesLO2-1Howaredatausedandstoredintheaccountingcycle?LO2-2Howaredatastoredinrelationaldatabases?LO2-3Whatdoesitmeantoextract,transform,andload?IntheIMPACTcycle,we’regoingtolookatMasteringtheData.IdentifythequestionsMasterthedataPerformtestplanAddressandrefineresultsCommunicateinsightsTrackoutcomesExhibit1-1TheIMPACTCycleHowaredatausedandstoredintheaccountingcycle?LO2-1Understandthedatabylookingathowitisorganized.Datacanbefoundthroughoutvarioussystems.Inmostcases,youneedtoknowwhichtablesandattributescontaintherelevantdata.UnifiedModelingLanguage(UML)isonewaytounderstanddatabases.FGI_ProductProduct_Code[PK]Product_Description…Sales_SubsetSales_Order_ID[PK]Product_Code[FK]Customer_ID[FK]…CustomerCustomer_ID[PK]Customer_Name…Howaredatastoredinrelationaldatabases?LO2-2Relationaldatabasesensurethatdata:Arecomplete,orincludealldata.Aren’tredundant,sotheydon’ttakeuptoomuchspace.Followbusinessrulesandinternalcontrols.Aidcommunicationandintegrationofbusinessprocesses.Therearefourtypesofattributes.Primarykeysareuniqueidentifiers.Foreignkeysareattributesthatpointtoaprimarykeyinanothertable.Compositekeysareacombinationoftwoforeignkeysusedforlineitems.Descriptiveattributesincludeeverythingelse.SupplierTableSupplierIDSupplierNameSupplierAddressSupplierType1NorthernBreweryHomebrewSupply6021LyndaleAveS12HopsDirectLLC686GreenValleyRoad13TheHomeBrewery455E.TownshipSt.14ThePayrollCompany408N.WaltonBlvd2Examplesoftwotables,attributes,anddata.NoticethePK-FKrelationship.PurchaseOrderTablePONo.DateCreatedByApprovedBySupplierID(FK)178711/1/2017100110101178811/1/2017100510102178911/8/2017100210101179011/15/2017100510101SupplierTableSupplierID(PK)SupplierNameSupplierAddressSupplierType1NorthernBreweryHomebrewSupply6021LyndaleAveS12HopsDirectLLC686GreenValleyRoad13TheHomeBrewery455E.TownshipSt.14ThePayrollCompany408N.WaltonBlvd2Datadictionariesdefinewhatdataareacceptable.Foreachattribute,welearn:Whattypeofkeyitis.Whatdataarerequired.Whatdatacanbestoredinit.Howmuchdataisstored.SupplierTableDataDictionaryPrimaryorForeignKey?RequiredAttributeNameDescriptionDataTypeDefaultValueFieldSizeNotesPKYSupplierIDUniqueIdentifierforeachSupplier

Numbern/a10

NSupplierNameFirstandLastNameShortTextn/a30

FKNSupplierTypeTypeCodeforDifferentSupplierCategories

NumberNull101:Vendor2:MiscQ.Whatisthepurposeoftheprimarykey?Aforeignkey?Anon-keyattribute?Whatdoesitmeantoextract,transform,andload?LO2-2TheRequestingdataisaniterativepracticeinvolving5steps:Step1:Determinethepurposeandscopeofthedatarequest.Step2:Obtainthedata.Step3:Validatethedataforcompletenessandintegrity.Step4:Cleanthedata.Step5:Loadthedatafordataanalysis.Step1:DeterminethepurposeandscopeofthedatarequestAskafewquestionsbeforebeginningtheprocess:Whatisthepurposeofthedatarequest?Whatdoyouneedthedatatosolve?Whatbusinessproblemwillitaddress?Whatriskexistsindataintegrity(e.g.,reliability,usefulness)?Whatisthemitigationplan?Whatotherinformationwillimpactthenature,timing,andextentofthedataanalysis?Step2:ObtainthedataHowwilldataberequestedand/orobtained?Doyouhaveaccesstothedatayourself,ordoyouneedtorequestadatabaseadministratorortheinformationsystemsdepartmenttoprovidethedataforyou?Ifyouneedtorequestthedata,isthereastandarddatarequestformthatyoushoulduse?Fromwhomdoyourequestthedata?Wherearethedatalocatedinthefinancialorotherrelatedsystems?Whatspecificdataareneeded(tablesandfields)?Whattoolswillbeusedtoperformdataanalytictestsorproceduresandwhy?Step2:ObtainthedataThereareacoupleoptions:ObtaindatathroughadatarequesttotheITdepartment.Obtaindatayourself.ExampleStandardDataRequestFormSECTION1:REQUESTDETAILSRequestorName:RequestorContact

Number:RequestorEmailAddress:Pleaseprovideadescriptionoftheinformationneeded(indicatewhichtablesandwhichfieldsyourequire):Whatwilltheinformationbeusedfor?Frequency(circleone)One-OffAnnuallyTermlyOther:___________Formatyouwishthedatatobedeliveredin(circleone):Spreadsheet

WordDocumentTextFile

Other:____________RequestDate:RequiredDate:IntendedAudience:Customer(ifnotrequestor):ExampleStandardDataRequestFormSECTION2:TOBECOMPLETEDBYINFORMATIONSYSTEMSDEPARTMENTRequestNumberDateReceivedReceivedbyAssignedtoInitialreviewcomments

(discussionwithclient—revisionsrequired?agreementtoproceed?etc.)Workinprogresscomments

(additionalnotesandcommentsduringproductionofdata)SECTION3:COMPLETIONDETAILSDateCompleted

DateProvidedRevisionsRequiredFeedbackfromclient

(ifapplicable)ObtainthedatayourselfIfyouhavedirectaccesstoadatawarehouse,youcanuseSQLandothertoolstopullthedatayourself.Identifythetablesthatcontaintheinformationyouneed.Youcandothisbylookingthroughthedatadictionaryortherelationshipmodel.Identifywhichattributes,specifically,holdtheinformationyouneedineachtable.Identifyhowthosetablesarerelatedtoeachother.Step3:ValidatethedataforcompletenessandintegrityChancesarethedatayourequestisn’tcomplete.Beforeyoubegin,doalittleworktomakesureyourdataarevalid:ComparethenumberofrecordsComparedescriptivestatisticsfornumericfieldsValidateDate/TimefieldsComparestringlimitsfortextfieldsStep4:CleanthedataOnceyouhavevaliddata,thereisstillsomeworkthatneedstobedonetomakesureitisconsistentandreadyforanalysis:RemoveheadingsorsubtotalsCleanleadingzeroesandnonprintablecharactersFormatnegativenumbersCorrectinconsistenciesacrossdata,ingeneralStep5:LoadthedatafordataanalysisFinally,youcannowimportyourdataintothetoolofyourchoiceandexpectthefunctionstoworkproperly.Q.Whatarefourcommonissueswithdatathatmustbefixedbeforeanalysiscantakeplace?SummaryThefirststepintheIMPACTcycleistoidentifythequestionsthatyouintendtoanswerthroughyourdataanalysisproject.Onceadataanalysisproblemorquestionhasbeenidentified,thenextstepintheIMPACTcycleismasteringthedata,whichcanbebrokendowntomeanobtainingthedataneededandpreparingitforanalysis.Inordertoobtaintherightdata,itisimportanttohaveafirmgraspofwhatdataareavailabletoyouandhowthatinformationisstored.Dataareoftenstoredinarelationaldatabase,whichhelpstoensurethatanorganization’sdataarecompleteandtoavoidredundancy.Relationaldatabasesaremadeupoftableswithuniquelyidentifiedrecords(thisisdonethroughprimarykeys)andarerelatedthroughtheusageofforeignkeys.Toobtainthedata,youwilleitherhaveaccesstoextractthedatayourselforyouwillneedtorequestthedatafromadatabaseadministratorortheinformationsystemsteam.Ifthelatteristhecase,youwillcompleteadatarequestform,indicatingexactlywhichdatayouneedandwhy.Onceyouhavethedata,theywillneedtobevalidatedforcompletenessandintegrity—thatis,youwill