數(shù)據(jù)倉(cāng)庫(kù)與數(shù)據(jù)挖掘-雙語(yǔ)第6章-dw introduction

DataWarehouseand

DataMiningYangYan2023/1/26HD-ITR2CourseOutlineDatawarehouseIntroductionDatamodelDataStorageOperatingAlgorithmsQueryProcessingandOptimizationFromdatawarehousingtodataminingDataminingIntroductionMiningAssociationRulesClassificationClusterAnalysisMiningComplexTypesofData2023/1/26HD-ITR3

Part2

DataWarehouse2023/1/26HD-ITR4ContentsDatawarehouseIntroductionDatamodelDataStorageandindexingOperatingAlgorithmsQueryProcessingandOptimizationFromdatawarehousingtodatamining2023/1/26HD-ITR5Chapter1Introduction6.1Fromdatabasetodatawarehouse6.2Whatisadatawarehouse6.3DBMSvs.DWMS6.4Datawarehousearchitectureandproblems6.5Somerelatedconcepts

6.1Fromdatabasetodatawarehouse2023/1/26HD-ITR66.1FromdatabasetodatawarehouseWhydatawarehouseDatabase:FortransactionprocessingIttriestodoanalyticalprocessing.Buttransactionprocessingandanalyticalprocessingareconflictwitheachother.2023/1/26HD-ITR7Whydatawarehouse“spiderweb”problemToavoidconflictamongdepartmentsorusersinacorporationandsimplifydataviewofusers,aextractionprogramisappliedwidely.Exactionwithoutanycontrolresultsinacomplicatednet,whichiscalled“spiderweb”.Thebiggerthecorporation,theseriousthespiderwebproblem.Eventhedataintwodifferentnodescomesfromthesamedatabase,theyhavedifferentextractingtime,differentextractingalgorithms,differentlevelofdataandrefertodifferentexternaldata.Thus,forthesamequestion,differentnodeswillgivedifferentanswers.Howaboutthedecisionmaker?6.1Fromdatabasetodatawarehouse2023/1/26HD-ITR8SeparatinganalyticalprocessingsystemfromtransactionprocessingsystemtransactionprocessingsystemDaytodayoperationbasedondatabaseForexample,bankdatabaseUsers:operatorsDataprocessed:detaileddataPurpose:operationofcorporationanalyticalprocessingsystem

AnalysisofdataforassociationandrulesForexample,analyzingtheemploymentofgraduatestudentstoplantherecruit.Users:managersorengineersofdataanalysisDataprocessed:summarizeddataPurpose:supportdecisionmaking6.1FromdatabasetodatawarehouseDatawarehouseisforanalyticalprocessing2023/1/26HD-ITR9DataprocessingisdividedintotwotypesOLTPHigherfrequencyofdataaccessingandshortertimeforeachoperation

----databasesystemOLAPADSSapplicationprogrammayrunseveralhoursandconsumelotsofresource.

----datawarehousesystem6.1Fromdatabasetodatawarehouse2023/1/26HD-ITR10Chapter1Introduction6.1Fromdatabasetodatawarehouse6.2Whatisadatawarehouse6.3DBMSvs.DWMS6.4Datawarehousearchitectureandproblems6.5Somerelatedconcepts

6.2Whatisadatawarehouse2023/1/26HD-ITR11Whatisadatawarehouse

“Adatawarehouseisasubject-oriented,

integrated,time-variant,andnonvolatilecollectionofdatainsupportofmanagement’sdecision-makingprocess.”

－－W.H.Inmon.19926.2Whatisadatawarehouse2023/1/26HD-ITR12DataWarehousePropertiesSubjectOrientedIntegratedTimeVariantNonVolatileDataWarehouse2023/1/26HD-ITR13FourpropertiesofdatawarehouseSubject-OrientedOrganizedaroundmajorsubjects,suchascustomer,product,sales.Focusingonthemodelingandanalysisofdatafordecisionmakers,notondailyoperationsortransactionprocessing.Provideasimpleandconciseviewaroundparticularsubjectissuesbyexcludingdatathatarenotusefulinthedecisionsupportprocess.6.2Whatisadatawarehouse2023/1/26HD-ITR14

OperationalSystemsSavingsSharesLoansInsuranceEquityPlansCustomerFinancialInformationDataWarehouseSubjectArea2023/1/26HD-ITR156.2Whatisadatawarehouse2023/1/26HD-ITR16FourpropertiesofdatawarehouseIntegratedConstructedbyintegratingmultiple,heterogeneousdatasourcesrelationaldatabases,flatfiles,on-linetransactionrecordsDatacleaninganddataintegrationtechniquesareapplied.Ensureconsistencyinnamingconventions,encodingstructures,attributemeasures,etc.amongdifferentdatasourcesE.g.,Hotelprice:currency,tax,breakfastcovered,etc.Whendataismovedtothewarehouse,itisconverted.6.2Whatisadatawarehouse2023/1/26HD-ITR17JJones女1945年7月20日J(rèn)Jones去年有兩張罰單一次大事故……人壽保險(xiǎn)汽車保險(xiǎn)JJonesMain大街123號(hào)已婚……房產(chǎn)保險(xiǎn)JJones兩個(gè)孩子高血壓……健康保險(xiǎn)JJones女1945年7月20日出生去年有兩張罰單一次大事故Main大街123號(hào)已婚兩個(gè)孩子高血壓……顧客2023/1/26HD-ITR186.2WhatisadatawarehouseFourpropertiesofdatawarehouseTime-VariantThetimehorizonforthedatawarehouseissignificantlylongerthanthatofoperationalsystems.Operationaldatabase:currentvaluedata.Datawarehousedata:provideinformationfromahistoricalperspective(e.g.,past5-10years)EverykeystructureinthedatawarehouseContainsanelementoftime,explicitlyorimplicitlyButthekeyofoperationaldatamayormaynotcontain“timeelement”.Time-variantHistoricaldatashouldbeappendedwiththetimepassed.Olddatamaybedeleted.Integrateddatashouldbechangedfortheabovetworeasons.

2023/1/26HD-ITR196.2WhatisadatawarehouseDataTime01/0902/0903/09DataforJanuaryDataforFebruaryDataforMarchDataWarehouse2023/1/26HD-ITR206.2WhatisadatawarehouseFourpropertiesofdatawarehouseNon-VolatileAphysicallyseparatestoreofdatatransformedfromtheoperationalenvironment.Operationalupdateofdatadoesnotoccurinthedatawarehouseenvironment.Doesnotrequiretransactionprocessing,recovery,andconcurrencycontrolmechanismsRequiresonlytwooperationsindataaccessing:initialloadingofdataandaccessofdata.2023/1/26HD-ITR21Typicallydatainthedatawarehouseisnotupdatedordeleted.ReadLoadINSERTReadUPDATEDELETEOperationalDatabasesWarehouseDatabase2023/1/26HD-ITR22ChangingDataOperationalDatabasesWarehouseDatabaseFirsttimeloadRefreshRefreshRefreshPurgeorArchive2023/1/26HD-ITR236.2WhatisadatawarehouseOtherpropertiesofdatawarehouselargeamountofdata10GB,…TBManagingitsdatawithrelationaldatabasemanagementsystem.Fewusers.2023/1/26HD-ITR24Chapter6Introduction6.1Fromdatabasetodatawarehouse6.2Whatisadatawarehouse6.3DBMSvs.DWMS6.4Datawarehousearchitectureandproblems6.5Somerelatedconcepts

6.3DBMSvs.DWMS2023/1/26HD-ITR256.3DBMSvs.DWMS

OLTPOn-LineTransactionProcessingMajortaskoftraditionalrelationalDBMSDay-to-dayoperations:purchasing,inventory,banking,manufacturing,payroll,registration,accounting,etc.2023/1/26HD-ITR266.3DBMSvs.DWMS

OLAPOn-LineAnalytical

ProcessingMajortaskofdatawarehousesystemDataanalysisanddecisionmaking2023/1/26HD-ITR27Distinctfeatures(OLTPvs.OLAP):Userandsystemorientation:customervs.marketDatacontents:current,detailedvs.historical,consolidatedDatabasedesign:ER+applicationvs.star+subjectView:current,localvs.evolutionary,integratedAccesspatterns:updatevs.read-onlybutcomplexqueries6.3DBMSvs.DWMS

2023/1/26HD-ITR28DataWarehousevs.HeterogeneousDBMSTraditionalheterogeneousDBintegration:Buildwrappers/mediatorsontopofheterogeneousdatabasesQuerydrivenapproachWhenaqueryisposedtoaclientsite,ameta-dictionaryisusedtotranslatethequeryintoqueriesappropriateforindividualheterogeneoussitesinvolved,andtheresultsareintegratedintoaglobalanswersetComplexinformationfiltering,competeforresourcesDatawarehouse:update-driven,highperformanceInformationfromheterogeneoussourcesisintegratedinadvanceandstoredinwarehousesfordirectqueryandanalysis6.3DBMSvs.DWMS

2023/1/26HD-ITR296.3DBMSvs.DWMS2023/1/26HD-ITR306.3DBMSvs.DWMSWhySeparateDataWarehouse?HighperformanceforbothsystemsDBMS—tunedforOLTP:accessmethods,indexing,concurrencycontrol,recoveryWarehouse—tunedforOLAP:complexOLAPqueries,multidimensionalview,consolidation.Differentfunctionsanddifferentdatamissingdata:DecisionsupportrequireshistoricaldatawhichoperationalDBsdonottypicallymaintaindataconsolidation:DSrequiresconsolidation(aggregation,summarization)ofdatafromheterogeneoussourcesdataquality:differentsourcestypicallyuseinconsistentdatarepresentations,codesandformatswhichhavetobereconciledNote:TherearemoreandmoresystemswhichperformOLAPanalysisdirectlyonrelationaldatabases2023/1/26HD-ITR316.3DBMSvs.DWMSDoingOLTPandOLAPinthesamedatabasesystemisoftenimpracticalSolution:Builda“datawarehouse”CopydatafromvariousOLTPsystemsOptimizedataorganization,systemtuningforOLAPTransactionsaren’tslowedbybiganalysisqueriesPeriodicallyrefreshthedatainthewarehouse2023/1/26HD-ITR32Chapter6Introduction6.1Fromdatabasetodatawarehouse6.2Whatisadatawarehouse6.3DBMSvs.DWMS6.4Datawarehousearchitectureandproblems6.5Somerelatedconcepts

6.4Datawarehousearchitectureand

problems2023/1/26HD-ITR336.4DatawarehousearchitectureandproblemsE:ExtractT:TransformL:LoadDataMartDataMartDataSourcesMulti-TieredArchitectureDataStorageFront-EndToolsDatawarehousearchitecture2023/1/26HD-ITR34DatawarehousearchitectureDataSourceDataStorageApplicationToolsVisualization6.4Datawarehousearchitectureandproblems2023/1/26HD-ITR356.4DatawarehousearchitectureandproblemsProblemsofdatawarehouseDatamodelLogicaldatastructureAlgebraicoperationsdatadefinitionandmanagementlanguageStorageandindexstructureAlgorithmsofOLAPQueryprocessingandoptimizationofOLAPDataextraction,transformationandloadingDatamaintenance2023/1/26HD-ITR36Chapter6Introduction6.1Fromdatabasetodatawarehouse6.2Whatisadatawarehouse6.3DBMSvs.DWMS6.4Datawarehousearchitectureandproblems6.5Somerelatedconcepts

6.5Somerelatedconcepts2023/1/26HD-ITR376.5SomerelatedconceptsETL：extract/transformation/loadETL工具就是進(jìn)行數(shù)據(jù)的抽取、轉(zhuǎn)換和“凈化提煉”處理?！皟艋釤挕奔磳?duì)從多個(gè)不同業(yè)務(wù)數(shù)據(jù)庫(kù)所抽取的數(shù)據(jù)，進(jìn)行數(shù)據(jù)項(xiàng)名稱的統(tǒng)一、位數(shù)的統(tǒng)一、編碼的統(tǒng)一和形式的統(tǒng)一，消除重復(fù)數(shù)據(jù)。ETL工具包括：dataextract,datatransform,datacleaning,dataloading.2023/1/26HD-ITR386.5SomerelatedconceptsDataRepository

Spacefordataandmetadata.Thereare3storagemethods:MultidimensionaldatabaseRelationaldatabaseHybridoftheabove

2023/1/26HD-ITR396.5Somerelatedconcepts數(shù)據(jù)業(yè)務(wù)系統(tǒng)中提取的或者從外部數(shù)據(jù)源中導(dǎo)入的數(shù)據(jù)經(jīng)過(guò)清洗、轉(zhuǎn)化后成為數(shù)據(jù)倉(cāng)庫(kù)的原始數(shù)據(jù)。由于需要數(shù)據(jù)倉(cāng)庫(kù)進(jìn)行OLAP分析和數(shù)據(jù)挖掘，因此需要在原始數(shù)據(jù)的基礎(chǔ)上增加冗余信息，比如進(jìn)行大量的預(yù)運(yùn)算，建立多維數(shù)據(jù)庫(kù)，以求迅速的展現(xiàn)數(shù)據(jù)。2023/1/26HD-ITR406.5Somerelatedconcepts元數(shù)據(jù)（Metadata）數(shù)據(jù)是對(duì)事物的描述，“元數(shù)據(jù)”就是描述數(shù)據(jù)的數(shù)據(jù)，它提供了有關(guān)數(shù)據(jù)的環(huán)境，用于構(gòu)造、維持、管理和使用數(shù)據(jù)倉(cāng)庫(kù)。數(shù)據(jù)倉(cāng)庫(kù)的元數(shù)據(jù)主要包含兩類數(shù)據(jù)：第一種是為了從操作型環(huán)境向數(shù)據(jù)倉(cāng)庫(kù)環(huán)境轉(zhuǎn)換而建立的元數(shù)據(jù)，它包括所有源數(shù)據(jù)項(xiàng)的名稱、屬性及其在提取倉(cāng)庫(kù)中的轉(zhuǎn)化；第二種元數(shù)據(jù)在數(shù)據(jù)倉(cāng)庫(kù)中是用來(lái)與最終用戶的多維商業(yè)模型和前端工具之間建立映射的。2023/1/26HD-ITR411.5數(shù)據(jù)倉(cāng)庫(kù)的基本概念在轉(zhuǎn)換后，(User_ID,User_Name,Address）3列原始的存放位置、進(jìn)行的清洗轉(zhuǎn)化處理、數(shù)據(jù)最終的存放位置、數(shù)據(jù)格式、數(shù)據(jù)使用的規(guī)則等等都將作為元數(shù)據(jù)的一部分。2023/1/26HD-ITR426.5Somerelatedconcepts主題（Subject）主題（Subject）是一個(gè)在較高層次上將數(shù)據(jù)歸類的標(biāo)準(zhǔn)，每一個(gè)主題基本對(duì)應(yīng)一個(gè)宏觀的分析領(lǐng)域。面向主題的數(shù)據(jù)組織方式，就是在較高層次上對(duì)分析對(duì)象數(shù)據(jù)的一個(gè)完整、一致的描述，能完整、統(tǒng)一地刻畫各個(gè)分析對(duì)象所涉及的企業(yè)各項(xiàng)數(shù)據(jù)，以及數(shù)據(jù)之間的聯(lián)系。2023/1/26HD-ITR