《EDW-(DM数据仓库数据建模)模型设计ppt课件.pptx》由会员分享，可在线阅读，更多相关《EDW-(DM数据仓库数据建模)模型设计ppt课件.pptx（60页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、BI.Insurance i.DWM for P&C 模型设计说明模型设计说明日程n为什么需要模型n模型的组织结构n模型实施方法n模型设计策略nQ & A | 日程n为什么需要模型为什么需要模型n模型的组织结构n模型实施方法n模型设计策略nQ & A | EDW体系架构源系统层源系统层ETL层层数据仓库层数据仓库层ETL层层数据集市层数据集市层应用层应用层展现层展现层手工数据手工数据外部数据外部数据数据仓库数据仓库保险数据模型核心业务核心业务财务系统财务系统再保险系统再保险系统人意险系统人意险系统精算系统精算系统客户关系客户关系管理管理OCRM客户讯息客户讯息ECIF业务量分析业务量分析数据集
2、市数据集市业务持续性业务持续性分析数据集市分析数据集市ALM数据集市数据集市财务分析财务分析数据集市数据集市车险承保分析车险承保分析通用承保分析通用承保分析风险管理风险管理应用应用ALM应用应用财务分析财务分析应用应用aCRM 数据集市数据集市aCRM报告报告大客户分析管理系统大客户分析管理系统aCRM引擎引擎数据挖数据挖掘引擎掘引擎数据挖数据挖掘应用掘应用企企业业信信息息门门户户企业统一分析平台企业统一分析平台元数据库元数据库监管报表监管报表管理报表管理报表运营报表运营报表仪表盘仪表盘随机查询随机查询多维分析多维分析“数据和信息集成平台数据和信息集成平台”“统一的分析平台统一的分析平台”“唯
3、一的信息出口唯一的信息出口”为什么需要企业模型？EDW 数据模型在项目实施中的作用 DWM数据仓库模型数据仓库模型BAM业务分析模型业务分析模型运营型业务系统运营型业务系统数据仓库数据仓库数据集市数据集市报表报表分析型应用分析型应用XML FileFlat FileInformix OracleSQLDB2BSA业务模版应用业务模版应用日程n为什么需要模型n模型的组织结构模型的组织结构n模型实施方法n模型设计策略nQ & A | 模型总体结构EM & DataMarts核心原子数据核心原子数据事实表和维度事实表和维度企业模型Party041PartyPlace R214Contact Poin
4、t203Place007Object028Object Place RPlace R238Place Label823218366Party Name927926P LabelCP RP NameCP RPlace-CP R208CP R2041423营销管理快速入门营销管理快速入门客户细分和管理客户细分和管理保险盈利性分析保险盈利性分析潜在客户管理潜在客户管理数据集市导出业务数据模型业务数据模型映射指标要素指标要素 需求模型需求模型财务报表数据集市财务报表数据集市中介绩效分析数据集市中介绩效分析数据集市健康险盈利性管理数据集市健康险盈利性管理数据集市DWM 数据模型逻辑结构当事人当事人营销和
5、沟通营销和沟通组织组织产品产品协议协议保险标的保险标的交易交易渠道渠道资源资源与理赔相关的活动及各理赔环节理赔理赔保险公司的有形资产和无形资产信息与客户之间资金或非资金活动的信息与客户交易或接触的渠道信息任何市场化的产品或服务和客户之间为某种产品或服务而设定的协议信息被保险的标的物及标的物的相关信息个人或团体及其基本信息和相关信息为增加客户、保留客户、拓展业务而进行的策略、规划或促销事件分支机构、部门和职员的信息地理区域，物理的或电子的地址信息地理位置地理位置与当事人或协议相关的一系列事件事件事件BI.Insurance i.DWM for P&Cn底层数据模型主题域说明：Agreement：
6、保单、批单申请及管理；Claim：理赔Financial Transaction：应收应付、实收实付以及交易关联Party：当事方，包括当事方的组织结构、角色结构及类型Money Provision：资金管理Specification And Product：规范及产品管理Place：地点Code：标准代码Activity：活动管理Physical Object：实物、标的管理BI.Insurance i.DWM-AgreementBI.Insurance i.DWM-ClaimBI.Insurance i.DWM-Physical Object日程n为什么需要模型n模型的组织结构n模型实施方
7、法模型实施方法n模型设计策略nQ & A | 表级映射表级映射字段映射字段映射实体、属性建模实体、属性建模关联、属性建模关联、属性建模SA建模建模需求划分需求划分多维建模多维建模使用模型、产生报表使用模型、产生报表需求收集需求收集数据分析数据分析模型映射模型映射数据建模数据建模ETL前端前端提供需求及模版提供需求及模版客户提供需求客户提供需求需求整理需求整理步骤：流程：产出：原则：需求文档：1.报表需求2.功能需求3. 非功能需求1.目前的报表2.想做的报表3.想做的功能1.数据筛选清单2.数据源报告：3.数据质量分析报告4.代码清单Mapping文档： 源-模型对应关系A筛选：筛选：去掉ET
8、L需要而模型不需要的字段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.单纯的险种定义信息不要，但是具体保单
9、中涉及的险种定义信息可以要。Mapping设计设计Mapping程序开程序开发测试发测试数据加载数据加载1.多维模型设计文多维模型设计文档档：l维度l指标l派生指标2.需求需求-模型映射文模型映射文档档3.报表样张报表样张4.操作说明操作说明数据筛选：1.表一级筛选2.字段级筛选数据筛选：1.模型的数据筛选2.ETL映射数据筛选EDW具体实施流程日程n为什么需要模型n模型的组织结构n模型实施方法n模型设计策略模型设计策略nQ & A | Hash coden问题的提出: 进行增量加载时无法快速判断对表的原有记录是否新插入。例如：l1. 理赔案件发生的时候,增量文件会把保单数据也传来 l2. 保
10、单增量过来,可能只是投保人的信息改了,而目标保单表所需信息并没有改变 n解决方案：使用增量的比较字段生成 Hash code。在对表进行增量加载时，对增量文件中的每一条记录生成 Hash code将生成完的 Hash code 与原表中同一anchor id并且最新的记录的 Hash code 进行比较如果一致的话，即不动作；如果不一致的话，即新插入。n使用示例：在 individual agreement 表中使用各个需要保留历史信息的字段生成 hash code。在增量加载时，使用业务增量文件中的字段生成 hash code。与 Individual agreement 表中同一agree
11、ment id的最新记录的hash code 进行比较。l如果一致，即不动作l如果不一致，则插入新记录。n备注：备注：relationship表是要根据业务去判断是否关系已经存在，然后，如果有其他属性（如：Role player - Physical object Rlship.Usage），才需要用hashcode判别是否重复。 | Hash code字段组成规则n带anchor的实体带status表的实体（Commercial agreement、Group agreement、Individual agreement、Claim folder、Elementary claim）l除表的主
12、键、type id、Partition key、Status、Status date、Status reason、 Valid from date、Valid to date、Effective from date、Effective to date、 Population timestamp之外的所有字段不带status表的实体l除表的主键、 type id、 Partition key、 Valid from date、Valid to date、Effective from date、Effective to date、 Population timestamp之外的所有字段n不带anc
13、hor的实体原则上不需要保留历史，一般执行Update操作。如果有需要的，ETL Mapping特别指明n关联实体对于需要保留历史的关联类型，除Identifier、Partition key、Nature id、 Left anchor identifier、 Right anchor identifier、 Left entity identifier、Left entity type id、Right entity identifier、Right entity type id、Valid from date、Valid to date、Effective from date、Effec
14、tive to date、Population timestamp之外的所有字段 | Partition keyn问题的提出: 在进行多表关联时，所涉及的关联表行数巨大，关联速度达不到要求。n解决方案：在所有大表中建立 Partition key, 按照该键的键值对表进行物理分区。Partition key 从Partition config 表中获得。分区策略是按照分公司进行分区。n使用示例：表 A 与表 B 进行关联时，如下进行select A.column1, B.column2 from A, B where A.foreign_key=B.Primary_key and A.part
15、ition_key in (select Storage partition from Partition config where Branch company id=xxxx) and B.partition_key in (select Storage partition from Partition config where Branch company id=xxxxxxx) | | 对保单和理赔状态的特殊处理对保单和理赔状态的特殊处理问题的提出：-保单在承保和保全的整个过程中状态变化比较多，如按照 IIW 的原有设计，保单表中的会有巨量的历史记录；理赔在报案、立案和估损的整个过程中
16、状态变化较多，如按照 IIW 的原有设计，理赔表中会有很多的历史记录。解决方案：-将保单的状态变化过程剥离出来单独建表，在该表中保留与保单的关联；当有新状态插入时，更新对应的保单表中的状态。-将理赔的状态变化过程剥离出来单独建表，在该表中保留与理赔的关联；当有新状态插入时，更新对应的理赔表中的状态。使用示例：-增加Commercial agreement status，Group agreement status，Individual agreement status表，分别记录 Commercial agreement , Group agreement ,Individual agreem
17、ent 的状态变化历史。-当前面状态发生该变时，在status表中插入新记录，更新对于原表中的状态字段。对保单和理赔状态的特殊处理示例 | Individual agreementIndividual agreement statusLeft/Right Entity ID in Relationship or Role Entityn问题的提出在IIW中的不同subject area的实体关联通常是走关联实体的，例如：Physical object - Agreement Rlship。在关联实体中是以anchor id进行连接的。在分析的时候，通常是应该按照当时的状况进行分析才有意义。由于
18、EDW是保留历史信息的，同一个Physical object或Agreement会有多条记录，如何找到当时的记录，必须通过effective from/to date的比对才能实现，这非常影响效率。n解决方案在关联实体中增加Left/Right entity identifier，Left/Right entity type idLeft/Right entity type id是指具体基础表的id号l例如：Road vehicle（2001260001）Left/Right entity identifier是指具体基础表中记录的主键id值l例如： Road vehicle中牌照号沪A000
19、001车辆的第一条记录的Road vehicle id值适用范围：lFS RolelPhysical object - Agreement Rlship | Sample of Left/Right Entity ID in Relationship or Role Entity | Road vehicleIndividual agreementAgreementPhysical objectPhysical object Agreement Rlship被保标的Party role in operation/Internal person n问题的提出在业务中有很多操作员角色，只有工号、姓
20、名信息，没有身份证等其他信息；一个操作员在一个业务流程中会同时扮演不同角色，如在A保单核保中他是录入人，在B保单核保中他是复核人或者可能出现在A保单核保中他既是录入人又是复核人解决方案建立Internal person表保存业务员、公司管理人员的个人信息，这些信息质量较差建立Party role in operation表保存操作员角色信息，每次都生成新记录。录单员冗余到保单中，理赔的操作员也冗余到claim folder中 | Role player - Activity RlshipParty role in operation（ Role player id ）Internal Pers
21、on（Role player id）Financial Services Role关联实体的版本问题n由于关联实体本身没有对应的anchor实体，不存在版本问题，但是关联存在有以下两种变化情况。1.人“王五”拥有一栋房屋，在2007/1/1卖掉了。l更新原有的Role player physical object Rlship记录的valid to date：if 源系统有系统更新日期，则更新日期1；else，则“2006/12/31”effective to date： “2006/12/31”2.人“王五”拥有一栋房屋，在2007/1/1卖掉50的产权。l更新原有的Role player
22、physical object Rlship记录的valid to date：if 源系统有系统更新日期，则更新日期1；else，则“2006/12/31”effective to date： “2006/12/31”（Ownership percentage： 100）l插入新的Role player physical object Rlship记录valid from date：if 源系统有系统更新日期，则更新日期1；else，则“2007/1/1”effective from date： “2007/1/1”Ownership percentage： 50 | Financial Se
23、rvices Rolen问题的提出Person存放人的基本信息，External organisation和Internal organisation存放机构的基本信息一个人和机构在不同环境下分别扮演不同角色，所以Financial Services Role存放与保单（各种协议）相关的金融服务角色，如保单持有人，被保险人，受益人等。Channel role存放中介渠道角色信息，如营销员、收展员在分析集市中需要获取保单与业务员的关联信息，IIW原连接方式如图： | Financial Services Role（Financial services role player id）Person（
24、Role player id）Channel role（Channel role player id）优点：结构清晰统一缺点：渠道角色信息关联的太远，需要Financial Services Role+Channel role+Person，影响效率 Person（Role player id）External organisation（Role player id）Financial Services Rolen解决方案Financial Services Role用把用把basis role player type id确定应确定应连接Person 还是还是External organis
25、ationFinancial Services Role用把用把basis role player id确定确定Person或或External organisation中记录中记录的的role player idFinancial Services Role用把用把basis role player entity identifier确定确定Person或或External organisation中记录的中记录的person id或或External organisation idn使用示例 | Financial Services Role（Financial services role
26、 player id）Person（Role player id）Channel role（ Role player idChannel role player id）Person（Role player id）External organisation（Role player id）Currency coden问题的提出：在 CPIC 的实际业务中，可能出现多币种，在统计中需要进行多币种的转换。n解决方案：在 IIW 模型中凡出现金额字段的表，都增加金额的币种及对应的 RMB 金额两类字段。原字段存放原币中金额， RMB 金额存放折算成RMB的金额n使用示例：Elementary claim
27、 表中增加 Total cost currency 和 Total cost RMB 字段n备注：备注：由于CPIC对多币种金额的统计有多种统计方式，不全部是按照发生制来折算RMB的。因此，统计转换金额到RMB的工作，留给统计部分执行，在原子层不计算。币种一定要填。 | 维度表的snapshotn问题的提出在分析层中，常用的维度表如：保单、立案。分析常用的属性是分散在各个表中的，如：保费、保额在Particular Money Provision中。分析时如果再通过关联来找到这些信息，效率非常低。n解决方案建立维度的snapshot表，将这些信息冗余存放在这些表中，每个月全量刷新一次。n使用示
28、例：Claim folder dimensionPolicy dimensionElementary claim dimensionEvent dimension | Commercial agreement/Group agreement/Individual agreement的边界区分nCommercial agreement存放保险公司和机构投保人签订的关于承保要素约束的框架性协议；不是具体的保单。具体的保单要遵循该协议。nGroup agreement团单l单位和保险公司签订的保一组成员的保单，如：寿险团单、雇主责任险、旅游责任险。l如果源系统提供了每个被保人的投保情况，这些记录在i
29、ndividual agreement（type id个人凭证）中的。如：雇主责任险下每个人的投保份数。nIndividual agreement个单/个人凭证备注：根据国内系统的情况做了些调整，和机构投保人（非个人）签订的个单也存放在此。n投保单按保单处理，只是状态是投保状态 | Group agreement/Individual agreement在ETL时处理n车险系统保单进入Individual agreementn寿险保单根据来源表，决定进入group agreement还是individual agreementnCIBS(包括老系统)和人意险保单根据Financial serv
30、ices product中的Individual insurance flag判断l个险，进入Individual agreementl团险、个团皆可，进入group agreement | 最新记录标志nEffective to date = 9999/12/31 00:00:00 | 公司的拆分合并，partition key的处理 1/4n分公司的拆分合并，不需要程序考虑，发生后手工处理。n公司合并举例：原来有分公司A,分公司B,在2006/1/1分公司B合并到分公司A。 | 合并前合并前Partition configExternal organisationIndividual ag
31、reement公司的拆分合并，partition key的处理 2/4n公司合并举例：原来有分公司A,分公司B,在2006/1/1分公司B合并到分公司A。 | 合并后合并后Partition configExternal organisationIndividual agreementRole player Rlship公司的拆分合并，partition key的处理 3/4n公司合并举例：原来有分公司A, 在2006/1/1分公司A,拆分成分公司A和分公司B。 | 拆分前拆分前Partition configExternal organisationIndividual agreement公
32、司的拆分合并，partition key的处理 4/4n公司合并举例：原来有分公司A, 在2006/1/1分公司A,拆分成分公司A和分公司B。 | 拆分后拆分后Partition configExternal organisationIndividual agreementRole player Rlship按照type id分表n将有些大表按照 Type id 进行拆分n举例：Individual agreement 表按照保单和投保单拆成两张表 | 历史信息的处理n对含有历史记录的大表，应考虑将历史记录剥离出来单独建表，即原表保留最新的信息，而在剥离出来的表中包含这些信息的变化历史。n举例
33、：Individual agreement 原来保留有保单的最新信息及这些信息的历史变化记录。这样这张表就将很大，记录数数以亿计。目前将它拆成 2 个表：1.表一，存放保单的最新信息，如最新状态，最新确认的起保日期等，同时保留每条记录最新的刷新时间2.表二，存放保单经常变化的值的变化历史，如：保单状态的变化历史3.表三，存放保单所有历史变化的信息 | 增加表的冗余字段n问题的提出：原有设计中，一条业务上具有完整意义的信息被拆分在多个表中，在生成分析层（或进行分析时）又要将被拆分的信息通过多表关联的方式关联起来。n解决方案：在表中尽量增加冗余字段。要注意的是，冗余字段并非任意增加，而是要增加：1
34、.冗余关联类型为m:1 的字段，如：保单的所属分公司。2.从业务上说，基本不变化的冗余字段 | 增加表与表之间的外键，减少走关联表n问题的提出：原有设计中，一条业务上具有完整意义的信息被拆分在多个表中，在生成分析层（或进行分析时）又要将被拆分的信息通过多表关联的方式关联起来。而这样的关联可能要跨多个表。n解决方案：1.增加有业务含义的信息之间的直接关联。即如两表的信息如果有业务关联，而在原有设计中这两表之间的关联要借助其他中间表的，应在此两表之间建立直接的关联。1.例如：selling channel role id在individual agreement表中的冗余。否则，要走FS Role
35、连接channel role。2.尽可能减少关联的层级。即减少不必要的关联中间表1.例如：如保单的操作员直接建立到保单中，保单和理赔的科室、部门直接建立到保单和理赔中。n备注：m:m的关系必须走关联表。 | 模型优化任务分配nJeffrey: Activity, Reinsurance, Claim, Goal and need, Legal action, Physical object, Place, Registration, Specification and product, Standard text and communication, Technical entity, Typ
36、en王正茂: Account and fund, Actuarial statistics and index, Assessment and condition, Category, Contact point and preferences, Event, Financial account, Goal and neednBen: Agreement, Financial transaction, Money provision, Party, | EDW和ODS的关系nPerson、External organisation 、FS Role（投保人/被保人）通过ODS产生n由于加载顺序
37、的关系，保单表由业务系统产生，产生保单信息时，ODS数据还没有加载。因此， Individual agreement中的Policyholder id、Group agreement中的Policyholder organisation id不冗余产生，而是通过FS Role走。 | Id产生规则n每个表单独使用id序列n建id序列表 | Anchor表是否需要物理产生n建物理产生Anchor表n所有和Anchor直接外键关联的type id冗余 | Person归并决策nPerson，external organisation在EDW不再进行归并 | Booleann0 falsen1 tr
38、ue | Atomic derived datanAtomic层表中，有部分字段保存的是从其他表或字段中导出的数据，如：claim folder中total cost，是从internal claim cost中统计来的。n对于这部分字段，分为两部分：1.目前analytical层或data mart用到的，需要处理2.暂时没用的字段，暂不处理n这部分字段的处理，在atomic产生完成后，产生analytical层前处理。 | AtomicAnalytical12物理分表Subject areaSubject areaEntityEntity分表分表ActivityParticular act
39、ivity治疗 2000060002:PA_Medical treat查勘 2000060004/回勘 2000060005:PA_InvestigationAgreementCoverage componentCoverage component historyIndividual agreementIndividual agreement historyClaimClaim folder报案 2000360001：CF_Report case立案案卷2000360002：CF_files赔案 2000360004/追偿赔案2000360005: CF_indemnityMoney prov
40、isionMoney schedulerMoney in: Money in schedulerMoney out: Money out schedulerMoney scheduler在物理层不使用Particular money provision保额: PMP insured amount保费: PMP premium PartyFinancial services role2000660002 投保人: FSR applicator2000660003 被保人: FSR InsuredPaymentPayment保费来源的：Premium payment 赔款来源的：Claim pay
41、ment Payment due保费来源: Premium payment due赔款来源: Claim payment duePhysical objectOther physical objectGeneral cargo2000850001：CargoMachine item2000850004：Machine StructureDwelling 2000890001：DwellingHotel 2000890003: HotelPlatform 2000890004: Platform | 备注：没特别注备注：没特别注明的其他类型的明的其他类型的在原在原Entity中中Money sc
42、hedulernMoney in scheduler用于连接对于保险公司来说是进来的钱的particular money provision/Financial transaction，如：保费、储金（包括批增、批减）nMoney out scheduler用于连接对于保险公司来说是出去的钱的particular money provision/Financial transaction，如：赔款、支付的养老金（包括摊回赔款）n每个保单签单产生一个Money in scheduler，保单不含批单的保额、保费挂在该Money in scheduler下；每个批单单产生一个Money in sc
43、heduler，该批单对应的保额、保费变化挂在该Money in scheduler下。n每个赔案产生一个Money out scheduler，该赔案对应的Particular money provision挂在该Money out scheduler下。 | 关联表冗余进实体表中 1/2Subject areaSubject areaEntityEntity加字段加字段备注备注ActivityActivity - Place Rlshipclaim folder.报案地点Activity - Place Rlship.Nature_id=报案地点 2000030003直接挪到claim f
44、older中ActivityActivity - Place RlshipParticular activity.活动地点Activity - Place Rlship.Nature_id=查勘地点2000030004 工程作业地域 2000030005建造地点 2000030006交船地点 2000030007直接挪到Particular activity.活动地点ActivityActivity - Place RlshipTransportation.启运港/Transportation.中转港1/Transportation.中转港2/Transportation.目的地Activit
45、y - Place Rlship.Nature_id=启运港 2000030008中转港 2000030009直接挪到Transportation.启运港/Transportation.中转港加代码字段和名称字段如果有超过2个的中转港，则走原有关联表ActivityClaim checkOperation role player id执行人ActivityClaim checkReview role player id复核/审核人ActivityUnderwritig checkOperation role player id执行人ActivityUnderwritig checkReview
46、 role player id复核/审核人AgreementChange requestOperation role player id操作员代码AgreementGroup agreementOperation role player id原通过 Financial service role 关联保单和 Party role in operation,现在直接将操作员 id 冗余进 Group agreement 表中AgreementGroup agreementDepartment id保单部门，原先必须通过与 Party 主题关联得到，现在增加冗余 | 关联表冗余进实体表中 2/2Su
47、bject areaSubject areaEntityEntity加字段加字段备注备注AgreementGroup agreementSection id科室代码，原先必须通过与 Party 主题关联得到，现在增加冗余AgreementIndividual agreementOperation role player id原通过 Financial service role 关联保单和 Party role in operation,现在直接将操作员 id 冗余进 Individual agreement 表中AgreementIndividual agreementDepartment i
48、d保单部门，原先必须通过与 Party 主题关联得到，现在增加冗余AgreementIndividual agreementSection id科室代码，原先必须通过与 Party 主题关联得到，现在增加冗余AgreementIndividual agreementCommercial agreement id到 commercial agreement的直接关联Financial transactionPaymentAgreement request id批单号Financial transactionPaymentAgreement request type id批单表type id类型F
49、inancial transactionPayment dueAgreement request id批单号Financial transactionPayment dueAgreement request type id批单表type id类型PaymentPaymentPayment.Agreement id协议号PaymentPaymentPayment.Agreement type id协议类型PaymentPayment duePayment due.Agreement id协议号PaymentPayment duePayment due.Agreement type id协议类型P
50、hysical objectPhysical object - Registration rlshipship.船籍(代码)船籍2000790001 | Anchor的type id冗余到外键关联的表中 -1/2AnchorAnchorEntityEntity新增冗余字段新增冗余字段Accountaccount entryaccount type idActivityMarketing objectiveMarketing activity type idActivityParticular money provisionactivity type idAgreementFinancial s