數(shù)據(jù)挖掘概念與技巧 課件chapter 2. data warehouse and olap technology for data mining

1、Data Mining: Concepts and Techniques Slides for Textbook Chapter 2 Jiawei Han and Micheline KamberIntelligent Database Systems Research LabSchool of Computing Science Simon Fraser University, Canada汛餡辮缸杠迭浙覆疙宋螺雛暖蔬渡巒嗚燃耗間曙秘壟盂酶盈皋萊搭攙叢離數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Min

2、ing數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/20221Data Mining: Concepts and TechniquesChapter 2: Data Warehousing and OLAP Technology for Data MiningWhat is a data warehouse? A multi-dimensional data modelData warehouse architectureData warehouse implementationFur

3、ther development of data cube technologyFrom data warehousing to data mining邢陷艦箍戈搗龐鷗頸歐桶斯郡密牛蠱唬嬸頤殲兆膚焦秒耗第痢蹤醒航呢銑數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/20222Data Mining: Concepts and Techniques

4、What is Data Warehouse?Defined in many different ways, but not rigorously.A decision support database that is maintained separately from the organizations operational databaseSupport information processing by providing a solid platform of consolidated, historical data for analysis.“A data warehouse

5、is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of managements decision-making process.”W. H. InmonData warehousing:The process of constructing and using data warehouses佩昭榆峻殷及蛛垣柒頸盂只薄陳嘗盜稀節(jié)見咱礙駝添剩鉗皖崗擊無麥脾羽數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Techn

6、ology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/20223Data Mining: Concepts and TechniquesData WarehouseSubject-OrientedOrganized around major subjects, such as customer, product, sales.Focusing on the modeling and analysis of data for decision maker

7、s, not on daily operations or transaction processing.Provide a simple and concise view around particular subject issues by excluding data that are not useful in the decision support process.旺鈞戮縣鄉(xiāng)惰憂塹漸矽壕擋倘堰茫昌輥宴零蘋警碧劣泵哺判胡僑巡海顱寵數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概

8、念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/20224Data Mining: Concepts and TechniquesData WarehouseIntegratedConstructed by integrating multiple, heterogeneous data sourcesrelational databases, flat files, on-line transaction recordsData cleaning and data integration tec

9、hniques are applied.Ensure consistency in naming conventions, encoding structures, attribute measures, etc. among different data sourcesE.g., Hotel price: currency, tax, breakfast covered, etc.When data is moved to the warehouse, it is converted. 瞬獰秋污猛謙哼桶課欠迷突記怨掠蟹不喂哀戲明標(biāo)刻當(dāng)倆數(shù)篷寒露拾匝兒數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2

10、. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/20225Data Mining: Concepts and TechniquesData WarehouseTime VariantThe time horizon for the data warehouse is significantly longer than that of operational systems.Operat

11、ional database: current value data.Data warehouse data: provide information from a historical perspective (e.g., past 5-10 years)Every key structure in the data warehouseContains an element of time, explicitly or implicitlyBut the key of operational data may or may not contain “time element”.辦腥洋蘆嗎烙菩

12、唇準(zhǔn)論攤葫本吭盯鉀魯都喲適疥恬墾寒歪宦雍抉朋墾閩賬數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/20226Data Mining: Concepts and TechniquesData WarehouseNon-VolatileA physically separate store of data transformed from the

13、operational environment.Operational update of data does not occur in the data warehouse environment.Does not require transaction processing, recovery, and concurrency control mechanismsRequires only two operations in data accessing: initial loading of data and access of data.柿猖油到肆沃您擇虹忻羽舜佰須磊禁桐丹蠟稽樟懷熾上

14、繭園遂岔神贅事瑩數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/20227Data Mining: Concepts and TechniquesData Warehouse vs. Heterogeneous DBMSTraditional heterogeneous DB integration: Build wrappers/mediat

15、ors on top of heterogeneous databases Query driven approachWhen a query is posed to a client site, a meta-dictionary is used to translate the query into queries appropriate for individual heterogeneous sites involved, and the results are integrated into a global answer setComplex information filteri

16、ng, compete for resourcesData warehouse: update-driven, high performanceInformation from heterogeneous sources is integrated in advance and stored in warehouses for direct query and analysis崩塔抱扳閃啡窄處警葉巳墩巒奢欺細(xì)茂恩廈觀慈下辛濫秦芭手舟懂鬧聊耽數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概

17、念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/20228Data Mining: Concepts and TechniquesData Warehouse vs. Operational DBMSOLTP (on-line transaction processing)Major task of traditional relational DBMSDay-to-day operations: purchasing, inventory, banking, manufacturing, pay

18、roll, registration, accounting, etc.OLAP (on-line analytical processing)Major task of data warehouse systemData analysis and decision makingDistinct features (OLTP vs. OLAP):User and system orientation: customer vs. marketData contents: current, detailed vs. historical, consolidatedDatabase design:

19、ER + application vs. star + subjectView: current, local vs. evolutionary, integratedAccess patterns: update vs. read-only but complex queries重枕毆轅鍵乏些丫耪豹烽褲擦咎芭墟軀頸險(xiǎn)李網(wǎng)左溝呂瑯眷軸匠范統(tǒng)運(yùn)調(diào)數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technol

20、ogy for Data Mining7/15/20229Data Mining: Concepts and TechniquesOLTP vs. OLAP鄧民娃凝檔焦賢滅櫻避雹硝勸子卸約夕祥挖辱頤淑僵冷熙儉佬迎俗怪豎餓數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202210Data Mining: Concepts and Techniq

21、uesWhy Separate Data Warehouse?High performance for both systemsDBMS tuned for OLTP: access methods, indexing, concurrency control, recoveryWarehousetuned for OLAP: complex OLAP queries, multidimensional view, consolidation.Different functions and different data:missing data: Decision support requir

22、es historical data which operational DBs do not typically maintaindata consolidation: DS requires consolidation (aggregation, summarization) of data from heterogeneous sourcesdata quality: different sources typically use inconsistent data representations, codes and formats which have to be reconcile

23、d蓬陳筋貍桑擊氯埔哺形旺灘丫微尋稚壓頰劉婿合舔茲闊津路程特呆濘財(cái)滯數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202211Data Mining: Concepts and TechniquesChapter 2: Data Warehousing and OLAP Technology for Data MiningWhat is a d

24、ata warehouse? A multi-dimensional data modelData warehouse architectureData warehouse implementationFurther development of data cube technologyFrom data warehousing to data mining踐拖片鴨義彎蓋醬作秒城寡午嚴(yán)繕塘封連熟蟲惦拍轍伺畏軒郊鄂潭挽渙噬數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Cha

25、pter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202212Data Mining: Concepts and TechniquesFrom Tables and Spreadsheets to Data CubesA data warehouse is based on a multidimensional data model which views data in the form of a data cubeA data cube, such as sales, allows data to be model

26、ed and viewed in multiple dimensionsDimension tables, such as item (item_name, brand, type), or time(day, week, month, quarter, year) Fact table contains measures (such as dollars_sold) and keys to each of the related dimension tablesIn data warehousing literature, an n-D base cube is called a base

27、cuboid. The top most 0-D cuboid, which holds the highest-level of summarization, is called the apex cuboid. The lattice of cuboids forms a data cube.拆吵待嘲劉阜癸改暫他體僥配獵倦洋歐協(xié)敬襖霞洗何凈水慈烴衛(wèi)你逝乓陷數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP

28、 Technology for Data Mining7/15/202213Data Mining: Concepts and TechniquesCube: A Lattice of Cuboidsalltimeitemlocationsuppliertime,itemtime,locationtime,supplieritem,locationitem,supplierlocation,suppliertime,item,locationtime,item,suppliertime,location,supplieritem,location,suppliertime, item, loc

29、ation, supplier0-D(apex) cuboid1-D cuboids2-D cuboids3-D cuboids4-D(base) cuboid延募靜咽茬愈關(guān)甚續(xù)愈曲黍銑街洞吩瘍炬壩汲逮擠鋅喧泥屯遲敏愚寞夜候數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202214Data Mining: Concepts and Techn

30、iquesConceptual Modeling of Data WarehousesModeling data warehouses: dimensions & measuresStar schema: A fact table in the middle connected to a set of dimension tables Snowflake schema: A refinement of star schema where some dimensional hierarchy is normalized into a set of smaller dimension tables

31、, forming a shape similar to snowflakeFact constellations: Multiple fact tables share dimension tables, viewed as a collection of stars, therefore called galaxy schema or fact constellation 芭叛值瘧隅苫羽偏菜糖乳棒龐郎澈碼但菊擋澇溯弊沈榜授物篩懈拒濁蝎馳數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概

32、念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202215Data Mining: Concepts and TechniquesExample of Star Schema time_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcityprovince_or_streetcountrylocationSales Fact Table time_key item_key branch_key location_key un

33、its_sold dollars_sold avg_salesMeasuresitem_keyitem_namebrandtypesupplier_typeitembranch_keybranch_namebranch_typebranch稻珊柜醫(yī)沮移云札柯鐮作瑞青戀澆峪效在式歉水壯鉑囂祟悲咨話遂嗽壘芝數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/

34、15/202216Data Mining: Concepts and TechniquesExample of Snowflake Schematime_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcity_keylocationSales Fact Table time_key item_key branch_key location_key units_sold dollars_sold avg_salesMeasuresitem_keyitem_namebrandtypesupplier_keyitembranch

35、_keybranch_namebranch_typebranchsupplier_keysupplier_typesuppliercity_keycityprovince_or_streetcountrycity翰樞臟閹斜紹翠王魂棵撈抱甕逸惱駱箭柏利隴匯蔥煤闡州冤涵雨敦帳脅展數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202217Data

36、Mining: Concepts and TechniquesExample of Fact Constellationtime_keydayday_of_the_weekmonthquarteryeartimelocation_keystreetcityprovince_or_streetcountrylocationSales Fact Tabletime_key item_key branch_key location_key units_sold dollars_sold avg_salesMeasuresitem_keyitem_namebrandtypesupplier_typei

37、tembranch_keybranch_namebranch_typebranchShipping Fact Tabletime_key item_key shipper_key from_location to_location dollars_cost units_shippedshipper_keyshipper_namelocation_keyshipper_typeshipper迢漿坍每悸熾疽俏麗黍拭鹵車令堡躬?jiǎng)?wù)獻(xiàn)爭詭敦霄輩鱗笆瀕抹攘憐斃剔緩數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Minin

38、g數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202218Data Mining: Concepts and TechniquesA Data Mining Query Language, DMQL: Language PrimitivesCube Definition (Fact Table)define cube : Dimension Definition ( Dimension Table )define dimension as ()Special Case (Shared

39、 Dimension Tables)First time as “cube definition”define dimension as in cube 枉秋眉僵尾溜著撾孕叛諱樟鄉(xiāng)責(zé)趣靴兒爐霹屢鳴儈八坊背術(shù)此微錐卞客落數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202219Data Mining: Concepts and Techniqu

40、esDefining a Star Schema in DMQLdefine cube sales_star time, item, branch, location:dollars_sold = sum(sales_in_dollars), avg_sales = avg(sales_in_dollars), units_sold = count(*)define dimension time as (time_key, day, day_of_week, month, quarter, year)define dimension item as (item_key, item_name,

41、brand, type, supplier_type)define dimension branch as (branch_key, branch_name, branch_type)define dimension location as (location_key, street, city, province_or_state, country)狡勝折助悲傲疆嚼戚猩窺浙短痘漁阿鑼論習(xí)餃光飲遼孫糟跺勝腳蔓椅拎布數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapte

42、r 2. Data Warehouse and OLAP Technology for Data Mining7/15/202220Data Mining: Concepts and TechniquesDefining a Snowflake Schema in DMQLdefine cube sales_snowflake time, item, branch, location:dollars_sold = sum(sales_in_dollars), avg_sales = avg(sales_in_dollars), units_sold = count(*)define dimen

43、sion time as (time_key, day, day_of_week, month, quarter, year)define dimension item as (item_key, item_name, brand, type, supplier(supplier_key, supplier_type)define dimension branch as (branch_key, branch_name, branch_type)define dimension location as (location_key, street, city(city_key, province

44、_or_state, country)統(tǒng)桅夕署枕聳帶箋撕侮岸授埂數(shù)素蛋綴氈香滁皿癥邦侍衷蓉祖孕暖虛窟讀數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202221Data Mining: Concepts and TechniquesDefining a Fact Constellation in DMQLdefine cube sales t

45、ime, item, branch, location:dollars_sold = sum(sales_in_dollars), avg_sales = avg(sales_in_dollars), units_sold = count(*)define dimension time as (time_key, day, day_of_week, month, quarter, year)define dimension item as (item_key, item_name, brand, type, supplier_type)define dimension branch as (b

46、ranch_key, branch_name, branch_type)define dimension location as (location_key, street, city, province_or_state, country)define cube shipping time, item, shipper, from_location, to_location:dollar_cost = sum(cost_in_dollars), unit_shipped = count(*)define dimension time as time in cube salesdefine d

47、imension item as item in cube salesdefine dimension shipper as (shipper_key, shipper_name, location as location in cube sales, shipper_type)define dimension from_location as location in cube salesdefine dimension to_location as location in cube sales搜溜礙渣馮輯恨熱匠愛衷返救邵泳瓊騰轎匡嘛容啦瑪跪破斂散凱蕉侖媒瞅數(shù)據(jù)挖掘概念與技術(shù) 課件Chapte

48、r 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202222Data Mining: Concepts and TechniquesMeasures: Three Categoriesdistributive: if the result derived by applying the function to n aggregate values is the same as t

49、hat derived by applying the function on all the data without partitioning.E.g., count(), sum(), min(), max().algebraic: if it can be computed by an algebraic function with M arguments (where M is a bounded integer), each of which is obtained by applying a distributive aggregate function.E.g., avg(),

50、 min_N(), standard_deviation().holistic: if there is no constant bound on the storage size needed to describe a subaggregate. E.g., median(), mode(), rank().怠肄算破寂封滁葷俠靜嗆繳濺額撿種燒猩疲蕩惰梁臆嘉雙太簡舔適架沛乘數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse

51、and OLAP Technology for Data Mining7/15/202223Data Mining: Concepts and TechniquesA Concept Hierarchy: Dimension (location)allEuropeNorth_AmericaMexicoCanadaSpainGermanyVancouverM. WindL. Chan.allregionofficecountryTorontoFrankfurtcity磊慘惡風(fēng)礬表穴傾梧擋凋洪樣維萄嗜庶曳頃筋整轅仰白屹殷泅頰凈說廠貶數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Wareh

52、ouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202224Data Mining: Concepts and TechniquesView of Warehouses and HierarchiesSpecification of hierarchiesSchema hierarchyday month quarter; week yearSet_grouping hierarchy1.10 inexpen

53、sive泌刁估諜驕翰肢爭圓含申諺始蛹倡婉斬實(shí)萎狙賣嘔澀雀界貨絮便嚇給嗎傭數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202225Data Mining: Concepts and TechniquesMultidimensional DataSales volume as a function of product, month, and

54、regionProductRegionMonthDimensions: Product, Location, TimeHierarchical summarization pathsIndustry Region YearCategory Country QuarterProduct City Month Week Office Day破轟齡墮焰淑涸叢塑章敘玻符撩杖槽敖談音熊蒲缽夠練晦鬼右厘蕉盼混亞數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Dat

55、a Warehouse and OLAP Technology for Data Mining7/15/202226Data Mining: Concepts and TechniquesA Sample Data CubeTotal annual salesof TV in U.S.A.DateProductCountryAll, All, Allsumsum TVVCRPC1Qtr2Qtr3Qtr4QtrU.S.ACanadaMexicosum勝旺瘍遂丫轄淆霄某郴犯屯獄虞究介宿砍畔咳濁自酮彰鱗吱淘干己豺芝裂數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and

56、OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202227Data Mining: Concepts and TechniquesCuboids Corresponding to the Cubeallproductdatecountryproduct,dateproduct,countrydate, countryproduct, date, country0-D(apex) cuboid1-D cuboids2-D cu

57、boids3-D(base) cuboid趴磁輥雍舍炎扇碧磕殘落路刻伊隘斤褪宋干騰咸啞王固去甕烽款因瓣庇和數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202228Data Mining: Concepts and TechniquesBrowsing a Data CubeVisualizationOLAP capabilitiesInte

58、ractive manipulation癥澀秦勁朽肩寒完庇告竟鉑聾雍烈饅資孺罵勿胡護(hù)雇侗嘔焉雁躲中僳酞腔數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining7/15/202229Data Mining: Concepts and TechniquesTypical OLAP OperationsRoll up (drill-up): summarize da

59、taby climbing up hierarchy or by dimension reductionDrill down (roll down): reverse of roll-upfrom higher level summary to lower level summary or detailed data, or introducing new dimensionsSlice and dice: project and select Pivot (rotate): reorient the cube, visualization, 3D to series of 2D planes

60、.Other operationsdrill across: involving (across) more than one fact tabledrill through: through the bottom level of the cube to its back-end relational tables (using SQL)改塢毗即溫悶注愈袖彤耍臣薦宵彈彈盡洋兔稍舟飾歌貼孺旋舅遣惶蜜咐弛數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. Data Warehouse and OLAP Technology for Data Mining數(shù)據(jù)挖掘概念與技術(shù) 課件Chapter 2. D