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

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 updataanalysisDeterminehowthedataareextractedandrepresentedStoredinaDBMS sdatadictionaryUsedbyOLAPtooltoaccesswarehouseproperly 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 SAP sBusinessInformationWarehouse anEnterprise WideInformationHub Anend to endenterprise wideinformationhubtosupportplanninganddecision making AcentraldatarepositoryofSAP non SAP current andhistoricalbusinesstransactionsandmetadata Timelyinformationtoalllevelsandroles fromanalysttoexecutive YearsofSAPfinancial logistic andhumanresourceinformationsystemsexperienceweddedwithmoderndatawarehousemethodologies ASampleOfCurrentDataWarehousingAndDataMiningVendors Table13 10 SuccessStoriesatPepsi Usingthedatawarehouse we vebeenabletoidentifyimportantitems 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 Duetoitshugesizeanditsintrinsicnat