EDW-(DM數據倉庫數據建模)模型設計.pptx

《EDW-(DM數據倉庫數據建模)模型設計.pptx》由會員分享，可在線閱讀，更多相關《EDW-(DM數據倉庫數據建模)模型設計.pptx（60頁珍藏版）》請在裝配圖網上搜索。

BI.Insurance i.DWM for P&C 模型設計說明,張海彪,日程,為什么需要模型 模型的組織結構 模型實施方法 模型設計策略 Q & A,|,日程,為什么需要模型 模型的組織結構 模型實施方法 模型設計策略 Q & A,|,EDW體系架構,,,,,,,,,,源系統層,ETL層,數據倉庫層,ETL層,數據集市層,應用層,展現層,手工數據,外部數據,,數據倉庫,保險數據模型,,,,,,,,核心業(yè)務,財務系統,再保險系統,人意險系統,精算系統,客戶關系 管理OCRM,客戶訊息 ECIF,,,,,,業(yè)務量分析 數據集市,業(yè)務持續(xù)性 分析數據集市,ALM 數據集市,財務分析 數據集市,車險承保分析 通用承保分析,風險管理 應用,ALM應用,財務分析 應用,,aCRM 數據集市,aCRM 報告,大客戶分析管理系統,aCRM 引擎,,,數據挖 掘引擎,數據挖 掘應用,企 業(yè) 信 息 門 戶,企業(yè)統一分析平臺,,,,,,,,,,,,,,,,,,,,,,,,,,,元數據庫,,,,,,,,,監(jiān)管報表,管理報表,運營報表,儀表盤,隨機查詢,多維分析,,,,,,,,,,,,,,,,,,,,,,,,,“數據和信息集成平臺” “統一的分析平臺” “唯一的信息出口”,為什么需要企業(yè)模型？,EDW 數據模型在項目實施中的作用,DWM 數據倉庫模型,BAM 業(yè)務分析模型,運營型業(yè)務系統,數據倉庫,數據集市,報表 分析型應用,,,,,,,,,,BSA 業(yè)務模版應用,,,日程,為什么需要模型 模型的組織結構 模型實施方法 模型設計策略 Q & A,|,模型總體結構－EM & DataMarts,,,核心原子數據,事實表和維度,企業(yè)模型,,營銷管理快速入門,客戶細分和管理,保險盈利性分析,潛在客戶管理,數據集市,,,導出,業(yè)務數據模型,,映射,,指標要素,,需求模型,財務報表數據集市,中介績效分析數據集市,健康險盈利性管理數據集市,,DWM 數據模型邏輯結構,BI.Insurance i.DWM for P&C,底層數據模型主題域說明： Agreement：保單、批單申請及管理； Claim：理賠 Financial Transaction：應收應付、實收實付以及交易關聯 Party：當事方，包括當事方的組織結構、角色結構及類型 Money Provision：資金管理 Specification And Product：規(guī)范及產品管理 Place：地點 Code：標準代碼 Activity：活動管理 Physical Object：實物、標的管理,BI.Insurance i.DWM-Agreement,BI.Insurance i.DWM-Claim,BI.Insurance i.DWM-Physical Object,日程,為什么需要模型 模型的組織結構 模型實施方法 模型設計策略 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.映射到EM 2.結合性能考慮 3.結合實現考慮,數據篩選： 1.程序控制,計算,通訊,安全控制配置,日志 2.匯總類結果一般不要 3.可以由其它字段算出的字段一般不要 4.從其它系統導入的數據不要. 5.代碼表不要。 6.單純的險種定義信息不要，但是具體保單中涉及的險種定義信息可以要。,1.多維模型設計文檔： 維度 指標 派生指標 2.需求-模型映射文檔 3.報表樣張 4.操作說明,,,EDW具體實施流程,,,日程,為什么需要模型 模型的組織結構 模型實施方法 模型設計策略 Q & A,|,Hash code,問題的提出: 進行增量加載時無法快速判斷對表的原有記錄是否新插入。例如： 1. 理賠案件發(fā)生的時候,增量文件會把保單數據也傳來 2. 保單增量過來,可能只是投保人的信息改了,而目標保單表所需信息并沒有改變 解決方案： 使用增量的比較字段生成 Hash code。在對表進行增量加載時，對增量文件中的每一條記錄生成 Hash code 將生成完的 Hash code 與原表中同一anchor id并且最新的記錄的 Hash code 進行比較 如果一致的話，即不動作；如果不一致的話，即新插入。 使用示例： 在 individual agreement 表中使用各個需要保留歷史信息的字段生成 hash code。 在增量加載時，使用業(yè)務增量文件中的字段生成 hash code。 與 Individual agreement 表中同一agreement id的最新記錄的hash code 進行比較。 如果一致，即不動作 如果不一致，則插入新記錄。 備注： relationship表是要根據業(yè)務去判斷是否關系已經存在，然后，如果有其他屬性（如：Role player - Physical object Rlship.Usage），才需要用hashcode判別是否重復。,|,Hash code字段組成規(guī)則,帶anchor的實體 帶status表的實體（Commercial agreement、Group agreement、Individual agreement、Claim folder、Elementary claim） 除表的主鍵、type id、Partition key、Status、Status date、Status reason、 Valid from date、Valid to date、Effective from date、Effective to date、 Population timestamp之外的所有字段 不帶status表的實體 除表的主鍵、 type id、 Partition key、 Valid from date、Valid to date、Effective from date、Effective to date、 Population timestamp之外的所有字段 不帶anchor的實體 原則上不需要保留歷史，一般執(zhí)行Update操作。如果有需要的，ETL Mapping特別指明 關聯實體 對于需要保留歷史的關聯類型，除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、Effective to date、Population timestamp之外的所有字段,|,Partition key,問題的提出: 在進行多表關聯時，所涉及的關聯表行數巨大，關聯速度達不到要求。 解決方案：在所有大表中建立 Partition key, 按照該鍵的鍵值對表進行物理分 區(qū)。Partition key 從Partition config 表中獲得。分區(qū)策略是 按照分公司進行分區(qū)。 使用示例：表 A 與表 B 進行關聯時，如下進行 select A.column1, B.column2 from A, B where A.foreign_key=B.Primary_key and A.partition_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),|,|,對保單和理賠狀態(tài)的特殊處理,問題的提出： 保單在承保和保全的整個過程中狀態(tài)變化比較多，如按照 IIW 的原有設計，保單表中的會有巨量的歷史記錄；理賠在報案、立案和估損的整個過程中狀態(tài)變化較多，如按照 IIW 的原有設計，理賠表中會有很多的歷史記錄。 解決方案： 將保單的狀態(tài)變化過程剝離出來單獨建表，在該表中保留與保單的關聯；當有新狀態(tài)插入時，更新對應的保單表中的狀態(tài)。 將理賠的狀態(tài)變化過程剝離出來單獨建表，在該表中保留與理賠的關聯；當有新狀態(tài)插入時，更新對應的理賠表中的狀態(tài)。 使用示例： 增加Commercial agreement status，Group agreement status，Individual agreement status 表，分別記錄 Commercial agreement , Group agreement ,Individual agreement 的狀態(tài)變化歷史。 當前面狀態(tài)發(fā)生該變時，在status表中插入新記錄，更新對于原表中的狀態(tài)字段。,對保單和理賠狀態(tài)的特殊處理示例,|,Individual agreement,Individual agreement status,,Left/Right Entity ID in Relationship or Role Entity,問題的提出 在IIW中的不同subject area的實體關聯通常是走關聯實體的，例如：Physical object - Agreement Rlship。在關聯實體中是以anchor id進行連接的。在分析的時候，通常是應該按照當時的狀況進行分析才有意義。由于EDW是保留歷史信息的，同一個Physical object或Agreement會有多條記錄，如何找到當時的記錄，必須通過effective from/to date的比對才能實現，這非常影響效率。 解決方案 在關聯實體中增加Left/Right entity identifier，Left/Right entity type id Left/Right entity type id是指具體基礎表的id號 例如：Road vehicle（2001260001） Left/Right entity identifier是指具體基礎表中記錄的主鍵id值 例如： Road vehicle中牌照號滬A－000001車輛的第一條記錄的Road vehicle id值 適用范圍： FS Role Physical object - Agreement Rlship,|,Sample of Left/Right Entity ID in Relationship or Role Entity,|,Road vehicle,Individual agreement,Agreement,Physical object,Physical object – Agreement Rlship,,,被保標的,,,,,,,Party role in operation/Internal person,問題的提出 在業(yè)務中有很多操作員角色，只有工號、姓名信息，沒有身份證等其他信息； 一個操作員在一個業(yè)務流程中會同時扮演不同角色，如在A保單核保中他是錄入人，在B保單核保中他是復核人或者可能出現在A保單核保中他既是錄入人又是復核人 解決方案 建立Internal person表保存業(yè)務員、公司管理人員的個人信息，這些信息質量較差 建立Party role in operation表保存操作員角色信息，每次都生成新記錄。 錄單員冗余到保單中，理賠的操作員也冗余到claim folder中,|,關聯實體的版本問題,由于關聯實體本身沒有對應的anchor實體，不存在版本問題，但是關聯存在有以下兩種變化情況。 人“王五”擁有一棟房屋，在2007/1/1賣掉了。 更新原有的Role player － physical object Rlship記錄的 valid to date：if 源系統有系統更新日期，則＝更新日期－1；else，則＝“2006/12/31” effective to date： ＝ “2006/12/31” 人“王五”擁有一棟房屋，在2007/1/1賣掉50％的產權。 更新原有的Role player － physical object Rlship記錄的 valid to date：if 源系統有系統更新日期，則＝更新日期－1；else，則＝“2006/12/31” effective to date： ＝ “2006/12/31” （Ownership percentage： ＝100％） 插入新的Role player － physical object Rlship記錄 valid from date：if 源系統有系統更新日期，則＝更新日期－1；else，則＝“2007/1/1” effective from date： ＝ “2007/1/1” Ownership percentage： ＝50％,|,Financial Services Role,問題的提出 Person存放人的基本信息，External organisation和Internal organisation存放機構的基本信息 一個人和機構在不同環(huán)境下分別扮演不同角色，所以 Financial Services Role存放與保單（各種協議）相關的金融服務角色，如保單持有人，被保險人，受益人等。 Channel role存放中介渠道角色信息，如營銷員、收展員 在分析集市中需要獲取保單與業(yè)務員的關聯信息，IIW原連接方式如圖：,|,Financial Services Role （Financial services role player id）,Person （Role player id）,Channel role （Channel role player id）,優(yōu)點：結構清晰統一 缺點：渠道角色信息關聯的太遠，需要Financial Services Role+Channel role+Person，影響效率,Person （Role player id）,External organisation （Role player id）,Financial Services Role,解決方案 Financial Services Role用把basis role player type id確定應連接Person 還是External organisation Financial Services Role用把basis role player id確定Person或External organisation中記錄的role player id Financial Services Role用把basis role player entity identifier確定Person或External organisation中記錄的person id或External organisation id 使用示例,|,Financial Services Role （Financial services role player id）,Person （Role player id）,Channel role （ Role player id Channel role player id）,Person （Role player id）,External organisation （Role player id）,Currency code,問題的提出： 在 CPIC 的實際業(yè)務中，可能出現多幣種，在統計中需要進行多幣種的轉換。 解決方案： 在 IIW 模型中凡出現金額字段的表，都增加金額的幣種及對應的 RMB 金額兩類字段。 原字段存放原幣中金額， RMB 金額存放折算成RMB的金額 使用示例： Elementary claim 表中增加 Total cost currency 和 Total cost RMB 字段 備注：由于CPIC對多幣種金額的統計有多種統計方式，不全部是按照發(fā)生制來折算RMB的。因此，統計轉換金額到RMB的工作，留給統計部分執(zhí)行，在原子層不計算。幣種一定要填。,|,維度表的snapshot,問題的提出 在分析層中，常用的維度表如：保單、立案。分析常用的屬性是分散在各個表中的，如：保費、保額在Particular Money Provision中。分析時如果再通過關聯來找到這些信息，效率非常低。 解決方案 建立維度的snapshot表，將這些信息冗余存放在這些表中，每個月全量刷新一次。 使用示例： Claim folder dimension Policy dimension Elementary claim dimension Event dimension,|,Commercial agreement/Group agreement/Individual agreement的邊界區(qū)分,Commercial agreement 存放保險公司和機構投保人簽訂的關于承保要素約束的框架性協議；不是具體的保單。具體的保單要遵循該協議。 Group agreement 團單 單位和保險公司簽訂的保一組成員的保單，如：壽險團單、雇主責任險、旅游責任險。 如果源系統提供了每個被保人的投保情況，這些記錄在individual agreement（type id＝個人憑證）中的。如：雇主責任險下每個人的投保份數。 Individual agreement 個單/個人憑證 備注：根據國內系統的情況做了些調整，和機構投保人（非個人）簽訂的個單也存放在此。 投保單按保單處理，只是狀態(tài)是投保狀態(tài),|,Group agreement/Individual agreement在ETL時處理,車險系統保單 進入Individual agreement 壽險保單 根據來源表，決定進入group agreement還是individual agreement CIBS(包括老系統)和人意險保單 根據Financial services product中的Individual insurance flag判斷 個險，進入Individual agreement 團險、個團皆可，進入group agreement,|,最新記錄標志,Effective to date = ‘9999/12/31 00:00:00’,|,公司的拆分合并，partition key的處理 – 1/4,分公司的拆分合并，不需要程序考慮，發(fā)生后手工處理。 公司合并 舉例：原來有分公司A,分公司B,在2006/1/1分公司B合并到分公司A。,|,,,,,合并前,Partition config,External organisation,Individual agreement,公司的拆分合并，partition key的處理 – 2/4,公司合并 舉例：原來有分公司A,分公司B,在2006/1/1分公司B合并到分公司A。,|,合并后,Partition config,External organisation,Individual agreement,Role player Rlship,,,,,公司的拆分合并，partition key的處理 – 3/4,公司合并 舉例：原來有分公司A, 在2006/1/1分公司A,拆分成分公司A和分公司B。,|,拆分前,Partition config,External organisation,Individual agreement,,,,,公司的拆分合并，partition key的處理 – 4/4,公司合并 舉例：原來有分公司A, 在2006/1/1分公司A,拆分成分公司A和分公司B。,|,拆分后,Partition config,External organisation,Individual agreement,Role player Rlship,,,,,按照type id分表,將有些大表按照 Type id 進行拆分 舉例：Individual agreement 表按照保單和投保單拆成兩張表,|,歷史信息的處理,對含有歷史記錄的大表，應考慮將歷史記錄剝離出來單獨建表，即原表保留最新的信息，而在剝離出來的表中包含這些信息的變化歷史。 舉例： Individual agreement 原來保留有保單的最新信息及這些信息的歷史變化記錄。這樣這張表就將很大，記錄數數以億計。目前將它拆成 2 個表： 表一，存放保單的最新信息，如最新狀態(tài)，最新確認的起保日期等，同時保留每條記錄最新的刷新時間 表二，存放保單經常變化的值的變化歷史，如：保單狀態(tài)的變化歷史 表三，存放保單所有歷史變化的信息,|,增加表的冗余字段,問題的提出：原有設計中，一條業(yè)務上具有完整意義的信息被拆分在多個表中，在生成分析層（或進行分析時）又要將被拆分的信息通過多表關聯的方式關聯起來。 解決方案：在表中盡量增加冗余字段。要注意的是，冗余字段并非任意增加，而是要增加： 冗余關聯類型為m:1 的字段，如：保單的所屬分公司。 從業(yè)務上說，基本不變化的冗余字段,|,增加表與表之間的外鍵，減少走關聯表,問題的提出：原有設計中，一條業(yè)務上具有完整意義的信息被拆分在多個表中，在生成分析層（或進行分析時）又要將被拆分的信息通過多表關聯的方式關聯起來。而這樣的關聯可能要跨多個表。 解決方案： 增加有業(yè)務含義的信息之間的直接關聯。即如兩表的信息如果有業(yè)務關聯，而在原有設計中這兩表之間的關聯要借助其他中間表的，應在此兩表之間建立直接的關聯。 例如：selling channel role id在individual agreement表中的冗余。否則，要走FS Role連接channel role。 盡可能減少關聯的層級。即減少不必要的關聯中間表 例如：如保單的操作員直接建立到保單中，保單和理賠的科室、部門直接建立到保單和理賠中。 備注：m:m的關系必須走關聯表。,|,模型優(yōu)化任務分配,Jeffrey: Activity, Reinsurance, Claim, Goal and need, Legal action, Physical object, Place, Registration, Specification and product, Standard text and communication, Technical entity, Type 王正茂: Account and fund, Actuarial statistics and index, Assessment and condition, Category, Contact point and preferences, Event, Financial account, Goal and need Ben: Agreement, Financial transaction, Money provision, Party,,|,EDW和ODS的關系,Person、External organisation 、FS Role（投保人/被保人）通過ODS產生 由于加載順序的關系，保單表由業(yè)務系統產生，產生保單信息時，ODS數據還沒有加載。因此， Individual agreement中的Policyholder id、Group agreement中的Policyholder organisation id不冗余產生，而是通過FS Role走。,|,Id產生規(guī)則,每個表單獨使用id序列 建id序列表,|,Anchor表是否需要物理產生,建物理產生Anchor表 所有和Anchor直接外鍵關聯的type id冗余,|,Person歸并決策,Person，external organisation在EDW不再進行歸并,|,Boolean,0 false 1 true,|,Atomic derived data,Atomic層表中，有部分字段保存的是從其他表或字段中導出的數據，如：claim folder中total cost，是從internal claim cost中統計來的。 對于這部分字段，分為兩部分： 目前analytical層或data mart用到的，需要處理 暫時沒用的字段，暫不處理 這部分字段的處理，在atomic產生完成后，產生analytical層前處理。,|,Atomic,Analytical,,,1,2,物理分表,|,備注：沒特別注明的其他類型的在原Entity中,Money scheduler,Money in scheduler 用于連接對于保險公司來說是進來的錢的particular money provision/Financial transaction，如：保費、儲金（包括批增、批減） Money out scheduler 用于連接對于保險公司來說是出去的錢的particular money provision/Financial transaction，如：賠款、支付的養(yǎng)老金（包括攤回賠款） 每個保單簽單產生一個Money in scheduler，保單不含批單的保額、保費掛在該Money in scheduler下；每個批單單產生一個Money in scheduler，該批單對應的保額、保費變化掛在該Money in scheduler下。 每個賠案產生一個Money out scheduler，該賠案對應的Particular money provision掛在該Money out scheduler下。,|,關聯表冗余進實體表中 －1/2,|,關聯表冗余進實體表中 －2/2,|,Anchor的type id冗余到外鍵關聯的表中 -1/2,|,Anchor的type id冗余到外鍵關聯的表中 -2/2,|,Claim中涉及的各種金額存放規(guī)則 －1/2,,|,Claim中涉及的各種金額存放規(guī)則 －2/2,Particular money provision對應賠案級，其中不存放金額，起關聯作用（Agreement id，Agreement type id，Money scheduler id，（Money payee id，Money payee type id，Money payer id，Money payer type id有則產生））。Money scheduler一個賠案產生一條記錄，該賠案下的Particular money provision都掛在這個Money scheduler下。 Claim offer可以存放多種offer，可以為服務、物品或金錢（包括狀態(tài)是未決的） Internal claim cost用于存放理算后的公司內部發(fā)生的各種理賠費用，如查勘費、施救費、律師費等。 Internal claim cost通過Internal claim cost - Claim Rlship 與claim folder關聯 查勘表中記錄的明細費用（如查勘費、施救費、律師費等）放在Money provision part，通過Money provision cash flow到 Particular money provision中的activity id連接到查勘活動中 Money provision part用于存放賠給客戶的賠款明細(包括狀態(tài)是未決的)、到賠付項目代碼級別。 Money provision cash flow用于存放子險級合計的賠款金額,和claim offer對應 放在Money provision cash flow和Money provision part中賠款的錢金額變化、 Money provision cash flow /Money provision part中賠款金額不保留版本，當出現修改時直接修改這些表中的記錄 Claim Offer 中的賠款金額不保留歷史，將來如果業(yè)務需要，再做保留歷史處理 立案的估損/定損都放在Financial valuation中 估損/定損明細單獨建表,|,核保核賠在EDW模型中的處理,核保核賠本身是一個工作流程的活動，每個核保核賠又細分成不同的步驟，如：收集單證，復核等活動步驟。類似于IIW中的campaign，campaign cell和campaign step的關系。 在EDW中產險使用underwriting check/claim check來存放核保核賠信息，主活動和活動步驟通過不同的type來區(qū)分。主活動和活動步驟通過underwriting check/claim check的自關聯實現。 每個主活動只需要保留一條記錄，有變化update；因為每個具體的步驟包括了該活動變化的信息。 對于一個投保單多次核保的情況，每個核保一個主活動。,|,投保單/協議申請單的處理,以下投保單指：投保單或協議申請單 投保單作為保單的一個歷史狀態(tài)處理。 全量數據： 即使投保單已成為保單，也要插入一條投保單的記錄。Valid from date＝錄入日期，valid to date＝簽單日期。 Effective from date＝投保日期（如果沒有投保日期，同valid from date），Effective to date＝簽單日期。如果該投保單記錄的目標表是individual agreement，則投保單的記錄直接插入individual agreement history。 如果投保單還沒有成為保單，投保單的Valid from date＝錄入日期，valid to date＝9999/12/31。 Effective from date＝投保日期（如果沒有投保日期，同valid from date），Effective to date＝ 9999/12/31 。 增量數據： 如果投保單已經變?yōu)楸瘟耍妥鳛楸蔚臍v史記錄，新插入保單的記錄， 如果目標表是individual agreement，則投保單的記錄挪到individual agreement history中。 如果目標表是不是individual agreement，則投保單的記錄仍然在原表中。如：group agreement，commercial agreement。 投保單和保單共用同一個agreement id，type id分別為投保單、保單（團單，個單，個單憑證等）。 投保單只保持一條記錄，最新狀況update該記錄。 備注：如果將來投保單數據太多，影響執(zhí)行效率，由其他程序定期執(zhí)行歸檔處理，將已成為保單的n年前的投保單數據歸檔。 有的系統投保單和保單是同一條記錄，有的系統投保單和保單是不同條記錄。在EDW中必須保持一致的處理方式。,|,批改申請單的處理,批改不記錄歷史，因此，批改申請和批改使用同一條記錄，有改變直接Update該記錄。 在change request中新增一個字段存放批改申請?zhí)枴?|,日程,為什么需要模型 模型的組織結構 模型實施方法 模型設計策略 Q & A,|,日程,Thank you!,|,