安邦BI系统数据模型全面介绍

1、安邦BI系统数据模型介绍主题题维度建模模的基本本概念模型概述述模型介绍绍模型运用用方法与与技巧相关技术术问题如何建立立分析集集市模型型行业经验验BI.Insurance DWM工作组和和建模持续发展展业务需求求安邦分析集市模型业务驱动动根据固定定报表和和KPI、持续率率、承保保理赔、执行力力、再保保五个数数据集市市的业务务需求,结合BI.InsuranceDWM的建模方方法,建立Taiping EDW分析集市市模型分析集市市模型概概念维度建模模一种非规规范化的的关系模模型由一组属属性构成成的表所所组成表跟表之之间的关关系通过过关键字字和外键键来定义义以良好的的可理解解性和方方便的产产生报表表来

2、进行行数据组组织，很很少考虑虑修改的的性能通过SQL或者相关关的工具具实现数数据的查查询和维维护分析集市市模型概概念事实表（一）每一个事事实表通通常包含含了业务务需求所所关心的的一系列列的指标标值每一个事事实表的的行包括括具有可加加性的数数值型的的指标值值文本事实实通常具具有不可可预见的的内容，很难进进行分析析与维表相相连接的的外键通常具有有两个和和两个以以上的外外键外键之间间表示维维表之间间多对多多的关系系分析集市市模型概概念事实表（二）事实表的的特征非常的大大包含含几万、几十万万甚至几几百几千千条的记记录内容相对对的窄列列数数较少经常发生生变化现现实实世界中中新事件件的发生生事实实表中增增

3、加一条条记录典型情况况下，仅仅仅是数数据的追追加事实表的的使用各类指标标值的聚聚集计算算分析集市市模型概概念维表（一一）每一张维维表对应应现实世世界中的的一个对对象或者者概念例如如：客户户、产品品、保单单、标的的、案件件维表的特特征包含了众众多描述述性的属属性列如保单维维表里的的销售渠渠道、保保费区间间、风险险等级等等通常情况况下，跟跟事实表表相比，行数相相对较小小通常 10万条内容相对对固定几乎就是是一类查查找表分析集市市模型概概念维表（二二）维表的应应用基于维属属性的过过滤（切切片、切切块等）基于维属属性的各各种聚集集操作报表中各各类标签签的主要要来源事实表通通过维表表进行引引用分析集市市

4、模型概概念Facts属性个数数少（窄窄）记录行数数多（大大）数值型指指标随着时间间的推移移，数据据增长Dimensions属性个数数多（宽宽）记录行数数少（小小）描述性属属性静态的，很少发发生变化化 事实表与与维表的的比较分析主题题域数据据(AnalyticalSubject Areas)通过事实实（Facts）和维度度（Dimensions）模型方方式表达达，为典典型保险险分析应应用提供供分析型型数据架架构，为为数据集集市提供供统一的的指标和和维度，为数据据应用提提供统一一的数据据信息。分析主题题域数据据，将业业务需求求阶段定定义的分分析主题题域，按按照维度度建模方方法，格格式化成成符合维维

5、度事事实表的的星型模模型或雪雪花模型型。其中中维度信信息由核核心原子子数据按按照定义义的层次次形成相相关的维维度视图图，事实实表数据据通过核核心原子子数据相相关主题题域数据据经过汇汇总计算算得到。核心原原子数据据和分析析数据分分别映射射到不同同的数据据集市中中。其中，典典型的分分析主题题域如通通用承保保分析（Universalunderwriting analysis）如下图图：分析集市市模型设设计步骤骤需求整理指标标维度矩矩阵设计集市市层模型型设计分析析层模型型维度指标标矩阵维度指标标矩阵是是把需求求转化为为多维模模型的重重要工具具业务分析析需求举举例与上一年年相比，各险种种所占的的份额有有

6、何差异异?每个机构构在各险险种所占占份额是是多少?机构险种时间2006和2007年度各险险种份额额对比20062007Thousands2006年度各机机构在各各险种上上的份额额维度建模模举例时间机构险种客户主题题维度建模模的基本本概念模型概述述模型介绍绍模型运用用方法与与技巧相关技术术问题元数据模型在整整个系统统架构中中的定位位ETL过程核心收付车险理赔财务面向业务务应用数据集市Cube最终用户基本上与与业务数数据同构构保留详细细交易数数据数据仓库库面向分析主题轻度汇总数据Star Schema 建模中间表财务账户活动理赔事件收付费处理资金供应角色实物地理位置协议产品分析层分析层模模型主题题

7、域分析层模模型每个个主题域域为同类类的业务务处理，共享相相同的维维度承保理赔赔分析记录安邦邦保险所所关注的的主要承承保理赔赔业务指指标。收付费分分析记录财务务收付中中应收保保费、实实收保费费、应收收率、应应付赔款款、实付付赔款等等收付类类信息。核赔效率率分析记录理赔赔环节中中查勘、定损、核损、缮制过过程的相相关指标标。主题题维度建模模的基本本概念模型概述述模型介绍绍模型运用用方法与与技巧相关技术术问题分析层模模型结构构收付费分分析的星星型结构构分析层模模型介绍绍“承保理理赔分析析事实表表”、“收付费费分析事事实表”、“核核赔效率率分析事事实表”都是经经轻度汇汇总生成成的表，它们全全部都是是事务

8、型型事实表表中间事实实表都是是经过版版本化的的历史表表，保存存最细粒粒度的数数据中间事实实表主要要用于生生成轻度度汇总表表的过渡渡，除此此之外，通过冗冗余相关关属性，中间事事实表的的设计也也考虑了了其它无无法从直直接从分分析层计计算的指指标的计计算事务型事事实和快快照型事事实机构险种日期未决变化201010210112008-9-121000201010210112008-9-13-300201010210112008-9-122200201020120312008-9-12100201020220012008-9-121000201020220012008-9-131200201020220

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

10、9-12100201020120312008-9-13100201020120312008-9-14100201020220012008-9-121000201020220012008-9-132200201020220012008-9-140中间层历历史数据据的版本本化数据仓库库中的历历史数据据决不会会被删除除或覆盖盖，因此此需要加加入数据据版本特特性来区区分随时时间变化化的业务务信息，使得在在任一给给定时间间点上都都能检索索到正确确的数据据。数据是否否需要版版本化，首先依依赖于实实体是否否有时变变特性，比如“报案信信息”通通常是在在某个时时间点上上发生，它就没没有时变变特性，因此就就不需要

11、要版本化化。在实体中中加入时时间戳属属性可以以实现数数据版本本化，这这个时间间戳可以以基于业业务涵义义，如对对保单的的某次批批改的生生效日期期；也可可以仅仅仅是技术术上的，比如员员工某项项登记信信息被修修改，则则生成新新的员工工记录，并指定定该记录录的有效效(Valid)期间，ETL处理需要要实现数数据相邻邻版本时时间戳的的链接。需要为版版本化的的实体找找出一个个锚ID来唯一标标识每个个数据实实例，比比如保单单实体的的锚ID可以是保保单号，表明该该实体中中最原子子的粒度度是保单单。时间戳与与数据版版本业务信息息的不同同版本的的时间链链时间戳(start_date/end_date)员工姓名职位

12、离职时间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-31以上start_date为红色的的是该数数据实例例被创建建时的版版本，该该值从逻逻辑上看看应该为为数据的的业务创创建日期期，比如如入职日日期，但但为了避避免受到到数据质质量的影影响，我我们通常常把每个个数据实实例的第第一个版版本的start_date置为1900-01-01承保中间间表场景景示例保

13、单号批单号保险起期保险金额保费核保日期批改原因start dateend date00012006-3-712000802006-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-31000320

14、07-1-121200008902007-1-8N/A1900-1-19999-12-3100042007-5-2570005002007-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收付费中中间表场场景示例例单据号type状态应收保费实收保费应付赔款实付赔款应付手续费实

15、付手续费start 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.00 0.00 1900-1-19999-12-310004赔款应付26700.00 0.00 1900-1-19999-12-

16、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理赔中间间事实表表场景示示例赔案号次数立案日期估损金额赔付金额结案日期结案状态start dateend date000112006-3-730009999-12-31N1900-1-12006-53-7300032002006-5-18Y2006-5-189999-12-31000212006-4-19120009999-1

17、2-31N1900-1-12006-5-10000212006-4-19110009999-12-31N2006-5-102006-5-21000212006-4-1911000100002006-5-21Y2006-5-219999-12-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-

18、4-69999-12-31000522006-4-163003002006-5-26Y1900-1-19999-12-31中间层的的构建算算法请安邦项项目组自自行用Visio画出算法法流程图图，分页页贴在此此处分析层模模型未涵涵盖的特特殊分析析应用需求中有有部分的的指标在在计算上上比较独独特，且且与其它它指标没没有太多多的共享享价值，因此未未纳入分分析层模模型的考考查范畴畴，这些些指标主主要有：核保效率率分析用mid_policy_fact与核保信信息表关关联，再再经过聚聚合计算算可得到到相应的的分析指指标理财险网网点活动动率该指标为为非累计计指标，不考虑虑安置在在分析层层，可在在构建CUBE

19、时按原始始算法计计算，但但日、月月、季、年的汇汇总值要要分别计计算车贷险指指标该类指标标的数据据源比较较特殊，不考虑虑建立分分析层，可在构构建CUBE时按原始始算法计计算从中间层层到分析析层承保理理赔签单类只计算保保单，口口径为：核保日日期在当当期andstart_date=1900-1-1主要指标标有：保保险金额额、保单单保费、保单笔笔数、累累计储金金、已验验车数量量、应验验车数量量需要减去去退保和和注销会计年度度类不考虑版版本，所所有记录录一起计计算，口口径为：核保日日期在当当期保额和保保费只累累计变化化值主要指标标有：保保费收入入、批增增批减保保费（件件数）、退保（注销）保费（件数）等理

20、赔类口径：结结案日期期在当期期或估损损修改日日期在当当期部分指标标只考虑虑起始两两个版本本，即立立案和结结案，比比如立案案估损金金额、结结案天数数主要指标标有：已已决笔数数、已决决赔款、未决笔笔数变化化、未决决赔款变变化、立立案估损损金额、估损偏偏差绝对对值、加加权估损损金额、结案天天数特殊口径径类已赚保费费算算法参见见“模型型运用方方法与技技巧”有部分指指标不需需要定义义在分析析层，因因为它们们可以从从以上指指标衍生生，比如如车险续续保指标标、理财财险指标标，都可可以从承承保理赔赔分析表表以保险险止期维维度为口口径聚合合生成。从中间层层到分析析层收付费费收付费指指标的主主要口径径基本上上都是

21、以以收付确确认日期期来计算算，在源源系统中中体现为为应收(付)确认日期期和实收收(付)确认日期期需求中要要求按币币种分别别展现人人民币和和非人民民币的统统计值，由于本本事实表表已经包包含保单单维度，因此这这些指标标都可以以根据保保单维度度中的币币种重新新汇总生生成应收保费费变化的的计算规规则是：应收一一笔保费费以正数数累计，实收一一笔保费费以负数数累计，对实收收的冲正正以正数数累计滚动12个月财务务保费收收入是应应收保费费收入的的衍生指指标核赔效率率分析核赔效率率分析的的指标基基本都有有共享的的维度，因此为为其建立立一个分分析层主主题是合合适的核赔效率率分析涉涉及的源源表比较较分散，且增量量机

22、制较较为复杂杂，因此此建立中中间表有有一定的的难度，目前的的策略是是参照原原指标算算法分别别计算出出各指标标，并插插入一临临时表中中，再对对该表执执行聚合合操作由于源系系统可能能会更新新核赔数数据的状状态，因因而导致致部分指指标存在在“过期期”现象象，在目目前没有有历史数数据支持持的情况况，我们们的解决决办法是是由业务务部门确确定一个个过期阀阀值天数数N，超超过N天天之前的的指标不不会再过过期，那那么每天天加载核核赔效率率事实表表时，不不仅仅计计算当天天的事实实数据，而是把把N天之之前的数数据也都都复算一一遍主题题维度建模模的基本本概念模型概述述模型介绍绍模型运用用方法与与技巧相关技术术问题数

23、据版本本的截取取时间点点查询出2008-4-10各业务员员的数据据：select*from业务员表表wherestart_date 2008-4-10保单中间间表连接接员工表表获取员员工的职职位属性性：selecta.policy, ,b.职位from mid_policy_facta,业务员表表bwherea.c_emp_cde= b.c_emp_cdeandb.start_date t_udr_date截取时间间为常量量截取时间间为连接接主表的的相关时时间字段段时间链的的生成技技巧假定经过过相应的的处理后后得到一一个数据据集T，T可以是一一个临时时表、视视图或子子查询，并包含含一个日日期字

24、段段Start_date(不可有空空值)作为时间间链的开开始日期期，但End_date还未生成成，则可可以用以以下SQL生成时间间链：Selectanchor_id, ,start_date,lead(start_date,1,to_date(99991231,yyyymmdd)over(partition by anchor_idorderbystart_date)asend_dateFrom T;其中Anchor_id是数据集集的锚ID，比如保保单号、单据号号等历史表增增量加载载策略在常规情情况下，该算法法也适合合全量加加载，此此时数据据集是全全量数据据，而目目标表的的初始状状况是空空表。

25、由于目标标表中要要求第一一条版本本记录的的Start_date应该为1900-1-1，这里的的算法要要考虑T01的增量在在目标表表中没有有前续版版本记录录的情况况已赚保费费的计算算以上有效效时间段段不能采采用保单单版本的的时间链链，而要要按业务务上的保保单生效效规则来来确定时时间范围围一般情况况下，保保单的生生效开始始日期就就是保险险起期，但对于于倒签单单，则按按核保日日期计算算，因此此回避了了在此之之前已汇汇总的漏漏算问题题最好在承承保中间间表中事事先计算算出业务务有效时时间范围围，作为为业务时时间链，不仅避避免在汇汇总指标标时多做做计算，而且有有利于测测试时核核对数据据由于涉及及到除法法，

26、如果果时间维维的粒度度太细，可能导导致过多多的精度度误差，建议汇汇总表中中该指标标字段保保留更多多的小数数位，比比如68位2007.12007.6安邦以前前的算法法累加各版版本保费费变化乘乘以当期期内的保保险期限限时间段段在当前前保险期期限内的的占比2007.12007.6较准确的的算法累加各版版本当前前保费乘乘以当期期内的有有效时间间段在当当前保险险期限内内的占比比每个线段段表示保保单各版版本用于于计算当当期有效效天数的的基准时时间段，而被阴阴影覆盖盖的部分分作为时时间占比比的分子子承保理赔赔分析应应用场景假假设Ply_prof_idDpt_id统计月份签单件数已决件数未决件数变化10012

27、0012007.2103100120012007.321-1100120022007.3101100220022007.3112100120012007.4011100120022007.4102100220022007.421-1100220032007.4101100220032007.5100100120022007.5010100220022007.501-1100220032007.6011Ply_prof_id保险起期10012007.210022007.3Ply_prof_dm为简化起起见，假假定保单单维表只只有一个个维属性性：保险险起期（月）DM_FACT承保理赔赔分析事事实表

28、经经过聚合合计算后后生成的的局部视视图，含含有三个个指标，除统计计月份外外的维度度组合有有四种（不同的的颜色区区分）承保理赔赔分析应应用转换成成快照型型事实经过与月月维表的的分区外外连接，以上查查询填充充了遗漏漏的月份份，并利利用分析析函数计计算出“期末未未决”这这个时点点值。可可以利用用此查询询建立CubeDM_FACT应该是经经聚合后后粒度已已经较粗粗的事实实，可以以是表、视图、物化视视图或子子查询可以通过过对月维维表增加加查询条条件来减减少最终终快照型型事实的的维度组组合，比比如保单单维表中中实际上上是包含含“核保保月份”的，则则可以增增加一个个连接条条件为“v1.mon_id=核保月份

29、份”，但但这种做做法必须须要有数数据质量量的保证证会计年度度的报表表或分析析应用可可以在该该查询结结果的基基础上直直接以mon_id为时间维维度进行行聚合得得到WITH v1 AS (SELECTmon_idFROM cal_monWHERE mon_id=200706)SELECTply_prof_id,dpt_id,mon_id,签单件数数,已决件数数，sum(未决件数数变化) OVER(PARTITION BY ply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING) as期末未决决件数FROM(SELECT ply_prof_id,d

30、pt_id,v1.mon_id,签单件数数,已决件数数，未决决件数变变化FROM dm_factPARTITION BY (ply_prof_id,dpt_id)RIGHTOUTERJOIN v1ON(v1.mon_id =统计月份份)承保理赔赔分析应应用保单年年度算法法保单年度度分析是是在两个个时间维维度上的的快照型型事实，两个时时间维度度分别是是保险起起期和截截止月份份，所有有指标都都是在截截止月份份维度上上的时点点值，计计算该快快照型事事实可参参考下面面的SQL。该算法同同样可以以增加条条件减少少维度组组合，比比如通常常在保险险责任结结束N年后，保保单年度度分析的的价值已已经减退退，因此此可以在在连接条条件中增增加“v1.mon_id=pf.保险止期期+ N”，注意：由由于实际际模型中中保单维维表不止止保险起起期一个个属性，则以下下查询在在维度上上还存在在重复值值，直接接使用需需要再进进行聚合合。WITH v1 AS (SELECTmon_idFROM cal_monWHERE mon_id=pf.保险起期期)承保理赔赔分析应应用保单年年度快照照事实保险起期Dpt_id截止月份签单件数已决件数未决件数2007.220012007.21032007.2