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

安邦BI系統(tǒng)數(shù)據(jù)模型引見主題維度建模的根本概念模型概述模型引見模型運用方法與技巧相關(guān)技術(shù)問題如何建立分析集市模型行業(yè)閱歷BI.InsuranceDWM任務組和建模繼續(xù)開展業(yè)務需求安邦分析集市模型業(yè)務驅(qū)動根據(jù)固定報表和KPI、繼續(xù)率、承保理賠、執(zhí)行力、再保五個數(shù)據(jù)集市的業(yè)務需求,結(jié)合BI.InsuranceDWM的建模方法,建立TaipingEDW分析集市模型分析集市模型概念維度建模一種非規(guī)范化的關(guān)系模型由一組屬性構(gòu)成的表所組成表跟表之間的關(guān)系經(jīng)過關(guān)鍵字和外鍵來定義以良好的可了解性和方便的產(chǎn)生報表來進展數(shù)據(jù)組織，很少思索修正的性能經(jīng)過SQL或者相關(guān)的工具實現(xiàn)數(shù)據(jù)的查詢和維護分析集市模型概念現(xiàn)實表〔一〕每一個現(xiàn)實表通常包含了業(yè)務需求所關(guān)懷的一系列的目的值每一個現(xiàn)實表的行包括具有可加性的數(shù)值型的目的值文身手實通常具有不可預見的內(nèi)容，很難進展分析與維表相銜接的外鍵通常具有兩個和兩個以上的外鍵外鍵之間表示維表之間多對多的關(guān)系分析集市模型概念現(xiàn)實表〔二〕現(xiàn)實表的特征非常的大包含幾萬、幾十萬甚至幾百幾千條的記錄內(nèi)容相對的窄列數(shù)較少經(jīng)常發(fā)生變化現(xiàn)實世界中新事件的發(fā)生→現(xiàn)實表中添加一條記錄典型情況下，僅僅是數(shù)據(jù)的追加現(xiàn)實表的運用各類目的值的聚集計算分析集市模型概念維表〔一〕每一張維表對應現(xiàn)實世界中的一個對象或者概念例如：客戶、產(chǎn)品、保單、標的、案件維表的特征包含了眾多描畫性的屬性列如保單維表里的銷售渠道、保費區(qū)間、風險等級等通常情況下，跟現(xiàn)實表相比，行數(shù)相對較小通常<10萬條內(nèi)容相對固定幾乎就是一類查找表分析集市模型概念維表〔二〕維表的運用基于維屬性的過濾〔切片、切塊等〕基于維屬性的各種聚集操作報表中各類標簽的主要來源現(xiàn)實表經(jīng)過維表進展援用分析集市模型概念

Facts屬性個數(shù)少〔窄〕記錄行數(shù)多〔大〕數(shù)值型目的隨著時間的推移，數(shù)據(jù)增長

Dimensions

屬性個數(shù)多〔寬〕記錄行數(shù)少〔小〕描畫性屬性靜態(tài)的，很少發(fā)生變化

現(xiàn)實表與維表的比較

分析主題域數(shù)據(jù)(AnalyticalSubjectAreas)經(jīng)過現(xiàn)實〔Facts〕和維度〔Dimensions〕模型方式表達，為典型保險分析運用提供分析型數(shù)據(jù)架構(gòu)，為數(shù)據(jù)集市提供一致的目的和維度，為數(shù)據(jù)運用提供一致的數(shù)據(jù)信息。分析主題域數(shù)據(jù)，將業(yè)務需求階段定義的分析主題域，按照維度建模方法，格式化成符合維度－現(xiàn)實表的星型模型或雪花模型。其中維度信息由中心原子數(shù)據(jù)按照定義的層次構(gòu)成相關(guān)的維度視圖，現(xiàn)實表數(shù)據(jù)經(jīng)過中心原子數(shù)據(jù)相關(guān)主題域數(shù)據(jù)經(jīng)過匯總計算得到。中心原子數(shù)據(jù)和分析數(shù)據(jù)分別映射到不同的數(shù)據(jù)集市中。其中，典型的分析主題域如通用承保分析〔Universalunderwritinganalysis〕如以下圖：分析集市模型設(shè)計步驟

需求整理目的維度矩陣設(shè)計集市層模型設(shè)計分析層模型維度目的矩陣維度目的矩陣是把需求轉(zhuǎn)化為多維模型的重要工具業(yè)務分析需求舉例與上一年相比，各險種所占的份額有何差別?每個機構(gòu)在各險種所占份額是多少?機構(gòu)險種時間2006和2007年度各險種份額對比20062007Thousands2006年度各機構(gòu)在各險種上的份額維度建模舉例時間機構(gòu)險種客戶主題維度建模的根本概念模型概述模型引見模型運用方法與技巧相關(guān)技術(shù)問題元數(shù)據(jù)模型在整個系統(tǒng)架構(gòu)中的定位ETL過程中心收付車險理賠財務面向業(yè)務運用數(shù)據(jù)集市Cube最終用戶根本上與業(yè)務數(shù)據(jù)同構(gòu)保管詳細買賣數(shù)據(jù)數(shù)據(jù)倉庫面向分析主題輕度匯總數(shù)據(jù)StarSchema建模中間表財務賬戶活動理賠事件收付費處理資金供應角色實物地理位置協(xié)議產(chǎn)品分析層分析層模型主題域分析層模型每個主題域為同類的業(yè)務處置，共享一樣的維度承保理賠分析記錄安邦保險所關(guān)注的主要承保理賠業(yè)務目的。收付費分析記錄財務收付中應收保費、實收保費、應收率、應付賠款、實付賠款等收付類信息。核賠效率分析記錄理賠環(huán)節(jié)中查勘、定損、核損、繕制過程的相關(guān)目的。主題維度建模的根本概念模型概述模型引見模型運用方法與技巧相關(guān)技術(shù)問題分析層模型構(gòu)造收付費分析的星型構(gòu)造分析層模型引見“承保理賠分析現(xiàn)實表〞、“收付費分析現(xiàn)實表〞、“核賠效率分析現(xiàn)實表〞都是經(jīng)輕度匯總生成的表，它們?nèi)慷际鞘聞招同F(xiàn)實表中間現(xiàn)實表都是經(jīng)過版本化的歷史表，保管最細粒度的數(shù)據(jù)中間現(xiàn)實表主要用于生成輕度匯總表的過渡，除此之外，經(jīng)過冗余相關(guān)屬性，中間現(xiàn)實表的設(shè)計也思索了其它無法從直接從分析層計算的目的的計算事務型現(xiàn)實和快照型現(xiàn)實機構(gòu)險種日期未決變化201010210112008-9-121000201010210112008-9-13-300201010210112008-9-122200201020120312008-9-12100201020220012008-9-121000201020220012008-9-131200201020220012008-9-14-2200事務型快照型快照型現(xiàn)實表的目的通常包含時點值，隨著時間積累，維度組合會越來越細，此類型的現(xiàn)實表通常用于粒度較粗的數(shù)據(jù)集市或Cube；而事務型現(xiàn)實表的度量都是可累加的，其對于維度較多的輕度匯總表是有利。假設(shè)OLAPserver支持在事務型現(xiàn)實表上計算時點值，建議在維度較細的Cube上運用事務型現(xiàn)實。機構(gòu)險種日期期末未決201010210112008-9-121000201010210112008-9-13700201010210112008-9-142900201020120312008-9-12100201020120312008-9-13100201020120312008-9-14100201020220012008-9-121000201020220012008-9-132200201020220012008-9-140中間層歷史數(shù)據(jù)的版本化數(shù)據(jù)倉庫中的歷史數(shù)據(jù)決不會被刪除或覆蓋，因此需求參與數(shù)據(jù)版本特性來區(qū)分隨時間變化的業(yè)務信息，使得在任一給定時間點上都能檢索到正確的數(shù)據(jù)。數(shù)據(jù)能否需求版本化，首先依賴于實體能否有時變特性，比如“報案信息〞通常是在某個時間點上發(fā)生，它就沒有時變特性，因此就不需求版本化。在實體中參與時間戳屬性可以實現(xiàn)數(shù)據(jù)版本化，這個時間戳可以基于業(yè)務涵義，如對保單的某次修正的生效日期；也可以僅僅是技術(shù)上的，比如員工某項登記信息被修正，那么生成新的員工記錄，并指定該記錄的有效(Valid)期間，ETL處置需求實現(xiàn)數(shù)據(jù)相鄰版本時間戳的鏈接。需求為版本化的實體找出一個錨ID來獨一標識每個數(shù)據(jù)實例，比如保單實體的錨ID可以是保單號，闡明該實體中最原子的粒度是保單。時間戳與數(shù)據(jù)版本業(yè)務信息的不同版本的時間鏈時間戳(start_date/end_date)員工姓名職位離職時間start_dateend_date張三初級核保人2006-5-212007-12-3張三中級核保人2007-12-39999-12-31李四業(yè)務員2004-3-122006-10-8李四業(yè)務經(jīng)理2006-10-82008-5-9李四業(yè)務經(jīng)理2008-5-92008-5-99999-12-31以上start_date為紅色的是該數(shù)據(jù)實例被創(chuàng)建時的版本，該值從邏輯上看應該為數(shù)據(jù)的業(yè)務創(chuàng)建日期，比如入職日期，但為了防止遭到數(shù)據(jù)質(zhì)量的影響，我們通常把每個數(shù)據(jù)實例的第一個版本的start_date置為1900-01-01承保中間表場景例如保單號批單號保險起期保險金額保費核保日期批改原因startdateenddate0001－－－2006-3-712000802006-3-4N/A1900-1-12006-5-18000100012006-3-7002006-5-18注銷2006-5-189999-12-310002－－－2006-4-19223001202006-4-7N/A1900-1-12006-5-10000200022006-4-19223001202006-5-10修改2006-5-102006-5-21000200032006-4-19223001382006-5-21加保2006-5-219999-12-310003－－－2007-1-121200008902007-1-8N/A1900-1-19999-12-310004－－－2007-5-2570005002007-4-22N/A1900-1-19999-12-310005－－－2008-3-1440000036002008-3-8N/A1900-1-12008-4-6000500042008-3-1431000030002008-4-6退保2008-4-62008-4-16000500052006-3-1431000030002008-4-16修改2008-4-169999-12-31收付費中間表場景例如單據(jù)號type狀態(tài)應收保費實收保費應付賠款實付賠款應付手續(xù)費實付手續(xù)費startdateenddate0001保費應收2000.000.00

1900-1-12006-5-180001保費實收2000.002000.00

2006-5-189999-12-310002手續(xù)費應付

132.000.001900-1-12006-5-100002手續(xù)費實付

132.00132.002006-5-102006-5-110002手續(xù)費沖正

132.000.002006-5-119999-12-310003保費應收10000.000.00

1900-1-19999-12-310004賠款應付

26700.000.00

1900-1-19999-12-310005賠款應付

14700.000.00

1900-1-12006-4-60005賠款實付

14700.0014700.00

2006-4-69999-12-310006保費應收3500.000.00

1900-1-19999-12-31理賠中間現(xiàn)實表場景例如賠案號次數(shù)立案日期估損金額賠付金額結(jié)案日期結(jié)案狀態(tài)startdateenddate000112006-3-73000

9999-12-31N1900-1-12006-53-7300032002006-5-18Y2006-5-189999-12-31000212006-4-1912000

9999-12-31N1900-1-12006-5-10000212006-4-1911000

9999-12-31N2006-5-102006-5-21000212006-4-1911000100002006-5-21Y2006-5-219999-12-31000312007-1-12900

9999-12-31N1900-1-19999-12-31000412007-5-257000

9999-12-31N2007-5-29999-12-31000512008-3-1421000

9999-12-31N1900-1-12006-4-6000512008-3-1421000200002006-4-6Y2006-4-69999-12-31000522006-4-163003002006-5-26Y1900-1-19999-12-31中間層的構(gòu)建算法請安邦工程組自行用Visio畫出算法流程圖，分頁貼在此處分析層模型未涵蓋的特殊分析運用需求中有部分的目的在計算上比較獨特，且與其它目的沒有太多的共享價值，因此未納入分析層模型的調(diào)查范疇，這些目的主要有：核保效率分析——用mid_policy_fact與核保信息表關(guān)聯(lián)，再經(jīng)過聚合計算可得到相應的分析目的理財險網(wǎng)點活動率——該目的為非累計目的，不思索安頓在分析層，可在構(gòu)建CUBE時按原始算法計算，但日、月、季、年的匯總值要分別計算車貸險目的——該類目的的數(shù)據(jù)源比較特殊，不思索建立分析層，可在構(gòu)建CUBE時按原始算法計算從中間層到分析層——承保理賠簽單類只計算保單，口徑為：核保日期在當期andstart_date=‘1900-1-1’主要目的有：保險金額、保單保費、保單筆數(shù)、累計儲金、已驗車數(shù)量、應驗車數(shù)量需求減去退保和注銷會計年度類不思索版本，一切記錄一同計算，口徑為：核保日期在當期保額和保費只累計變化值主要目的有：保費收入、批增批減保費〔件數(shù)〕、退?！沧N〕保費〔件數(shù)〕等理賠類口徑：結(jié)案日期在當期或估損修正日期在當期部分目的只思索起始兩個版本，即立案和結(jié)案，比如立案估損金額、結(jié)案天數(shù)主要目的有：已決筆數(shù)、已決賠款、未決筆數(shù)變化、未決賠款變化、立案估損金額、估損偏向絕對值、加權(quán)估損金額、結(jié)案天數(shù)特殊口徑類已賺保費－－算法參見“模型運用方法與技巧〞有部分目的不需求定義在分析層，由于它們可以從以上目的衍生，比如車險續(xù)保目的、理財險目的，都可以從承保理賠分析表以保險止期維度為口徑聚合生成。從中間層到分析層——收付費收付費目的的主要口徑根本上都是以收付確認日期來計算，在源系統(tǒng)中表達為應收(付)確認日期和實收(付)確認日期需求中要求按幣種分別展現(xiàn)人民幣和非人民幣的統(tǒng)計值，由于身手實表曾經(jīng)包含保單維度，因此這些目的都可以根據(jù)保單維度中的幣種重新匯總生成應收保費變化的計算規(guī)那么是：應收一筆保費以正數(shù)累計，實收一筆保費以負數(shù)累計，對實收的沖正以正數(shù)累計滾動12個月財務保費收入是應收保費收入的衍生目的核賠效率分析核賠效率分析的目的根本都有共享的維度，因此為其建立一個分析層主題是適宜的核賠效率分析涉及的源表比較分散，且增量機制較為復雜，因此建立中間表有一定的難度，目前的戰(zhàn)略是參照原目的算法分別計算出各目的，并插入一暫時表中，再對該表執(zhí)行聚合操作由于源系統(tǒng)能夠會更新核賠數(shù)據(jù)的形狀，因此導致部分目的存在“過期〞景象，在目前沒有歷史數(shù)據(jù)支持的情況，我們的處理方法是由業(yè)務部門確定一個過期閥值天數(shù)N，超越N天之前的目的不會再過期，那么每天加載核賠效率現(xiàn)實表時，不僅僅計算當天的現(xiàn)實數(shù)據(jù)，而是把N天之前的數(shù)據(jù)也都復算一遍主題維度建模的根本概念模型概述模型引見模型運用方法與技巧相關(guān)技術(shù)問題數(shù)據(jù)版本的截取時間點查詢出2021-4-10各業(yè)務員的數(shù)據(jù)：select*from業(yè)務員表wherestart_date<=‘2021-4-10’andend_date>‘2021-4-10’保單中間表銜接員工表獲取員工的職位屬性：selecta.policy,…,b.職位frommid_policy_facta,業(yè)務員表bwherea.c_emp_cde=b.c_emp_cdeandb.start_date<=t_udr_dateandb.end_date>t_udr_date截取時間為常量截取時間為銜接主表的相關(guān)時間字段時間鏈的生成技巧假定經(jīng)過相應的處置后得到一個數(shù)據(jù)集T，T可以是一個暫時表、視圖或子查詢，并包含一個日期字段Start_date(不可有空值)作為時間鏈的開場日期，但End_date還未生成，那么可以用以下SQL生成時間鏈：Selectanchor_id,…,start_date,lead(start_date,1,to_date(‘99991231’,’yyyymmdd’)over(partitionbyanchor_idorderbystart_date)asend_dateFromT;其中Anchor_id是數(shù)據(jù)集的錨ID，比如保單號、單據(jù)號等歷史表增量加載戰(zhàn)略在常規(guī)情況下，該算法也適宜全量加載，此時數(shù)據(jù)集是全量數(shù)據(jù)，而目的表的初始情況是空表。由于目的表中要求第一條版本記錄的Start_date應該為‘1900-1-1‘，這里的算法要思索T01的增量在目的表中沒有前續(xù)版本記錄的情況已賺保費的計算以上有效時間段不能采用保單版本的時間鏈，而要按業(yè)務上的保單生效規(guī)那么來確定時間范圍普通情況下，保單的生效開場日期就是保險起期，但對于倒簽單，那么按核保日期計算，因此逃避了在此之前已匯總的漏算問題最好在承保中間表中事先計算出業(yè)務有效時間范圍，作為業(yè)務時間鏈，不僅防止在匯總目的時多做計算，而且有利于測試時核對數(shù)據(jù)由于涉及到除法，假設(shè)時間維的粒度太細，能夠?qū)е逻^多的精度誤差，建議匯總表中該目的字段保管更多的小數(shù)位，比如6~8位2007.12007.6安邦以前的算法累加各版本保費變化乘以當期內(nèi)的保險期限時間段在當前保險期限內(nèi)的占比2007.12007.6較準確的算法累加各版本當前保費乘以當期內(nèi)的有效時間段在當前保險期限內(nèi)的占比每個線段表示保單各版本用于計算當期有效天數(shù)的基準時間段，而被陰影覆蓋的部分作為時間占比的分子承保理賠分析運用——場景假設(shè)Ply_prof_idDpt_id統(tǒng)計月份簽單件數(shù)已決件數(shù)未決件數(shù)變化100120012007.2103100120012007.321-1100120022007.3101100220022007.3112100120012007.4011100120022007.4102100220022007.421-1100220032007.4101100220032007.5100100120022007.5010100220022007.501-1100220032007.6011Ply_prof_id保險起期10012007.210022007.3Ply_prof_dm為簡化起見，假定保單維表只需一個維屬性：保險起期〔月〕DM_FACT承保理賠分析現(xiàn)實表經(jīng)過聚合計算后生成的部分視圖，含有三個目的，除統(tǒng)計月份外的維度組合有四種〔不同的顏色區(qū)分〕承保理賠分析運用——轉(zhuǎn)換成快照型現(xiàn)實經(jīng)過與月維表的分區(qū)外銜接，以上查詢填充了脫漏的月份，并利用分析函數(shù)計算出“期末未決〞這個時點值?？梢岳么瞬樵兘ubeDM_FACT應該是經(jīng)聚合后粒度曾經(jīng)較粗的現(xiàn)實，可以是表、視圖、物化視圖或子查詢可以經(jīng)過對月維表添加查詢條件來減少最終快照型現(xiàn)實的維度組合，比如保單維表中實踐上是包含“核保月份〞的，那么可以添加一個銜接條件為“v1.mon_id>=核保月份〞，但這種做法必需求有數(shù)據(jù)質(zhì)量的保證會計年度的報表或分析運用可以在該查詢結(jié)果的根底上直接以mon_id為時間維度進展聚合得到WITHv1AS(SELECTmon_idFROMcal_monWHEREmon_id<=200706)SELECTply_prof_id,dpt_id,mon_id,簽單件數(shù),已決件數(shù)，sum(未決件數(shù)變化)OVER(PARTITIONBYply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING)as期末未決件數(shù)FROM(SELECTply_prof_id,dpt_id,v1.mon_id,簽單件數(shù),已決件數(shù)，未決件數(shù)變化FROMdm_factPARTITIONBY(ply_prof_id,dpt_id)RIGHTOUTERJOINv1ON(v1.mon_id=統(tǒng)計月份))承保理賠分析運用——保單年度算法保單年度分析是在兩個時間維度上的快照型現(xiàn)實，兩個時間維度分別是保險起期和截止月份，一切目的都是在截止月份維度上的時點值，計算該快照型現(xiàn)實可參考下面的SQL。該算法同樣可以添加條件減少維度組合，比如通常在保險責任終了N年后，保單年度分析的價值曾經(jīng)減退，因此可以在銜接條件中添加“v1.mon_id<=pf.保險止期+N〞，留意：由于實踐模型中保單維表不止保險起期一個屬性，那么以下查詢在維度上還存在反復值，直接運用需求再進展聚合。WITHv1AS(SELECTmon_idFROMcal_monWHEREmon_id<=200706)SELECT保險起期,dpt_id,mon_id,sum(簽單件數(shù))OVER(PARTITIONBYply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING)AS簽單件數(shù)sum(已決件數(shù))OVER(PARTITIONBYply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING)AS已決件數(shù)sum(未決件數(shù)變化)OVER(PARTITIONBYply_prof_id,dpt_idORDERBYmon_idROWSUNBOUNDEDPRECEDING)AS未決件數(shù)FROM(SELECTpf.保險起期,dpt_id,v1.mon_id,簽單件數(shù),已決件數(shù)，未決件數(shù)變化FROMdm_factINNERJOINply_prof_dmpfON(pf.ply_prof_id=dm_fact.ply_prof_id)PARTITIONBY(ply_prof_id,dpt_id)RIGHTOUTERJOINv1ON(v1.mon_id=統(tǒng)計月份andv1_mon_id