保险仓库数据模型设计

安邦BI系统数据模型介绍,主 题,维度建模的基本概念 模型概述 模型介绍 模型运用方法与技巧 相关技术问题,如何建立分析集市模型,行业经验,BI.Insurance DWM,工作组和建模,持续发展,业务需求,安邦分析 集市模型,业务驱动 根据固定报表和KPI、持续率、承保理赔、执行力、再保五个数据集市的业务需求,结合BI.Insurance DWM的建模方法, 建立Taiping EDW 分析集市模型,分析集市模型概念,维度建模 一种非规范化的关系模型 由一组属性构成的表所组成 表跟表之间的关系通过关键字和外键来定义 以良好的可理解性和方便的产生报表来进行数据组织，很少考虑修改的性能 通过SQL或者相关的工具实现数据的查询和维护,分析集市模型概念,事实表（一） 每一个事实表通常包含了业务需求所关心的一系列的指标值 每一个事实表的行包括 具有可加性的数值型的指标值 文本事实通常具有不可预见的内容，很难进行分析 与维表相连接的外键 通常具有两个和两个以上的外键 外键之间表示维表之间多对多的关系,分析集市模型概念,事实表（二） 事实表的特征 非常的大 包含几万、几十万甚至几百几千条的记录 内容相对的窄 列数较少 经常发生变化 现实世界中新事件的发生事实表中增加一条记录 典型情况下，仅仅是数据的追加 事实表的使用 各类指标值的聚集计算,分析集市模型概念,维表（一） 每一张维表对应现实世界中的一个对象或者概念 例如：客户、产品、保单、标的、案件 维表的特征 包含了众多描述性的属性列 如保单维表里的销售渠道、保费区间、风险等级等 通常情况下，跟事实表相比，行数相对较小 通常 10万条 内容相对固定 几乎就是一类查找表,分析集市模型概念,维表（二） 维表的应用 基于维属性的过滤（切片、切块等） 基于维属性的各种聚集操作 报表中各类标签的主要来源 事实表通过维表进行引用,分析集市模型概念,Facts 属性个数少（窄） 记录行数多（大） 数值型指标 随着时间的推移，数据增长,Dimensions 属性个数多（宽） 记录行数少（小） 描述性属性 静态的，很少发生变化,事实表与维表的比较,分析主题域数据 (Analytical Subject Areas),通过事实（Facts）和维度（Dimensions）模型方式表达，为典型保险分析应用提供分析型数据架构，为数据集市提供统一的指标和维度，为数据应用提供统一的数据信息。 分析主题域数据，将业务需求阶段定义的分析主题域，按照维度建模方法，格式化成符合维度事实表的星型模型或雪花模型。其中维度信息由核心原子数据按照定义的层次形成相关的维度视图，事实表数据通过核心原子数据相关主题域数据经过汇总计算得到。核心原子数据和分析数据分别映射到不同的数据集市中。 其中，典型的分析主题域如通用承保分析（Universal underwriting analysis）如下图：,分析集市模型设计步骤,需求,整理指标维度矩阵,设计集市层模型,设计分析层模型,维度指标矩阵,维度指标矩阵是把需求转化为多维模型的重要工具,业务分析需求举例,与上一年相比，各险种所占的份额有何差异? 每个机构在各险种所占份额是多少?,机构,险种,时间,2006和2007年度各险种份额对比,2006,2007,Thousands,2006年度各机构在各险种上的份额,维度建模举例,时间,机构,险种,客户,主 题,维度建模的基本概念 模型概述 模型介绍 模型运用方法与技巧 相关技术问题,元数据,模型在整个系统架构中的定位,面向业务应用,基本上与业务数据同构 保留详细交易数据,数据仓库,分析层,分析层模型主题域,分析层模型每个主题域为同类的业务处理，共享相同的维度 承保理赔分析 记录安邦保险所关注的主要承保理赔业务指标。 收付费分析 记录财务收付中应收保费、实收保费、应收率、应付赔款、实付赔款等收付类信息。 核赔效率分析 记录理赔环节中查勘、定损、核损、缮制过程的相关指标。,主 题,维度建模的基本概念 模型概述 模型介绍 模型运用方法与技巧 相关技术问题,分析层模型结构,收付费分析的星型结构,分析层模型介绍,“承保理赔分析事实表”、“收付费分析事实表”、“核赔效率分析事实表”都是经轻度汇总生成的表，它们全部都是事务型事实表 中间事实表都是经过版本化的历史表，保存最细粒度的数据 中间事实表主要用于生成轻度汇总表的过渡，除此之外，通过冗余相关属性，中间事实表的设计也考虑了其它无法从直接从分析层计算的指标的计算,事务型事实和快照型事实,事务型,快照型,快照型事实表的指标通常包含时点值，随着时间积累，维度组合会越来越细，此类型的事实表通常用于粒度较粗的数据集市或Cube；而事务型事实表的度量都是可累加的，其对于维度较多的轻度汇总表是有利。如果OLAP server支持在事务型事实表上计算时点值，建议在维度较细的Cube上使用事务型事实。,中间层历史数据的版本化,数据仓库中的历史数据决不会被删除或覆盖，因此需要加入数据版本特性来区分随时间变化的业务信息，使得在任一给定时间点上都能检索到正确的数据。 数据是否需要版本化，首先依赖于实体是否有时变特性，比如“报案信息”通常是在某个时间点上发生，它就没有时变特性，因此就不需要版本化。 在实体中加入时间戳属性可以实现数据版本化，这个时间戳可以基于业务涵义，如对保单的某次批改的生效日期；也可以仅仅是技术上的，比如员工某项登记信息被修改，则生成新的员工记录，并指定该记录的有效(Valid)期间，ETL处理需要实现数据相邻版本时间戳的链接。 需要为版本化的实体找出一个锚ID来唯一标识每个数据实例，比如保单实体的锚ID可以是保单号，表明该实体中最原子的粒度是保单。,时间戳与数据版本,业务信息的不同版本的时间链,时间戳(start_date/end_date),以上start_date为红色的是该数据实例被创建时的版本，该值从逻辑上看应该为数据的业务创建日期，比如入职日期，但为了避免受到数据质量的影响，我们通常把每个数据实例的第一个版本的start_date置为1900-01-01,承保中间表场景示例,收付费中间表场景示例,理赔中间事实表场景示例,中间层的构建算法,请安邦项目组自行用Visio画出算法流程图，分页贴在此处,分析层模型未涵盖的特殊分析应用,需求中有部分的指标在计算上比较独特，且与其它指标没有太多的共享价值，因此未纳入分析层模型的考查范畴，这些指标主要有： 核保效率分析用mid_policy_fact与核保信息表关联，再经过聚合计算可得到相应的分析指标 理财险网点活动率该指标为非累计指标，不考虑安置在分析层，可在构建CUBE时按原始算法计算，但日、月、季、年的汇总值要分别计算 车贷险指标该类指标的数据源比较特殊，不考虑建立分析层，可在构建CUBE时按原始算法计算,从中间层到分析层承保理赔,签单类 只计算保单，口径为：核保日期在当期 and start_date=1900-1-1 主要指标有：保险金额、保单保费、保单笔数、累计储金、已验车数量、应验车数量 需要减去退保和注销 会计年度类 不考虑版本，所有记录一起计算，口径为：核保日期在当期 保额和保费只累计变化值 主要指标有：保费收入、批增批减保费（件数）、退保（注销）保费（件数）等 理赔类 口径：结案日期在当期或估损修改日期在当期 部分指标只考虑起始两个版本，即立案和结案，比如立案估损金额、结案天数 主要指标有：已决笔数、已决赔款、未决笔数变化、未决赔款变化、立案估损金额、估损偏差绝对值、加权估损金额、结案天数 特殊口径类 已赚保费算法参见“模型运用方法与技巧” 有部分指标不需要定义在分析层，因为它们可以从以上指标衍生，比如车险续保指标、理财险指标，都可以从承保理赔分析表以保险止期维度为口径聚合生成。,从中间层到分析层收付费,收付费指标的主要口径基本上都是以收付确认日期来计算，在源系统中体现为应收(付)确认日期和实收(付)确认日期 需求中要求按币种分别展现人民币和非人民币的统计值，由于本事实表已经包含保单维度，因此这些指标都可以根据保单维度中的币种重新汇总生成 应收保费变化的计算规则是：应收一笔保费以正数累计，实收一笔保费以负数累计，对实收的冲正以正数累计 滚动12个月财务保费收入是应收保费收入的衍生指标,核赔效率分析,核赔效率分析的指标基本都有共享的维度，因此为其建立一个分析层主题是合适的 核赔效率分析涉及的源表比较分散，且增量机制较为复杂，因此建立中间表有一定的难度，目前的策略是参照原指标算法分别计算出各指标，并插入一临时表中，再对该表执行聚合操作 由于源系统可能会更新核赔数据的状态，因而导致部分指标存在“过期”现象，在目前没有历史数据支持的情况，我们的解决办法是由业务部门确定一个过期阀值天数N，超过N天之前的指标不会再过期，那么每天加载核赔效率事实表时，不仅仅计算当天的事实数据，而是把N天之前的数据也都复算一遍,主 题,维度建模的基本概念 模型概述 模型介绍 模型运用方法与技巧 相关技术问题,数据版本的截取时间点,查询出2008-4-10各业务员的数据： select * from 业务员表 where start_date 2008-4-10,保单中间表连接员工表获取员工的职位属性： select a.policy, , b.职位 from mid_policy_fact a, 业务员表 b where a.c_emp_cde = b.c_emp_cde and b.start_date t_udr_date,截取时间为常量,截取时间为连接主表的相关时间字段,时间链的生成技巧,假定经过相应的处理后得到一个数据集T，T可以是一个临时表、视图或子查询，并包含一个日期字段Start_date(不可有空值)作为时间链的开始日期，但End_date还未生成，则可以用以下SQL生成时间链： Select anchor_id, , start_date, lead(start_date,1,to_date(99991231,yyyymmdd) over (partition by anchor_id order by start_date) as end_date From T; 其中Anchor_id是数据集的锚ID，比如保单号、单据号等,历史表增量加载策略,在常规情况下，该算法也适合全量加载，此时数据集是全量数据，而目标表的初始状况是空表。,由于目标表中要求第一条版本记录的Start_date应该为1900-1-1，这里的算法要考虑T01的增量在目标表中没有前续版本记录的情况,已赚保费的计算,以上有效时间段不能采用保单版本的时间链，而要按业务上的保单生效规则来确定时间范围 一般情况下，保单的生效开始日期就是保险起期，但对于倒签单，则按核保日期计算，因此回避了在此之前已汇总的漏算问题 最好在承保中间表中事先计算出业务有效时间范围，作为业务时间链，不仅避免在汇总指标时多做计算，而且有利于测试时核对数据 由于涉及到除法，如果时间维的粒度太细，可能导致过多的精度误差，建议汇总表中该指标字段保留更多的小数位，比如68位,2007.1,2007.6,安邦以前的算法 累加各版本保费变化乘以当期内的保险期限时间段在当前保险期限内的占比,2007.1,2007.6,较准确的算法 累加各版本当前保费乘以当期内的有效时间段在当前保险期限内的占比,每个线段表示保单各版本用于计算当期有效天数的基准时间段，而被阴影覆盖的部分作为时间占比的分子,承保理赔分析应用场景假设,Ply_prof_dm 为简化起见，假定保单维表只有一个维属性：保险起期（月）,DM_FACT 承保理赔分析事实表经过聚合计算后生成的局部视图，含有三个指标，除统计月份