Oracle数据仓库用户案例

1、 内容内容 q 新西兰电信新西兰电信 法国电信数据仓库法国电信数据仓库 法国电信法国电信(France Telecom) q 欧洲第三大电信运营商，欧洲第三大电信运营商，2000年营年营 业额为业额为337亿欧元亿欧元 q 在五大洲在五大洲220个国家和地区拥有个国家和地区拥有 9100多万客户多万客户 q 下属下属Orange是第二大移动运营商是第二大移动运营商 实际的数据仓库案例实际的数据仓库案例 Winter VLDB Survey 14 Sep 2005 Largest Database Size, Decision Support qSears Teradata4.63 qHCIA

2、Informix 4.50 qWal-Mart Teradata 4.42 qTele Danmark DB2 2.84 qCitiCorp DB2 2.47 qMCI Informix 1.88 qNDC Health Oracle 1.85 qSprint Teradata 1.30 qFord Oracle 1.20 qAcxiom Oracle 1.13 SBCTeradata10.50 First UnionInformix 4.50 Dialog Proprietary 4.25 Telecom ItaliaDB2 3.71 FedEx Teradata 3.70 Office D

3、epotTeradata 3.08 AT 利用分区忽略提高大表的查询性能利用分区忽略提高大表的查询性能 分区忽略分区忽略: 只有相关分区被访问只有相关分区被访问 99-May 99-Apr 99-Feb 99-Jan 99-Mar 99-Jun Sales 系统测试结果系统测试结果 q 数据访问数据访问: 每秒钟每秒钟23到到100个并发查询操作个并发查询操作 95% 的查询在的查询在1秒钟内完成秒钟内完成 系统中使用的系统中使用的Oracle关键技术关键技术 可传输的表空间可传输的表空间 业务系统业务系统 数据仓库数据仓库 Mar Feb Jan Mar Feb q 操作系统文件的直接复操作

4、系统文件的直接复 制制 q 不需要数据的导入不需要数据的导入/导出导出 实现按小时的数据加载 OLTP Data Warehouse Data Marts Archives Information distribution Staging 可传输表空间在数据仓库中的使用可传输表空间在数据仓库中的使用 举例举例 利用可传输的表空间和利用可传输的表空间和 分区技术实现快速数据分区技术实现快速数据 装载装载 详单数据的导出详单数据的导出 q 把需要传输的数据放在单独的表空间把需要传输的数据放在单独的表空间 CREATE TABLE temp_jan_sales CREATE TABLE temp_j

5、an_sales NOLOGGING TABLESPACE ts_temp_sales NOLOGGING TABLESPACE ts_temp_sales AS SELECT AS SELECT * * FROM sales FROM sales WHERE time_id BETWEEN 31-DEC-1999 AND 01-FEB-2000;WHERE time_id BETWEEN 31-DEC-1999 AND 01-FEB-2000; q 导出数据字典导出数据字典 EXP TRANSPORT_TABLESPACE=y EXP TRANSPORT_TABLESPACE=y TABLE

6、SPACES=ts_temp_sales TABLESPACES=ts_temp_sales q 复制数据文件和数据字典到数据仓库服务器复制数据文件和数据字典到数据仓库服务器 详单数据的导入详单数据的导入 q 导入数据字典导入数据字典 IMP TRANSPORT_TABLESPACE=y IMP TRANSPORT_TABLESPACE=y DATAFILES=/db/tempjan.f DATAFILES=/db/tempjan.f TABLESPACES=ts_temp_sales TABLESPACES=ts_temp_sales q 通过分区交换，将新数据加入目标表通过分区交换，将新数

7、据加入目标表 1. 1. ALTER TABLE sales ALTER TABLE sales ADD PARTITION sales_00jan ADD PARTITION sales_00jan VALUES LESS THAN (TO_DATE(01-feb-2000,dd-mon-yyyy); VALUES LESS THAN (TO_DATE(01-feb-2000,dd-mon-yyyy); 2. ALTER TABLE sales 2. ALTER TABLE sales EXCHANGE PARTITION sales_00jan WITH TABLE temp_sales_

8、jan EXCHANGE PARTITION sales_00jan WITH TABLE temp_sales_jan INCLUDING INDEXES WITH VALIDATION; INCLUDING INDEXES WITH VALIDATION; 系统测试结果：数据装载系统测试结果：数据装载 q 数据装载：装载包含数据装载：装载包含5000万条详单的一个分区和万条详单的一个分区和2个个 索引的一个表空间索引的一个表空间 文件传输文件传输: 4 50 数据导入到数据导入到Oracle数据仓库数据仓库: 瞬时瞬时 创建索引：创建索引：750” 预计算及其它运算：预计算及其它运算：14

9、 合计：合计：3226” 物化视图提高汇总数据的访问性能物化视图提高汇总数据的访问性能 按月汇总的按月汇总的 不同种类通话不同种类通话 时长数据时长数据 按地区汇总的按地区汇总的 每月通话时长每月通话时长 查询查询 查询查询 自动重写自动重写 法国法国和意大和意大 利的用户在利的用户在 过去三个月过去三个月 的漫游通话的漫游通话 总时长是多总时长是多 少？少？ 使用使用Oracle内置的内置的SQL分析函数分析函数 q 汇总汇总 ROLLUP & CUBE q 抽样抽样 Sampling q 排名排名 Rank - rank, percentile, ntile, top, bottom q

10、滑动窗口滑动窗口 Moving Window - avg, sum, min, max, count, variance, stddev, firstvalue q 同比同比 Period-over-period comparisons q 时间序列时间序列 Lag q 报表函数报表函数 sum, avg, min, max, variance, stddev, count, ratiotoreport q 统计函数统计函数 covariance, correlation, linear regression TimekeySales Sales_last_year Sales_change

11、98-1 1100 - - . 99-112001100100 99-21500145050 99-317001350250 99-416001700-100 99-518001600200 99-61500145050 99-71300125050 99-814001200200 SELECT timekey, sales, LAG(sales,12) OVER (ORDER BY timekey) as sales_last_year, (sales - sales_last_year) as sales_change FROM sales 举例：使用时间序列函数举例：使用时间序列函数 系

12、统使用情况系统使用情况 q 数据汇总数据汇总: 1亿亿7000万个汇总运算在万个汇总运算在2个小时内完成个小时内完成 不需要专门的服务器不需要专门的服务器 q 数据访问数据访问: 每秒钟每秒钟23到到100个并发查询操作个并发查询操作 95% 的查询在的查询在1秒钟内完成秒钟内完成 数据仓库系统为法国电信带来的好处数据仓库系统为法国电信带来的好处 q 为各个业务部门的用户提供统一的数据视图为各个业务部门的用户提供统一的数据视图 保证数据的一致性和质量保证数据的一致性和质量 提高信息的及时性提高信息的及时性 q 整合系统，节省投资整合系统，节省投资 已经将已经将10个系统整合，未来会整合更多的系

13、统个系统整合，未来会整合更多的系统 q 比原来的专有系统管理更灵活，升级更容易比原来的专有系统管理更灵活，升级更容易 数据仓库系统为法国电信带来的好处数据仓库系统为法国电信带来的好处 q 全面了解客户的使用行为，实现市场细分全面了解客户的使用行为，实现市场细分 q 欺诈行为检测欺诈行为检测 建立欺诈模式库建立欺诈模式库 及时检测客户通话行为发生的突然变化及时检测客户通话行为发生的突然变化, 运用模式运用模式 库进行识别库进行识别 通过预警系统通知相关人员进行调查通过预警系统通知相关人员进行调查 “我们每天装载5亿条话单. Oracle 数据仓库在实际使用中已经被证明非 常稳定、可靠和具有扩展性

14、 Jean-Luc Cochennec France Telecom “从Winter Corporation最近的数据库可 伸缩性调查发现，法国电信的数据仓库 系统无论是从数据库规模和记录数方面， 都是世界上单一数据库中最大的。” Richard Winter, March 2002 “Field Experience with Large Scale Data Warehousing on Oracle” 新西兰电信数据仓库新西兰电信数据仓库 新西兰电信简介新西兰电信简介 q 新西兰最大的综合电信运营商新西兰最大的综合电信运营商 在在新西兰新西兰和和澳洲澳洲拥有拥有210万用户万用户 员工

15、数为员工数为6900多名多名 总资产总资产83亿新西兰元亿新西兰元 年收入年收入55亿新西兰元亿新西兰元 q 业务范围：业务范围： 市话、长途、移动、互联网、宽带、增值业务市话、长途、移动、互联网、宽带、增值业务 项目建设背景项目建设背景 q 电信市场变化电信市场变化 开放的电信市场开放的电信市场 日益增多的竞争对手日益增多的竞争对手 q 公司战略转变公司战略转变 从扩大用户群转变为提高现有用户对企业的贡献度从扩大用户群转变为提高现有用户对企业的贡献度 从大规模营销转向目标市场营销从大规模营销转向目标市场营销 Multiple Legacy Application Systems Same c

16、ustomer held in multiple locations in different formats. 原有系统的状况原有系统的状况 q 各个系统采用不同平台各个系统采用不同平台 q 缺乏数据标准缺乏数据标准 q 数据采集和集成困难数据采集和集成困难 q 多个系统中冗余的数据多个系统中冗余的数据 数据仓库项目概况数据仓库项目概况PROBE PROactive Business Enabler q从从1996年年6月开始建设，月开始建设，1997年建成上线年建成上线 q到到2002年为止，开发了年为止，开发了50多个主题：多个主题： 客户客户, 产品产品, 收入收入, 通话行为通话行为

17、, 市场营销市场营销, 缴费缴费 q用户包括市场部、销售部和财务部等部门用户包括市场部、销售部和财务部等部门1400多人多人 Oracle在项目中的参与情况在项目中的参与情况 q1996 2002： 设计和建设数据仓库设计和建设数据仓库 逐步增加逐步增加50个新的分析应用个新的分析应用 q目前：目前： 与新西兰电信和与新西兰电信和EDS合作合作 实施实施“360 客户视图客户视图” 项目项目 建立数据仓库与业务系统的实时接口建立数据仓库与业务系统的实时接口 实现实时的客户细分实现实时的客户细分 PROBE项目为公司带来的效益有形效益项目为公司带来的效益有形效益 q 增加收入，提高增加收入，提高

18、ARPU q 减少客户流失减少客户流失 q 提高营销活动的响应率提高营销活动的响应率 从从5提高到提高到30 q 降低营销成本和服务成本降低营销成本和服务成本 PROBE项目的投资回报项目的投资回报 q 目标市场营销目标市场营销 每年节省每年节省1200万元万元 q “市场赢回市场赢回”计划计划 每年增加每年增加800万元万元 q 更合理的国际长途收费定价更合理的国际长途收费定价 增加收入与节约成本，每年增加收入与节约成本，每年700万元万元 PROBE为外部客户服务为外部客户服务 q向外部客户出售统计分析结果向外部客户出售统计分析结果Vision： 800客户客户 按地区、时段、服务类型按地

19、区、时段、服务类型 q价格：价格： 每月一次：每月一次：25$/月月 每周一次：每周一次：85$/月月 议程议程 q 项目概况项目概况 q 项目实施方法项目实施方法 q 数据仓库技术方案数据仓库技术方案 建设原则需求驱动建设原则需求驱动 q从多种渠道识别客户从多种渠道识别客户 q实现精确营销实现精确营销 q提升客户价值提升客户价值 业务目标业务目标 q获得客户的全业务视图获得客户的全业务视图 （固话、移动、数据）（固话、移动、数据） q生成营销客户名单，支生成营销客户名单，支 持对活动反馈的收集和分持对活动反馈的收集和分 析析 q实现客户和市场的细分实现客户和市场的细分 项目目标项目目标 建设

20、原则实用性建设原则实用性 紧密配合业务部门的市场经营活动紧密配合业务部门的市场经营活动 q 客户维系：流失客户名单客户维系：流失客户名单 q 营销活动管理：营销活动名单，反馈分析营销活动管理：营销活动名单，反馈分析 q 新业务开发：产品定价，关联分析新业务开发：产品定价，关联分析 发展战略发展战略 规划规划 网络管理网络管理 目标市场目标市场 营销营销 客户满意度客户满意度 销售销售 财务表现财务表现 Customer and Product Information 运营效率运营效率 制订企业发展战略制订企业发展战略 监控其执行情况监控其执行情况 Business Intelligence F

21、or Telecom 建设方法整体规划，分步实施建设方法整体规划，分步实施 需求预测需求预测 能力规划能力规划 投资预测投资预测 通信能力分析通信能力分析 通信质量分析通信质量分析 业务覆盖分析业务覆盖分析 欺诈检测欺诈检测 业务开展业务开展 业务使用业务使用 供应链分析供应链分析 客户流失分析客户流失分析 客服中心分析客服中心分析 服务质量保证服务质量保证 市场细分市场细分 响应模型响应模型 营销活动营销活动ROI分析分析 新产品设计新产品设计 价格模型价格模型 收入总量及变化收入总量及变化 收入结构分析收入结构分析 缴缴/欠费分析欠费分析 整体规划的实现整体规划的实现 q 统一规划：统一规

22、划： 数据模型数据模型 数据获取策略数据获取策略 元数据元数据 确定新增应用的优先级确定新增应用的优先级 效益效益 低低 高高 不可能不可能 下次考虑下次考虑 没有价值没有价值 目标应用目标应用 高高 低低 成本和风险成本和风险 数据仓库的效益、成本和风险数据仓库的效益、成本和风险 严格的投资回报分析严格的投资回报分析 q 效益：效益：我们能通过这个项目的实施获得什么？我们能通过这个项目的实施获得什么？ 有形：增加收入、降低成本有形：增加收入、降低成本 无形：工作效率提高、企业更无形：工作效率提高、企业更“智能化智能化” q 成本：成本：为了实现这些收益需要付出什么代价？为了实现这些收益需要付

23、出什么代价？ 一次性成本一次性成本 持续性成本持续性成本 q 风险：风险：会遇到哪些障碍？会遇到哪些障碍？ 螺旋递增式建设方法螺旋递增式建设方法 q 每次迭代目标明确，快速见效每次迭代目标明确，快速见效 q 滚动投资，每年追加费用是初始投资的滚动投资，每年追加费用是初始投资的1520 应用推广应用推广 q 领导带头使用领导带头使用 q 设置专职的业务分析员设置专职的业务分析员 q 系统使用情况统计系统使用情况统计 q 注重对业务人员的培训注重对业务人员的培训 议程议程 q 项目概况项目概况 q 项目实施方法项目实施方法 q 数据仓库技术方案数据仓库技术方案 PROBE 系统架构和数据流系统架构

24、和数据流 加载 20+内部源系统 (12 CPU, 3Tb disk)Tape, ftp, DB link PROBEPROBE 外部信息源 email, ftp, CD Oracle Designer 元数据库元数据库 Oracle Portal and Discoverer 报表 Oracle 9iAS 抽取 OLAP分析和数据挖掘 内部系统 SQL, and Query Builder DB link, ftp ftp, CD Oracle 9i release 2 外部机构 PROBE 数据模型分层设计数据模型分层设计 内部和外部信息源 加载 Staging Area Feed Con

25、trolFeed Control 任务调度管理 原子层 (relational schema) 访问层 (star / snowflake schema) PROBE 数据挖掘和分析 SQL, and Query Builder Oracle Portal Oracle Discoverer 报表 营销管理 PROBE中的数据功能划分中的数据功能划分 功能区功能区数据模型数据模型使用者使用者用途用途 Staging Area与源数据相似与源数据相似清洗转换的缓冲区清洗转换的缓冲区 Atomic Layer （原子层）原子层） 10的访问量的访问量 关系模型关系模型 轻度非规范化轻度非规范化 1

26、. 专职分析员专职分析员 2. 营销活动主管营销活动主管 自定义分析自定义分析 生成市场活动目标客生成市场活动目标客 户名单户名单(数据挖掘数据挖掘) Access Layer （访问层）访问层） 90的访问量的访问量 汇总数据汇总数据 维度模型维度模型(星型和星型和 雪花型雪花型) 客户经理客户经理 产品经理产品经理 生成统计报表生成统计报表 PROBE项目的演进项目的演进“360 客户视图客户视图” 项目项目 q EDB 消除数据重复和错误消除数据重复和错误 集成原有系统客户数据，建集成原有系统客户数据，建 立统一的客户视图立统一的客户视图(基于基于 Oracle TCA模型模型) q P

27、ROBE 升级为企业数据仓库升级为企业数据仓库(EDW) 实时数据采集和分析结果发实时数据采集和分析结果发 布布 q 引入引入EAI技术技术 NADIS PROBE ICMS EDBEDW EAI Legacy Apps CrossWorlds 9iAS Portal Oracle TCA ASBOASBO ASBO GBO APIAPI ETL Service Element MQ Series JDBC Connector JDBC Connector PROBE 新的系统架构新的系统架构 门户(EIS) 动态查询 Oracle Discoverer 用户访问 Oracle Custome

28、rs Online Oracle 11i CRM Suite EAI ICMS 计费系统 客户, 帐户, 地址 PROBE 群体，关系 Staging Area 实时维度 数据维护 EAI Staging AreaEAI Staging Area 访问层 (star / snowflake schema) 原子层 (relational schema) 联机数据存储联机数据存储 ( (OLTP)OLTP) 下游系统访问下游系统访问 9iAS 构建闭环的信息流构建闭环的信息流 数据仓库数据仓库 计费系统 (3 & 9) 应用规则计 算客户分群 (2生成细分规则 和黄金问题 客户资料/收入/ 产品

29、样本数据 CTI 市场营销活动管 理 (5) 制订营销计 划 (6) 目标客户名 单 (8) 更新客户资料 (7) 客户回答问题 呼叫中心 黄金问题 (4) 客户细分结果 (4) 传递客户 细分结果 Call HandoffCall Handoff 数据挖掘数据挖掘 服务器服务器 数据模型的设计方法数据模型的设计方法 q 从业务需求出发从业务需求出发 q 识别各主题分析所需要的数据识别各主题分析所需要的数据 q 数据探索分析数据源的数据质量数据探索分析数据源的数据质量 q 找出来自多个系统的公共表统一维护找出来自多个系统的公共表统一维护 q 建立原子层数据模型建立原子层数据模型 q 模型非规范化模型非规范化 q 建立访问层数据模型维度模型建立访问层数据模型维度模型 PROBE中的主题域中的主题域 产品 产品使用 网络事件网元 地域 帐务 参与方 客户交互 营销 PROBE中的主题分析中的主题分析 客户特征客户特征 客户细分客户细分 销售机会销售机会 营销活动营销活动 Analysis and Reporting 客户客户 产品产品营销营销收入收入网络网络 产品定购产品定购 产品特征产品特征利用率利用率 流量流量 产品使用产品使用 活动响应活动响应 支付支付 使用量变化使用量变化新客户获