数据挖掘6-5Cluster-HierMeth (16)教材

DataModelReviewthebasicconceptsofdatabaseWhatisadatawarehouse?Amulti-dimensionaldatamodelDatawarehousearchitectureDatawarehouseimplementationFromdatawarehousingtodatamining数据立方体(1)数据仓库基于一个多维度数据模型，以数据立方体的方式看待数据一个数据立方体，例如销售量，允许数据进行建模，并在多个层面观看Dimensiontables（维表）,例如项目(项目名字,品牌,类型),或者时间(天,周,月,季度,年)Facttable（事实表）包含相关维度表的层组（例如销售额）和键

在数据仓库文献中,一个n维基本立方体被称为基本方体。

拥有最高级汇总的最上层的0维方体,被称为顶端立方体。长方体晶格形成了一个数据立方体。数据立方体(2)维度和维度表维度:是一个组织要保留的观点或实体.维度表:是进一步描述一个维度的一组属性.每个维度有可能有与之相联系的一个维度表.

时间，项目，地点，供应者

事实和事实表事实:衡量一个主题事实表:事实的表现.它包含每个相关维表的事实和键名。事实是数值，销售金额DataCube(3)数据立方的维度数量观察到的维度数量.

Sales（itemtimelocationdollars_sold）基本方体:包含所有在数据仓库中可以被观察到的维度的立方体.顶端立方体:

不包含维度的立方体.数据立方:

一个多维度数据模型中的所有立方体.数据立方—OneExample(1)ALLElectronicssales

维度：时间，项目，地点，品牌

维度表：

time(time_keydayday_of_weekmonthquarteryear) item(item_keyitem_namebrandtypesupplier_key)

facttable：(time_keyitem_keybrand_keylocation_keydollars_soldunits_sold)数据立方—OneExample(2)2维数据立方：

location＝”Vancouver”

item(type)Time(quarter)entertainment

computer

security

Q1605825400Q2680920512Q37811026501Q48241120580数据立方—OneExample(3)4维数据立方家庭娱乐计算机电话安全Q1Q2Q3Q4VanciuerTorontoNewYorkChicagoitemtimeLocationsupplierCube:ALatticeofCuboidsalltimeitemlocationsuppliertime,locationtime,supplieritem,locationitem,supplierlocation,suppliertime,item,suppliertime,location,supplieritem,location,supplier0-D(apex)cuboid1-Dcuboids2-Dcuboids3-Dcuboids4-D(base)cuboid数据仓库的概念模型数据仓库模型:维度&层组星型模式:中间的事实表和一组维度表相连雪花模式:是星型模式的改进，一些维度层级标准化成一组更小的维度表，形成类似雪花的形状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的多维数据定义语法多维数据集定义(事实表)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..................全部地区办公室国家TorontoFrankfurt城市多维度数据作为产品，月份和地区的一个函数的销售量ProductRegionMonth维度:产品,地点,时间层级汇总路径IndustryRegionYearCategoryCountryQuarterProductCityMonthWeekOfficeDay一个数据立方样本TotalannualsalesofTVinU.S.A.DateProductCountryAll,All,Allsumsum

TVVCRPC1Qtr2Qtr3Qtr4QtrU.S.ACanadaMexicosumCuboidsCorrespondingtotheCubeallproductdatecountryproduct,dateproduct,countrydate,countryproduct,date,country0-D(apex)cuboid1-Dcuboids2-Dcuboids3-D(base)cuboid数据仓库和层级结构的观察可视化OLAP功能交互操作典型的OLAP操作上卷(上钻):

汇总数据通过爬升到更高的层级或者是减少维度下钻(下卷):

与上卷相反从更高层级的汇总到更低层级的汇总或者使数据详细化，或者引进新的维度切片和切块:

在一个或更多的维度上投射或选择旋转(rotate):

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

涉及不止一个事实表钻取:

从立方体的最底层到它后端的相关表(用SQL)典型的OLAP操作(1)Rollup上卷(drill-up上钻):汇总数据通过爬升到更高的层级(减少维度)

roll-uponlocationfromcitiestocountiesQ1Q2Q3Q4ChicagoNewYorkVancouverTVCDPC710820402471605Q1Q2Q3Q4USACanada1181605TVCDPC典型的OLAP操作(2)Rolldown下卷(Drilldown下钻):与上卷相反从更高层级的汇总到更低层级的汇总或者使数据详细化，或者引进新的维度

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

投射和选择

Q1Q2Q3Q4ChicagoNewYorkVancouverTVCD