EDW-(DM数据仓库数据建模)模型设计

BI.Insurancei.DWMforP&C模型设计说明,张海彪,日程,为什么需要模型模型的组织结构模型实施方法模型设计策略Q&A,|,日程,为什么需要模型模型的组织结构模型实施方法模型设计策略Q&A,|,EDW体系架构,源系统层,ETL层,数据仓库层,ETL层,数据集市层,应用层,展现层,手工数据,外部数据,数据仓库,保险数据模型,核心业务,财务系统,再保险系统,人意险系统,精算系统,客户关系管理OCRM,客户讯息ECIF,业务量分析数据集市,业务持续性分析数据集市,ALM数据集市,财务分析数据集市,车险承保分析通用承保分析,风险管理应用,ALM应用,财务分析应用,aCRM数据集市,aCRM报告,大客户分析管理系统,aCRM引擎,数据挖掘引擎,数据挖掘应用,企业信息门户,企业统一分析平台,元数据库,监管报表,管理报表,运营报表,仪表盘,随机查询,多维分析,“数据和信息集成平台”“统一的分析平台”“唯一的信息出口”,为什么需要企业模型？,EDW数据模型在项目实施中的作用,DWM数据仓库模型,BAM业务分析模型,运营型业务系统,数据仓库,数据集市,报表分析型应用,BSA业务模版应用,日程,为什么需要模型模型的组织结构模型实施方法模型设计策略Q&A,|,模型总体结构EM&DataMarts,核心原子数据,事实表和维度,企业模型,营销管理快速入门,客户细分和管理,保险盈利性分析,潜在客户管理,数据集市,导出,业务数据模型,映射,指标要素,需求模型,财务报表数据集市,中介绩效分析数据集市,健康险盈利性管理数据集市,DWM数据模型逻辑结构,BI.Insurancei.DWMforP&C,底层数据模型主题域说明：Agreement：保单、批单申请及管理；Claim：理赔FinancialTransaction：应收应付、实收实付以及交易关联Party：当事方，包括当事方的组织结构、角色结构及类型MoneyProvision：资金管理SpecificationAndProduct：规范及产品管理Place：地点Code：标准代码Activity：活动管理PhysicalObject：实物、标的管理,BI.Insurancei.DWM-Agreement,BI.Insurancei.DWM-Claim,BI.Insurancei.DWM-PhysicalObject,日程,为什么需要模型模型的组织结构模型实施方法模型设计策略Q&A,|,步骤：,流程：,产出：,原则：,需求文档：1.报表需求2.功能需求3.非功能需求,1.目前的报表2.想做的报表3.想做的功能,1.数据筛选清单2.数据源报告：3.数据质量分析报告4.代码清单,Mapping文档：源-模型对应关系,A筛选：去掉ETL需要而模型不需要的字段,1.逻辑模型2.物理模型3逻辑物理数据元素对照表,设计文档：1.Mapping流程图2.数据元素Mapping文档,A：数据源报告：1.主要功能2.历史数据情况3.与其它系统关系4.联系人,B：数据质量报告：1.数据类型2.值分布3.关联情况,B映射：1.映射到EM2.结合性能考虑3.结合实现考虑,数据筛选：1.程序控制,计算,通讯,安全控制配置,日志2.汇总类结果一般不要3.可以由其它字段算出的字段一般不要4.从其它系统导入的数据不要.5.代码表不要。6.单纯的险种定义信息不要，但是具体保单中涉及的险种定义信息可以要。,1.多维模型设计文档：维度指标派生指标2.需求-模型映射文档3.报表样张4.操作说明,EDW具体实施流程,日程,为什么需要模型模型的组织结构模型实施方法模型设计策略Q&A,|,Hashcode,问题的提出:进行增量加载时无法快速判断对表的原有记录是否新插入。例如：1.理赔案件发生的时候,增量文件会把保单数据也传来2.保单增量过来,可能只是投保人的信息改了,而目标保单表所需信息并没有改变解决方案：使用增量的比较字段生成Hashcode。在对表进行增量加载时，对增量文件中的每一条记录生成Hashcode将生成完的Hashcode与原表中同一anchorid并且最新的记录的Hashcode进行比较如果一致的话，即不动作；如果不一致的话，即新插入。使用示例：在individualagreement表中使用各个需要保留历史信息的字段生成hashcode。在增量加载时，使用业务增量文件中的字段生成hashcode。与Individualagreement表中同一agreementid的最新记录的hashcode进行比较。如果一致，即不动作如果不一致，则插入新记录。备注：relationship表是要根据业务去判断是否关系已经存在，然后，如果有其他属性（如：Roleplayer-PhysicalobjectRlship.Usage），才需要用hashcode判别是否重复。,|,Hashcode字段组成规则,带anchor的实体带status表的实体（Commercialagreement、Groupagreement、Individualagreement、Claimfolder、Elementaryclaim）除表的主键、typeid、Partitionkey、Status、Statusdate、Statusreason、Validfromdate、Validtodate、Effectivefromdate、Effectivetodate、Populationtimestamp之外的所有字段不带status表的实体除表的主键、typeid、Partitionkey、Validfromdate、Validtodate、Effectivefromdate、Effectivetodate、Populationtimestamp之外的所有字段不带anchor的实体原则上不需要保留历史，一般执行Update操作。如果有需要的，ETLMapping特别指明关联实体对于需要保留历史的关联类型，除Identifier、Partitionkey、Natureid、Leftanchoridentifier、Rightanchoridentifier、Leftentityidentifier、Leftentitytypeid、Rightentityidentifier、Rightentitytypeid、Validfromdate、Validtodate、Effectivefromdate、Effectivetodate、Populationtimestamp之外的所有字段,|,Partitionkey,问题的提出:在进行多表关联时，所涉及的关联表行数巨大，关联速度达不到要求。解决方案：在所有大表中建立Partitionkey,按照该键的键值对表进行物理分区。Partitionkey从Partitionconfig表中获得。分区策略是按照分公司进行分区。使用示例：表A与表B进行关联时，如下进行selectA.column1,B.column2fromA,BwhereA.foreign_key=B.Primary_keyandA.partition_keyin(selectStoragepartitionfromPartitionconfigwhereBranchcompanyid=xxxx)andB.partition_keyin(selectStoragepartitionfromPartitionconfigwhereBranchcompanyid=xxxxxxx),|,|,对保单和理赔状态的特殊处理,问题的提出：保单在承保和保全的整个过程中状态变化比较多，如按照IIW的原有设计，保单表中的会有巨量的历史记录；理赔在报案、立案和估损的整个过程中状态变化较多，如按照IIW的原有设计，理赔表中会有很多的历史记录。解决方案：将保单的状态变化过程剥离出来单独建表，在该表中保留与保单的关联；当有新状态插入时，更新对应的保单表中的状态。将理赔的状态变化过程剥离出来单独建表，在该表中保留与理赔的关联；当有新状态插入时，更新对应的理赔表中的状态。使用示例：增加Commercialagreementstatus，Groupagreementstatus，Individualagreementstatus表，分别记录Commercialagreement,Groupagreement,Individualagreement的状态变化历史。当前面状态发生该变时，在status表中插入新记录，更新对于原表中的状态字段。,对保单和理赔状态的特殊处理示例,|,Individualagreement,Individualagreementstatus,Left/RightEntityIDinRelationshiporRoleEntity,问题的提出在IIW中的不同subjectarea的实体关联通常是走关联实体的，例如：Physicalobject-AgreementRlship。在关联实体中是以anchorid进行连接的。在分析的时候，通常是应该按照当时的状况进行分析才有意义。由于EDW是保留历史信息的，同一个Physicalobject或Agreement会有多条记录，如何找到当时的记录，必须通过effectivefrom/todate的比对才能实现，这非常影响效率。解决方案在关联实体中增加Left/Rightentityidentifier，Left/RightentitytypeidLeft/Rightentitytypeid是指具体基础表的id号例如：Roadvehicle（2001260001）Left/Rightentityidentifier是指具体基础表中记录的主键id值例如：Roadvehicle中牌照号沪A000001车辆的第一条记录的Roadvehicleid值适用范围：FSRolePhysicalobject-AgreementRlship,|,SampleofLeft/RightEntityIDinRelationshiporRoleEntity,|,Roadvehicle,Individualagreement,Agreement,Physicalobject,PhysicalobjectAgreementRlship,被保标的,Partyroleinoperation/Internalperson,问题的提出在业务中有很多操作员角色，只有工号、姓名信息，没有身份证等其他信息；一个操作员在一个业务流程中会同时扮演不同角色，如在A保单核保中他是录入人，在B保单核保中他是复核人或者可能出现在A保单核保中他既是录入人又是复核人解决方案建立Internalperson表保存业务员、公司管理人员的个人信息，这些信息质量较差建立Partyroleinoperation表保存操作员角色信息，每次都生成新记录。录单员冗余到保单中，理赔的操作员也冗余到claimfolder中,|,关联实体的版本问题,由于关联实体本身没有对应的anchor实体，不存在版本问题，但是关联存在有以下两种变化情况。人“王五”拥有一栋房屋，在2007/1/1卖掉了。更新原有的RoleplayerphysicalobjectRlship记录的validtodate：if源系统有系统更新日期，则更新日期1；else，则“2006/12/31”effectivetodate：“2006/12/31”人“王五”拥有一栋房屋，在2007/1/1卖掉50的产权。更新原有的RoleplayerphysicalobjectRlship记录的validtodate：if源系统有系统更新日期，则更新日期1；else，则“2006/12/31”effectivetodate：“2006/12/31”（Ownershippercentage：100）插入新的RoleplayerphysicalobjectRlship记录validfromdate：if源系统有系统更新日期，则更新日期1；else，则“2007/1/1”effectivefromdate：“2007/1/1”Ownershippercentage：50,|,FinancialServicesRole,问题的提出Person存放人的基本信息，Externalorganisation和Internalorganisation存放机构的基本信息一个人和机构在不同环境下分别扮演不同角色，所以FinancialServicesRole存放与保单（各种协议）相关的金融服务角色，如保单持有人，被保险人，受益人等。Channelrole存放中介渠道角色信息，如营销员、收展员在分析集市中需要获取保单与业务员的关联信息，IIW原连接方式如图：,|,FinancialServicesRole（Financialservicesroleplayerid）,Person（Roleplayerid）,Channelrole（Channelroleplayerid）,优点：结构清晰统一缺点：渠道角色信息关联的太远，需要FinancialServicesRole+Channelrole+Person，影响效率,Person（Roleplayerid）,Externalorganisation（Roleplayerid）,FinancialServicesRole,解决方案FinancialServicesRole用把basisroleplayertypeid确定应连接Person还是ExternalorganisationFinancialServicesRole用把basisroleplayerid确定Person或Externalorganisation中记录的roleplayeridFinancialServicesRole用把basisroleplayerentityidentifier确定Person或Externalorganisation中记录的personid或Externalorganisationid使用示例,|,FinancialServicesRole（Financialservicesroleplayerid）,Person（Roleplayerid）,Channelrole（RoleplayeridChannelroleplayerid）,Person（Roleplayerid）,Externalorganisation（Roleplayerid）,Currencycode,问题的提出：在CPIC的实际业务中，可能出现多币种，在统计中需要进行多币种的转换。解决方案：在IIW模型中凡出现金额字段的表，都增加金额的币种及对应的RMB金额两类字段。原字段存放原币中金额，RMB金额存放折算成RMB的金额使用示例：Elementaryclaim表中增加Totalcostcurrency和TotalcostRMB字段备注：由于CPIC对多币种金额的统计有多种统计方式，不全部是按照发生制来折算RMB的。因此，统计转换金额到RMB的工作，留给统计部分执行，在原子层不计算。币种一定要填。,|,维度表的snapshot,问题的提出在分析层中，常用的维度表如：保单、立案。分析常用的属性是分散在各个表中的，如：保费、保额在ParticularMoneyProvision中。分析时如果再通过关联来找到这些信息，效率非常低。解决方案建立维度的snapshot表，将这些信息冗余存放在这些表中，每个月全量刷新一次。使用示例：ClaimfolderdimensionPolicydimensionElementaryclaimdimensionEventdimension,|,Commercialagreement/Groupagreement/Individualagreement的边界区分,Commercialagreement存放保险公司和机构投保人签订的关于承保要素约束的框架性协议；不是具体的保单。具体的保单要遵循该协议。Groupagreement团单单位和保险公司签订的保一组成员的保单，如：寿险团单、雇主责任险、旅游责任险。如果源系统提供了每个被保人的投保情况，这些记录在individualagreement（typeid个人凭证）中的。如：雇主责任险下每个人的投保份数。Individualagreement个单/个人凭证备注：根据国内系统的情况做了些调整，和机构投保人（非个人）签订的个单也存放在此。投保单按保单处理，只是状态是投保状态,|,Groupagreement/Individualagreement在ETL时处理,车险系统保单进入Individualagreement寿险保单根据来源表，决定进入groupagreement还是individualagreementCIBS(包括老系统)和人意险保单根据Financialservicesproduct中的Individualinsuranceflag判断个险，进入Individualagreement团险、个团皆可，进入groupagreement,|,最新记录标志,Effectivetodate=9999/12/3100:00:00,|,公司的拆分合并，partitionkey的处理1/4,分公司的拆分合并，不需要程序考虑，发生后手工处理。公司合并举例：原来有分公司A,分公司B,在2006/1/1分公司B合并到分公司A。,|,合并前,Partitionconfig,Externalorganisation,Individualagreement,公司的拆分合并，partitionkey的处理2/4,公司合并举例：原来有分公司A,分公司B,在2006/1/1分公司B合并到分公司A。,|,合并后,Partitionconfig,Externalorganisation,Individualagreement,RoleplayerRlship,公司的拆分合并，partitionkey的处理3/4,公司合并举例：原来有分公司A,在2006/1/1分公司A,拆分成分公司A和分公司B。,|,拆分前,Partitionconfig,Externalorganisation,Individualagreement,公司的拆分合并，partitionkey的处理4/4,公司合并举例：原来有分公司A,在2006/1/1分公司A,拆分成分公司A和分公司B。,|,拆分后,Partitionconfig,Externalorganisation,Individualagreement,RoleplayerRlship,按照typeid分表,将有些大表按照Typeid进行拆分举例：Individualagreement表按照保单和投保单拆成两张表,|,历史信息的处理,对含有历史记录的大表，应考虑将历史记录剥离出来单独建表，即原表保留最新的信息，而在剥离出来的表中包含这些信息的变化历史。举例：Individualagreement原来保留有保单的最新信息及这些信息的历史变化记录。这样这张表就将很大，记录数数以亿计。目前将它拆成2个表：表一，存放保单的最新信息，如最新状态，最新确认的起保日期等，同时保留每条记录最新的刷新时间表二，存放保单经常变化的值的变化历史，如：保单状态的变化历史表三，存放保单所有历史变化的信息,|,增加表的冗余字段,问题的提出：原有设计中，一条业务上具有完整意义的信息被拆分在多个表中，在生成分析层（或进行分析时）又要将被拆分的信息通过多表关联的方式关联起来。解决方案：在表中尽量增加冗余字段。要注意的是，冗余字段并非任意增加，而是要增加：冗余关联类型为m:1的字段，如：保单的所属分公司。从业务上说，基本不变化的冗余字段,|,增加表与表之间的外键，减少走关联表,问题的提出：原有设计中，一条业务上具有完整意义的信息被拆分在多个表中，在生成分析层（或进行分析时）又要将被拆分的信息通过多表关联的方式关联起来。而这样的关联可能要跨多个表。解决方案：增加有业务含义的信息之间的直接关联。即如两表的信息如果有业务关联，而在原有设计中这两表之间的关联要借助其他中间表的，应在此两表之间建立直接的关联。例如：sellingchannelroleid在individualagreement表中的冗余。否则，要走FSRole连接channelrole。尽可能减少关联的层级。即减少不必要的关联中间表例如：如保单的操作员直接建立到保单中，保单和理赔的科室、部门直接建立到保单和理赔中。备注：m:m的关系必须走关联表。,|,模型优化任务分配,Jeffrey:Activity,Reinsurance,Claim,Goalandneed,Legalaction,Physicalobject,Place,Registration,Specificationandproduct,Standardtextandcommunication,Technicalentity,Type王正茂:Accountandfund,Actuarialstatisticsandindex,Assessmentandcondition,Category,Contactpointandpreferences,Event,Financialaccount,GoalandneedBen:Agreement,Financialtransaction,Moneyprovision,Party,|,EDW和ODS的关系,Person、Externalorganisation、FSRole（投保人/被保人）通过ODS产生由于加载顺序的关系，保单表由业务系统产生，产生保单信息时，ODS数据还没有加载。因此，Individualagreement中的Policyholderid、Groupagreement中的Policyholderorganisationid不冗余产生，而是通过FSRole走。,|,Id产生规则,每个表单独使用id序列建id序列表,|,Anchor表是否需要物理产生,建物理产生Anchor表所有和Anchor直接外键关联的typeid冗余,|,Person归并决策,Person，externalorganisation在EDW不再进行归并,|,Boolean,0false1true,|,Atomicderiveddata,Atomic层表中，有部分字段保存的是从其他表或字段中导出的数据，如：claimfolder中totalcost，是从internalclaimcost中统计来的。对于这部分字段，分为两部分：目前analytical层或datamart用到的，需要处理暂时没用的字段，暂不处理这部分字段的处理，在atomic产生完成后，产生analytical层前处理。,|,Atomic,Analytical,1,2,物理分表,|,备注：没特别注明的其他类型的在原Entity中,Moneyscheduler,Moneyinscheduler用于连接对于保险公司来说是进来的钱的particularmoneyprovision/Financialtransaction，如：保费、储金（包括批增、批减）Moneyoutscheduler用于连接对于保险公司来说是出去的钱的particularmoneyprovision/Financialtransaction，如：赔款、支付的养老金（包括摊回赔款）每个保单签单产生一个Moneyinscheduler，保单不含批单的保额、保费挂在该Moneyinscheduler下；每个批单单产生一个Moneyinscheduler，该批单对应的保额、保费变化挂在该Moneyinscheduler下。每个赔案产生一个Moneyoutscheduler，该赔案对应的Particularmoneyprovision挂在该Moneyoutscheduler下。,|,关联表冗余进实体表中1/2,|,关联表冗余进实体表中2/2,|,Anchor的typeid冗余到外键关联的表中-1/2,|,Anchor的typeid冗余到外键关联的表中-2/2,|,Claim中涉及的各种金额存放规则1/2,|,Claim中涉及的各种金额存放规则2/2,Particularmoneyprovision对应赔案级，其中不存放金额，起关联作用（Agreementid，Agreementtypeid，Moneyschedulerid，（Moneypayeeid，Moneypayeetypeid，Moneypayerid，Moneypayertypeid有则产生）。Moneyscheduler一个赔案产生一条记录，该赔案下的Particularmoneyprovision都挂在这个Moneyscheduler下。Claimoffer可以存放多种offer，可以为服务、物品或金钱（包括状态是未决的）Internalclaimcost用于存放理算后的公司内部发生的各种理赔费用，如查勘费、施救费、律师费等。Internalclaimcost通过Internalclaimcost-ClaimRlship与claimfolder关联查勘表中记录的明细费用（如查勘费、施救费、律师费等）放在Moneyprovisionpart，通过Moneyprovisioncashflow到Particularmoneyprovision中的activityid连接到查勘活动中Moneyprovisionpart用于存放赔给客户的赔款明细(包括状态是未决的)、到赔付项目代码级别。Moneyprovisioncashflow用于存放子险级合计的赔款金额,和claimoffer对应放在Moneyprovisioncashflow和Moneyprovisionpart中赔款的钱金额变化、Moneyprovisioncashflow/Moneyprovisionpart中赔款金额不保留版本，当出现修改时直接修改这些表中的记录ClaimOffer中的赔款金额不保留历史，将来如果业务需要，再做保留历史处理立案的估损/定损都