《人工智能与数据挖掘教学课件》2.datawarehou

DataWarehouse,WhyDatawarehouse,Themostcommonissuecompaniesfacewhenlookingatdataminingisthattheinformationisnotinoneplace.Thebiggestchallengebusinessanalystsfaceinusingdataminingishowtoextract,integrate,cleanse,andpreparedatatosolvetheirmostpressingbusinessproblems.,WhatisDataWarehouse,Theideaofadatawarehouseistoputawiderangeofoperationaldatafrominternalandexternalsourcesintooneplacesoitcanbebetterutilizedbyexecutives,lineofbusinessmanagersandotherbusinessanalysts.Oncetheinformationisgathered,OLAP(on-lineanalyticalprocessing)softwarecomesintoplaybyprovidingthedesktopanalysistoolsforquerying,manipulatingandreportingthedatafromthedatawarehouse.,DataWarehouseenvironment,thesourcesystemsfromwhichdataisextractedthetoolsusedtoextractdataforloadingthedatawarehousethedatawarehousedatabaseitselfwherethedataisstoredthedesktopqueryandreportingtoolsusedfordecisionsupport,DataWarehousingProcessOverview,OperationalVs.MultidimensionalViewOfSales,CreatingADataWarehouse,TheDataWarehouse,TheDataWarehouseisanintegrated,subject-oriented,time-variant,non-volatiledatabasethatprovidessupportfordecisionmaking.,TheDataWarehouse,IntegratedTheDataWarehouseisacentralized,consolidateddatabasethatintegratesdataretrievedfromtheentireorganization.Subject-OrientedTheDataWarehousedataisarrangedandoptimizedtoprovideanswerstoquestionscomingfromdiversefunctionalareaswithinacompany.,TheDataWarehouse,TimeVariantTheWarehousedatarepresenttheflowofdatathroughtime.Itcanevencontainprojecteddata.Non-VolatileOncedataentertheDataWarehouse,theyareneverremoved.TheDataWarehouseisalwaysgrowing.,OperationalDatabasevs.Datawarehouse,OperationalDBSimilardatacanhavedifferentrepresentationsormeaningsFunctionalorprocessorientationCurrenttransactionFrequentupdating,DataWarehouseUnifiedviewofalldataelementsSubjectorientationfordecisionsupportHistoricalinformationwithtimedimensionDataareaddedwithoutchange,DataMart,Adatamartisasmall,single-subjectdatawarehousesubsetthatprovidesdecisionsupporttoasmallgroupofpeople.,DataMart,DataMartscanserveasatestvehicleforcompaniesexploringthepotentialbenefitsofDataWarehouses.DataMartsaddresslocalordepartmentalproblems,whileaDataWarehouseinvolvesacompany-wideefforttosupportdecisionmakingatalllevelsintheorganization.,EnterpriseDataWarehouse(EDW),AlargescaredatawarehousethatisusedacrosstheenterprisefordecisionsupportEDWareusedtoprovidedataformanytypesofDSS,includingCRM,SCM,BPM,BAM,PLM,andKMS.BPM:BusinessperformancemanagementBAM:BusinessactivitymonitoringPLM:productlifecyclemanagementKMS:Knowledgemanagementsystems,Metadata,Metadataisthedataaboutdata.Inadatawarehouse,metadatadescribethecontentsofadatawarehouseandthemannerofitsuseGoodmetadataisessentialtotheeffectiveoperationofadatawarehouseanditisusedindataacquisition/collection,datatransformation,anddataaccess.,TheneedsforTechnicalmetadata,Theuseofdatawarehousinganddecisionprocessingofteninvolvesawiderangeofdifferentproducts,andcreatingandmaintainingthemetadatafortheseproductsistime-consuminganderrorprone.Automatingthemetadatamanagementprocessandenablingthesharingofthisso-calledtechnicalmetadatabetweenproductscanreducebothcostsanderrors.,TheNeedsforBusinessmetadata,Businessusersneedtohaveagoodunderstandingofwhatinformationexistsinadatawarehouse.Theyneedtounderstandwhattheinformationmeansfromabusinessviewpoint,howitwasderived,fromwhatsourcesystemsitcomes,whenitwascreated,whatpre-builtreportsandanalysesexistformanipulatingtheinformation,andsoforth.,metadatainadatawarehouse,Kimballliststhefollowingtypesofmetadatainadatawarehouse:SourcesystemmetadataDatastagingmetadataDBMSmetadataRalphKimball,TheDataWarehouseLifecycleToolkit,Wiley,1998,ISBN0-471-25547-5,sourcesystemmetadata,sourcespecifications,suchasrepositories,andsourcelogicalschemassourcedescriptiveinformation,suchasownershipdescriptions,updatefrequenciesandaccessmethodsprocessinformation,suchasjobschedulesandextractioncode,datastagingmetadata,dataacquisitioninformation,suchasdatatransmissionschedulingandresults,andfileusagedimensiontablemanagement,suchasdefinitionsofdimensions,andsurrogatekeyassignmentstransformationandaggregation,suchasdataenhancementandmapping,DBMSloadscripts,andaggregatedefinitionsaudit,joblogsanddocumentation,suchasdatalineagerecords,datatransformlogs,StarSchema,Thestarschemaisadatamodelingtechniqueusedtomapmultidimensionaldecisionsupportintoarelationaldatabase.Starschemasyieldaneasilyimplementedmodelformultidimensionaldataanalysiswhilestillpreservingtherelationalstructureoftheoperationaldatabase.,StarSchema,FourComponents:FactsDimensionsAttributesAttributehierarchies,Figure13.14AThree-DimensionalViewofSales,Figure13.17AttributeHierarchiesinMultidimensionalAnalysis,Facts,NumericmeasurementsthatrepresentspecificbusinessaspectoractivityNormallystoredinfacttablethatiscenterofstarschemaFacttablecontainsfactslinkedthroughtheirdimensionsMetricsarefactscomputedatruntime,Dimensions,QualifyingcharacteristicsprovideadditionalperspectivestoagivenfactDecisionsupportdataalmostalwaysviewedinrelationtootherdataStudyfactsviadimensionsDimensionsstoredindimensiontables,Attributes,DimensionsprovidedescriptionsoffactsthroughtheirattributesNomathematicallimittothenumberofdimensionsUsetosearch,filter,andclassifyfactsSliceanddice:focusonslicesofthedatacubformoredetailedanalysis,AttributeHierarchies,Providetop-downdataorganizationTwopurpose:AggregationDrill-down/roll-updataanalysisDeterminehowthedataareextractedandrepresentedStoredinaDBMSsdatadictionaryUsedbyOLAPtooltoaccesswarehouseproperly.,StarSchema,Astarschemaconsistsoffacttablesanddimensiontables.Facttablescontainthequantitativeorfactualdataaboutabusiness-theinformationbeingqueried.Thisinformationisoftennumerical,additivemeasurementsandcanconsistofmanycolumnsandmillionsorbillionsofrows.Dimensiontablesareusuallysmallerandholddescriptivedatathatreflectsthedimensions,orattributes,ofabusiness.,Figure13.17StarSchemaForSales,StarSchemaRepresentation,Factsanddimensionsarenormallyrepresentedbyphysicaltablesinthedatawarehousedatabase.Thefacttableisrelatedtoeachdimensiontableinamany-to-one(M:1)relationship.Factanddimensiontablesarerelatedbyforeignkeysandaresubjecttotheprimary/foreignkeyconstraints.,Figure13.18OrdersStarSchema,StarSchema,Performance-ImprovingTechniquesNormalizationofdimensionaltablesMultiplefacttablesrepresentingdifferentaggregationlevelsDenormalizationoffacttablesTablepartitioningandreplication,Figure13.19NormalizedDimensionTables,MultipleFactTables,Practice,Howtodesignastarschemaforanautoinsurancecompanytodoriskanalysis?WhatistheObjective?WhataretheFacts?WhataretheDimensions?WhataretheAttributes?WhataretheAttributehierarchy?,AutoinsuranceDWstarschema,DataWarehouseDesign,GrainAdefinitionofthehighestlevelofdetailthatissupportedinadatawarehouseDrill-downTheprocessofprobingbeyondasummarizedvaluetoinvestigateeachofthedetailtransactionsthatcomprisethesummary,DataWarehouseImplementation,TheDataWarehouseasanActiveDecisionSupportNetworkACompany-WideEffortthatRequiresUserInvolvementandCommitmentatAllLevelsSatisfytheTrilogy:Data,Analysis,andUsersApplyDatabaseDesignProcedures,DataWarehouseImplementation,ImplementingadatawarehouseisgenerallyamassiveeffortthatmustbeplannedandexecutedaccordingtoestablishedmethodsTherearemanyfacetstotheprojectlifecycle,andnosinglepersoncanbeanexpertineacharea,DataWarehouseImplementationRoadMap,DataIntegrationandtheExtraction,Transformation,andLoad(ETL)Process,Dataintegrationcomprisesthreemajorprocesses:dataaccess(theabilitytoaccessandextractdatafromanydatasource)datafederation(theintegrationofbusinessviewsacrossmultipledatastores),andchangecapture(theidentification,capture,anddeliveryofthechangesmadetoenterprisedatasources).,DataIntegrationandtheExtraction,Transformation,andLoad(ETL)Process,Extraction,transformation,andload(ETL)Extraction-readingdatafromadatabaseTransformation-convertingtheextracteddatafromitspreviousformintotheformthatcanbeplacedintoadatawarehouseLoad-puttingthedataintothedatawarehouse,DataIntegrationandtheExtraction,Transformation,andLoad(ETL)Process,DataCleanse,Datacleansingordatascrubbingistheactofdetectingandcorrecting(orremoving)corruptorinaccuraterecordsfromarecordset,table,ordatabase.Usedmainlyindatabases,thetermreferstoidentifyingincomplete,incorrect,inaccurate,irrelevantetc.partsofthedataandthenreplacing,modifyingordeletingthisdirtydata.,ETLtools,AgoodETLtoolmustbeabletocommunicatewiththemanydifferentrelationaldatabasesandreadthevariousfileformatsusedthroughoutanorganization.ETLtoolshavestartedtomigrateintoEnterpriseApplicationIntegration,orevenEnterpriseServiceBus,systemsthatnowcovermuchmorethanjusttheextraction,transformationandloadingofdata.ManyETLvendorsnowhavedataprofiling,dataqualityandmetadatacapabilities.,On-LineAnalyticalProcessing,On-LineAnalyticalProcessing(OLAP)isanadvanceddataanalysisenvironmentthatsupportsdecisionmaking,businessmodeling,andoperationsresearchactivities.FourMainCharacteristicsofOLAPUsemultidimensionaldataanalysistechniques.Provideadvanceddatabasesupport.Provideeasy-to-useenduserinterfaces.Supportclient/serverarchitecture.,On-LineAnalyticalProcessing,AdditionalFunctionsofMultidimensionalDataAnalysisTechniquesAdvanceddatapresentationfunctionsAdvanceddataaggregation,consolidation,andclassificationfunctionsAdvancedcomputationalfunctionsAdvanceddatamodelingfunctions,IntegrationOfOLAPWithASpreadsheetProgram,Figure13.7OLAPServerArrangement,SAPsBusinessInformationWarehouse:anEnterprise-WideInformationHub,Anend-to-endenterprise-wideinformationhubtosupportplanninganddecision-making.AcentraldatarepositoryofSAP,non-SAP,current,andhistoricalbusinesstransactionsandmetadata.Timelyinformationtoalllevelsandroles,fromanalysttoexecutive.YearsofSAPfinancial,logistic,andhumanresourceinformationsystemsexperienceweddedwithmoderndatawarehousemethodologies.,ASampleOfCurrentDataWarehousingAndDataMiningVendors,Table13.10,SuccessStoriesatPepsi,Usingthedatawarehouse,wevebeenabletoidentifyimportantitems,findnationalsuppliersforthem,andleveragethoserelationshipstoreducecosts.“Thankstothewarehouse,Pepsicanmonitorpurchasingcomplianceattheuserlevel,anabilitythathasboostedpriceandproductcompliancewellover90percent.Thewarehousealsohelpsensure100percentsalestaxcompliance,saysBridgman.Sincegoingonlinein1995,thewarehousehashelpedgenerateprocurementsavingsinexcessof$100million.,LevelsofDWSupportforEnterpriseDecisionMaking,Theneedforreal-timedata,AbusinessoftencannotaffordtowaitawholedayforitsoperationaldatatoloadintothedatawarehouseforanalysisProvidesincrementalreal-timedatashowingeverystatechangeandalmostanalogouspatternsovertimeMaintainingmetadatainsyncispossibleLesscostlytodevelop,maintain,andsecureonehugedatawarehousesothatdataarecentralizedforBI/BAtoolsAnEAIwithreal-timedatacollectioncanreduceoreliminatethenightlybatchprocesses,Real-Time/ActiveDataWarehouse(RDW/ADW),Loadingandandprovidingdataviathedatawarehouseastheybecomeavailable.ExpandtraditionaldatawarehousefunctionsintotherealmoftacticaldecisionmakingEmpowerdecisionmakingwheninteractdirectlywithcustomersandsuppliers.,Real-TimeDataWarehousing,DataWarehouseAdministration,Duetoitshugesizeanditsintrinsicnature,