1、XuXinPresalesConsultantOracle(China) Co.,Ltd.數據倉庫庫的概念念What is.數據倉庫庫 (DataWarehouse)/ 數據據集市(Data Mart)決策支持持系統(tǒng)(DecisionSupport System)聯機分析析處理(OLAP )/ROLAP/ MOLAP元數據(Meta Data)分析指標標 (Measure) /維 (Dimension )星型模型型 (StarSchema)/ 雪花花模型(Snow Schema)數據鉆入入 /數數據鉆鉆出( Drill Down/DrillUp)表旋轉(TableRotation)數據挖掘掘

2、(DataMining)數據倉庫庫幾大功功能Query/ReportDrillup/DrillDownCompareExceptionForcast,WhatifData Mining數據倉庫庫實施方方法建立數據據倉庫需需要考慮慮的因素素擴展性靈活性集成性可靠性數據倉庫庫專家的的建議需要業(yè)務務人員的的積極參參與通過原型型設計驗驗證需求求確定數據據倉庫的的范圍，不要試試圖Warehouse所有數據據為不同需需求選擇擇合適工工具控制風險險利用外部部Consultant的經驗重點放在在不同系系統(tǒng)的集集成建立數據據倉庫舉舉例UseaBuildingEstateOLTPdatabaseasanexamp

3、le to illustratethe concepts andhowtobuildasuccessful DataWarehouse which usedtocheckand forecast therentalrateandsellamountinHongKong.步驟1：確定數數據倉庫庫的問題題范圍列出4月月份香港港地區(qū)每每日房屋屋銷售情情況找出銷售售額大于于4百萬萬的居民民住宅項項目比較Whampoo和Kornhill地區(qū)上月月銷售情情況找出售屋屋數量最最多的前前3個地地區(qū)截止到當當月的累累計銷售售數量用圖表反反映最佳佳銷售模模式時間序列列分析確定數據據倉庫的的問題范范圍確定業(yè)務務需求

4、和和用戶需需求:用戶查詢詢執(zhí)行的的頻度系統(tǒng)保留留數據的的年限用戶主要要希望從從哪些角角度,哪哪些層層次分析析數據數據源是是哪些系系統(tǒng)步驟2:選選擇合適適的軟硬硬件平臺臺可靠的供供應商數據建模模和管理理工具易用性開放集中管理理性能并行處理理選擇數據據庫平臺臺的依據據：前3位的的考慮因因素:易用性92.4%集中管理理65.2%可靠的供供應商65.2%數據倉庫庫的考慮慮因素(Source:Data WarehouseInstitute-February96)MOLAP還是ROLAP？Client interfaceMetadataOLAP engineMDDBDataWarehouseOperati

5、onalDataData loadingOLAP serverSQL pass throughOLAP engineOLAP serverDataWarehouseMetadataClient interfaceMOLAPROLAPMiddle-tier serverCLIENTDatabase serverSQLROLAP和MOLAP的功能區(qū)區(qū)別Legacy/Operational/External SystemsTransactionSystemsDecisionSupportSystemsKnowledge WorkersStrategicTacticalWhat-IfAnalysisC

6、ustomizedApplicationsDrill & PivotAnalysisForecastingModelingTime-SeriesQuery &ReportingMOLAPROLAPMDBRDBMSData Cachelinkage步驟3：根據需需要創(chuàng)建建新的實實體#Code_noNo_of_transactionConstructor_IDDeveloper_IDBuildingdatePurchasedatePurchasepriceAddressAreaApartment#Code_no#Transaction_noName/CompanyHKIDContact Phone

7、#Contact AddressPurchaseDatePurchasePriceOwner#Code_no#Flat#Transaction_noNameHKIDOccupy_type(P,R)Contact Phone#Contact AddressDatePriceOccupantContractor_IDCompany NameAddressContact Phone#Constructor#Code_no#FlatNo_of_transTypeFloorArea (Building)Area (Actual)Flat DetailsDeveloper_IDCompany NameAd

8、dressContact Phone#DeveloperDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousing Types步驟4:確確定維表表刪除不不必要的的表#Code_no No_of_transaction Constructor_ID Developer_ID Building date Purchase date Purchase price Address Area Apartment#Code_no#Transaction_no Name/

9、Company HKID Contact Phone# Contact AddressPurchase Date Purchase PriceOwner#Code_no#Flat#Transaction_noNameHKIDOccupy_type (P,R)Contact Phone#Contact AddressDatePriceOccupantContractor_IDCompany NameAddressContact Phone#Constructor#Code_no#Flat No_of_trans Type Floor Area (Building) Area (Actual)Fl

10、at DetailsDeveloper_IDCompany NameAddressContact Phone#DeveloperDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousing Types步驟5:建建立層次次結構Date1-Jan-9413-Jun-9512-Jan-9612-Apr-9615-Apr-9620-Oct-9620-Oct-9612-Dec-961-Jan-9731-Mar-9715-Apr-97?.TimeYearQuarterMo

11、nthDayTime Hierarchy步驟6:確確定屬性性ClassSSMMMLLLSMLSMArea sq. m10-39.940-49.950-69.970-99.9100-129.9130-159.9160-199.9Over 20010-129.9130-299.9Over 30010-49.950-129.9SizeABCDETypeDomesticIndustrialShops & CommercialOthers/MiscArea sq.m2563035125400810400300100210300?.TypeSizeAreaClass:Attributes ofHousin

12、g TypeHousing TypeOccupantHousing Typedimension lookuptableAttributes步驟7:建建立FactTable，確定合適適的粒度度TimeLocationTypeAreaOccupantNamePurchasePriceRent?.SalesFactTable步驟8:建建立數據據倉庫模模型BuildingEstateOLTP EnvironmentTimeLocationTypeAreaOccupantNamePurchasePriceRent?.SalesFact TableDayMonthQuarterYearTimeTerrit

13、oryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousing Types#Code_noNo_of_transactionConstructor_IDDeveloper_IDBuildingdatePurchasedatePurchasepriceAddressAreaApartment#Code_no#Transaction_noName/CompanyHKIDContact Phone#Contact AddressPurchaseDatePurchasePriceOwner#Code_no#Flat#Trans

14、action_noNameHKIDOccupy_type(P,R)Contact Phone#Contact AddressDatePriceOccupantContractor_IDCompany NameAddressContact Phone#Constructor#Code_no#FlatNo_of_transHousing TypeFloorArea (Building)Area (Actual)Flat DetailsDeveloper_IDCompany NameAddressContact Phone#DeveloperTransformBuildingEstateData W

15、arehouseOLAP Environment步驟9:數數據倉庫庫模型優(yōu)優(yōu)化TerritoryDistrictTimeLocationTypeAreaOccupantNamePurchasePriceRent?.SalesFact TableTypeSizeAreaDistrictDiatrictRegionRegionRegionBuilding/EstateYearQuarterQuarterYearQuarterMonthQuarterMonthDayMonthDayHousing TypesTimeLocationTypeAreaOccupantNamePurchasePriceRe

16、nt?.SalesFact TableDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousing TypesStar schemaSnowflakeschema數據倉庫庫設計優(yōu)優(yōu)化的原原則避免數據據實時匯匯總(建立匯匯總表)減少表連連接操作作 (不不要超過過3-5個)用IDcode作關鍵字字減少I/O競爭利用分區(qū)區(qū)技術提提高性能能和可管管理性估算數據據倉庫容容量的算算法X = size of one row in the fact table Therefore, Sales Fact Table = 98 TimeLocationTypeAreaOccupant NamePurchase PriceRent?.Sales Fact TableDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographic LocationTypeSizeAreaHousing TypesBuilding Property DW DesignDimensionEstimated#Time96 monthHou