保險(xiǎn)倉庫數(shù)據(jù)模型設(shè)計(jì)ppt課件.ppt

1、安邦BI系統(tǒng)數(shù)據(jù)模型介紹,1,主 題,維度建模的基本概念 模型概述 模型介紹 模型運(yùn)用方法與技巧 相關(guān)技術(shù)問題,2,如何建立分析集市模型,行業(yè)經(jīng)驗(yàn),BI.Insurance DWM,工作組和建模,持續(xù)發(fā)展,業(yè)務(wù)需求,安邦分析 集市模型,業(yè)務(wù)驅(qū)動(dòng) 根據(jù)固定報(bào)表和KPI、持續(xù)率、承保理賠、執(zhí)行力、再保五個(gè)數(shù)據(jù)集市的業(yè)務(wù)需求,結(jié)合BI.Insurance DWM的建模方法, 建立Taiping EDW 分析集市模型,3,分析集市模型概念,維度建模 一種非規(guī)范化的關(guān)系模型 由一組屬性構(gòu)成的表所組成 表跟表之間的關(guān)系通過關(guān)鍵字和外鍵來定義 以良好的可理解性和方便的產(chǎn)生報(bào)表來進(jìn)行數(shù)據(jù)組織，很少考慮修改的性

2、能 通過SQL或者相關(guān)的工具實(shí)現(xiàn)數(shù)據(jù)的查詢和維護(hù),4,分析集市模型概念,事實(shí)表（一） 每一個(gè)事實(shí)表通常包含了業(yè)務(wù)需求所關(guān)心的一系列的指標(biāo)值 每一個(gè)事實(shí)表的行包括 具有可加性的數(shù)值型的指標(biāo)值 文本事實(shí)通常具有不可預(yù)見的內(nèi)容，很難進(jìn)行分析 與維表相連接的外鍵 通常具有兩個(gè)和兩個(gè)以上的外鍵 外鍵之間表示維表之間多對多的關(guān)系,5,分析集市模型概念,事實(shí)表（二） 事實(shí)表的特征 非常的大 包含幾萬、幾十萬甚至幾百幾千條的記錄 內(nèi)容相對的窄 列數(shù)較少 經(jīng)常發(fā)生變化 現(xiàn)實(shí)世界中新事件的發(fā)生事實(shí)表中增加一條記錄 典型情況下，僅僅是數(shù)據(jù)的追加 事實(shí)表的使用 各類指標(biāo)值的聚集計(jì)算,6,分析集市模型概念,維表（一）

3、每一張維表對應(yīng)現(xiàn)實(shí)世界中的一個(gè)對象或者概念 例如：客戶、產(chǎn)品、保單、標(biāo)的、案件 維表的特征 包含了眾多描述性的屬性列 如保單維表里的銷售渠道、保費(fèi)區(qū)間、風(fēng)險(xiǎn)等級等 通常情況下，跟事實(shí)表相比，行數(shù)相對較小 通常 10萬條 內(nèi)容相對固定 幾乎就是一類查找表,7,分析集市模型概念,維表（二） 維表的應(yīng)用 基于維屬性的過濾（切片、切塊等） 基于維屬性的各種聚集操作 報(bào)表中各類標(biāo)簽的主要來源 事實(shí)表通過維表進(jìn)行引用,8,分析集市模型概念,Facts 屬性個(gè)數(shù)少（窄） 記錄行數(shù)多（大） 數(shù)值型指標(biāo) 隨著時(shí)間的推移，數(shù)據(jù)增長,Dimensions 屬性個(gè)數(shù)多（寬） 記錄行數(shù)少（?。?描述性屬性 靜態(tài)的，很少

4、發(fā)生變化,事實(shí)表與維表的比較,9,分析主題域數(shù)據(jù) (Analytical Subject Areas),通過事實(shí)（Facts）和維度（Dimensions）模型方式表達(dá)，為典型保險(xiǎn)分析應(yīng)用提供分析型數(shù)據(jù)架構(gòu)，為數(shù)據(jù)集市提供統(tǒng)一的指標(biāo)和維度，為數(shù)據(jù)應(yīng)用提供統(tǒng)一的數(shù)據(jù)信息。 分析主題域數(shù)據(jù)，將業(yè)務(wù)需求階段定義的分析主題域，按照維度建模方法，格式化成符合維度事實(shí)表的星型模型或雪花模型。其中維度信息由核心原子數(shù)據(jù)按照定義的層次形成相關(guān)的維度視圖，事實(shí)表數(shù)據(jù)通過核心原子數(shù)據(jù)相關(guān)主題域數(shù)據(jù)經(jīng)過匯總計(jì)算得到。核心原子數(shù)據(jù)和分析數(shù)據(jù)分別映射到不同的數(shù)據(jù)集市中。 其中，典型的分析主題域如通用承保分析（Unive

5、rsal underwriting analysis）如下圖：,10,分析集市模型設(shè)計(jì)步驟,需求,整理指標(biāo)維度矩陣,設(shè)計(jì)集市層模型,設(shè)計(jì)分析層模型,11,維度指標(biāo)矩陣,維度指標(biāo)矩陣是把需求轉(zhuǎn)化為多維模型的重要工具,12,業(yè)務(wù)分析需求舉例,與上一年相比，各險(xiǎn)種所占的份額有何差異? 每個(gè)機(jī)構(gòu)在各險(xiǎn)種所占份額是多少?,機(jī)構(gòu),險(xiǎn)種,時(shí)間,2006和2007年度各險(xiǎn)種份額對比,2006,2007,Thousands,2006年度各機(jī)構(gòu)在各險(xiǎn)種上的份額,13,維度建模舉例,時(shí)間,機(jī)構(gòu),險(xiǎn)種,客戶,14,主 題,維度建模的基本概念 模型概述 模型介紹 模型運(yùn)用方法與技巧 相關(guān)技術(shù)問題,15,元數(shù)據(jù),模型在整

6、個(gè)系統(tǒng)架構(gòu)中的定位,面向業(yè)務(wù)應(yīng)用,基本上與業(yè)務(wù)數(shù)據(jù)同構(gòu) 保留詳細(xì)交易數(shù)據(jù),數(shù)據(jù)倉庫,分析層,16,分析層模型主題域,分析層模型每個(gè)主題域?yàn)橥惖臉I(yè)務(wù)處理，共享相同的維度 承保理賠分析 記錄安邦保險(xiǎn)所關(guān)注的主要承保理賠業(yè)務(wù)指標(biāo)。 收付費(fèi)分析 記錄財(cái)務(wù)收付中應(yīng)收保費(fèi)、實(shí)收保費(fèi)、應(yīng)收率、應(yīng)付賠款、實(shí)付賠款等收付類信息。 核賠效率分析 記錄理賠環(huán)節(jié)中查勘、定損、核損、繕制過程的相關(guān)指標(biāo)。,17,主 題,維度建模的基本概念 模型概述 模型介紹 模型運(yùn)用方法與技巧 相關(guān)技術(shù)問題,18,分析層模型結(jié)構(gòu),19,收付費(fèi)分析的星型結(jié)構(gòu),20,分析層模型介紹,“承保理賠分析事實(shí)表”、“收付費(fèi)分析事實(shí)表”、“核賠效率

7、分析事實(shí)表”都是經(jīng)輕度匯總生成的表，它們?nèi)慷际鞘聞?wù)型事實(shí)表 中間事實(shí)表都是經(jīng)過版本化的歷史表，保存最細(xì)粒度的數(shù)據(jù) 中間事實(shí)表主要用于生成輕度匯總表的過渡，除此之外，通過冗余相關(guān)屬性，中間事實(shí)表的設(shè)計(jì)也考慮了其它無法從直接從分析層計(jì)算的指標(biāo)的計(jì)算,21,事務(wù)型事實(shí)和快照型事實(shí),事務(wù)型,快照型,快照型事實(shí)表的指標(biāo)通常包含時(shí)點(diǎn)值，隨著時(shí)間積累，維度組合會(huì)越來越細(xì)，此類型的事實(shí)表通常用于粒度較粗的數(shù)據(jù)集市或Cube；而事務(wù)型事實(shí)表的度量都是可累加的，其對于維度較多的輕度匯總表是有利。如果OLAP server支持在事務(wù)型事實(shí)表上計(jì)算時(shí)點(diǎn)值，建議在維度較細(xì)的Cube上使用事務(wù)型事實(shí)。,22,中間層歷史

8、數(shù)據(jù)的版本化,數(shù)據(jù)倉庫中的歷史數(shù)據(jù)決不會(huì)被刪除或覆蓋，因此需要加入數(shù)據(jù)版本特性來區(qū)分隨時(shí)間變化的業(yè)務(wù)信息，使得在任一給定時(shí)間點(diǎn)上都能檢索到正確的數(shù)據(jù)。 數(shù)據(jù)是否需要版本化，首先依賴于實(shí)體是否有時(shí)變特性，比如“報(bào)案信息”通常是在某個(gè)時(shí)間點(diǎn)上發(fā)生，它就沒有時(shí)變特性，因此就不需要版本化。 在實(shí)體中加入時(shí)間戳屬性可以實(shí)現(xiàn)數(shù)據(jù)版本化，這個(gè)時(shí)間戳可以基于業(yè)務(wù)涵義，如對保單的某次批改的生效日期；也可以僅僅是技術(shù)上的，比如員工某項(xiàng)登記信息被修改，則生成新的員工記錄，并指定該記錄的有效(Valid)期間，ETL處理需要實(shí)現(xiàn)數(shù)據(jù)相鄰版本時(shí)間戳的鏈接。 需要為版本化的實(shí)體找出一個(gè)錨ID來唯一標(biāo)識(shí)每個(gè)數(shù)據(jù)實(shí)例，比如保

9、單實(shí)體的錨ID可以是保單號(hào)，表明該實(shí)體中最原子的粒度是保單。,23,時(shí)間戳與數(shù)據(jù)版本,業(yè)務(wù)信息的不同版本的時(shí)間鏈,時(shí)間戳(start_date/end_date),以上start_date為紅色的是該數(shù)據(jù)實(shí)例被創(chuàng)建時(shí)的版本，該值從邏輯上看應(yīng)該為數(shù)據(jù)的業(yè)務(wù)創(chuàng)建日期，比如入職日期，但為了避免受到數(shù)據(jù)質(zhì)量的影響，我們通常把每個(gè)數(shù)據(jù)實(shí)例的第一個(gè)版本的start_date置為1900-01-01,24,承保中間表場景示例,25,收付費(fèi)中間表場景示例,26,理賠中間事實(shí)表場景示例,27,中間層的構(gòu)建算法,請安邦項(xiàng)目組自行用Visio畫出算法流程圖，分頁貼在此處,28,分析層模型未涵蓋的特殊分析應(yīng)用,需求中

10、有部分的指標(biāo)在計(jì)算上比較獨(dú)特，且與其它指標(biāo)沒有太多的共享價(jià)值，因此未納入分析層模型的考查范疇，這些指標(biāo)主要有： 核保效率分析用mid_policy_fact與核保信息表關(guān)聯(lián)，再經(jīng)過聚合計(jì)算可得到相應(yīng)的分析指標(biāo) 理財(cái)險(xiǎn)網(wǎng)點(diǎn)活動(dòng)率該指標(biāo)為非累計(jì)指標(biāo)，不考慮安置在分析層，可在構(gòu)建CUBE時(shí)按原始算法計(jì)算，但日、月、季、年的匯總值要分別計(jì)算 車貸險(xiǎn)指標(biāo)該類指標(biāo)的數(shù)據(jù)源比較特殊，不考慮建立分析層，可在構(gòu)建CUBE時(shí)按原始算法計(jì)算,29,從中間層到分析層承保理賠,簽單類 只計(jì)算保單，口徑為：核保日期在當(dāng)期 and start_date=1900-1-1 主要指標(biāo)有：保險(xiǎn)金額、保單保費(fèi)、保單筆數(shù)、累計(jì)儲(chǔ)金、

11、已驗(yàn)車數(shù)量、應(yīng)驗(yàn)車數(shù)量 需要減去退保和注銷 會(huì)計(jì)年度類 不考慮版本，所有記錄一起計(jì)算，口徑為：核保日期在當(dāng)期 保額和保費(fèi)只累計(jì)變化值 主要指標(biāo)有：保費(fèi)收入、批增批減保費(fèi)（件數(shù)）、退保（注銷）保費(fèi)（件數(shù)）等 理賠類 口徑：結(jié)案日期在當(dāng)期或估損修改日期在當(dāng)期 部分指標(biāo)只考慮起始兩個(gè)版本，即立案和結(jié)案，比如立案估損金額、結(jié)案天數(shù) 主要指標(biāo)有：已決筆數(shù)、已決賠款、未決筆數(shù)變化、未決賠款變化、立案估損金額、估損偏差絕對值、加權(quán)估損金額、結(jié)案天數(shù) 特殊口徑類 已賺保費(fèi)算法參見“模型運(yùn)用方法與技巧” 有部分指標(biāo)不需要定義在分析層，因?yàn)樗鼈兛梢詮囊陨现笜?biāo)衍生，比如車險(xiǎn)續(xù)保指標(biāo)、理財(cái)險(xiǎn)指標(biāo)，都可以從承保理賠分析

12、表以保險(xiǎn)止期維度為口徑聚合生成。,30,從中間層到分析層收付費(fèi),收付費(fèi)指標(biāo)的主要口徑基本上都是以收付確認(rèn)日期來計(jì)算，在源系統(tǒng)中體現(xiàn)為應(yīng)收(付)確認(rèn)日期和實(shí)收(付)確認(rèn)日期 需求中要求按幣種分別展現(xiàn)人民幣和非人民幣的統(tǒng)計(jì)值，由于本事實(shí)表已經(jīng)包含保單維度，因此這些指標(biāo)都可以根據(jù)保單維度中的幣種重新匯總生成 應(yīng)收保費(fèi)變化的計(jì)算規(guī)則是：應(yīng)收一筆保費(fèi)以正數(shù)累計(jì)，實(shí)收一筆保費(fèi)以負(fù)數(shù)累計(jì)，對實(shí)收的沖正以正數(shù)累計(jì) 滾動(dòng)12個(gè)月財(cái)務(wù)保費(fèi)收入是應(yīng)收保費(fèi)收入的衍生指標(biāo),31,核賠效率分析,核賠效率分析的指標(biāo)基本都有共享的維度，因此為其建立一個(gè)分析層主題是合適的 核賠效率分析涉及的源表比較分散，且增量機(jī)制較為復(fù)雜，因

13、此建立中間表有一定的難度，目前的策略是參照原指標(biāo)算法分別計(jì)算出各指標(biāo)，并插入一臨時(shí)表中，再對該表執(zhí)行聚合操作 由于源系統(tǒng)可能會(huì)更新核賠數(shù)據(jù)的狀態(tài)，因而導(dǎo)致部分指標(biāo)存在“過期”現(xiàn)象，在目前沒有歷史數(shù)據(jù)支持的情況，我們的解決辦法是由業(yè)務(wù)部門確定一個(gè)過期閥值天數(shù)N，超過N天之前的指標(biāo)不會(huì)再過期，那么每天加載核賠效率事實(shí)表時(shí)，不僅僅計(jì)算當(dāng)天的事實(shí)數(shù)據(jù)，而是把N天之前的數(shù)據(jù)也都復(fù)算一遍,32,主 題,維度建模的基本概念 模型概述 模型介紹 模型運(yùn)用方法與技巧 相關(guān)技術(shù)問題,33,數(shù)據(jù)版本的截取時(shí)間點(diǎn),查詢出2008-4-10各業(yè)務(wù)員的數(shù)據(jù)： select * from 業(yè)務(wù)員表 where start_

14、date 2008-4-10,保單中間表連接員工表獲取員工的職位屬性： select a.policy, , b.職位 from mid_policy_fact a, 業(yè)務(wù)員表 b where a.c_emp_cde = b.c_emp_cde and b.start_date t_udr_date,截取時(shí)間為常量,截取時(shí)間為連接主表的相關(guān)時(shí)間字段,34,時(shí)間鏈的生成技巧,假定經(jīng)過相應(yīng)的處理后得到一個(gè)數(shù)據(jù)集T，T可以是一個(gè)臨時(shí)表、視圖或子查詢，并包含一個(gè)日期字段Start_date(不可有空值)作為時(shí)間鏈的開始日期，但End_date還未生成，則可以用以下SQL生成時(shí)間鏈： Select an

15、chor_id, , start_date, lead(start_date,1,to_date(99991231,yyyymmdd) over (partition by anchor_id order by start_date) as end_date From T; 其中Anchor_id是數(shù)據(jù)集的錨ID，比如保單號(hào)、單據(jù)號(hào)等,35,歷史表增量加載策略,在常規(guī)情況下，該算法也適合全量加載，此時(shí)數(shù)據(jù)集是全量數(shù)據(jù)，而目標(biāo)表的初始狀況是空表。,由于目標(biāo)表中要求第一條版本記錄的Start_date應(yīng)該為1900-1-1，這里的算法要考慮T01的增量在目標(biāo)表中沒有前續(xù)版本記錄的情況,36,已賺保

16、費(fèi)的計(jì)算,以上有效時(shí)間段不能采用保單版本的時(shí)間鏈，而要按業(yè)務(wù)上的保單生效規(guī)則來確定時(shí)間范圍 一般情況下，保單的生效開始日期就是保險(xiǎn)起期，但對于倒簽單，則按核保日期計(jì)算，因此回避了在此之前已匯總的漏算問題 最好在承保中間表中事先計(jì)算出業(yè)務(wù)有效時(shí)間范圍，作為業(yè)務(wù)時(shí)間鏈，不僅避免在匯總指標(biāo)時(shí)多做計(jì)算，而且有利于測試時(shí)核對數(shù)據(jù) 由于涉及到除法，如果時(shí)間維的粒度太細(xì)，可能導(dǎo)致過多的精度誤差，建議匯總表中該指標(biāo)字段保留更多的小數(shù)位，比如68位,2007.1,2007.6,安邦以前的算法 累加各版本保費(fèi)變化乘以當(dāng)期內(nèi)的保險(xiǎn)期限時(shí)間段在當(dāng)前保險(xiǎn)期限內(nèi)的占比,2007.1,2007.6,較準(zhǔn)確的算法 累加各版本

17、當(dāng)前保費(fèi)乘以當(dāng)期內(nèi)的有效時(shí)間段在當(dāng)前保險(xiǎn)期限內(nèi)的占比,每個(gè)線段表示保單各版本用于計(jì)算當(dāng)期有效天數(shù)的基準(zhǔn)時(shí)間段，而被陰影覆蓋的部分作為時(shí)間占比的分子,37,承保理賠分析應(yīng)用場景假設(shè),Ply_prof_dm 為簡化起見，假定保單維表只有一個(gè)維屬性：保險(xiǎn)起期（月）,DM_FACT 承保理賠分析事實(shí)表經(jīng)過聚合計(jì)算后生成的局部視圖，含有三個(gè)指標(biāo)，除統(tǒng)計(jì)月份外的維度組合有四種（不同的顏色區(qū)分）,38,承保理賠分析應(yīng)用轉(zhuǎn)換成快照型事實(shí),經(jīng)過與月維表的分區(qū)外連接，以上查詢填充了遺漏的月份，并利用分析函數(shù)計(jì)算出“期末未決”這個(gè)時(shí)點(diǎn)值。可以利用此查詢建立Cube DM_FACT應(yīng)該是經(jīng)聚合后粒度已經(jīng)較粗的事實(shí)，可

18、以是表、視圖、物化視圖或子查詢 可以通過對月維表增加查詢條件來減少最終快照型事實(shí)的維度組合，比如保單維表中實(shí)際上是包含“核保月份”的，則可以增加一個(gè)連接條件為“v1.mon_id=核保月份”，但這種做法必須要有數(shù)據(jù)質(zhì)量的保證 會(huì)計(jì)年度的報(bào)表或分析應(yīng)用可以在該查詢結(jié)果的基礎(chǔ)上直接以mon_id為時(shí)間維度進(jìn)行聚合得到,WITH v1 AS (SELECT mon_id FROM cal_mon WHERE mon_id = 200706) SELECT ply_prof_id,dpt_id, mon_id, 簽單件數(shù),已決件數(shù)， sum(未決件數(shù)變化) OVER (PARTITION BY ply

19、_prof_id,dpt_id ORDER BY mon_id ROWS UNBOUNDED PRECEDING ) as 期末未決件數(shù) FROM (SELECT ply_prof_id,dpt_id, v1.mon_id, 簽單件數(shù), 已決件數(shù)，未決件數(shù)變化 FROM dm_fact PARTITION BY (ply_prof_id, dpt_id) RIGHT OUTER JOIN v1 ON (v1.mon_id = 統(tǒng)計(jì)月份),39,承保理賠分析應(yīng)用保單年度算法,保單年度分析是在兩個(gè)時(shí)間維度上的快照型事實(shí)，兩個(gè)時(shí)間維度分別是保險(xiǎn)起期和截止月份，所有指標(biāo)都是在截止月份維度上的時(shí)點(diǎn)值，計(jì)算該快照型事實(shí)可參考下面的SQL。 該算法同樣可以增加條件減少維度組合，比如通常在保險(xiǎn)責(zé)任結(jié)束N年后，保單年度分析的價(jià)值已經(jīng)減退，因此可以在連接條件中增加“v1.mon_id = pf.保險(xiǎn)止期 + N”， 注意：由于實(shí)際模型中保單維表不止保險(xiǎn)起期一個(gè)屬性，則以下查詢在維度上還存在重復(fù)值，直接使用需要再進(jìn)行聚合。,WITH v1 AS (SELECT mon_id FRO