Oracle的数据仓库概念中文ppt28页.ppt

,Xu Xin,Presales Consultant Oracle (China) Co.,Ltd.,数据仓库的概念,What is.,数据仓库 ( Data Warehouse ) / 数据集市 ( Data Mart ) 决策支持系统 ( Decision Support System ) 联机分析处理 ( OLAP ) / ROLAP / MOLAP 元数据 ( Meta Data ) 分析指标 ( Measure ) / 维 ( Dimension ) 星型模型 ( Star Schema ) / 雪花模型 ( Snow Schema ) 数据钻入 / 数据钻出 ( Drill Down / Drill Up ) 表旋转 ( Table Rotation ) 数据挖掘 ( Data Mining ),数据仓库几大功能,Query/Report Drill up/ Drill Down Compare Exception Forcast, Whatif Data Mining,数据仓库实施方法,建立数据仓库需要考虑的因素,扩展性 灵活性 集成性 可靠性,数据仓库专家的建议,需要业务人员的积极参与 通过原型设计验证需求 确定数据仓库的范围，不要试图Warehouse所有数据 为不同需求选择合适工具 控制风险 利用外部Consultant的经验 重点放在不同系统的集成,建立数据仓库举例,Use a Building Estate OLTP database as an example to illustrate the concepts and how to build a successful Data Warehouse which used to check and forecast the rental rate and sell amount in Hong Kong.,步骤1：确定数据仓库的问题范围,列出4月份香港地区每日房屋销售情况 找出销售额大于4百万的居民住宅项目 比较 Whampoo 和 Kornhill 地区上月销售情况 找出售屋数量最多的前3个地区 截止到当月的累计销售数量 用图表反映最佳销售模式 时间序列分析,确定数据仓库的问题范围,确定业务需求和用户需求: 用户查询执行的频度 系统保留数据的年限 用户主要希望从哪些角度, 哪些层次分析数据 数据源是哪些系统,步骤2 : 选择合适的软硬件平台,可靠的供应商 数据建模和管理工具 易用性 开放 集中管理 性能 并行处理,选择数据库平台的依据：,前3位的考虑因素 : 易用性 92.4% 集中管理 65.2% 可靠的供应商 65.2%,数据仓库的考虑因素,(Source: Data Warehouse Institute - February 96),MOLAP 还是 ROLAP？,ROLAP 和 MOLAP的功能区别,Transaction Systems,Decision Support Systems,Strategic,Tactical,MDB,RDBMS,Data Cache,linkage,步骤3：根据需要创建新的实体,#Code_no,No_of_transaction,Constructor_ID,Developer_ID,Building date,Purchase date,Purchase price,Address,Area,Apartment,#Code_no,#Transaction_no,Name/Company,HKID,Contact Phone#,Contact Address,Purchase Date,Purchase Price,Owner,#Code_no,#Flat,#Transaction_no,Name,HKID,Occupy_type (P,R),Contact Phone#,Contact Address,Date,Price,Occupant,Contractor_ID,Company Name,Address,Contact Phone#,Constructor,#Code_no,#Flat,No_of_trans,Type,Floor,Area (Building),Area (Actual),Flat Details,Developer_ID,Company Name,Address,Contact Phone#,Developer,Day,Month,Quarter,Year,Time,Territory,District,Region,Building/Estate,Geographic,Location,Type,Size,Area,Housing Types,步骤 4: 确定维表删除不必要的表,步骤 5: 建立层次结构,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,Time Hierarchy,步骤 6: 确定属性,Type Size Area Class : Attributes of Housing Type,Housing Type,Occupant,Housing Type dimension lookup table,Attributes,步骤 7: 建立FactTable，确定合适的粒度,Time,Location,Type,Area,Occupant Name,Purchase Price,Rent,?.,Sales Fact Table,步骤 8: 建立数据仓库模型,Building Estate OLTP Environment,Time Location Type Area Occupant Name Purchase Price Rent ?.,Sales Fact Table,Day Month Quarter Year,Time,Territory District Region Building/Estate,Geographic Location,Type Size Area,Housing Types,#Code_no No_of_transaction Constructor_ID Developer_ID Building date Purchase date Purchase price Address Area,Apartment,#Code_no #Transaction_no Name/Company HKID Contact Phone# Contact Address Purchase Date Purchase Price,Owner,#Code_no #Flat #Transaction_no Name HKID Occupy_type (P,R) Contact Phone# Contact Address Date Price,Occupant,Contractor_ID Company Name Address Contact Phone#,Constructor,#Code_no #Flat No_of_trans Housing Type Floor Area (Building) Area (Actual),Flat Details,Developer_ID Company Name Address Contact Phone#,Developer,Transform,Building Estate Data Warehouse OLAP Environment,步骤 9: 数据仓库模型优化,Time Location Type Area Occupant Name Purchase Price Rent ?.,Sales Fact Table,Type Size Area,Housing Types,Time Location Type Area Occupant Name Purchase Price Rent ?.,Sales Fact Table,Day Month Quarter Year,Time,Territory District Region Building/Estate,Geographic Location,Type Size Area,Housing Types,Star schema,Snowflake schema,数据仓库设计优化的原则,避免数据实时汇总 (建立汇总表) 减少表连接操作 (不要超过3-5个) 用ID code作关键字 减少I/O竞争 利用分区技术提高性能和可管理性,估算数据仓库容