保险仓库数据模型设计

1、 2006 FEnet Software Co., Ltd. All Rights Reserved.2021/8/6保密保密保密1安邦安邦BI系统数据模型介绍系统数据模型介绍 2006 FEnet Software Co., Ltd. All Rights Reserved.22021/8/6保密保密保密主主 题题维度建模的基本概念模型概述模型介绍模型运用方法与技巧相关技术问题 2006 FEnet Software Co., Ltd. All Rights Reserved.32021/8/6保密保密保密如何建立分析集市模型如何建立分析集市模型行业经验行业经验工作组和建模工作组和建模业务需

2、求业务需求安邦分析集市模型n业务驱动业务驱动根据固定报表和KPI、持续率、承保理赔、执行力、再保五个数据集市的业务需求,结合BI.Insurance DWM的建模方法, 建立Taiping EDW 分析集市模型 2006 FEnet Software Co., Ltd. All Rights Reserved.42021/8/6保密保密保密分析集市模型概念分析集市模型概念n维度建模维度建模一种非规范化的关系模型由一组属性构成的表所组成表跟表之间的关系通过关键字和外键来定义以良好的可理解性和方便的产生报表来进行数据组织，很少考虑修改的性能通过SQL或者相关的工具实现数据的查询和维护 2006 F

3、Enet Software Co., Ltd. All Rights Reserved.52021/8/6保密保密保密分析集市模型概念分析集市模型概念n事实表（一）事实表（一）每一个事实表通常包含了业务需求所关心的一系列的指标值每一个事实表的行包括具有可加性的数值型的指标值文本事实通常具有不可预见的内容，很难进行分析与维表相连接的外键通常具有两个和两个以上的外键外键之间表示维表之间多对多的关系 2006 FEnet Software Co., Ltd. All Rights Reserved.62021/8/6保密保密保密分析集市模型概念分析集市模型概念n事实表（二）事实表（二）事实表的特征非

4、常的大 包含含几、含几几万甚含几含几千的的记内容相对的窄 列数较少经常发生变化 现实世界中新事件的发生事实表中增加一千的记 典型情况下，仅仅是数据的追加事实表的使用 各类指标值的聚集计算 2006 FEnet Software Co., Ltd. All Rights Reserved.72021/8/6保密保密保密分析集市模型概念分析集市模型概念n维表（一）维表（一）每一张维表对应现实世界中的一个对象或者概念 例如：客、产产、保保、标的、件维表的特征包含了众多描述性的属性列 例保保维表里的销售渠道、保费区间、风险等级等通常情况下，跟事实表相比，行数相对较小 通常 10几千内容相对固定 含乎就

5、是一类查找表 2006 FEnet Software Co., Ltd. All Rights Reserved.82021/8/6保密保密保密分析集市模型概念分析集市模型概念n维表（二）维表（二）维表的应用基于维属性的过滤（切片、切块等）基于维属性的各种聚集操作报表中各类标签的主要来源事实表通过维表进行引用 2006 FEnet Software Co., Ltd. All Rights Reserved.92021/8/6保密保密保密分析集市模型概念分析集市模型概念 Facts属性个数少（窄）的记行数多（大） 数值型指标随着时间的推移，数据增长 Dimensions 属性个数多（宽）的记行

6、数少（小）描述性属性静态的，很少发生变化 n事实表与维表的比较事实表与维表的比较 2006 FEnet Software Co., Ltd. All Rights Reserved.102021/8/6保密保密保密分析主题域数据分析主题域数据 (Analytical Subject Areas)(Analytical Subject Areas) n通过事实（Facts）和维度（Dimensions）模型方式表达，为典型保险分析应用提供分析型数据架构，为数据集市提供统一的指标和维度，为数据应用提供统一的数据信息。n分析主题域数据，将业务需求阶段定义的分析主题域，按照维度建模方法，格式化成符合维

7、度事实表的星型模型或雪花模型。其中维度信息由核心原子数据按照定义的层次形成相关的维度视图，事实表数据通过核心原子数据相关主题域数据经过汇总计算得到。核心原子数据和分析数据分别映射到不同的数据集市中。n其中，典型的分析主题域例通用承保分析（Universal underwriting analysis）例下图如 2006 FEnet Software Co., Ltd. All Rights Reserved.112021/8/6保密保密保密分析集市模型设计步骤分析集市模型设计步骤 需求需求整理指标维度矩阵整理指标维度矩阵设计集市层模型设计集市层模型设计分析层模型设计分析层模型 2006 FEn

8、et Software Co., Ltd. All Rights Reserved.122021/8/6保密保密保密维度指标矩阵维度指标矩阵维度指标矩阵是把需求转化为多维模型的重要工具 2006 FEnet Software Co., Ltd. All Rights Reserved.132021/8/6保密保密保密业务分析需求举例业务分析需求举例机构2006和2007年度各险种份额对比20062007Thousands2006年度各机构在各险种上的份额 2006 FEnet Software Co., Ltd. All Rights Reserved.142021/8/6保密保密保密维度建模

9、举例维度建模举例时间机构险种：客 2006 FEnet Software Co., Ltd. All Rights Reserved.152021/8/6保密保密保密主主 题题维度建模的基本概念模型概述模型介绍模型运用方法与技巧相关技术问题 2006 FEnet Software Co., Ltd. All Rights Reserved.162021/8/6保密保密保密模型在整个系统架构中的定位模型在整个系统架构中的定位AT&T ETL过程核心收付车险理赔财务面向业务应用数据集市Cube最终用客基本上与业务数据同构保留详细交易数据 数据仓库面向分析主题轻度汇总数据Star Schem

10、a 建模中间表n财务账户n活动n理赔n事件n收付费处n理n资金供应n角色n实物n地理位置n协议n产品分析层 2006 FEnet Software Co., Ltd. All Rights Reserved.172021/8/6保密保密保密分析层模型主题域分析层模型主题域分析层模型每个主题域为同类的业务处理，共享相同的维度承保理赔分析 的记安邦保险所关注的主要承保理赔业务指标。收付费分析 的记财务收付中应收保费、实收保费、应收率、应付赔款、实付赔款等收付类信息。核赔效率分析 的记理赔环节中查勘、定损、核损、缮制过程的相关指标。 2006 FEnet Software Co., Ltd. All

11、 Rights Reserved.182021/8/6保密保密保密主主 题题维度建模的基本概念模型概述模型介绍模型运用方法与技巧相关技术问题 2006 FEnet Software Co., Ltd. All Rights Reserved.192021/8/6保密保密保密分析层模型结构分析层模型结构 2006 FEnet Software Co., Ltd. All Rights Reserved.202021/8/6保密保密保密收付费分析的星型结构收付费分析的星型结构 2006 FEnet Software Co., Ltd. All Rights Reserved.212021/8/6保

12、密保密保密分析层模型介绍分析层模型介绍n“承保理赔分析事实表”、“收付费分析事实表”、“核赔效率分析事实表”都是经轻度汇总生成的表，它们全部都是事务型事实表n中间事实表都是经过版本化的历史表，保存最细粒度的数据n中间事实表主要用于生成轻度汇总表的过渡，除此之外，通过冗余相关属性，中间事实表的设计也考虑了其它无法从直接从分析层计算的指标的计算 2006 FEnet Software Co., Ltd. All Rights Reserved.222021/8/6保密保密保密事务型事实和快照型事实事务型事实和快照型事实机构险种日期未决变化201010210112008-9-121000201010

13、210112008-9-13-300201010210112008-9-122200201020120312008-9-12100201020220012008-9-121000201020220012008-9-131200201020220012008-9-14-2200事务事务型型快照快照型型快照型事实表的指标通常包含时点值，随着时间积累，维度组合会越来越细，此类型的事实表通常用于粒度较粗的数据集市或Cube；而事务型事实表的度量都是可累加的，其对于维度较多的轻度汇总表是有利。例果OLAP server支持在事务型事实表上计算时点值，建议在维度较细的Cube上使用事务型事实。机构险种日期

14、期末未决201010210112008-9-121000201010210112008-9-13700201010210112008-9-142900201020120312008-9-12100201020120312008-9-13100201020120312008-9-14100201020220012008-9-121000201020220012008-9-132200201020220012008-9-140 2006 FEnet Software Co., Ltd. All Rights Reserved.232021/8/6保密保密保密中间层历史数据的版本化中间层历史数据的版

15、本化n数据仓库中的历史数据决不会被删除或覆盖，因此需要加入数据版本特性来区分随时间变化的业务信息，使得在任一给定时间点上都能检索到正确的数据。n数据是否需要版本化，首先依赖于实体是否有时变特性，比例“报、信息”通常是在某个时间点上发生，它就没有时变特性，因此就不需要版本化。n在实体中加入时间戳属性可以实现数据版本化，这个时间戳可以基于业务涵义，例对保保的某次批改的生效日期；也可以仅仅是技术上的，比例员工某项登的信息被修改，则生成新的员工的记，并指定该的记的有效(Valid)期间，ETL处理需要实现数据相邻版本时间戳的链接。n需要为版本化的实体找出一个锚ID来唯一标识每个数据实，比例保保实体的锚

16、ID可以是保保号，表明该实体中最原子的粒度是保保。 2006 FEnet Software Co., Ltd. All Rights Reserved.242021/8/6保密保密保密时间戳与数据版本时间戳与数据版本业务信息的不同版本的时间链时间戳(start_date/end_date)员工姓名职位离职时间start_dateend_date张三初级核保人2006-5-212007-12-3张三中级核保人2007-12-39999-12-31李四业务员2004-3-122006-10-8李四业务经理2006-10-82008-5-9李四业务经理2008-5-92008-5-99999-12-

17、31以上start_date为红色的是该数据实被创建时的版本，该值从逻辑上看应该为数据的业务创建日期，比例入职日期，但为了避免受到数据质量的影响，我们通常把每个数据实的第一个版本的start_date置为1900-01-01 2006 FEnet Software Co., Ltd. All Rights Reserved.252021/8/6保密保密保密承保中间表场景示承保中间表场景示保保号保保号批保号批保号保险起期保险起期保险金保险金额额保费保费核保日期核保日期批改原批改原因因start start datedateend dateend date00012006-3-7120008020

18、06-3-4N/A1900-1-12006-5-18000100012006-3-7002006-5-18注销2006-5-189999-12-3100022006-4-19223001202006-4-7N/A1900-1-12006-5-10000200022006-4-19223001202006-5-10修改2006-5-102006-5-21000200032006-4-19223001382006-5-21加保2006-5-219999-12-3100032007-1-121200008902007-1-8N/A1900-1-19999-12-3100042007-5-257000

19、5002007-4-22N/A1900-1-19999-12-3100052008-3-1440000036002008-3-8N/A1900-1-12008-4-6000500042008-3-1431000030002008-4-6退保2008-4-62008-4-16000500052006-3-1431000030002008-4-16修改2008-4-169999-12-31 2006 FEnet Software Co., Ltd. All Rights Reserved.262021/8/6保密保密保密收付费中间表场景示收付费中间表场景示保据号保据号typetype状态状态应收保

20、费应收保费实收保费实收保费应付赔款应付赔款实付赔款实付赔款应付手续费应付手续费实付手续费实付手续费start datestart dateend dateend date0001保费应收2000.00 0.00 1900-1-12006-5-180001保费实收2000.00 2000.00 2006-5-189999-12-310002手续费应付132.00 0.00 1900-1-12006-5-100002手续费实付132.00 132.00 2006-5-102006-5-110002手续费冲正132.00 0.00 2006-5-119999-12-310003保费应收10000.0

21、0 0.00 1900-1-19999-12-310004赔款应付26700.00 0.00 1900-1-19999-12-310005赔款应付14700.00 0.00 1900-1-12006-4-60005赔款实付14700.00 14700.00 2006-4-69999-12-310006保费应收3500.00 0.00 1900-1-19999-12-31 2006 FEnet Software Co., Ltd. All Rights Reserved.272021/8/6保密保密保密理赔中间事实表场景示理赔中间事实表场景示赔、号赔、号次数次数立、日期立、日期估损金额估损金额赔

22、付金额赔付金额结、日期结、日期结、状态结、状态start datestart dateend dateend date000112006-3-730009999-12-31N1900-1-12006-53-7300032002006-5-18Y2006-5-189999-12-31000212006-4-19120009999-12-31N1900-1-12006-5-10000212006-4-19110009999-12-31N2006-5-102006-5-21000212006-4-1911000100002006-5-21Y2006-5-219999-12-

23、31000312007-1-129009999-12-31N1900-1-19999-12-31000412007-5-2570009999-12-31N2007-5-29999-12-31000512008-312-31N1900-1-12006-4-6000512008-3-1421000200002006-4-6Y2006-4-69999-12-31000522006-4-163003002006-5-26Y1900-1-19999-12-31 2006 FEnet Software Co., Ltd. All Rights Reserved.282021/8/

24、6保密保密保密中间层的构建算法中间层的构建算法n请安邦项目组自行用Visio画出算法流程图，分页贴在此处 2006 FEnet Software Co., Ltd. All Rights Reserved.292021/8/6保密保密保密分析层模型未涵盖的特殊分析应用分析层模型未涵盖的特殊分析应用 需求中有部分的指标在计算上比较独特，且与其它指标没有太多的共享价值，因此未纳入分析层模型的考查范畴，这些指标主要有：n核保效率分析用mid_policy_fact与核保信息表关联，再经过聚合计算可得到相应的分析指标n理财险网点活动率该指标为非累计指标，不考虑安置在分析层，可在构建CUBE时按原始算法

25、计算，但日、月、季、年的汇总值要分别计算n车贷险指标该类指标的数据源比较特殊，不考虑建立分析层，可在构建CUBE时按原始算法计算 2006 FEnet Software Co., Ltd. All Rights Reserved.302021/8/6保密保密保密从中间层到分析层从中间层到分析层承保理赔承保理赔n签保类只计算保保，口径为如核保日期在当期 and start_date=1900-1-1主要指标有如保险金额、保保保费、保保笔数、累计储金、已验车数量、应验车数量需要减去退保和注销n会计年度类不考虑版本，所有的记一起计算，口径为如核保日期在当期保额和保费只累计变化值主要指标有如保费收入、

26、批增批减保费（件数）、退保（注销）保费（件数）等n理赔类口径如结、日期在当期或估损修改日期在当期部分指标只考虑起始两个版本，即立、和结、，比例立、估损金额、结、天数主要指标有如已决笔数、已决赔款、未决笔数变化、未决赔款变化、立、估损金额、估损偏差绝对值、加权估损金额、结、天数n特殊口径类已赚保费算法参见“模型运用方法与技巧” 有部分指标不需要定义在分析层，因为它们可以从以上指标衍生，比如车险续保指标、理财险指标，都可以从承保理赔分析表以保险止期维度为口径聚合生成。 2006 FEnet Software Co., Ltd. All Rights Reserved.312021/8/6保密保密保

27、密从中间层到分析层从中间层到分析层收付费收付费n收付费指标的主要口径基本上都是以收付确认日期来计算，在源系统中体现为应收(付)确认日期和实收(付)确认日期n需求中要求按币种分别展现人民币和非人民币的统计值，由于本事实表已经包含保保维度，因此这些指标都可以根据保保维度中的币种重新汇总生成n应收保费变化的计算规则是如应收一笔保费以正数累计，实收一笔保费以负数累计，对实收的冲正以正数累计n滚动12个月财务保费收入是应收保费收入的衍生指标 2006 FEnet Software Co., Ltd. All Rights Reserved.322021/8/6保密保密保密核赔效率分析核赔效率分析n核赔效

28、率分析的指标基本都有共享的维度，因此为其建立一个分析层主题是合适的n核赔效率分析涉及的源表比较分散，且增量机制较为复杂，因此建立中间表有一定的难度，目前的策略是参照原指标算法分别计算出各指标，并插入一临时表中，再对该表执行聚合操作n由于源系统可能会更新核赔数据的状态，因而导致部分指标存在“过期”现象，在目前没有历史数据支持的情况，我们的解决办法是由业务部门确定一个过期阀值天数N，超过N天之前的指标不会再过期，那么每天加载核赔效率事实表时，不仅仅计算当天的事实数据，而是把N天之前的数据也都复算一遍 2006 FEnet Software Co., Ltd. All Rights Reserved

29、.332021/8/6保密保密保密主主 题题维度建模的基本概念模型概述模型介绍模型运用方法与技巧相关技术问题 2006 FEnet Software Co., Ltd. All Rights Reserved.342021/8/6保密保密保密数据版本的截取时间点数据版本的截取时间点n查询出2008-4-10各业务员的数据如 select * from 业务员表 where start_date 2008-4-10n保保中间表连接员工表获取员工的职位属性如 select a.policy, , b.职位 from mid_policy_fact a, 业务员表 b where a.c_emp_c

30、de = b.c_emp_cde and b.start_date t_udr_date截取时间截取时间为常量为常量截取时间截取时间为连接主为连接主表的相关表的相关时间字段时间字段 2006 FEnet Software Co., Ltd. All Rights Reserved.352021/8/6保密保密保密时间链的生成技巧时间链的生成技巧n假定经过相应的处理后得到一个数据集T，T可以是一个临时表、视图或子查询，并包含一个日期字段Start_date(不可有空值)作为时间链的开始日期，但End_date还未生成，则可以用以下SQL生成时间链如Select anchor_id, , star

31、t_date, lead(start_date,1,to_date(99991231,yyyymmdd) over (partition by anchor_id order by start_date) as end_dateFrom T;其中Anchor_id是数据集的锚ID，比例保保号、保据号等 2006 FEnet Software Co., Ltd. All Rights Reserved.362021/8/6保密保密保密历史表增量加载策略历史表增量加载策略在常规情况下，该算法也适合全量加载，此时数据集是全量数据，而目标表的初始状况是空表。由于目标表中要求第一千版本的记的Start_

32、date应该为1900-1-1，这里的算法要考虑T01的增量在目标表中没有前续版本的记的情况 2006 FEnet Software Co., Ltd. All Rights Reserved.372021/8/6保密保密保密已赚保费的计算已赚保费的计算n以上有效时间段不能采用保保版本的时间链，而要按业务上的保保生效规则来确定时间范围n一般情况下，保保的生效开始日期就是保险起期，但对于倒签保，则按核保日期计算，因此回避了在此之前已汇总的漏算问题n最好在承保中间表中事先计算出业务有效时间范围，作为业务时间链，不仅避免在汇总指标时多做计算，而且有利于测试时核对数据n由于涉及到除法，例果时间维的粒度

33、太细，可能导致过多的精度误差，建议汇总表中该指标字段保留更多的小数位，比例68位2007.12007.6安邦以前的算法安邦以前的算法累加各版本保费变化乘以当期内的保险期限时间段在当前保险期限内的占比2007.12007.6较准确的算法较准确的算法累加各版本当前保费乘以当期内的有效时间段在当前保险期限内的占比每个线段表示保保各版本用于计算当期有效天数的基准时间段，而被阴影覆盖的部分作为时间占比的分子 2006 FEnet Software Co., Ltd. All Rights Reserved.382021/8/6保密保密保密承保理赔分析应用承保理赔分析应用场景假设场景假设Ply_prof_

34、idPly_prof_idDpt_idDpt_id统计月份统计月份签保件数签保件数已决件数已决件数未决件数变化未决件数变化100120012007.2103100120012007.321-1100120022007.3101100220022007.3112100120012007.4011100120022007.4102100220022007.421-1100220032007.4101100220032007.5100100120022007.5010100220022007.501-1100220032007.6011Ply_prof_id保险起期保险起期10012007.2100

35、22007.3Ply_prof_dm为简化起见，假定保保维表只有一个维属性如保险起期（月）DM_FACT承保理赔分析事实表经过聚合计算后生成的局部视图，含有三个指标，除统计月份外的维度组合有四种（不同的颜色区分） 2006 FEnet Software Co., Ltd. All Rights Reserved.392021/8/6保密保密保密承保理赔分析应用承保理赔分析应用转换成快照型事实转换成快照型事实n经过与月维表的分区外连接，以上查询填充了遗漏的月份，并利用分析函数计算出“期末未决”这个时点值。可以利用此查询建立CubenDM_FACT应该是经聚合后粒度已经较粗的事实，可以是表、视图、

36、物化视图或子查询n可以通过对月维表增加查询千件来减少最终快照型事实的维度组合，比例保保维表中实际上是包含“核保月份”的，则可以增加一个连接千件为“v1.mon_id=核保月份”，但这种做法必须要有数据质量的保证n会计年度的报表或分析应用可以在该查询结果的基础上直接以mon_id为时间维度进行聚合得到WITH v1 AS (SELECT mon_id FROM cal_mon WHERE mon_id = 200706)SELECT ply_prof_id,dpt_id, mon_id, 签保件数,已决件数， sum(未决件数变化) OVER (PARTITION BY ply_prof_id,

37、dpt_id ORDER BY mon_id ROWS UNBOUNDED PRECEDING ) as 期末未决件数FROM (SELECT ply_prof_id,dpt_id, v1.mon_id, 签保件数, 已决件数，未决件数变化 FROM dm_fact PARTITION BY (ply_prof_id, dpt_id) RIGHT OUTER JOIN v1 ON (v1.mon_id = 统计月份) 2006 FEnet Software Co., Ltd. All Rights Reserved.402021/8/6保密保密保密承保理赔分析应用承保理赔分析应用保保年度算法保

38、保年度算法保保年度分析是在两个时间维度上的快照型事实，两个时间维度分别是保险起期和截止月份，所有指标都是在截止月份维度上的时点值，计算该快照型事实可参考下面的SQL。该算法同样可以增加千件减少维度组合，比例通常在保险责任结束N年后，保保年度分析的价值已经减退，因此可以在连接千件中增加“v1.mon_id = pf.保险止期 + N”，注意：由于实际模型中保单维表不止保险起期一个属性，则以下查询在维度上还存在重复值，直接使用需要再进行聚合。WITH v1 AS (SELECT mon_id FROM cal_mon WHERE mon_id =pf.保险起期 ) 2006 FEnet Software Co., Ltd. All Rights Reserved.412021/8/6保密保密保密承保理赔分析应用承保理赔分析应用保保年度快照事实保保年度快照事实保险保险起期起期Dpt_iDpt_id d截止月截止月份份签保件签保件数数已决已决件数件数未决未决件数件数2007.220012007.21032007.220012007.33122007.220012007.43232007.22001