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

安邦BI系统数据模型介绍主题维度建模的基本概念模型概述模型介绍模型运用方法与技巧相关技术问题如何建立分析集市模型行业经验BI.InsuranceDWM工作组和建模持续发展业务需求安邦分析集市模型业务驱动根据固定报表和KPI、持续率、承保理赔、执行力、再保五个数据集市的业务需求,结合BI.InsuranceDWM的建模方法,建立TaipingEDW分析集市模型分析集市模型概念维度建模一种非规范化的关系模型由一组属性构成的表所组成表跟表之间的关系通过关键字和外键来定义以良好的可理解性和方便的产生报表来进行数据组织，很少考虑修改的性能通过SQL或者相关的工具实现数据的查询和维护分析集市模型概念事实表（一）每一个事实表通常包含了业务需求所关心的一系列的指标值每一个事实表的行包括具有可加性的数值型的指标值文本事实通常具有不可预见的内容，很难进行分析与维表相连接的外键通常具有两个和两个以上的外键外键之间表示维表之间多对多的关系分析集市模型概念事实表（二）事实表的特征非常的大包含几万、几十万甚至几百几千条的记录内容相对的窄列数较少经常发生变化现实世界中新事件的发生→事实表中增加一条记录典型情况下，仅仅是数据的追加事实表的使用各类指标值的聚集计算分析集市模型概念维表（一）每一张维表对应现实世界中的一个对象或者概念例如：客户、产品、保单、标的、案件维表的特征包含了众多描述性的属性列如保单维表里的销售渠道、保费区间、风险等级等通常情况下，跟事实表相比，行数相对较小通常<10万条内容相对固定几乎就是一类查找表分析集市模型概念维表（二）维表的应用基于维属性的过滤（切片、切块等）基于维属性的各种聚集操作报表中各类标签的主要来源事实表通过维表进行引用分析集市模型概念

Facts属性个数少（窄）记录行数多（大）数值型指标随着时间的推移，数据增长

Dimensions

属性个数多（宽）记录行数少（小）描述性属性静态的，很少发生变化

事实表与维表的比较

分析主题域数据(AnalyticalSubjectAreas)

通过事实（Facts）和维度（Dimensions）模型方式表达，为典型保险分析应用提供分析型数据架构，为数据集市提供统一的指标和维度，为数据应用提供统一的数据信息。分析主题域数据，将业务需求阶段定义的分析主题域，按照维度建模方法，格式化成符合维度－事实表的星型模型或雪花模型。其中维度信息由核心原子数据按照定义的层次形成相关的维度视图，事实表数据通过核心原子数据相关主题域数据经过汇总计算得到。核心原子数据和分析数据分别映射到不同的数据集市中。其中，典型的分析主题域如通用承保分析（Universalunderwritinganalysis）如下图：分析集市模模型设计步步骤需求整理指标维维度矩阵设计集市层层模型设计分析层层模型维度指标矩矩阵维度指标矩矩阵是把需需求转化为为多维模型型的重要工工具业务分析需需求举例与上一年相相比，各险险种所占的的份额有何何差异?每个机构在在各险种所所占份额是是多少?机构险种时间2006和2007年度各险种种份额对比比20062007Thousands2006年度各机构构在各险种种上的份额额维度建模举举例时间机构险种客户主题维度建模的的基本概念念模型概述模型介绍模型运用方方法与技巧巧相关技术问问题元数据模型在整个个系统架构构中的定位位ETL过程核心收付车险理赔财务面向业务应应用数据集市Cube最终用户基本上与业业务数据同同构保留详细交交易数据数据仓库面向分析主题轻度汇总数据StarSchema建模中间表财务账户活动理赔事件收付费处理资金供应角色实物地理位置协议产品分析层分析层模型型主题域分析层模型型每个主题题域为同类类的业务处处理，共享享相同的维维度承保理赔分分析记录安邦保保险所关注注的主要承承保理赔业业务指标。。收付费分析析记录财务收收付中应收收保费、实实收保费、、应收率、、应付赔款款、实付赔赔款等收付付类信息。。核赔效率分分析记录理赔环环节中查勘勘、定损、、核损、缮缮制过程的的相关指标标。主题维度建模的的基本概念念模型概述模型介绍模型运用方方法与技巧巧相关技术问问题分析层模型型结构收付费分析析的星型结结构分析层模型型介绍“承保理赔赔分析事实实表”、““收付费分分析事实表表”、“核核赔效率分分析事实表表”都是经经轻度汇总总生成的表表，它们全全部都是事事务型事实实表中间事实表表都是经过过版本化的的历史表，，保存最细细粒度的数数据中间事实表表主要用于于生成轻度度汇总表的的过渡，除除此之外，，通过冗余余相关属性性，中间事事实表的设设计也考虑虑了其它无无法从直接接从分析层层计算的指指标的计算算事务型事实实和快照型型事实机构险种日期未决变化201010210112008-9-121000201010210112008-9-13-300201010210112008-9-122200201020120312008-9-12100201020220012008-9-121000201020220012008-9-131200201020220012008-9-14-2200事务型快照型快照型事实实表的指标标通常包含含时点值，，随着时间间积累，维维度组合会会越来越细细，此类型型的事实表表通常用于于粒度较粗粗的数据集集市或Cube；而事务型型事实表的的度量都是是可累加的的，其对于于维度较多多的轻度汇汇总表是有有利。如果果OLAPserver支持在事务务型事实表表上计算时时点值，建建议在维度度较细的Cube上使用事务务型事实。。机构险种日期期末未决201010210112008-9-121000201010210112008-9-13700201010210112008-9-142900201020120312008-9-12100201020120312008-9-13100201020120312008-9-14100201020220012008-9-121000201020220012008-9-132200201020220012008-9-140中间层历史史数据的版版本化数据仓库中中的历史数数据决不会会被删除或或覆盖，因因此需要加加入数据版版本特性来来区分随时时间变化的的业务信息息，使得在在任一给定定时间点上上都能检索索到正确的的数据。数据是否需需要版本化化，首先依依赖于实体体是否有时时变特性，，比如“报报案信息””通常是在在某个时间间点上发生生，它就没没有时变特特性，因此此就不需要要版本化。。在实体中加加入时间戳戳属性可以以实现数据据版本化，，这个时间间戳可以基基于业务涵涵义，如对对保单的某某次批改的的生效日期期；也可以以仅仅是技技术上的，，比如员工工某项登记记信息被修修改，则生生成新的员员工记录，，并指定该该记录的有有效(Valid)期间，ETL处理需要实实现数据相相邻版本时时间戳的链链接。需要为版本本化的实体体找出一个个锚ID来唯一标识识每个数据据实例，比比如保单实实体的锚ID可以是保单单号，表明明该实体中中最原子的的粒度是保保单。时间戳与数数据版本业务信息的的不同版本本的时间链链时间戳(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-31以上start_date为红色的是是该数据实实例被创建建时的版本本，该值从从逻辑上看看应该为数数据的业务务创建日期期，比如入入职日期，，但为了避避免受到数数据质量的的影响，我我们通常把把每个数据据实例的第第一个版本本的start_date置为1900-01-01承保中间表表场景示例例保单号批单号保险起期保险金额保费核保日期批改原因startdateenddate0001－－－2006-3-712000802006-3-4N/A1900-1-12006-5-18000100012006-3-7002006-5-18注销2006-5-189999-12-310002－－－2006-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-310003－－－2007-1-121200008902007-1-8N/A1900-1-19999-12-310004－－－2007-5-2570005002007-4-22N/A1900-1-19999-12-310005－－－2008-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状态应收保费实收保费应付赔款实付赔款应付手续费实付手续费startdateenddate0001保费应收2000.000.00

1900-1-12006-5-180001保费实收2000.002000.00

2006-5-189999-12-310002手续费应付

132.000.001900-1-12006-5-100002手续费实付

132.00132.002006-5-102006-5-110002手续费冲正

132.000.002006-5-119999-12-310003保费应收10000.000.00

1900-1-19999-12-310004赔款应付

26700.000.00

1900-1-19999-12-310005赔款应付

14700.000.00

1900-1-12006-4-60005赔款实付

14700.0014700.00

2006-4-69999-12-310006保费应收3500.000.00

1900-1-19999-12-31理赔中间事事实表场景景示例赔案号次数立案日期估损金额赔付金额结案日期结案状态startdateenddate000112006-3-73000

9999-12-31N1900-1-12006-53-7300032002006-5-18Y2006-5-189999-12-31000212006-4-1912000

9999-12-31N1900-1-12006-5-10000212006-4-1911000

9999-12-31N2006-5-102006-5-21000212006-4-1911000100002006-5-21Y2006-5-219999-12-31000312007-1-12900

9999-12-31N1900-1-19999-12-31000412007-5-257000

9999-12-31N2007-5-29999-12-31000512008-3-1421000

9999-12-31N1900-1-12006-4-6000512008-3-1421000200002006-4-6Y2006-4-69999-12-31000522006-4-163003002006-5-26Y1900-1-19999-12-31中间层的构构建算法请安邦项目目组自行用用Visio画出算法流流程图，分分页贴在此此处分析层模型型未涵盖的的特殊分析析应用需求中有部部分的指标标在计算上上比较独特特，且与其其它指标没没有太多的的共享价值值，因此未未纳入分析析层模型的的考查范畴畴，这些指指标主要有有：核保效率分分析——用mid_policy_fact与核保信息息表关联，，再经过聚聚合计算可可得到相应应的分析指指标理财险网点点活动率——该指标为非非累计指标标，不考虑虑安置在分分析层，可可在构建CUBE时按原始算算法计算，，但日、月月、季、年年的汇总值值要分别计计算车贷险指标标——该类指标的的数据源比比较特殊，，不考虑建建立分析层层，可在构构建CUBE时按原始算算法计算从中间层到到分析层———承保理理赔签单类只计算保单单，口径为为：核保日日期在当期期andstart_date=‘1900-1-1’’主要指标有有：保险金金额、保单单保费、保保单笔数、、累计储金金、已验车车数量、应应验车数量量需要减去退退保和注销销会计年度类类不考虑版本本，所有记记录一起计计算，口径径为：核保保日期在当当期保额和保费费只累计变变化值主要指标有有：保费收收入、批增增批减保费费（件数））、退保（（注销）保保费（件数数）等理赔类口径：结案案日期在当当期或估损损修改日期期在当期部分指标只只考虑起始始两个版本本，即立案案和结案，，比如立案案估损金额额、结案天天数主要指标有有：已决笔笔数、已决决赔款、未未决笔数变变化、未决决赔款变化化、立案估估损金额、、估损偏差差绝对值、、加权估损损金额、结结案天数特殊口径类类已赚保费－－－算法参参见“模型型运用方法法与技巧””有部分指标标不需要定定义在分析析层，因为为它们可以以从以上指指标衍生，，比如车险险续保指标标、理财险险指标，都都可以从承承保理赔分分析表以保保险止期维维度为口径径聚合生成成。从中间层到到分析层———收付费费收付费指标标的主要口口径基本上上都是以收收付确认日日期来计算算，在源系系统中体现现为应收(付)确认日期和和实收(付)确认日期需求中要求求按币种分分别展现人人民币和非非人民币的的统计值，，由于本事事实表已经经包含保单单维度，因因此这些指指标都可以以根据保单单维度中的的币种重新新汇总生成成应收保费变变化的计算算规则是：：应收一笔笔保费以正正数累计，，实收一笔笔保费以负负数累计，，对实收的的冲正以正正数累计滚动12个月财务保保费收入是是应收保费费收入的衍衍生指标核赔效率分分析核赔效率分分析的指标标基本都有有共享的维维度，因此此为其建立立一个分析析层主题是是合适的核赔效率分分析涉及的的源表比较较分散，且且增量机制制较为复杂杂，因此建建立中间表表有一定的的难度，目目前的策略略是参照原原指标算法法分别计算算出各指标标，并插入入一临时表表中，再对对该表执行行聚合操作作由于源系统统可能会更更新核赔数数据的状态态，因而导导致部分指指标存在““过期”现现象，在目目前没有历历史数据支支持的情况况，我们的的解决办法法是由业务务部门确定定一个过期期阀值天数数N，超过过N天之前前的指标不不会再过期期，那么每每天加载核核赔效率事事实表时，，不仅仅计计算当天的的事实数据据，而是把把N天之前前的数据也也都复算一一遍主题维度建模的的基本概念念模型概述模型介绍模型运用方方法与技巧巧相关技术问问题数据版本的的截取时间间点查询出2008-4-10各业务员的的数据：select*from业务员表wherestart_date<=‘2008-4-10’andend_date>‘2008-4-10’保单中间表表连接员工工表获取员员工的职位位属性：selecta.policy,……,b.职位frommid_policy_facta,业务员表bwherea.c_emp_cde=b.c_emp_cdeandb.start_date<=t_udr_dateandb.end_date>t_udr_date截取时间为为常量截取时间为为连接主表表的相关时时间字段时间链的生生成技巧假定经过相相应的处理理后得到一一个数据集集T，T可以是一个个临时表、、视图或子子查询，并并包含一个个日期字段段Start_date(不可有空值值)作为时间链链的开始日日期，但End_date还未生成，，则可以用用以下SQL生成时间链链：Selectanchor_id,…,start_date,lead(start_date,1,to_date(‘99991231’,’yyyymmdd’)over(partitionbyanchor_idorderbystart_date)asend_dateFromT;其中Anchor_id是数据集的的锚ID，比如保单单号、单据据号等历史表增量量加载策略略在常规情况况下，该算算法也适合合全量加载载，此时数数据集是全全量数据，，而目标表表的初始状状况是空表表。由于目标表表中要求第第一条版本本记录的Start_date应该为‘1900-1-1‘‘，这里的算算法要考虑虑T01的增量在目目标表中没没有前续版版本记录的的情况已赚保费的的计算以上有效时时间段不能能采用保单单版本的时时间链，而而要按业务务上的保单单生效规则则来确定时时间范围一般情况下下，保单的的生效开始始日期就是是保险起期期，但对于于倒签单，，则按核保保日期计算算，因此回回避了在此此之前已汇汇总的漏算算问题最好在承保保中间表中中事先计算算出业务有有效时间范范围，作为为业务时间间链，不仅仅避免在汇汇总指标时时多做计算算，而且有有利于测试试时核对数数据由于涉及到到除法，如如果时间维维的粒度太太细，可能能导致过多多的精度误误差，建议议汇总表中中该指标字字段保留更更多的小数数位，比如如6~8位2007.12007.6安邦以前的的算法累加各版本本保费变化化乘以当期期内的保险险期限时间间段在当前前保险期限限内的占比比2007.12007.6较准确的算算法累加各版本本当前保费费乘以当期期内的有效效时间段在在当前保险险期限内的的占比每个线段表表示保单各各版本用于于计算当期期有效天数数的基准时时间段，而而被阴影覆覆盖的部分分作为时间间占比的分分子承保理赔分分析应用———场景假假设Ply_prof_idDpt_id统计月份签单件数已决件数未决件数变化100120012007.2103100120012007.321-1100120022007.3101100220022007.3112100120012007.4011100120022007.4102100220022007.421-1100220032007.4101100220032007.5100100120022007.5010100220022007.501-1100220032007.6011Ply_prof_id保险起期10012007.210022007.3Ply_prof_dm为简化起见见，假定保保单维表只只有一个维维属性：保保险起期（（月）DM_FACT承保理赔分分析事实表表经过聚合合计算后生生成的局部部视图，含含有三个指指标，除统统计月份外外的维度组组合有四种种（不同的的颜色区分分）承保理赔赔分析应应用———转换成成快照型型事实经过与月月维表的的分区外外连接，，以上查查询填充充了遗漏漏的月份份，并利利用分析析函数计计算出““期末未未决”这这个时点点值。可可以利用用此查询询建立CubeDM_FACT应该是经经聚合后后粒度已已经较粗粗的事实实，可以以是表、、视图、、物化视视图或子子查询可以通过过对月维维表增加加查询条条件来减减少最终终快照型型事实的的维度组组合，比比如保单单维表中中实际上上是包含含“核保保月份””的，则则可以增增加一个个连接条条件为““v1.mon_id>=核保月份份”，但但这种做做法必须须要有数数据质量量的保证证会计年度度的报表表或分析析应用可可以在该该查询结结果的基基础上直直接以mon_id为时间维维度进行行聚合得得到WITHv1AS(SELECTmon_idFROMcal_monWHEREmon_id<=200706)SELECTply_prof_id,dpt_id,mon_id,签单件数数,已决件数数，sum(未决件数数变化)OVER(PARTITIONBYply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING)as期末未决决件数FROM(SELECTply_prof_id,dpt_id,v1.mon_id,签单件数数,已决件数数，未决决件数变变化FROMdm_factPARTITIONBY(ply_prof_id,dpt_id)RIGHTOUTERJOINv1ON(v1.mon_id=统计月份份))承保理赔赔分析应应用———保单年年度算法法保单年度度分析是是在两个个时间维维度上的的快照型型事实，，两个时时间维度度分别是是保险起起期和截截止月份份，所有有指标都都是在截截止月份份维度上上的时点点值，计计算该快快照型事事实可参参考下面面的SQL。该算法同同样可以以增加条条件减少少维度组组合，比比如通常常在保险险责任结结束N年后，保保单年度度分析的的价值已已经减退退，因此此可以在在连接条条件中增增加“v1.mon_id<=pf.保险止期期+N””，注意：由由于实际际模型中中保单维维表不止止保险起起期一个个属性，，则以下下查询在在维度上上还存在在重复值值，直接接使用需需要再进进行聚合合。WITHv1AS(SELECTmon_idFROMcal_monWHEREmon_id<=200706)SELECT保险起期期,dpt_id,mon_id,sum(签单件数数)OVER(PARTITIONBYply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING)AS签单件数数sum(已决件数数)OVER(PARTITIONBYply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING)AS已决件数数sum(未决件数数变化)OVER(PARTITIONBYply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING)AS未决件数数FROM(SELECTpf.保险起期期,dpt_id,v1.mon_id,签单件数数,已决件数数，未决决件数变变化FROMdm_factINNERJOINply_prof_dmpfON(pf.ply_prof_id=dm_fact.ply_prof_id)PARTITIONBY(ply_prof_id,dpt_id)RIGHTOUTERJOINv1ON(v1.mon_id=统计月份份andv1_mon_id>=pf.保险起期期))承保理赔赔分析应应用———保单年年度快照照事实保险起期Dpt_id截止月份签单件数已决件数未决件数2007.220012007.21032007.220012007.33122007.220012007.43232007.220012007.53232007.220012007.63232007.220022007.20002007