4數(shù)據(jù)挖掘概念與技術(shù)-第三章-數(shù)據(jù)倉庫和OLAP技術(shù)1

1、數(shù)據(jù)挖掘：概念與技術(shù) 第三章：數(shù)據(jù)倉庫與OLAP技術(shù)概述 2022/10/121數(shù)據(jù)挖掘：概念與技術(shù)第三章：數(shù)據(jù)倉倉庫與OLAP技術(shù)概述述什么是數(shù)數(shù)據(jù)倉庫庫?多維數(shù)據(jù)據(jù)集模型型數(shù)據(jù)倉庫庫體系結(jié)結(jié)構(gòu)數(shù)據(jù)倉庫庫實現(xiàn)從數(shù)據(jù)倉倉庫到數(shù)數(shù)據(jù)挖掘掘2020-03-012數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)什么是數(shù)數(shù)據(jù)倉庫庫?多種定義義方式，但不嚴(yán)嚴(yán)格從組織機機構(gòu)的操操作數(shù)據(jù)據(jù)庫分離離并獨立立維護(hù)的的決策支支持?jǐn)?shù)據(jù)據(jù)庫支持信息息處理，為統(tǒng)一一的歷史史數(shù)據(jù)分分析提供供堅實的的平臺數(shù)據(jù)倉庫庫是一個個面向主主題的、集成的的、時變變的和非非易失的的數(shù)據(jù)集集合，支支持管理理部門的的決策過過程。W.H.Inmon建立數(shù)據(jù)據(jù)倉庫：

2、構(gòu)建和使使用數(shù)據(jù)據(jù)倉庫的的過程2020-03-013數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫面向主題題的圍繞主題題組織數(shù)數(shù)據(jù),如顧客、產(chǎn)品、銷售等等。關(guān)注決策策者的數(shù)數(shù)據(jù)建模模與分析析，而不不是組織織機構(gòu)的的日常操操作和事事務(wù)處理理。提供特定定主題的的簡明視視圖，排排除對于于決策支支持過程程無用的的數(shù)據(jù)。2020-03-014數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫集成的由多個異異構(gòu)數(shù)據(jù)據(jù)源集成成構(gòu)建關(guān)系數(shù)據(jù)據(jù)庫,平面文件件,聯(lián)機事務(wù)務(wù)記錄應(yīng)用數(shù)據(jù)據(jù)清理和和數(shù)據(jù)集集成技術(shù)術(shù)確保命名名約定，編碼結(jié)結(jié)構(gòu)，屬屬性度量量等一致致性將數(shù)據(jù)遷遷入數(shù)據(jù)據(jù)倉庫時時需要進(jìn)進(jìn)行數(shù)據(jù)據(jù)轉(zhuǎn)換2020-03-015數(shù)據(jù)挖掘掘：

3、概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫時變的數(shù)據(jù)倉庫庫中數(shù)據(jù)據(jù)的時間間范圍比比業(yè)務(wù)操操作系統(tǒng)統(tǒng)中長得得多業(yè)務(wù)操作作數(shù)據(jù)庫庫：當(dāng)前前數(shù)據(jù)數(shù)據(jù)倉庫庫：提供供歷史數(shù)數(shù)據(jù)信息息(如過去5-10年)數(shù)據(jù)倉庫庫中的結(jié)結(jié)構(gòu)主鍵鍵：都隱式或或顯示包包含時間間元素但業(yè)務(wù)操操作數(shù)據(jù)據(jù)主鍵不不一定包包含時間間元素2020-03-016數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫非易失的的物理存儲儲：與操操作環(huán)境境分離，雖然來來自其中中。數(shù)據(jù)倉庫庫環(huán)境中中不發(fā)生生數(shù)據(jù)更更新操作作不需要事事務(wù)處理理，恢復(fù)復(fù)和并發(fā)發(fā)控制機機制只需要兩兩種數(shù)據(jù)據(jù)操作：i數(shù)據(jù)初始始化裝入入數(shù)據(jù)訪問問2020-03-017數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫與異構(gòu)

4、構(gòu)數(shù)據(jù)庫庫傳統(tǒng)異構(gòu)構(gòu)數(shù)據(jù)庫庫集成：查詢驅(qū)動動方法在異構(gòu)數(shù)數(shù)據(jù)庫上上建立包裝程序序和集成程序序（中介介程序）提交查詢詢時，使使用元數(shù)數(shù)據(jù)字典典將查詢詢轉(zhuǎn)換為為異構(gòu)站站點上的的查詢。然后將將查詢映映射和發(fā)發(fā)送到局局部查詢詢處理器器，由不不同站點點返回的的結(jié)果集集成為全全局查詢詢結(jié)果集集。復(fù)雜的信信息過濾濾和集成成處理，與局部部數(shù)據(jù)源源的處理理競爭資資源數(shù)據(jù)倉庫庫:更新驅(qū)動動方法，高性能將異構(gòu)源源的信息息預(yù)先集集成并存存儲在數(shù)數(shù)據(jù)倉庫庫中，供供直接查查詢和分分析不包含最最近的信信息支持復(fù)雜雜多維查查詢2020-03-018數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫與業(yè)務(wù)務(wù)操作數(shù)數(shù)據(jù)庫OLTP (on-li

5、ne transaction processing)，聯(lián)機事務(wù)務(wù)處理傳統(tǒng)關(guān)系系數(shù)據(jù)庫庫管理系系統(tǒng)的主主要任務(wù)務(wù)日常操作作：購物物，庫存存，制造造，銀行行，工資資，注冊冊，記賬賬等。OLAP (on-line analyticalprocessing)，聯(lián)機分析析處理數(shù)據(jù)倉庫庫系統(tǒng)的的主要任任務(wù)數(shù)據(jù)分析析和決策策制定區(qū)別(OLTPvs.OLAP):用戶和系系統(tǒng)的面面向性：顧客事務(wù)和查查詢（辦辦事員，打工仔仔）與市市場數(shù)據(jù)分析析（知識識工人，老板）數(shù)據(jù)內(nèi)容容：當(dāng)當(dāng)前的，細(xì)節(jié)的的vs.歷史的,匯總聚集集的數(shù)據(jù)庫設(shè)設(shè)計：E-R+業(yè)務(wù)應(yīng)用用vs.星形、雪雪花型+主題視圖：當(dāng)前的,局部的vs.進(jìn)化的,集成

6、的訪問模式式:更新vs.只讀但但查詢復(fù)復(fù)雜2020-03-019數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)OLTP vs.OLAP2020-03-0110數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)為什么需需要分離離數(shù)據(jù)倉倉庫?提高兩個個系統(tǒng)的的系能DBMStunedforOLTP:access methods, indexing,concurrencycontrol,recoveryWarehousetunedfor OLAP: complexOLAPqueries,multidimensional view, consolidation不同的功功能和不不同的數(shù)數(shù)據(jù):數(shù)據(jù)不全全: Decision supportrequir

7、eshistoricaldata which operational DBsdonottypically maintain數(shù)據(jù)聚合合:DSrequiresconsolidation (aggregation,summarization)ofdatafrom heterogeneoussources數(shù)據(jù)質(zhì)量量: differentsources typicallyuseinconsistent datarepresentations,codesandformatswhichhave to be reconciledNote:越來越多多的系統(tǒng)統(tǒng)直接在在DBMS上進(jìn)行2020-03-0111數(shù)據(jù)挖掘

8、掘：概念念與技術(shù)術(shù)Chapter 3: DataWarehousingand OLAPTechnology:AnOverviewWhat is adatawarehouse?A multi-dimensional datamodelData warehousearchitectureData warehouseimplementationFrom datawarehousingtodatamining2020-03-0112數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)由表和電電子數(shù)據(jù)據(jù)表到數(shù)數(shù)據(jù)立方方體數(shù)據(jù)倉庫庫和OLAP工具基于于多維數(shù)數(shù)據(jù)模型型，以數(shù)數(shù)據(jù)立方方體的方方式觀察察數(shù)據(jù)數(shù)據(jù)立方方體,如銷售,從多維

9、角角度對數(shù)數(shù)據(jù)建模模和觀察察維度表,如item (item_name, brand,type),ortime(day,week,month, quarter, year)事實表(如dollars_sold)包含度量量值和關(guān)關(guān)聯(lián)維度度表的碼碼名詞：數(shù)據(jù)立方方體成為為方體(cuboid)存放最底底層匯總總的方體體（n-D）成為基本方體體(basiccuboid)存放最高高層匯總總的方體體（0-D）稱為頂點方體體(apexcuboid)，用all標(biāo)記方體的格格成為數(shù)據(jù)立方方體(datacube)2020-03-0113數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)3-D數(shù)據(jù)立方方體2020-03-0114數(shù)據(jù)挖掘掘：概

10、念念與技術(shù)術(shù)4-D數(shù)據(jù)立方方體2020-03-0115數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)立方方體:方體的格格time,itemtime,item,locationtime,item,location,supplieralltimeitemlocationsuppliertime,locationtime,supplieritem,locationitem,supplierlocation,suppliertime,item,suppliertime,location,supplieritem,location,supplier0-D(apex) cuboid1-D cuboids2-D cuboi

11、ds3-D cuboids4-D(base) cuboid2020-03-0116數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫的概念念建模數(shù)據(jù)倉庫庫建模：dimensions &measures星形模型型:A facttableinthe middleconnectedtoasetofdimension tables雪花模型型:A refinementofstarschemawheresomedimensionalhierarchyisnormalizedinto aset of smallerdimension tables, formingashapesimilar to snowflake事實星

12、座座型:Multiplefact tablessharedimensiontables, viewedasa collectionofstars, thereforecalledgalaxyschemaorfact constellation2020-03-0117數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)星形模型型例子 time_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcitystate_or_provincecountrylocationSalesFact Tabletime_keyitem_keybranch_keylocat

13、ion_keyunits_solddollars_soldavg_salesMeasuresitem_keyitem_namebrandtypesupplier_typeitembranch_keybranch_namebranch_typebranch2020-03-0118數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)雪花模型型例子time_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcity_keylocationSalesFact Tabletime_keyitem_keybranch_keylocation_keyunits_so

14、lddollars_soldavg_salesMeasuresitem_keyitem_namebrandtypesupplier_keyitembranch_keybranch_namebranch_typebranchsupplier_keysupplier_typesuppliercity_keycitystate_or_provincecountrycity2020-03-0119數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)事實星座座型例子子time_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcityprovince_or_st

15、atecountrylocationSalesFact Tabletime_keyitem_keybranch_keylocation_keyunits_solddollars_soldavg_salesMeasuresitem_keyitem_namebrandtypesupplier_typeitembranch_keybranch_namebranch_typebranchShippingFact Tabletime_keyitem_keyshipper_keyfrom_locationto_locationdollars_costunits_shippedshipper_keyship

16、per_namelocation_keyshipper_typeshipper2020-03-0120數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)DMQL中的立方方體定義義語法立方體定定義(FactTable)definecube:維度定義義(Dimension Table)definedimensionas()Special Case(SharedDimensionTables)Firsttime as “cube definition”definedimensionasincube2020-03-0121數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)DMQL定義星形形模型definecubesales_star time,it

17、em,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_type)definedimensionbranchas(branch_key,branch_name, branch

18、_type)definedimensionlocationas(location_key,street, city, province_or_state,country)2020-03-0122數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)DMQL定義雪花花模型definecubesales_snowflaketime, item, branch,location:dollars_sold=sum(sales_in_dollars), avg_sales= avg(sales_in_dollars),units_sold= count(*)definedimensiontimeas(time_key, day,d

19、ay_of_week, month,quarter,year)definedimensionitemas(item_key, item_name,brand, type,supplier(supplier_key, supplier_type)definedimensionbranchas(branch_key,branch_name, branch_type)definedimensionlocationas(location_key,street,city(city_key,province_or_state,country)2020-03-0123數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)DMQL定義事

20、實實星座型型definecubesalestime,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_type)definedimensionbranchas(bra

21、nch_key,branch_name, branch_type)definedimensionlocationas(location_key,street, city, province_or_state,country)definecubeshippingtime,item,shipper,from_location,to_location:dollar_cost= sum(cost_in_dollars),unit_shipped=count(*)definedimensiontimeastimeincubesalesdefinedimensionitemasitemincubesale

22、sdefinedimensionshipperas(shipper_key, shipper_name,locationaslocationincubesales,shipper_type)definedimensionfrom_locationaslocationincubesalesdefinedimensionto_locationaslocationincubesales2020-03-0124數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)立方方體的度度量:三類分布式度度量:將函數(shù)用用于n個聚合值值得到的的結(jié)果與與將函數(shù)數(shù)用于整整個數(shù)據(jù)據(jù)集（不不劃分）得到的的結(jié)果一一樣E.g.,count(),sum

23、(),min(), max()代數(shù)度量量:能夠用具具有M個參數(shù)的的代數(shù)函函數(shù)計算算，而每每個參數(shù)數(shù)都可以以用一個個分布式式聚合函函數(shù)求得得E.g.,avg(),min_N(),standard_deviation()整體度量量:描述它的的子聚集集所需的的存儲沒沒有常數(shù)數(shù)界。E.g.,median(),mode(),rank()2020-03-0125數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)概念分層層:維度(location)allEuropeNorth_AmericaMexicoCanadaSpainGermanyVancouverM.WindL.Chan.allregionofficecountryTor

24、ontoFrankfurtcity2020-03-0126數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)概念分層層：數(shù)值值型數(shù)據(jù)據(jù)（price）2020-03-0127數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫和分層層視圖Specification of hierarchiesSchemahierarchydaymonthquarter;week yearSet_groupinghierarchy1.10 =minsup動機僅一小部部分“浮浮于水面面”的立立方體單單元存儲儲在稀疏疏立方體體中僅計算感感興趣的的單元數(shù)據(jù)超出出某一閾閾值避免立方方體的爆爆炸性增增長2020-03-0153數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)索引OLA

25、P數(shù)據(jù)：位位圖索引引某一具體體列上的的索引；列中的每每一個值值都有一一個位向向量：位位操作速速度快如果給定定屬性上上包含n個值，則則位圖索索引中每每項需要要n個位。位向量的的長度：基本表表的記錄錄數(shù)如果基表表給定行行上該屬屬性值為為v，則位圖圖索引對對應(yīng)行的的該值位位置1。不適合集集數(shù)很大大的域，導(dǎo)致位位向量很很長2020-03-0154數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)位圖索引引例子2020-03-0155數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)索引OLAP數(shù)據(jù):連接索引引連接索引引：JI(R-id,S-id)whereR (R-id,)S (S-id,)傳統(tǒng)的索索引將給給定列上上的值映映射到具具有該值值的行的的

26、列表上上在JI文件中物物化關(guān)系系連接，加快連連接速度度在數(shù)據(jù)倉倉庫中，連接索索引維護(hù)護(hù)維的屬屬性值與與事實表表的對應(yīng)應(yīng)行的聯(lián)聯(lián)系連接索引引可以跨跨越多維維，形成成符合連連接索引引2020-03-0156數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)連接索引引例子2020-03-0157數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)OLAP查詢的有有效處理理確定哪些些操作應(yīng)應(yīng)該在可可利用的的方體上上執(zhí)行將drill,roll等操作轉(zhuǎn)轉(zhuǎn)化為SQL或OLAP操作e.g.,dice =selection +projection確定相關(guān)關(guān)操作應(yīng)應(yīng)當(dāng)使使用哪些些物化的的方體假設(shè)對brand, province_or_state處理查詢詢，選擇擇

27、常量“year= 2004”,有4個物化方方體可用用：1)year,item_name,city2)year,brand, country3)year,brand, province_or_state4)item_name,province_or_statewhere year=2004應(yīng)該選擇擇哪一個個來處理理查詢?基于代價價的估計計。采用稀疏疏矩陣和和數(shù)據(jù)壓壓縮技術(shù)術(shù)2020-03-0158數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)Chapter 3: DataWarehousingand OLAPTechnology:AnOverviewWhat is adatawarehouse?A multi-di

28、mensional datamodelData warehousearchitectureData warehouseimplementationFrom datawarehousingtodatamining2020-03-0159數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫的使用用三種數(shù)據(jù)據(jù)倉庫應(yīng)應(yīng)用信息處理理支持查詢詢、基本本的統(tǒng)計計分析、使用交交叉表、表、圖圖表進(jìn)行行報告構(gòu)造低代代價的基基于Web的訪問工工具。分析處理理多維數(shù)據(jù)據(jù)分析支持OLAP操作：slice-dice,drilling,pivoting數(shù)據(jù)挖掘掘從隱藏的的模式中中發(fā)現(xiàn)知知識支持關(guān)聯(lián)聯(lián)分析，構(gòu)造分分析模型型，進(jìn)行行分類和和預(yù)

29、測，使用可可視化工工具提供供挖掘結(jié)結(jié)果2020-03-0160數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)數(shù)據(jù)倉庫庫的漸進(jìn)進(jìn)使用商務(wù)管理理人員使使用數(shù)據(jù)據(jù)倉庫和和數(shù)據(jù)集集市進(jìn)行行數(shù)據(jù)分分析和戰(zhàn)戰(zhàn)略決策策數(shù)據(jù)倉庫庫使用時時間越長長，進(jìn)化化的越好好開始，用用于產(chǎn)生生報告和和回答預(yù)預(yù)先定義義的查詢詢漸漸地，用于分分析匯總總和詳細(xì)細(xì)的數(shù)據(jù)據(jù)結(jié)果以以報表和和圖表提提供稍后，用用于戰(zhàn)略略目的，進(jìn)行多多維分析析和復(fù)雜雜的切片片和切塊塊操作。最后，用用于知識識發(fā)現(xiàn)，并使用用數(shù)據(jù)挖挖掘工具具進(jìn)行戰(zhàn)戰(zhàn)略決策策。數(shù)據(jù)倉庫庫工具：訪問與與檢索工工具，數(shù)數(shù)據(jù)庫報報表工具具，數(shù)據(jù)據(jù)分析工工具和數(shù)數(shù)據(jù)挖掘掘工具2020-03-0161數(shù)據(jù)挖

30、掘掘：概念念與技術(shù)術(shù)從聯(lián)機分分析處理理(OLAP)到聯(lián)機分分析挖掘掘(OLAM)為什么需需要聯(lián)機機分析挖挖掘？數(shù)據(jù)倉庫庫中數(shù)據(jù)據(jù)的高質(zhì)質(zhì)量DW中包含集集成，一一致，干干凈的數(shù)數(shù)據(jù)環(huán)繞數(shù)據(jù)據(jù)倉庫的的信息處處理基礎(chǔ)礎(chǔ)設(shè)施ODBC,OLEDB,Web訪問,服務(wù)機制制, reportingandOLAPtools基于OLAP的探測試試數(shù)據(jù)分分析Miningwithdrilling,dicing,pivoting,etc.數(shù)據(jù)挖掘掘功能的的聯(lián)機選選擇Integrationandswappingofmultiplemining functions,algorithms,and tasks2020-03-0

31、162數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)聯(lián)機分析析挖掘的的體系結(jié)結(jié)構(gòu)數(shù)據(jù)倉庫元數(shù)據(jù)MDDBOLAM引擎OLAP引擎圖形用戶界面 API立方體 API數(shù)據(jù)庫 API數(shù)據(jù)清理數(shù)據(jù)集成第三層OLAP/OLAM第2層多維數(shù)據(jù)庫第1層數(shù)據(jù)存儲第4層用戶界面層過濾&集成過濾數(shù)據(jù)庫挖掘查詢挖掘結(jié)果2020-03-0163數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)Chapter 3: DataWarehousingand OLAPTechnology:AnOverviewWhat is adatawarehouse?A multi-dimensional datamodelData warehousearchitectureData

32、 warehouseimplementationFrom datawarehousingtodataminingSummary2020-03-0164數(shù)據(jù)挖掘掘：概念念與技術(shù)術(shù)總結(jié):數(shù)據(jù)倉庫庫和OLAP技術(shù)為什么需需要數(shù)據(jù)據(jù)倉庫?數(shù)據(jù)倉庫庫的多維維數(shù)據(jù)模模型Star schema,snowflake schema,factconstellationsA datacubeconsistsofdimensions &measuresOLAP操作: drilling,rolling,slicing,dicingand pivoting數(shù)據(jù)倉庫庫結(jié)構(gòu)體體系OLAP服務(wù)器: ROLAP,MOLAP,HOLAP數(shù)據(jù)立方方體的有有效計算算Partial vs.full vs.nomaterializationIndexingOALP data: