保险仓库数据模型设计课件

?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.

安邦BI系统数据模型介绍

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.主

题

?维度建模的基本概念

?模型概述

?模型介绍

?模型运用方法与技巧

?相关技术问题

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.如何建立分析集市模型

行业经验

BI.InsuranceDWM工作组和建模

持续发展

业务需求

安邦分析

集市模型

?业务驱动

?根据固定报表和KPI、持续率、承保理赔、执行力、再保五个数据集市的业务需求,结合BI.InsuranceDWM的建模方法,建立TaipingEDW分析集市模型

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?维度建模

?一种非规范化的关系模型

?由一组属性构成的表所组成

?表跟表之间的关系通过关键字和外键来定义

?以良好的可理解性和方便的产生报表来进行数据组织，很少考虑修改的性能

?通过SQL或者相关的工具实现数据的查询和维护

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?事实表（一）

?每一个事实表通常包含了业务需求所关心的一系列的指标值

?每一个事实表的行包括

?具有可加性的数值型的指标值

?文本事实通常具有不可预见的内容，很难进行分析

?与维表相连接的外键

?通常具有两个和两个以上的外键

?外键之间表示维表之间多对多的关系

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?事实表（二）

?事实表的特征

?非常的大

??包含几万、几十万甚至几百几千条的记录

?内容相对的窄

??

列数较少

?经常发生变化

??

现实世界中新事件的发生→事实表中增加一条记录

典型情况下，仅仅是数据的追加

?事实表的使用

各类指标值的聚集计算

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?维表（一）

?每一张维表对应现实世界中的一个对象或者概念

??例如：客户、产品、保单、标的、案件

?维表的特征

?包含了众多描述性的属性列

如保单维表里的销售渠道、保费区间、风险等级等

?通常情况下，跟事实表相比，行数相对较小

通常<10万条

?内容相对固定

几乎就是一类查找表

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?维表（二）

?维表的应用

?基于维属性的过滤（切片、切块等）

?基于维属性的各种聚集操作

?报表中各类标签的主要来源

?事实表通过维表进行引用

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

Facts

?属性个数少（窄）

?记录行数多（大）

?数值型指标

?随着时间的推移，数据增长

Dimensions

?属性个数多（宽）

?记录行数少（小）

?描述性属性

?静态的，很少发生变化

?事实表与维表的比较

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析主题域数据(AnalyticalSubjectAreas)

?通过事实（Facts）和维度（Dimensions）模型方式表达，为典型保险分析应用提供分析型数据架构，为数据集市提供统一的指标和维度，为数据应用提供统一的数据信息。

?分析主题域数据，将业务需求阶段定义的分析主题域，按照维度建模方法，格式化成符合维度－事实表的星型模型或雪花模型。其中维度信息由核心原子数据按照定义的层次形成相关的维度视图，事实表数据通过核心原子数据相关主题域数据经过汇总计算得到。核心原子数据和分析数据分别映射到不同的数据集市中。

?其中，典型的分析主题域如通用承保分析（Universalunderwritinganalysis）如下图：

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型设计步骤

需求

整理指标维度矩阵

设计集市层模型

设计分析层模型

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.维度指标矩阵

维度指标矩阵是把需求转化为多维模型的重要工具

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.业务分析需求举例

?

与上一年相比，各险种所占的份额有何差异??

每个机构在各险种所占份额是多少?机构

险种

时间

2006和2007年度各险种份额对比

20062007Thousands2006年度各机构在各险种上的份额

车险财产险货运险其它险车险财产险货运险其它险0%20%40%60%80%100%机构1机构2机构3车险财产险货运险其它险?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.维度建模举例

时间

机构

险种

客户

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.主

题

?维度建模的基本概念

?模型概述

?模型介绍

?模型运用方法与技巧

?相关技术问题

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.元数据

模型在整个系统架构中的定位

AT&T?ETL过程

核心

收付

车险

理赔

财务

?面向业务应用

?数据集市

?Cube?最终用户

?基本上与业务数据同构

?保留详细交易数据

数据仓库

?面向分析主题

?轻度汇总数据

?StarSchema建模

?中间表

?财务账户?活动?理赔?事件?收付费处?理?资金供应?角色?实物?地理位置?协议?产品分析层/数据集市模型源系统模型ODS模型需求模型分析层

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析层模型主题域

分析层模型每个主题域为同类的业务处理，共享相同的维度

?承保理赔分析

记录安邦保险所关注的主要承保理赔业务指标。

?收付费分析

记录财务收付中应收保费、实收保费、应收率、应付赔款、实付赔款等收付类信息。

?核赔效率分析

记录理赔环节中查勘、定损、核损、缮制过程的相关指标。

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.主

题

?维度建模的基本概念

?模型概述

?模型介绍

?模型运用方法与技巧

?相关技术问题

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析层模型结构

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.收付费分析的星型结构

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析层模型介绍

?“承保理赔分析事实表”、“收付费分析事实表”、“核赔效率分析事实表”都是经轻度汇总生成的表，它们全部都是事务型事实表

?中间事实表都是经过版本化的历史表，保存最细粒度的数据

?中间事实表主要用于生成轻度汇总表的过渡，除此之外，通过冗余相关属性，中间事实表的设计也考虑了其它无法从直接从分析层计算的指标的计算

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.事务型事实和快照型事实

机构

险种

日期

未决变化

2010102

1011

2008-9-12

1000

2010102

1011

2008-9-13

-300

2010102

1011

2008-9-12

2200

2010201

2031

2008-9-12

100

2010202

2001

2008-9-12

1000

2010202

2001

2008-9-13

1200

2010202

2001

2008-9-14

-2200

事务型

快照型

快照型事实表的指标通常包含时点值，随着时间积累，维度组合会越来越细，此类型的事实表通常用于粒度较粗的数据集市或Cube；而事务型事实表的度量都是可累加的，其对于维度较多的轻度汇总表是有利。如果OLAPserver支持在事务型事实表上计算时点值，建议在维度较细的Cube上使用事务型事实。

机构

险种

日期

期末未决

2010102

1011

2008-9-12

1000

2010102

1011

2008-9-13

700

2010102

1011

2008-9-14

2900

2010201

2031

2008-9-12

100

2010201

2031

2008-9-13

100

2010201

2031

2008-9-14

100

2010202

2001

2008-9-12

1000

2010202

2001

2008-9-13

2200

2010202

2001

2008-9-14

0

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.中间层历史数据的版本化

?数据仓库中的历史数据决不会被删除或覆盖，因此需要加入数据版本特性来区分随时间变化的业务信息，使得在任一给定时间点上都能检索到正确的数据。

?数据是否需要版本化，首先依赖于实体是否有时变特性，比如“报案信息”通常是在某个时间点上发生，它就没有时变特性，因此就不需要版本化。

?在实体中加入时间戳属性可以实现数据版本化，这个时间戳可以基于业务涵义，如对保单的某次批改的生效日期；也可以仅仅是技术上的，比如员工某项登记信息被修改，则生成新的员工记录，并指定该记录的有效(Valid)期间，ETL处理需要实现数据相邻版本时间戳的链接。

?需要为版本化的实体找出一个锚ID来唯一标识每个数据实例，比如保单实体的锚ID可以是保单号，表明该实体中最原子的粒度是保单。

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.时间戳与数据版本

业务信息的不同版本的时间链

时间戳(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?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.承保中间表场景示例

保单号

批单号

保险起期

保险金额

保费

核保日期

批改原因

startdate

enddate

0001

－－－

2006-3-7

12000

80

2006-3-4

N/A

1900-1-1

2006-5-18

0001

0001

2006-3-7

0

0

2006-5-18

注销

2006-5-18

9999-12-31

0002

－－－

2006-4-19

22300

120

2006-4-7

N/A

1900-1-1

2006-5-10

0002

0002

2006-4-19

22300

120

2006-5-10

修改

2006-5-10

2006-5-21

0002

0003

2006-4-19

22300

138

2006-5-21

加保

2006-5-21

9999-12-31

0003

－－－

2007-1-12

120000

890

2007-1-8

N/A

1900-1-1

9999-12-31

0004

－－－

2007-5-2

57000

500

2007-4-22

N/A

1900-1-1

9999-12-31

0005

－－－

2008-3-14

400000

3600

2008-3-8

N/A

1900-1-1

2008-4-6

0005

0004

2008-3-14

310000

3000

2008-4-6

退保

2008-4-6

2008-4-16

0005

0005

2006-3-14

310000

3000

2008-4-16

修改

2008-4-16

9999-12-31

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.收付费中间表场景示例

单据号

type

状态

应收保费

实收保费

应付赔款

实付赔款

应付手续费

实付手续费

startdate

enddate

0001

保费

应收

2000.00

0.00

1900-1-1

2006-5-18

0001

保费

实收

2000.00

2000.00

2006-5-18

9999-12-31

0002

手续费

应付

132.00

0.00

1900-1-1

2006-5-10

0002

手续费

实付

132.00

132.00

2006-5-10

2006-5-11

0002

手续费

冲正

132.00

0.00

2006-5-11

9999-12-31

0003

保费

应收

10000.00

0.00

1900-1-1

9999-12-31

0004

赔款

应付

26700.00

0.00

1900-1-1

9999-12-31

0005

赔款

应付

14700.00

0.00

1900-1-1

2006-4-6

0005

赔款

实付

14700.00

14700.00

2006-4-6

9999-12-31

0006

保费

应收

3500.00

0.00

1900-1-1

9999-12-31

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.理赔中间事实表场景示例

赔案号

次数

立案日期

估损金额

赔付金额

结案日期

结案状态

startdate

enddate

0001

1

2006-3-7

3000

9999-12-31

N

1900-1-1

2006-5-18

0001

1

2006-3-7

3000

3200

2006-5-18

Y

2006-5-18

9999-12-31

0002

1

2006-4-19

12000

9999-12-31

N

1900-1-1

2006-5-10

0002

1

2006-4-19

11000

9999-12-31

N

2006-5-10

2006-5-21

0002

1

2006-4-19

11000

10000

2006-5-21

Y

2006-5-21

9999-12-31

0003

1

2007-1-12

900

9999-12-31

N

1900-1-1

9999-12-31

0004

1

2007-5-2

57000

9999-12-31

N

2007-5-2

9999-12-31

0005

1

2008-3-14

21000

9999-12-31

N

1900-1-1

2006-4-6

0005

1

2008-3-14

21000

20000

2006-4-6

Y

2006-4-6

9999-12-31

0005

2

2006-4-16

300

300

2006-5-26

Y

1900-1-1

9999-12-31

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.中间层的构建算法

?请安邦项目组自行用Visio画出算法流程图，分页贴在此处

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析层模型未涵盖的特殊分析应用

需求中有部分的指标在计算上比较独特，且与其它指标没有太多的共享价值，因此未纳入分析层模型的考查范畴，这些指标主要有：

?核保效率分析——用mid_policy_fact与核保信息表关联，再经过聚合计算可得到相应的分析指标

?理财险网点活动率——该指标为非累计指标，不考虑安置在分析层，可在构建CUBE时按原始算法计算，但日、月、季、年的汇总值要分别计算

?车贷险指标——该类指标的数据源比较特殊，不考虑建立分析层，可在构建CUBE时按原始算法计算

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.从中间层到分析层——承保理赔

?签单类

?只计算保单，口径为：核保日期在当期

andstart_date=‘1900-1-1'

?主要指标有：保险金额、保单保费、保单笔数、累计储金、已验车数量、应验车数量

?需要减去退保和注销

?会计年度类

?不考虑版本，所有记录一起计算，口径为：核保日期在当期

?保额和保费只累计变化值

?主要指标有：保费收入、批增批减保费（件数）、退保（注销）保费（件数）等

?理赔类

?口径：结案日期在当期或估损修改日期在当期

?部分指标只考虑起始两个版本，即立案和结案，比如立案估损金额、结案天数

?主要指标有：已决笔数、已决赔款、未决笔数变化、未决赔款变化、立案估损金额、估损偏差绝对值、加权估损金额、结案天数

?特殊口径类

?已赚保费－－算法参见“模型运用方法与技巧”

有部分指标不需要定义在分析层，因为它们可以从以上指标衍生，比如车险续保指标、理财险指标，都可以从承保理赔分析表以保险止期维度为口径聚合生成。

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.从中间层到分析层——收付费

?收付费指标的主要口径基本上都是以收付确认日期来计算，在源系统中体现为应收(付)确认日期和实收(付)确认日期

?需求中要求按币种分别展现人民币和非人民币的统计值，由于本事实表已经包含保单维度，因此这些指标都可以根据保单维度中的币种重新汇总生成

?应收保费变化的计算规则是：应收一笔保费以正数累计，实收一笔保费以负数累计，对实收的冲正以正数累计

?滚动12个月财务保费收入是应收保费收入的衍生指标

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.核赔效率分析

?核赔效率分析的指标基本都有共享的维度，因此为其建立一个分析层主题是合适的

?核赔效率分析涉及的源表比较分散，且增量机制较为复杂，因此建立中间表有一定的难度，目前的策略是参照原指标算法分别计算出各指标，并插入一临时表中，再对该表执行聚合操作

?由于源系统可能会更新核赔数据的状态，因而导致部分指标存在“过期”现象，在目前没有历史数据支持的情况，我们的解决办法是由业务部门确定一个过期阀值天数N，超过N天之前的指标不会再过期，那么每天加载核赔效率事实表时，不仅仅计算当天的事实数据，而是把N天之前的数据也都复算一遍

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.主

题

?维度建模的基本概念

?模型概述

?模型介绍

?模型运用方法与技巧

?相关技术问题

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.数据版本的截取时间点

?查询出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

截取时间为常量

截取时间为连接主表的相关时间字段

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.时间链的生成技巧

?假定经过相应的处理后得到一个数据集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，比如保单号、单据号等

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.历史表增量加载策略

在常规情况下，该算法也适合全量加载，此时数据集是全量数据，而目标表的初始状况是空表。

T02查询目标表，条件是：Achor_id存在于T01中；End_date为'9999-12-31'T01增量数据集目标表（初始状态）T03Unionall删除T02在目标表中的相应记录整理时间拉链插入有时间链的T03记录到目标表结束由于目标表中要求第一条版本记录的Start_date应该为‘1900-1-1‘，这里的算法要考虑T01的增量在目标表中没有前续版本记录的情况

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.已赚保费的计算

?以上有效时间段不能采用保单版本的时间链，而要按业务上的保单生效规则来确定时间范围

?一般情况下，保单的生效开始日期就是保险起期，但对于倒签单，则按核保日期计算，因此回避了在此之前已汇总的漏算问题

?最好在承保中间表中事先计算出业务有效时间范围，作为业务时间链，不仅避免在汇总指标时多做计算，而且有利于测试时核对数据

?由于涉及到除法，如果时间维的粒度太细，可能导致过多的精度误差，建议汇总表中该指标字段保留更多的小数位，比如6~8位

2007.12007.6安邦以前的算法

累加各版本保费变化乘以当期内的保险期限时间段在当前保险期限内的占比

2007.12007.6较准确的算法

累加各版本当前保费乘以当期内的有效时间段在当前保险期限内的占比

每个线段表示保单各版本用于计算当期有效天数的基准时间段，而被阴影覆盖的部分作为时间占比的分子

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.承保理赔分析应用——场景假设

Ply_prof_id

Dpt_id

统计月份

签单件数

已决件数

未决件数变化

1001

2001

2007.2

1

0

3

1001

2001

2007.3

2

1

-1

1001

2002

2007.3

1

0

1

1002

2002

2007.3

1

1

2

1001

2001

2007.4

0

1

1

1001

2002

2007.4

1

0

2

1002

2002

2007.4

2

1

-1

1002

2003

2007.4

1

0

1

1002

2003

2007.5

1

0

0

1001

2002

2007.5

0

1

0

1002

2002

2007.5

0

1

-1

1002

2003

2007.6

0

1

1

Ply_prof_id保险起期

10012007.210022007.3Ply_prof_dm为简化起见，假定保单维表只有一个维属性：保险起期（月）

DM_FACT承保理赔分析事实表经过聚合计算后生成的局部视图，含有三个指标，除统计月份外的维度组合有四种（不同的颜色区分）

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.承保理赔分析应用——转换成快照型事实

?经过与月维表的分区外连接，以上查询填充了遗漏的月份，并利用分析函数计算出“期末未决”这个时点值。可以利用此查询建立Cube?DM_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=统计月份))?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.承保理赔分析应用——保单年度算法

保单年度分析是在两个时间维度上的快照型事实，两个时间维度分别是保险起期和截止月份，所有指标都是在截止月份维度上的时点值，计算该快照型事实可参考下面的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.保险起期))?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.承保理赔分析应用——保单年度快照事实

保险起期

Dpt_id

截止月份

签单件数

已决件数

未决件数

2007.2

2001

2007.2

1

0

3

2007.2

2001

2007.3

3

1

2

2007.2

2001

2007.4

3

2

3

2007.2

2001

2007.5

3

2

3

2007.2

2001

2007.6

3

2

3

2007.2

2002

2007.2

0

0

0

2007.2

2002

2007.3

1

0

1

2007.2

2002

2007.4

2

0

3

2007.2

2002

2007.5

2

1

3

2007.2

2002

2007.6

2

1

3

2007.3

2002

2007.3

1

1

2

2007.3

2002

2007.4

3

2

1

2007.3

2002

2007.5

3

3

0

2007.3

2002

2007.6

3

3

0

2007.3

2003

2007.3

0

0

0

2007.3

2003

2007.4

1

0

1

2007.3

2003

2007.5

2

0

1

2007.3

2003

2007.6

2

1

2

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.保单年度分析应用的前端展现（一）

?保单年度分析应用基于前述快照事实构建，其展现的模式应该格外注意截止月份这一维度的运用

?截止月份是必需的展现维度，并且不能在其展现方向上做合计，常规的模式是把截止月份作为单选条件

?忽略截止月份的展现意味着要在该维度上进行聚合，这种计算是无意义的

保单年度分析01000002000003000004000005000006000002007年1月2007年2月2007年3月2007年4月2007年5月2007年6月2007年7月2007年8月2007年9月2007年10月2007年11月2007年12月保险起期满期保费正确的展现模式

错误的展现模式

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.保单年度分析应用的前端展现（二）

满期赔付率趋势分析是能体现保单年度分析价值的典型案例

满期赔付率趋势分析0.00%10.00%20.00%30.00%40.00%50.00%60.00%70.00%80.00%90.00%2008.12008.22008.32008.42008.52008.62008.72008.8截止日期满期赔付率2007年1季度2007年2季度2007年3季度2007年4季度?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.主

题

?维度建模的基本概念

?模型概述

?模型介绍

?模型运用方法与技巧

?相关技术问题

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.相关技术问题

?数据抽取增量机制主要涉及到中间层表，目前承保和收付费的可以忽略Update的影响，可以直接以业务创建时间为增量时间戳，而理赔中间表的增量加载要求业务系统作出变更，规范时间戳的更新，安邦的已经部署对业务系统的改造

?业务系统理赔模块的改造内容还包含估损历史的记载，以前的系统对估损金额的修改是直接地Update，改造后系统将保留估损的历史

?本期项目暂时不对编码进行全面的整合，而是以源系统编码为准

?财务的金额数据中未包含汇率系数，原报表系统以每年更新一次的汇率表来进行转换，本期项目仍按此方案实行

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.维表的整合问题

?机构和险种表从源系统抽取，重新生成层级结构

?员工作为独立的名称转换表

?发动机型号、厂牌车型都需要整合生成层级结构

?其它的维度直接归入Codestring?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.

安邦BI系统数据模型介绍

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.主

题

?维度建模的基本概念

?模型概述

?模型介绍

?模型运用方法与技巧

?相关技术问题

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.如何建立分析集市模型

行业经验

BI.InsuranceDWM工作组和建模

持续发展

业务需求

安邦分析

集市模型

?业务驱动

?根据固定报表和KPI、持续率、承保理赔、执行力、再保五个数据集市的业务需求,结合BI.InsuranceDWM的建模方法,建立TaipingEDW分析集市模型

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?维度建模

?一种非规范化的关系模型

?由一组属性构成的表所组成

?表跟表之间的关系通过关键字和外键来定义

?以良好的可理解性和方便的产生报表来进行数据组织，很少考虑修改的性能

?通过SQL或者相关的工具实现数据的查询和维护

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?事实表（一）

?每一个事实表通常包含了业务需求所关心的一系列的指标值

?每一个事实表的行包括

?具有可加性的数值型的指标值

?文本事实通常具有不可预见的内容，很难进行分析

?与维表相连接的外键

?通常具有两个和两个以上的外键

?外键之间表示维表之间多对多的关系

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?事实表（二）

?事实表的特征

?非常的大

??包含几万、几十万甚至几百几千条的记录

?内容相对的窄

??

列数较少

?经常发生变化

??

现实世界中新事件的发生→事实表中增加一条记录

典型情况下，仅仅是数据的追加

?事实表的使用

各类指标值的聚集计算

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?维表（一）

?每一张维表对应现实世界中的一个对象或者概念

??例如：客户、产品、保单、标的、案件

?维表的特征

?包含了众多描述性的属性列

如保单维表里的销售渠道、保费区间、风险等级等

?通常情况下，跟事实表相比，行数相对较小

通常<10万条

?内容相对固定

几乎就是一类查找表

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

?维表（二）

?维表的应用

?基于维属性的过滤（切片、切块等）

?基于维属性的各种聚集操作

?报表中各类标签的主要来源

?事实表通过维表进行引用

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型概念

Facts

?属性个数少（窄）

?记录行数多（大）

?数值型指标

?随着时间的推移，数据增长

Dimensions

?属性个数多（宽）

?记录行数少（小）

?描述性属性

?静态的，很少发生变化

?事实表与维表的比较

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析主题域数据(AnalyticalSubjectAreas)

?通过事实（Facts）和维度（Dimensions）模型方式表达，为典型保险分析应用提供分析型数据架构，为数据集市提供统一的指标和维度，为数据应用提供统一的数据信息。

?分析主题域数据，将业务需求阶段定义的分析主题域，按照维度建模方法，格式化成符合维度－事实表的星型模型或雪花模型。其中维度信息由核心原子数据按照定义的层次形成相关的维度视图，事实表数据通过核心原子数据相关主题域数据经过汇总计算得到。核心原子数据和分析数据分别映射到不同的数据集市中。

?其中，典型的分析主题域如通用承保分析（Universalunderwritinganalysis）如下图：

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析集市模型设计步骤

需求

整理指标维度矩阵

设计集市层模型

设计分析层模型

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.维度指标矩阵

维度指标矩阵是把需求转化为多维模型的重要工具

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.业务分析需求举例

?

与上一年相比，各险种所占的份额有何差异??

每个机构在各险种所占份额是多少?机构

险种

时间

2006和2007年度各险种份额对比

20062007Thousands2006年度各机构在各险种上的份额

车险财产险货运险其它险车险财产险货运险其它险0%20%40%60%80%100%机构1机构2机构3车险财产险货运险其它险?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.维度建模举例

时间

机构

险种

客户

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.主

题

?维度建模的基本概念

?模型概述

?模型介绍

?模型运用方法与技巧

?相关技术问题

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.元数据

模型在整个系统架构中的定位

AT&T?ETL过程

核心

收付

车险

理赔

财务

?面向业务应用

?数据集市

?Cube?最终用户

?基本上与业务数据同构

?保留详细交易数据

数据仓库

?面向分析主题

?轻度汇总数据

?StarSchema建模

?中间表

?财务账户?活动?理赔?事件?收付费处?理?资金供应?角色?实物?地理位置?协议?产品分析层/数据集市模型源系统模型ODS模型需求模型分析层

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析层模型主题域

分析层模型每个主题域为同类的业务处理，共享相同的维度

?承保理赔分析

记录安邦保险所关注的主要承保理赔业务指标。

?收付费分析

记录财务收付中应收保费、实收保费、应收率、应付赔款、实付赔款等收付类信息。

?核赔效率分析

记录理赔环节中查勘、定损、核损、缮制过程的相关指标。

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.主

题

?维度建模的基本概念

?模型概述

?模型介绍

?模型运用方法与技巧

?相关技术问题

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析层模型结构

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.收付费分析的星型结构

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.分析层模型介绍

?“承保理赔分析事实表”、“收付费分析事实表”、“核赔效率分析事实表”都是经轻度汇总生成的表，它们全部都是事务型事实表

?中间事实表都是经过版本化的历史表，保存最细粒度的数据

?中间事实表主要用于生成轻度汇总表的过渡，除此之外，通过冗余相关属性，中间事实表的设计也考虑了其它无法从直接从分析层计算的指标的计算

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.事务型事实和快照型事实

机构

险种

日期

未决变化

2010102

1011

2008-9-12

1000

2010102

1011

2008-9-13

-300

2010102

1011

2008-9-12

2200

2010201

2031

2008-9-12

100

2010202

2001

2008-9-12

1000

2010202

2001

2008-9-13

1200

2010202

2001

2008-9-14

-2200

事务型

快照型

快照型事实表的指标通常包含时点值，随着时间积累，维度组合会越来越细，此类型的事实表通常用于粒度较粗的数据集市或Cube；而事务型事实表的度量都是可累加的，其对于维度较多的轻度汇总表是有利。如果OLAPserver支持在事务型事实表上计算时点值，建议在维度较细的Cube上使用事务型事实。

机构

险种

日期

期末未决

2010102

1011

2008-9-12

1000

2010102

1011

2008-9-13

700

2010102

1011

2008-9-14

2900

2010201

2031

2008-9-12

100

2010201

2031

2008-9-13

100

2010201

2031

2008-9-14

100

2010202

2001

2008-9-12

1000

2010202

2001

2008-9-13

2200

2010202

2001

2008-9-14

0

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.中间层历史数据的版本化

?数据仓库中的历史数据决不会被删除或覆盖，因此需要加入数据版本特性来区分随时间变化的业务信息，使得在任一给定时间点上都能检索到正确的数据。

?数据是否需要版本化，首先依赖于实体是否有时变特性，比如“报案信息”通常是在某个时间点上发生，它就没有时变特性，因此就不需要版本化。

?在实体中加入时间戳属性可以实现数据版本化，这个时间戳可以基于业务涵义，如对保单的某次批改的生效日期；也可以仅仅是技术上的，比如员工某项登记信息被修改，则生成新的员工记录，并指定该记录的有效(Valid)期间，ETL处理需要实现数据相邻版本时间戳的链接。

?需要为版本化的实体找出一个锚ID来唯一标识每个数据实例，比如保单实体的锚ID可以是保单号，表明该实体中最原子的粒度是保单。

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.时间戳与数据版本

业务信息的不同版本的时间链

时间戳(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?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.承保中间表场景示例

保单号

批单号

保险起期

保险金额

保费

核保日期

批改原因

startdate

enddate

0001

－－－

2006-3-7

12000

80

2006-3-4

N/A

1900-1-1

2006-5-18

0001

0001

2006-3-7

0

0

2006-5-18

注销

2006-5-18

9999-12-31

0002

－－－

2006-4-19

22300

120

2006-4-7

N/A

1900-1-1

2006-5-10

0002

0002

2006-4-19

22300

120

2006-5-10

修改

2006-5-10

2006-5-21

0002

0003

2006-4-19

22300

138

2006-5-21

加保

2006-5-21

9999-12-31

0003

－－－

2007-1-12

120000

890

2007-1-8

N/A

1900-1-1

9999-12-31

0004

－－－

2007-5-2

57000

500

2007-4-22

N/A

1900-1-1

9999-12-31

0005

－－－

2008-3-14

400000

3600

2008-3-8

N/A

1900-1-1

2008-4-6

0005

0004

2008-3-14

310000

3000

2008-4-6

退保

2008-4-6

2008-4-16

0005

0005

2006-3-14

310000

3000

2008-4-16

修改

2008-4-16

9999-12-31

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.收付费中间表场景示例

单据号

type

状态

应收保费

实收保费

应付赔款

实付赔款

应付手续费

实付手续费

startdate

enddate

0001

保费

应收

2000.00

0.00

1900-1-1

2006-5-18

0001

保费

实收

2000.00

2000.00

2006-5-18

9999-12-31

0002

手续费

应付

132.00

0.00

1900-1-1

2006-5-10

0002

手续费

实付

132.00

132.00

2006-5-10

2006-5-11

0002

手续费

冲正

132.00

0.00

2006-5-11

9999-12-31

0003

保费

应收

10000.00

0.00

1900-1-1

9999-12-31

0004

赔款

应付

26700.00

0.00

1900-1-1

9999-12-31

0005

赔款

应付

14700.00

0.00

1900-1-1

2006-4-6

0005

赔款

实付

14700.00

14700.00

2006-4-6

9999-12-31

0006

保费

应收

3500.00

0.00

1900-1-1

9999-12-31

?2006FEnetSoftwareCo.,Lt?2006FEnetSoftwareCo.,Ltd.AllRightsReserved.理赔中间事实表场景示例

赔案号

次数

立案日期

估损金额

赔付金额

结案日期

结案状态

startdate

enddate

0001

1

2006-3-7

3000

9999-12-31

N

1900-1-1

2006-5-18

0001

1

2006-3-7