甲骨文《數(shù)據(jù)倉庫概念》28頁.ppt

《甲骨文《數(shù)據(jù)倉庫概念》28頁.ppt》由會員分享，可在線閱讀，更多相關《甲骨文《數(shù)據(jù)倉庫概念》28頁.ppt（28頁珍藏版）》請在裝配圖網(wǎng)上搜索。

,,XuXin,PresalesConsultantOracle(China)Co.,Ltd.,,,數(shù)據(jù)倉庫的概念,Whatis...,數(shù)據(jù)倉庫(DataWarehouse)/數(shù)據(jù)集市(DataMart)決策支持系統(tǒng)(DecisionSupportSystem)聯(lián)機分析處理(OLAP)/ROLAP/MOLAP元數(shù)據(jù)(MetaData)分析指標(Measure)/維(Dimension)星型模型(StarSchema)/雪花模型(SnowSchema)數(shù)據(jù)鉆入/數(shù)據(jù)鉆出(DrillDown/DrillUp)表旋轉(zhuǎn)(TableRotation)數(shù)據(jù)挖掘(DataMining),數(shù)據(jù)倉庫幾大功能,Query/ReportDrillup/DrillDownCompareExceptionForcast,WhatifDataMining,,,數(shù)據(jù)倉庫實施方法,建立數(shù)據(jù)倉庫需要考慮的因素,擴展性靈活性集成性可靠性,數(shù)據(jù)倉庫專家的建議,需要業(yè)務人員的積極參與通過原型設計驗證需求確定數(shù)據(jù)倉庫的范圍，不要試圖Warehouse所有數(shù)據(jù)為不同需求選擇合適工具控制風險利用外部Consultant的經(jīng)驗重點放在不同系統(tǒng)的集成,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,建立數(shù)據(jù)倉庫舉例,UseaBuildingEstateOLTPdatabaseasanexampletoillustratetheconceptsandhowtobuildasuccessfulDataWarehousewhichusedtocheckandforecasttherentalrateandsellamountinHongKong.,步驟1：確定數(shù)據(jù)倉庫的問題范圍,列出4月份香港地區(qū)每日房屋銷售情況找出銷售額大于4百萬的居民住宅項目比較Whampoo和Kornhill地區(qū)上月銷售情況找出售屋數(shù)量最多的前3個地區(qū)截止到當月的累計銷售數(shù)量用圖表反映最佳銷售模式時間序列分析,確定數(shù)據(jù)倉庫的問題范圍,確定業(yè)務需求和用戶需求:用戶查詢執(zhí)行的頻度系統(tǒng)保留數(shù)據(jù)的年限用戶主要希望從哪些角度,哪些層次分析數(shù)據(jù)數(shù)據(jù)源是哪些系統(tǒng),,,,步驟2:選擇合適的軟硬件平臺,可靠的供應商數(shù)據(jù)建模和管理工具易用性開放集中管理性能并行處理,選擇數(shù)據(jù)庫平臺的依據(jù)：,,前3位的考慮因素:易用性92.4%集中管理65.2%可靠的供應商65.2%,數(shù)據(jù)倉庫的考慮因素,(Source:DataWarehouseInstitute-February96),MOLAP還是ROLAP？,ROLAP和MOLAP的功能區(qū)別,,,TransactionSystems,,DecisionSupportSystems,,Strategic,Tactical,,,,MDB,RDBMS,,DataCache,,,,linkage,,步驟3：根據(jù)需要創(chuàng)建新的實體,,,,,,#Code_no,No_of_transaction,Constructor_ID,Developer_ID,Buildingdate,Purchasedate,Purchaseprice,Address,Area,Apartment,,#Code_no,#Transaction_no,Name/Company,HKID,ContactPhone#,ContactAddress,PurchaseDate,PurchasePrice,Owner,,#Code_no,#Flat,#Transaction_no,Name,HKID,Occupy_type(P,R),ContactPhone#,ContactAddress,Date,Price,Occupant,,Contractor_ID,CompanyName,Address,ContactPhone#,Constructor,,#Code_no,#Flat,No_of_trans,Type,Floor,Area(Building),Area(Actual),FlatDetails,,,Developer_ID,CompanyName,Address,ContactPhone#,Developer,,,Day,Month,Quarter,Year,Time,,Territory,District,Region,Building/Estate,Geographic,Location,,,,,Type,Size,Area,HousingTypes,,步驟4:確定維表刪除不必要的表,步驟5:建立層次結(jié)構(gòu),Date,1-Jan-94,13-Jun-95,12-Jan-96,12-Apr-96,15-Apr-96,20-Oct-96,20-Oct-96,12-Dec-96,1-Jan-97,31-Mar-97,15-Apr-97,?.,,,,Time,Year,Quarter,Month,Day,TimeHierarchy,,,,,,步驟6:確定屬性,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,TypeSizeAreaClass:AttributesofHousingType,HousingType,,,Occupant,HousingTypedimensionlookuptable,,,Attributes,步驟7:建立FactTable，確定合適的粒度,,Time,Location,Type,Area,OccupantName,PurchasePrice,Rent,?.,SalesFactTable,步驟8:建立數(shù)據(jù)倉庫模型,BuildingEstateOLTPEnvironment,TimeLocationTypeAreaOccupantNamePurchasePriceRent?.,SalesFactTable,DayMonthQuarterYear,Time,TerritoryDistrictRegionBuilding/Estate,GeographicLocation,,TypeSizeArea,HousingTypes,,,,,#Code_noNo_of_transactionConstructor_IDDeveloper_IDBuildingdatePurchasedatePurchasepriceAddressArea,Apartment,#Code_no#Transaction_noName/CompanyHKIDContactPhone#ContactAddressPurchaseDatePurchasePrice,Owner,#Code_no#Flat#Transaction_noNameHKIDOccupy_type(P,R)ContactPhone#ContactAddressDatePrice,Occupant,Contractor_IDCompanyNameAddressContactPhone#,Constructor,#Code_no#FlatNo_of_transHousingTypeFloorArea(Building)Area(Actual),FlatDetails,,Developer_IDCompanyNameAddressContactPhone#,Developer,,,Transform,BuildingEstateDataWarehouseOLAPEnvironment,,步驟9:數(shù)據(jù)倉庫模型優(yōu)化,TimeLocationTypeAreaOccupantNamePurchasePriceRent?.,SalesFactTable,TypeSizeArea,,,,,,,,HousingTypes,,TimeLocationTypeAreaOccupantNamePurchasePriceRent?.,SalesFactTable,DayMonthQuarterYear,Time,TerritoryDistrictRegionBuilding/Estate,GeographicLocation,,TypeSizeArea,HousingTypes,Starschema,Snowflakeschema,數(shù)據(jù)倉庫設計優(yōu)化的原則,避免數(shù)據(jù)實時匯總(建立匯總表)減少表連接操作(不要超過3-5個)用IDcode作關鍵字減少I/O競爭利用分區(qū)技術提高性能和可管理性,估算數(shù)據(jù)倉庫容量的算法,Estimatedsizeofdatabase=98*96*20*1000*0.75=141.12Mb,步驟10:從業(yè)務系統(tǒng)中抽取數(shù)據(jù)到數(shù)據(jù)倉庫,數(shù)據(jù)抽取的要求：可訪問各種數(shù)據(jù)源可滿足時間要求可滿足數(shù)據(jù)轉(zhuǎn)換要求可檢測源系統(tǒng)中數(shù)據(jù)的變化,步驟11:開發(fā)前端應用,步驟12:數(shù)據(jù)倉庫的管理,安全管理備份和恢復高可用性數(shù)據(jù)時效,