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

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

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

時間，項目，地點，供應(yīng)者

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

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

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

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

維度：時間，項目，地點，品牌

維度表：

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ù)立方家庭娛樂計算機電話安全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ù)倉庫模型:維度&層組星型模式:中間的事實表和一組維度表相連雪花模式:是星型模式的改進，一些維度層級標(biāo)準(zhǔn)化成一組更小的維度表，形成類似雪花的形狀Factconstellations（事實星座）:多個事實表共享維度表，看起來像星星的集合，因此被稱為星系模式或事實星座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ù)集定義(事實表)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定義事實星座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一個概念層級:維度一個概念層級定義了從一套更低級別的概念到更高、更一般的概念的映射序列。 類別:屬性的層級:地點,省,村屬性值的層級或分組對于一個給定的維度，或許會有不止一個概念層級.一個概念層級:維度(地點)allEuropeNorth_AmericaMexicoCanadaSpainGermanyVancouverM.WindL.Chan..................全部地區(qū)辦公室國家TorontoFrankfurt城市多維度數(shù)據(jù)作為產(chǎn)品，月份和地區(qū)的一個函數(shù)的銷售量ProductRegionMonth維度:產(chǎn)品,地點,時間層級匯總路徑IndustryRegionYearCategoryCountryQuarterProductCityMonthWeekOfficeDay一個數(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ù)倉庫和層級結(jié)構(gòu)的觀察可視化OLAP功能交互操作典型的OLAP操作上卷(上鉆):

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

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

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

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

涉及不止一個事實表鉆取:

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

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

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

投射和選擇

Q1Q2Q3Q4ChicagoNewYorkVancouverTVCD