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

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

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

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

4、發(fā)生變化,事實表與維表的比較,9,分析主題域數(shù)據(jù) (Analytical Subject Areas),通過事實（Facts）和維度（Dimensions）模型方式表達，為典型保險分析應(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ù)據(jù)按照定義的層次形成相關(guān)的維度視圖，事實表數(shù)據(jù)通過核心原子數(shù)據(jù)相關(guān)主題域數(shù)據(jù)經(jīng)過匯總計算得到。核心原子數(shù)據(jù)和分析數(shù)據(jù)分別映射到不同的數(shù)據(jù)集市中。 其中，典型的分析主題域如通用承保分析（Unive

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

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

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

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

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

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

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

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

13、此建立中間表有一定的難度，目前的策略是參照原指標(biāo)算法分別計算出各指標(biāo)，并插入一臨時表中，再對該表執(zhí)行聚合操作 由于源系統(tǒng)可能會更新核賠數(shù)據(jù)的狀態(tài)，因而導(dǎo)致部分指標(biāo)存在“過期”現(xiàn)象，在目前沒有歷史數(shù)據(jù)支持的情況，我們的解決辦法是由業(yè)務(wù)部門確定一個過期閥值天數(shù)N，超過N天之前的指標(biāo)不會再過期，那么每天加載核賠效率事實表時，不僅僅計算當(dāng)天的事實數(shù)據(jù)，而是把N天之前的數(shù)據(jù)也都復(fù)算一遍,32,主 題,維度建模的基本概念 模型概述 模型介紹 模型運用方法與技巧 相關(guān)技術(shù)問題,33,數(shù)據(jù)版本的截取時間點,查詢出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,截取時間為常量,截取時間為連接主表的相關(guān)時間字段,34,時間鏈的生成技巧,假定經(jīng)過相應(yīng)的處理后得到一個數(shù)據(jù)集T，T可以是一個臨時表、視圖或子查詢，并包含一個日期字段Start_date(不可有空值)作為時間鏈的開始日期，但End_date還未生成，則可以用以下SQL生成時間鏈： 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，比如保單號、單據(jù)號等,35,歷史表增量加載策略,在常規(guī)情況下，該算法也適合全量加載，此時數(shù)據(jù)集是全量數(shù)據(jù)，而目標(biāo)表的初始狀況是空表。,由于目標(biāo)表中要求第一條版本記錄的Start_date應(yīng)該為1900-1-1，這里的算法要考慮T01的增量在目標(biāo)表中沒有前續(xù)版本記錄的情況,36,已賺保

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

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

18、以是表、視圖、物化視圖或子查詢 可以通過對月維表增加查詢條件來減少最終快照型事實的維度組合，比如保單維表中實際上是包含“核保月份”的，則可以增加一個連接條件為“v1.mon_id=核保月份”，但這種做法必須要有數(shù)據(jù)質(zhì)量的保證 會計年度的報表或分析應(yīng)用可以在該查詢結(jié)果的基礎(chǔ)上直接以mon_id為時間維度進行聚合得到,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)計月份),39,承保理賠分析應(yīng)用保單年度算法,保單年度分析是在兩個時間維度上的快照型事實，兩個時間維度分別是保險起期和截止月份，所有指標(biāo)都是在截止月份維度上的時點值，計算該快照型事實可參考下面的SQL。 該算法同樣可以增加條件減少維度組合，比如通常在保險責(zé)任結(jié)束N年后，保單年度分析的價值已經(jīng)減退，因此可以在連接條件中增加“v1.mon_id = pf.保險止期 + N”， 注意：由于實際模型中保單維表不止保險起期一個屬性，則以下查詢在維度上還存在重復(fù)值，直接使用需要再進行聚合。,WITH v1 AS (SELECT mon_id FRO