數(shù)據(jù)挖掘6-5Cluster-HierMeth (16)教材

DataModelReviewthebasicconceptsofdatabaseWhatisadatawarehouse?Amulti-dimensionaldatamodelDatawarehousearchitectureDatawarehouseimplementationFromdatawarehousingtodatamining數(shù)據(jù)立方體(1)數(shù)據(jù)倉庫基于一個(gè)多維度數(shù)據(jù)模型，以數(shù)據(jù)立方體的方式看待數(shù)據(jù)一個(gè)數(shù)據(jù)立方體，例如銷售量，允許數(shù)據(jù)進(jìn)行建模，并在多個(gè)層面觀看Dimensiontables（維表）,例如項(xiàng)目(項(xiàng)目名字,品牌,類型),或者時(shí)間(天,周,月,季度,年)Facttable（事實(shí)表）包含相關(guān)維度表的層組（例如銷售額）和鍵

在數(shù)據(jù)倉庫文獻(xiàn)中,一個(gè)n維基本立方體被稱為基本方體。

擁有最高級(jí)匯總的最上層的0維方體,被稱為頂端立方體。長方體晶格形成了一個(gè)數(shù)據(jù)立方體。數(shù)據(jù)立方體(2)維度和維度表維度:是一個(gè)組織要保留的觀點(diǎn)或?qū)嶓w.維度表:是進(jìn)一步描述一個(gè)維度的一組屬性.每個(gè)維度有可能有與之相聯(lián)系的一個(gè)維度表.

時(shí)間，項(xiàng)目，地點(diǎn)，供應(yīng)者

事實(shí)和事實(shí)表事實(shí):衡量一個(gè)主題事實(shí)表:事實(shí)的表現(xiàn).它包含每個(gè)相關(guān)維表的事實(shí)和鍵名。事實(shí)是數(shù)值，銷售金額DataCube(3)數(shù)據(jù)立方的維度數(shù)量觀察到的維度數(shù)量.

Sales（itemtimelocationdollars_sold）基本方體:包含所有在數(shù)據(jù)倉庫中可以被觀察到的維度的立方體.頂端立方體:

不包含維度的立方體.數(shù)據(jù)立方:

一個(gè)多維度數(shù)據(jù)模型中的所有立方體.數(shù)據(jù)立方—OneExample(1)ALLElectronicssales

維度：時(shí)間，項(xiàng)目，地點(diǎn)，品牌

維度表：

time(time_keydayday_of_weekmonthquarteryear) item(item_keyitem_namebrandtypesupplier_key)

facttable：(time_keyitem_keybrand_keylocation_keydollars_soldunits_sold)數(shù)據(jù)立方—OneExample(2)2維數(shù)據(jù)立方：

location＝”Vancouver”

item(type)Time(quarter)entertainment

computer

security

Q1605825400Q2680920512Q37811026501Q48241120580數(shù)據(jù)立方—OneExample(3)4維數(shù)據(jù)立方家庭娛樂計(jì)算機(jī)電話安全Q1Q2Q3Q4VanciuerTorontoNewYorkChicagoitemtimeLocationsupplierCube:ALatticeofCuboidsalltimeitemlocationsuppliertime,locationtime,supplieritem,locationitem,supplierlocation,suppliertime,item,suppliertime,location,supplieritem,location,supplier0-D(apex)cuboid1-Dcuboids2-Dcuboids3-Dcuboids4-D(base)cuboid數(shù)據(jù)倉庫的概念模型數(shù)據(jù)倉庫模型:維度&層組星型模式:中間的事實(shí)表和一組維度表相連雪花模式:是星型模式的改進(jìn)，一些維度層級(jí)標(biāo)準(zhǔn)化成一組更小的維度表，形成類似雪花的形狀Factconstellations（事實(shí)星座）:多個(gè)事實(shí)表共享維度表，看起來像星星的集合，因此被稱為星系模式或事實(shí)星座ExampleofStarSchema

time_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcitystate_or_provincecountrylocationSalesFactTable

time_key

item_key

branch_key

location_key

units_sold

dollars_sold

avg_salesMeasuresitem_keyitem_namebrandtypesupplier_typeitembranch_keybranch_namebranch_typebranchExampleofSnowflakeSchematime_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcity_keylocationSalesFactTable

time_key

item_key

branch_key

location_key

units_sold

dollars_sold

avg_salesMeasuresitem_keyitem_namebrandtypesupplier_keyitembranch_keybranch_namebranch_typebranchsupplier_keysupplier_typesuppliercity_keycitystate_or_provincecountrycityExampleofFactConstellationsSchematime_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcityprovince_or_statecountrylocationSalesFactTabletime_key

item_key

branch_key

location_key

units_sold

dollars_sold

avg_salesMeasuresitem_keyitem_namebrandtypesupplier_typeitembranch_keybranch_namebranch_typebranchShippingFactTabletime_key

item_key

shipper_key

from_location

to_location

dollars_cost

units_shippedshipper_keyshipper_namelocation_keyshipper_typeshipper在DMQL的多維數(shù)據(jù)定義語法多維數(shù)據(jù)集定義(事實(shí)表)definecube<cube_name>[<dimension_list>]:<measure_list>維度定義(維度表)definedimension<dimension_name>as(<attribute_or_subdimension_list>)特例(共享維度表)Firsttimeas“cubedefinition”definedimension<dimension_name>as<dimension_name_first_time>incube<cube_name_first_time>在DMQL定義星型模式definecubesales_star[time,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(branch_key,branch_name,branch_type)definedimensionlocationas(location_key,street,city,province_or_state,country)在DMQL定義雪花模式definecubesales_snowflake[time,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_key,supplier_type))definedimensionbranchas(branch_key,branch_name,branch_type)definedimensionlocationas(location_key,street,city(city_key,province_or_state,country))在DMQL定義事實(shí)星座definecubesales[time,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(branch_key,branch_name,branch_type)definedimensionlocationas(location_key,street,city,province_or_state,country)definecubeshipping[time,item,shipper,from_location,to_location]:dollar_cost=sum(cost_in_dollars),unit_shipped=count(*)definedimensiontimeastimeincubesalesdefinedimensionitemasitemincubesalesdefinedimensionshipperas(shipper_key,shipper_name,locationaslocationincubesales,shipper_type)definedimensionfrom_locationaslocationincubesalesdefinedimensionto_locationaslocationincubesales一個(gè)概念層級(jí):維度一個(gè)概念層級(jí)定義了從一套更低級(jí)別的概念到更高、更一般的概念的映射序列。 類別:屬性的層級(jí):地點(diǎn),省,村屬性值的層級(jí)或分組對(duì)于一個(gè)給定的維度，或許會(huì)有不止一個(gè)概念層級(jí).一個(gè)概念層級(jí):維度(地點(diǎn))allEuropeNorth_AmericaMexicoCanadaSpainGermanyVancouverM.WindL.Chan..................全部地區(qū)辦公室國家TorontoFrankfurt城市多維度數(shù)據(jù)作為產(chǎn)品，月份和地區(qū)的一個(gè)函數(shù)的銷售量ProductRegionMonth維度:產(chǎn)品,地點(diǎn),時(shí)間層級(jí)匯總路徑IndustryRegionYearCategoryCountryQuarterProductCityMonthWeekOfficeDay一個(gè)數(shù)據(jù)立方樣本TotalannualsalesofTVinU.S.A.DateProductCountryAll,All,Allsumsum

TVVCRPC1Qtr2Qtr3Qtr4QtrU.S.ACanadaMexicosumCuboidsCorrespondingtotheCubeallproductdatecountryproduct,dateproduct,countrydate,countryproduct,date,country0-D(apex)cuboid1-Dcuboids2-Dcuboids3-D(base)cuboid數(shù)據(jù)倉庫和層級(jí)結(jié)構(gòu)的觀察可視化OLAP功能交互操作典型的OLAP操作上卷(上鉆):

匯總數(shù)據(jù)通過爬升到更高的層級(jí)或者是減少維度下鉆(下卷):

與上卷相反從更高層級(jí)的匯總到更低層級(jí)的匯總或者使數(shù)據(jù)詳細(xì)化，或者引進(jìn)新的維度切片和切塊:

在一個(gè)或更多的維度上投射或選擇旋轉(zhuǎn)(rotate):

重新定位立方體,可視化,3D到一系列的2D平面其他操作交叉探查:

涉及不止一個(gè)事實(shí)表鉆取:

從立方體的最底層到它后端的相關(guān)表(用SQL)典型的OLAP操作(1)Rollup上卷(drill-up上鉆):匯總數(shù)據(jù)通過爬升到更高的層級(jí)(減少維度)

roll-uponlocationfromcitiestocountiesQ1Q2Q3Q4ChicagoNewYorkVancouverTVCDPC710820402471605Q1Q2Q3Q4USACanada1181605TVCDPC典型的OLAP操作(2)Rolldown下卷(Drilldown下鉆):與上卷相反從更高層級(jí)的匯總到更低層級(jí)的匯總或者使數(shù)據(jù)詳細(xì)化，或者引進(jìn)新的維度

drill-downontimefromquarterstomonthsQ1Q2Q3Q4ChicagoNewYorkVancouverTVCDPC710820402471605TVCDPCNewYorkVancouverChicagoJanFebMarAprMayJunJulAugSepOctNovDec102150150典型的OLAP操作(3)Slice(切片)anddice(切塊):

投射和選擇

Q1Q2Q3Q4ChicagoNewYorkVancouverTVCD