數(shù)據(jù)倉庫概念

第一頁，共二十九頁。XuXinPresalesConsultantOracle(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)表旋轉(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？ClientinterfaceMetadataOLAPengineMDDBDataWarehouseOperationalDataDataloadingOLAPserverSQLpassthroughOLAPengineOLAPserverDataWarehouseMetadataClientinterfaceMOLAPROLAPMiddle-tierserverCLIENTDatabaseserverSQL第十四頁，共二十九頁。ROLAP和MOLAP的功能區(qū)別Legacy/Operational/ExternalSystemsTransactionSystemsDecisionSupportSystemsKnowledgeWorkersStrategicTacticalWhat-IfAnalysisCustomizedApplicationsDrill&PivotAnalysisForecastingModelingTime-SeriesQuery&ReportingMOLAPROLAPMDBRDBMSDataCachelinkage第十五頁，共二十九頁。步驟3：根據(jù)需要創(chuàng)建新的實體#Code_noNo_of_transactionConstructor_IDDeveloper_IDBuildingdatePurchasedatePurchasepriceAddressAreaApartment#Code_no#Transaction_noName/CompanyHKIDContactPhone#ContactAddressPurchaseDatePurchasePriceOwner#Code_no#Flat#Transaction_noNameHKIDOccupy_type(P,R)ContactPhone#ContactAddressDatePriceOccupantContractor_IDCompanyNameAddressContactPhone#Constructor#Code_no#FlatNo_of_transTypeFloorArea(Building)Area(Actual)FlatDetailsDeveloper_IDCompanyNameAddressContactPhone#DeveloperDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousingTypes第十六頁，共二十九頁。步驟4:確定維表刪除不必要的表#Code_noNo_of_transactionConstructor_IDDeveloper_IDBuildingdatePurchasedatePurchasepriceAddressAreaApartment#Code_no#Transaction_noName/CompanyHKIDContactPhone#ContactAddressPurchaseDatePurchasePriceOwner#Code_no#Flat#Transaction_noNameHKIDOccupy_type(P,R)ContactPhone#ContactAddressDatePriceOccupantContractor_IDCompanyNameAddressContactPhone#Constructor#Code_no#FlatNo_of_transTypeFloorArea(Building)Area(Actual)FlatDetailsDeveloper_IDCompanyNameAddressContactPhone#DeveloperDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousingTypes第十七頁，共二十九頁。步驟5:建立層次結構Date1-Jan-9413-Jun-9512-Jan-9612-Apr-9615-Apr-9620-Oct-9620-Oct-9612-Dec-961-Jan-9731-Mar-9715-Apr-97?.TimeYearQuarterMonthDayTimeHierarchy第十八頁，共二十九頁。步驟6:確定屬性ClassSSMMMLLLSMLSMAreasq.m10-39.940-49.950-69.970-99.9100-129.9130-159.9160-199.9Over20010-129.9130-299.9Over30010-49.950-129.9SizeABCDETypeDomesticIndustrialShops&CommercialOthers/MiscAreasq.m2563035125400810400300100210300?.TypeSizeAreaClass:AttributesofHousingTypeHousingTypeOccupantHousingTypedimensionlookuptableAttributes第十九頁，共二十九頁。步驟7:建立FactTable，確定合適的粒度TimeLocationTypeAreaOccupantNamePurchasePriceRent?.SalesFactTable第二十頁，共二十九頁。步驟8:建立數(shù)據(jù)倉庫模型BuildingEstateOLTPEnvironmentTimeLocationTypeAreaOccupantNamePurchasePriceRent?.SalesFactTableDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousingTypes#Code_noNo_of_transactionConstructor_IDDeveloper_IDBuildingdatePurchasedatePurchasepriceAddressArea

Apartment#Code_no#Transaction_noName/CompanyHKIDContactPhone#ContactAddressPurchaseDatePurchasePriceOwner#Code_no#Flat#Transaction_noNameHKIDOccupy_type(P,R)ContactPhone#ContactAddressDatePriceOccupantContractor_IDCompanyNameAddressContactPhone#Constructor#Code_no#FlatNo_of_transHousingTypeFloorArea(Building)Area(Actual)FlatDetailsDeveloper_IDCompanyNameAddressContactPhone#DeveloperTransformBuildingEstateDataWarehouseOLAPEnvironment第二十一頁，共二十九頁。步驟9:數(shù)據(jù)倉庫模型優(yōu)化TerritoryDistrictTimeLocationTypeAreaOccupantNamePurchasePriceRent?.SalesFactTableTypeSizeAreaDistrictDiatrictRegionRegionRegionBuilding/EstateYearQuarterQuarterYearQuarterMonthQuarterMonthDayMonthDayHousingTypesTimeLocationTypeAreaOccupantNamePurchasePriceRent?.SalesFactTableDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousingTypesStarschemaSnowflakeschema第二十二頁，共二十九頁。數(shù)據(jù)倉庫設計優(yōu)化的原則避免數(shù)據(jù)實時匯總(建立匯總表)減少表連接操作(不要超過3-5個)用IDcode作關鍵字減少I/O競爭利用分區(qū)技術提高性能和可管理性第二十三頁，共二十九頁。估算數(shù)據(jù)倉庫容量的算法X=sizeofonerowinthefacttableTherefore,SalesFactTable=98

TimeLocationTypeAreaOccupantNamePurchasePriceRent?.SalesFactTableDayMonthQuarterYearTimeTerritoryDistrictRegionBuilding/EstateGeographicLocationTypeSizeAreaHousingTypesBuildingPropertyDWDesignDimensionEstimated#Time96monthHousingTyp