11數(shù)據(jù)倉庫技術(shù)講座-57

1、12 十月 2022Data Warehousing and OLAP Technology1數(shù)據(jù)倉庫和OLAP技術(shù)什么是數(shù)據(jù)倉庫（ What is a data warehouse）? 多維數(shù)據(jù)模型（A multi-dimensional data model）數(shù)據(jù)倉庫體系結(jié)構(gòu)（Data warehouse architecture）數(shù)據(jù)倉庫實(shí)現(xiàn)（Data warehouse implementation）Further development of data cube technologyFrom data warehousing to data mining01三三月2020Data W

2、arehousing andOLAP Technology2數(shù)據(jù)庫的的定義傳統(tǒng)的數(shù)數(shù)據(jù)庫技技術(shù)是以以單一的的數(shù)據(jù)資資源為中中心，同同時(shí)進(jìn)行行從事務(wù)務(wù)處理，批處理理到?jīng)Q策策分析的的各類處處理；數(shù)據(jù)庫主主要是為為自動(dòng)化化，精簡(jiǎn)簡(jiǎn)工作任任務(wù)和高高速數(shù)據(jù)據(jù)采集服服務(wù)的。它的運(yùn)運(yùn)行是事事務(wù)驅(qū)動(dòng)動(dòng)，面向向應(yīng)用的的，數(shù)據(jù)據(jù)庫的根根本任務(wù)務(wù)是完成成數(shù)據(jù)操操作，即即及時(shí)安安全地將將當(dāng)前事事務(wù)所產(chǎn)產(chǎn)生的記記錄保存存下來。01三三月2020Data Warehousing andOLAP Technology3兩種不同同的數(shù)據(jù)據(jù)處理需需求計(jì)算機(jī)系系統(tǒng)中存存在著兩兩類不同同的數(shù)據(jù)據(jù)處理需需求，即即：操作型處處理（事務(wù)

3、處處理）：主要是是對(duì)一個(gè)個(gè)或一組組記錄的的查詢和和修改，這時(shí)候候人們關(guān)關(guān)心的是是響應(yīng)時(shí)時(shí)間、數(shù)據(jù)的安安全性和和完整性性；分析型處處理（信息型型處理）：用于于管理人人員的決決策分析析，如DDS(decisionsupportsystem)、多維分析析等。01三三月2020Data Warehousing andOLAP Technology4為什么要要建立數(shù)數(shù)據(jù)倉庫庫？數(shù)據(jù)DATA知識(shí)KNOWLEDGE決定DECISIONSPatternsTrendsFactsRelationsModelsAssociationsSequencesTargetMarketsFundsallocationTrad

4、ing optionsWheretoadvertiseCatalog mailinglistSalesgeography財(cái)經(jīng)的Financial經(jīng)濟(jì)的Economic政府Government銷售分?jǐn)?shù)數(shù)Point-of-Sale人口統(tǒng)計(jì)計(jì)學(xué)Demographic生活方式式Lifestyle痛苦:太多數(shù)據(jù)據(jù)，無法法作出正正確判斷斷！01三三月2020Data Warehousing andOLAP Technology5What is DataWarehouse?數(shù)據(jù)倉庫庫是在企企業(yè)管理理和決策策中面向主題題的,集成的,與時(shí)間相相關(guān)的和不可修改改的數(shù)據(jù)集集合“Adata warehouseisas

5、ubject-oriented,integrated,time-variant,andnonvolatilecollection of datainsupportofmanagementsdecision-making process.”W.H.InmonData warehousing:Theprocessofconstructingand using datawarehouses01三三月2020Data Warehousing andOLAP Technology6Data WarehouseSubject-OrientedOrganizedaround major subjects,s

6、uch ascustomer,product,sales.Focusingonthemodelingand analysis of datafor decision makers,not on daily operationsortransactionprocessing.Providea simpleandconciseview aroundparticular subjectissues byexcludingdatathat arenotuseful in thedecisionsupport process.01三三月2020Data Warehousing andOLAP Techn

7、ology7面向應(yīng)用用舉例采購子系系統(tǒng)：訂單（訂訂單號(hào)，供應(yīng)商商號(hào)，總總金額，日期）訂單細(xì)則則(訂單單號(hào)，商商品號(hào)，類別，單價(jià)，數(shù)量）供應(yīng)商（供應(yīng)商商號(hào)，供供應(yīng)商名名，地址址，電話話）銷售子系系統(tǒng)：顧客(顧顧客號(hào)，姓名，性別，年齡，地址，電話）銷售(員工號(hào)，顧客號(hào)號(hào)，商品品號(hào)，數(shù)數(shù)量，單單價(jià)日期期)庫存管理理子系統(tǒng)統(tǒng)：領(lǐng)料單(領(lǐng)料單號(hào)號(hào)，領(lǐng)料人人，商品品號(hào)，數(shù)數(shù)量，日日期)進(jìn)料單（進(jìn)料單號(hào)號(hào)，訂單單號(hào)，進(jìn)進(jìn)料人，收料人人，日期期）庫存（商商品號(hào)，庫房號(hào)號(hào)，庫存存量，日日期）庫房(庫房號(hào)，倉庫保保管員，地點(diǎn)，庫存商商品描述述)人事管理理子系統(tǒng)統(tǒng)：?jiǎn)T工（員員工號(hào)，姓名，性別，年齡，部門號(hào)號(hào)）部門（

8、部部門號(hào)，部門名名稱，部部門主管管，電話話）面向主題題舉例：商品：商品固有有信息：商品號(hào)號(hào)，商品品名，類類別，顏顏色等商品采購購信息：商品號(hào)號(hào)，供應(yīng)應(yīng)商號(hào)，供應(yīng)價(jià)價(jià)，供應(yīng)應(yīng)日期，供應(yīng)量量等商品銷售售信息：商品號(hào)號(hào)，顧客客號(hào)，售售價(jià)，銷銷售日期期，銷售售量等商品庫存存信息：商品號(hào)號(hào)，庫房房號(hào)，日日期，庫庫存量等等供應(yīng)商：供應(yīng)商固固有信息息：供應(yīng)應(yīng)商號(hào)，供應(yīng)商商名，地地址，電電話等供應(yīng)商品品信息：供應(yīng)商商號(hào)，商商品號(hào)，供應(yīng)價(jià)價(jià)，供應(yīng)應(yīng)日期，供應(yīng)量量等顧客：顧客固有有信息：顧客號(hào)號(hào)，顧客客名，性性別，年年齡，住住址，電電話等顧客購物物信息：顧客號(hào)號(hào)，商品品號(hào)，售售價(jià)，購購買日期期，購買買量等01三三

9、月2020Data Warehousing andOLAP Technology8Data WarehouseIntegratedConstructedbyintegratingmultiple,heterogeneousdata sourcesrelational databases,flatfiles,on-linetransactionrecordsData cleaning anddata integration techniquesare applied.Ensureconsistencyinnaming conventions,encodingstructures,attribut

10、emeasures, etc.amongdifferent datasourcesE.g.,Hotelprice:currency, tax,breakfast covered, etc.When dataismovedtothe warehouse,itisconverted.01三三月2020Data Warehousing andOLAP Technology9Data WarehouseTime VariantThetimehorizon forthedatawarehouseissignificantlylongerthanthat of operational systems.Op

11、erationaldatabase:currentvaluedata.Data warehousedata:provide information fromahistorical perspective (e.g.,past 5-10years)Everykeystructure in thedata warehouseContainsanelement of time, explicitlyorimplicitlyButthe keyofoperationaldata mayormaynot contain“timeelement”.01三三月2020Data Warehousing and

12、OLAP Technology10Data WarehouseNon-VolatileAphysically separate storeofdata transformed fromthe operational environment.Operationalupdateofdatadoes notoccurinthedatawarehouseenvironment.Does notrequire transaction processing, recovery,andconcurrencycontrolmechanismsRequiresonly twooperations in data

13、accessing:initial loadingofdataandaccessofdata.01三三月2020Data Warehousing andOLAP Technology11Data Warehousevs.HeterogeneousDBMSTraditionalheterogeneous DB integration:Buildwrappers/mediatorsontopofheterogeneousdatabasesQuerydrivenapproachWhen aqueryisposedtoaclientsite,ameta-dictionaryisusedtotransl

14、atethe query intoqueriesappropriateforindividualheterogeneous sites involved,andthe resultsare integratedintoa globalanswersetComplex information filtering,competeforresourcesData warehouse:update-driven, highperformanceInformationfrom heterogeneoussourcesisintegrated in advanceand storedinwarehouse

15、s fordirectqueryand analysis01三三月2020Data Warehousing andOLAP Technology12Data Warehousevs.OperationalDBMSOLTP (on-line transaction processing)Majortask of traditional relationalDBMSDay-to-day operations: purchasing, inventory,banking,manufacturing,payroll,registration,accounting,etc.OLAP (on-line a

16、nalyticalprocessing)Majortask of datawarehouse systemData analysis anddecisionmakingDistinctfeatures(OLTPvs.OLAP):User andsystemorientation: customer vs.marketData contents:current,detailedvs.historical,consolidatedDatabasedesign: ER +applicationvs. star+subjectView:current,localvs.evolutionary,inte

17、gratedAccesspatterns: updatevs.read-only butcomplex queries01三三月2020Data Warehousing andOLAP Technology13OLTP vs.OLAP01三三月2020Data Warehousing andOLAP Technology14WhySeparateDataWarehouse?High performance forboth systemsDBMStunedforOLTP:access methods, indexing,concurrencycontrol,recoveryWarehousetu

18、nedfor OLAP: complexOLAPqueries,multidimensional view, consolidation.Differentfunctions anddifferentdata:missing data: Decision supportrequireshistoricaldata which operational DBsdonottypically maintaindata consolidation:DSrequiresconsolidation (aggregation,summarization)ofdatafrom heterogeneoussour

19、cesdata quality: differentsources typicallyuseinconsistent datarepresentations,codesandformatswhichhave to be reconciled01三三月2020Data Warehousing andOLAP Technology15Data Warehousing andOLAP TechnologyWhat is adatawarehouse?A multi-dimensional datamodelData warehousearchitectureData warehouseimpleme

20、ntationFurther development of datacubetechnologyFrom datawarehousingtodatamining01三三月2020Data Warehousing andOLAP Technology16From TablesandSpreadsheets to DataCubesA datawarehouse is based on amultidimensional datamodelwhichviewsdata in theform of adatacubeA datacube,suchassales, allowsdata to be m

21、odeledand viewedinmultipledimensionsDimensiontables,such asitem (item_name, brand,type),ortime(day,week,month, quarter, year)Fact table contains measures (such asdollars_sold) andkeys to eachofthe relateddimension tablesIndata warehousing literature, an n-Dbase cubeiscalled abase cuboid. Thetopmost0

22、-Dcuboid,whichholdsthehighest-levelofsummarization,iscalledtheapex cuboid.Thelatticeofcuboids forms adata cube.01三三月2020Data Warehousing andOLAP Technology17Cube:A LatticeofCuboidsalltimeitemlocationsuppliertime,itemtime,locationtime,supplieritem,locationitem,supplierlocation,suppliertime,item,locat

23、iontime,item,suppliertime,location,supplieritem,location,suppliertime,item,location,supplier0-D(apex) cuboid1-D cuboids2-D cuboids3-D cuboids4-D(base) cuboid01三三月2020Data Warehousing andOLAP Technology18Conceptual Modeling of DataWarehousesModelingdata warehouses: dimensions&measuresStar schema:A fa

24、cttableinthe middleconnectedtoasetofdimension tablesSnowflakeschema:A refinementofstarschemawheresomedimensionalhierarchyisnormalizedinto aset of smallerdimension tables, formingashapesimilar to snowflakeFact constellations:Multiplefact tablessharedimensiontables, viewedasa collectionofstars, theref

25、orecalledgalaxyschemaorfact constellation01三三月2020Data Warehousing andOLAP Technology19Example of StarSchema time_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcityprovince_or_streetcountrylocationSalesFact Tabletime_keyitem_keybranch_keylocation_keyunits_solddollars_soldavg_salesMeasur

26、esitem_keyitem_namebrandtypesupplier_typeitembranch_keybranch_namebranch_typebranch01三三月2020Data Warehousing andOLAP Technology20Example of SnowflakeSchematime_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcity_keylocationSalesFact Tabletime_keyitem_keybranch_keylocation_keyunits_solddo

27、llars_soldavg_salesMeasuresitem_keyitem_namebrandtypesupplier_keyitembranch_keybranch_namebranch_typebranchsupplier_keysupplier_typesuppliercity_keycityprovince_or_streetcountrycity01三三月2020Data Warehousing andOLAP Technology21Example of FactConstellationtime_keydayday_of_the_weekmonthquarteryeartim

28、elocation_keystreetcityprovince_or_streetcountrylocationSalesFact Tabletime_keyitem_keybranch_keylocation_keyunits_solddollars_soldavg_salesMeasuresitem_keyitem_namebrandtypesupplier_typeitembranch_keybranch_namebranch_typebranchShippingFact Tabletime_keyitem_keyshipper_keyfrom_locationto_locationdo

29、llars_costunits_shippedshipper_keyshipper_namelocation_keyshipper_typeshipper01三三月2020Data Warehousing andOLAP Technology22A DataMining Query Language,DMQL:LanguagePrimitivesCube Definition(FactTable)definecube:DimensionDefinition( DimensionTable)definedimensionas()Special Case(SharedDimensionTables

30、)Firsttime as “cube definition”definedimensionasincube01三三月2020Data Warehousing andOLAP Technology23Defininga StarSchema in DMQLdefinecubesales_star time,item,branch, location:dollars_sold=sum(sales_in_dollars), avg_sales= avg(sales_in_dollars),units_sold= count(*)definedimensiontimeas(time_key, day

31、,day_of_week, month,quarter,year)definedimensionitemas(item_key, item_name,brand, type, supplier_type)definedimensionbranchas(branch_key,branch_name, branch_type)definedimensionlocationas(location_key,street, city, province_or_state,country)01三三月2020Data Warehousing andOLAP Technology24Defininga Sno

32、wflakeSchemainDMQLdefinecubesales_snowflaketime, item, branch,location:dollars_sold=sum(sales_in_dollars), avg_sales= avg(sales_in_dollars),units_sold= count(*)definedimensiontimeas(time_key, day,day_of_week, month,quarter,year)definedimensionitemas(item_key, item_name,brand, type,supplier(supplier_

33、key, supplier_type)definedimensionbranchas(branch_key,branch_name, branch_type)definedimensionlocationas(location_key,street,city(city_key,province_or_state,country)01三三月2020Data Warehousing andOLAP Technology25Defininga FactConstellationinDMQLdefinecubesalestime,item,branch,location:dollars_sold=su

34、m(sales_in_dollars), avg_sales= avg(sales_in_dollars),units_sold= count(*)definedimensiontimeas(time_key, day,day_of_week, month,quarter,year)definedimensionitemas(item_key, item_name,brand, type, supplier_type)definedimensionbranchas(branch_key,branch_name, branch_type)definedimensionlocationas(loc

35、ation_key,street, city, province_or_state,country)definecubeshippingtime,item,shipper,from_location,to_location:dollar_cost= sum(cost_in_dollars),unit_shipped=count(*)definedimensiontimeastimeincubesalesdefinedimensionitemasitemincubesalesdefinedimensionshipperas(shipper_key, shipper_name,locationas

36、locationincubesales,shipper_type)definedimensionfrom_locationaslocationincubesalesdefinedimensionto_locationaslocationincubesales01三三月2020Data Warehousing andOLAP Technology26Measures:ThreeCategoriesdistributive: if theresultderivedbyapplyingthefunctiontonaggregatevalues is thesame as thatderivedbya

37、pplyingthefunctiononall thedata withoutpartitioning.E.g.,count(),sum(),min(), max().algebraic:ifitcanbecomputedbyanalgebraic function withMarguments(whereMisa boundedinteger), eachofwhichisobtainedbyapplyingadistributiveaggregate function.E.g.,avg(),min_N(),standard_deviation().holistic:ifthereisnoc

38、onstantboundonthestoragesize neededtodescribea subaggregate.E.g.,median(),mode(),rank().01三三月2020Data Warehousing andOLAP Technology27A ConceptHierarchy:Dimension(location)allEuropeNorth_AmericaMexicoCanadaSpainGermanyVancouverM.WindL.Chan.allregionofficecountryTorontoFrankfurtcity01三三月2020Data Ware

39、housing andOLAP Technology28View of Warehousesand HierarchiesSpecification of hierarchiesSchemahierarchydaymonthquarter;week yearSet_groupinghierarchy1.10 inexpensive01三三月2020Data Warehousing andOLAP Technology29Multidimensional DataSalesvolumeasafunctionofproduct,month,and regionProductRegionMonthD

40、imensions:Product,Location,TimeHierarchicalsummarizationpathsIndustryRegionYearCategoryCountryQuarterProductCityMonthWeekOfficeDay01三三月2020Data Warehousing andOLAP Technology30A SampleData CubeTotalannualsalesofTVinU.S.A.DateProductCountryAll, All, Allsumsum TVVCRPC1Qtr2Qtr3Qtr4QtrU.S.ACanadaMexicos

41、um01三三月2020Data Warehousing andOLAP Technology31Cuboids Correspondingtothe Cubeallproductdatecountryproduct,dateproduct,countrydate,countryproduct,date,country0-D(apex) cuboid1-D cuboids2-D cuboids3-D(base) cuboid01三三月2020Data Warehousing andOLAP Technology32Browsinga DataCubeVisualizationOLAP capab

42、ilitiesInteractivemanipulation01三三月2020Data Warehousing andOLAP Technology33Typical OLAPOperationsRoll up (drill-up):summarizedatabyclimbinguphierarchyorbydimension reductionDrilldown (roll down):reverse of roll-upfrom higherlevelsummary to lower level summaryordetaileddata,orintroducingnew dimensio

43、nsSliceanddice:project andselectPivot(rotate):reorientthecube,visualization,3Dtoseries of 2D planes.Otheroperationsdrillacross:involving(across)morethan onefact tabledrillthrough:through thebottomlevelofthe cubetoits back-end relationaltables (usingSQL)01三三月2020Data Warehousing andOLAP Technology34A

44、 Star-Net Query Model ShippingMethodAIR-EXPRESSTRUCKORDERCustomerOrdersCONTRACTSCustomerProductPRODUCT GROUPPRODUCT LINEPRODUCT ITEMSALESPERSONDISTRICTDIVISIONOrganizationPromotionCITYCOUNTRYREGIONLocationDAILYQTRLYANNUALYTimeEach circleiscalledafootprint01三三月2020Data Warehousing andOLAP Technology3

45、5Data Warehousing andOLAP Technologyfor DataMiningWhat is adatawarehouse?A multi-dimensional datamodelData warehousearchitectureData warehouseimplementationFurther development of datacubetechnologyFrom datawarehousingtodatamining01三三月2020Data Warehousing andOLAP Technology36DesignofaData Warehouse:A

46、BusinessAnalysisFrameworkFour views regardingthedesign of adatawarehouseTop-downviewallowsselection of therelevantinformationnecessaryfor thedata warehouseData sourceviewexposes theinformationbeingcaptured,stored,andmanagedbyoperationalsystemsData warehouseviewconsistsoffact tablesanddimension table

47、sBusinessqueryviewsees theperspectivesofdatainthewarehouse fromthe viewofend-user01三三月2020Data Warehousing andOLAP Technology37Data WarehouseDesignProcessTop-down,bottom-up approachesoracombinationofbothTop-down: Startswith overalldesign andplanning(mature)Bottom-up: Startswith experiments andprotot

48、ypes (rapid)From software engineering point of viewWaterfall:structured andsystematic analysis at eachstepbeforeproceedingtothenextSpiral:rapidgeneration of increasinglyfunctional systems, short turnaround time, quick turnaroundTypical datawarehouse designprocessChooseabusinessprocesstomodel,e.g.,or

49、ders,invoices,etc.Choosethegrain(atomiclevelofdata)ofthebusinessprocessChoosethedimensionsthat willapplytoeachfact table recordChoosethemeasurethat willpopulateeachfact table record01三三月2020Data Warehousing andOLAP Technology38Multi-TieredArchitectureDataWarehouseExtractTransformLoadRefreshOLAP Engi

50、neAnalysisQueryReportsData miningMonitor&IntegratorMetadataData SourcesFront-EndToolsServeData MartsOperational DBsothersourcesData StorageOLAP Server01三三月2020Data Warehousing andOLAP Technology39SourceDatabasesData Extraction,Transformation, loadWarehouseAdmin.ToolsExtract, Transformand LoadDataMod

51、elingToolCentralMetadataArchitectedData MartsData Accessand AnalysisEnd-UserDW ToolsCentral DataWarehouseCentral DataWarehouseMid-TierMid-TierDataMartDataMartLocal MetadataLocal MetadataLocal MetadataMetadataExchangeMDBDataCleansingToolRelationalAppl. PackageLegacyExternalRDBMSRDBMS體系結(jié)構(gòu)構(gòu)Pieter,1998數(shù)

52、據(jù)倉庫庫的焦點(diǎn)點(diǎn)問題-數(shù)據(jù)的獲獲得、存存儲(chǔ)和使使用RelationalPackageLegacyExternalsourceDataCleanToolDataStagingEnterpriseDataWarehouseDatamartDatamartRDBMSROLAPRDBMSEnd-UserToolEnd-UserToolMDBEnd-UserToolEnd-UserTool數(shù)據(jù)倉庫庫和集市市的加載載能力至至關(guān)重要要數(shù)據(jù)倉庫庫和集市市的查詢?cè)冚敵瞿苣芰χ陵P(guān)關(guān)重要ETL工具去掉操作作型數(shù)據(jù)據(jù)庫中的的不需要要的數(shù)據(jù)據(jù)統(tǒng)一轉(zhuǎn)換換數(shù)據(jù)的的名稱和和定義計(jì)算匯總總數(shù)據(jù)和和派生數(shù)數(shù)據(jù)估計(jì)遺失失數(shù)據(jù)的的缺

53、省值值調(diào)節(jié)源數(shù)數(shù)據(jù)的定定義變化化01三三月2020Data Warehousing andOLAP Technology42ThreeData WarehouseModelsEnterprise warehousecollectsallofthe information about subjects spanning theentireorganizationData Marta subsetofcorporate-widedata thatisofvaluetoaspecificgroupsofusers.Itsscopeisconfinedtospecific, selected grou

54、ps,suchasmarketingdatamartIndependentvs.dependent (directlyfrom warehouse)datamartVirtual warehouseA setofviewsover operational databasesOnly someofthe possible summaryviewsmay be materialized01三三月2020Data Warehousing andOLAP Technology43Data WarehouseDevelopment:ARecommendedApproachDefineahigh-leve

55、l corporatedata modelData MartData MartDistributedData MartsMulti-Tier DataWarehouseEnterprise DataWarehouseModelrefinementModelrefinement01三三月2020Data Warehousing andOLAP Technology44OLAP ServerArchitecturesRelational OLAP(ROLAP)Userelationalorextended-relational DBMStostoreand managewarehousedataa

56、ndOLAPmiddlewaretosupport missingpiecesInclude optimizationofDBMS backend, implementation of aggregation navigationlogic, andadditional tools andservicesgreater scalabilityMultidimensional OLAP(MOLAP)Array-basedmultidimensional storageengine (sparsematrix techniques)fast indexing to pre-computedsumm

57、arized dataHybridOLAP(HOLAP)User flexibility,e.g.,low level:relational,high-level:arraySpecializedSQLserversspecializedsupport forSQLqueriesover star/snowflake schemas01三三月2020Data Warehousing andOLAP Technology45Data Warehousing andOLAP Technologyfor DataMiningWhat is adatawarehouse?A multi-dimensi

58、onal datamodelData warehousearchitectureData warehouseimplementationFurther development of datacubetechnologyFrom datawarehousingtodatamining01三三月2020Data Warehousing andOLAP Technology46EfficientDataCube ComputationData cubecan be viewedasa latticeofcuboidsThebottom-mostcuboid is thebase cuboidThet

59、op-mostcuboid (apex)containsonly onecellHowmanycuboids in an n-dimensionalcubewith Llevels?MaterializationofdatacubeMaterializeevery(cuboid)(fullmaterialization),none(nomaterialization),orsome (partial materialization)SelectionofwhichcuboidstomaterializeBasedonsize,sharing,accessfrequency,etc.01三三月2

60、020Data Warehousing andOLAP Technology47Cube OperationCube definitionand computation in DMQLdefinecubesalesitem,city,year:sum(sales_in_dollars)compute cubesalesTransformitintoa SQL-like language (with anew operatorcube by, introducedbyGrayetal.96)SELECTitem,city,year,SUM (amount)FROM SALESCUBE BYite