EDW_(DM数据仓库数据建模)模型设计.ppt

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中牌照号沪A 000001车辆的第一条记录的Roadvehicleid值适用范围 FSRolePhysicalobject AgreementRlship SampleofLeft RightEntityIDinRelationshiporRoleEntity Roadvehicle Individualagreement Agreement Physicalobject Physicalobject AgreementRlship 被保标的 Partyroleinoperation Internalperson 问题的提出在业务中有很多操作员角色 只有工号 姓名信息 没有身份证等其他信息 一个操作员在一个业务流程中会同时扮演不同角色 如在A保单核保中他是录入人 在B保单核保中他是复核人或者可能出现在A保单核保中他既是录入人又是复核人解决方案建立Internalperson表保存业务员 公司管理人员的个人信息 这些信息质量较差建立Partyroleinoperation表保存操作员角色信息 每次都生成新记录 录单员冗余到保单中 理赔的操作员也冗余到claimfolder中 关联实体的版本问题 由于关联实体本身没有对应的anchor实体 不存在版本问题 但是关联存在有以下两种变化情况 人 王五 拥有一栋房屋 在2007 1 1卖掉了 更新原有的Roleplayer physicalobjectRlship记录的validtodate if源系统有系统更新日期 则 更新日期 1 else 则 2006 12 31 effectivetodate 2006 12 31 人 王五 拥有一栋房屋 在2007 1 1卖掉50 的产权 更新原有的Roleplayer physicalobjectRlship记录的validtodate if源系统有系统更新日期 则 更新日期 1 else 则 2006 12 31 effectivetodate 2006 12 31 Ownershippercentage 100 插入新的Roleplayer physicalobjectRlship记录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中的赔款金额不保留历史 将来如果业务需要 再做保留历史处理立案的估损 定损都