个险渠道2009年度预算考核追踪详细设计

个险渠道2009年度预算考核追踪报表详细设计文件状态： 草稿 正式发布 正在修改文件标识：个险渠道2009年度预算考核追踪报表详细设计当前版本：1.0作 者：徐景春完成日期：2009-2-11版 本 历 史版本/状态作者参与者起止日期备注目 录1.概述12.体系结构13.数据模型24.ETL设计24.1包名列表24.2调度策略44.3任务参数74.4类型1的处理流程74.5类型2的处理流程94.6类型3的处理流程104.7类型4的处理流程144.8ETL详细说明144.9省公司上传和下载164.10总公司分发和装载164.11ETL规则175.cube设计18内部资料，注意保密 Page 19 of 211. 概述英文简写：YSKH中文名称：“个险渠道2009年度预算考核追踪报表”2. 体系结构 “个险渠道2009年度预算考核追踪报表”所需的数据来自amis4、amis5、收展系统。数据服务平台系统内部首先通过SSIS包把数据从增量层销售管理系统抽取到cubedb的事实表、维表中，然后再生成到多维数据集中，通过reporting services从多维数据集中做报表展示。报表用户登录sharepoint后，系统进行权限验证。系统的报表展示部分在总公司部署。省公司部署Amis系统的抽取程序及上传程序。职级映射关系、队伍数由省公司提供。省公司用户直接将excel导入到总公司，然后总公司的系统把这2部分数据分发到省公司的系统中。省公司系统中接收到数据后从amis系统抽取数据，抽取完成后把汇总结果上传到总公司。3. 数据模型“个险渠道2009年度预算考核追踪报表”基于的数据模型遵循零层数据模型的设计思路、命名原则。数据模型见附件2：个险渠道2009年度预算考核追踪_数据模型.rtf。保费部分在原来零层保费模型基础上，仅对总公司做结构升级。（1）增加计算标准保费函数：fun_get_std_prem()（2）F_CW_PREM增加以下字段签单日期KEY_SIGN_DATEdt_dim_key_longint保险年期KEY_INSURE_DUR_IDdt_dim_key_longint首年标准保费FACT_CW_FY_STD_PREMdt_fact_item_dec01续期标准保费FACT_CW_XQ_STD_PREMdt_fact_item_dec01（3）增加V_F_YXY_PLAN视图（4）增加R_MANAGE_CHANNEL（5）增加A_SNAP_TAB_STAT4. ETL设计遵循零层ETL设计思路，具体内容参见相关设计文档。模板：4.1 包名列表红色为已经有的SSIS包，需要进行升级。序号目标表描述目标表名SSIS包名处理类型任务类型1D_人员事件D_SALER_EVENTcdspj_flat_cube_incr_d_saler_event.dtsx类型160L已定义2D_营销员职级D_AGENT_RANKcdspj_flat_cube_incr_d_agent_rank.dtsx类型23D_基层营销机构D_AGENT_BRANCHamis4_incramis_cube_incr_d_agent_branch.dtsxamis5_incramis5_cube_incr_d_agent_branch.dtsxsz_incrsz_cube_incr_d_agent_branch.dtsx类型2AMIS4：84LAMIS5：89L收展：8BLCubedb：80L4D_营销员D_AGENT_SALERamis4_incramis_cube_incr_d_agent_saler.dtsxamis5_incramis5_cube_incr_d_agent_saler.dtsxsz_incrsz_cube_incr_d_agent_saler.dtsx类型25D_团队D_AGENT_TEAMamis4_incramis_cube_incr_d_agent_team.dtsxamis5_incramis5_cube_incr_d_agent_team.dtsxsz_incrsz_cube_incr_d_agent_team.dtsx6F_流动事实F_AGENT_TRANSamis4_incramis_cube_incr_f_agent_trans.dtsxamis5_incramis5_cube_incr_f_agent_trans.dtsxsz_incrsz_cube_incr_f_agent_trans.dtsx类型27F_持证事实F_AGENT_HOLDamis4_incramis_cube_incr_f_agent_hold.dtsxamis5_incramis5_cube_incr_f_agent_hold.dtsxsz_incrsz_cube_incr_f_agent_hold.dtsx类型28F_业绩事实F_AGENT_ACHIVEamis4_incramis_cube_incr_f_agent_achive.dtsxamis5_incramis5_cube_incr_f_agent_achive.dtsxsz_incrsz_cube_incr_f_agent_achive.dtsx类型39F_人力日周期事实表F_SNAP_D_AGENT_HRcdspj_cube_cube_incr_f_snap_d_agent_hr.dtsx类型310F_人力月周期事实表F_SNAP_M_AGENT_HRcdspj_cube_cube_incr_f_snap_m_agent_hr.dtsx类型311F_营销队伍事实F_AGENT_CONTcdspj_colectdb_cubedb_incr_f_agent_cont.dtsx类型290L12M_省级职级映射关系表M_CODE_AGENT_PROV_RANKcdspj_colectdb_cubedb_incr_agent_prov_rank.dtsx类型213cdspj_cube_asmddb_f_snap_agent.dtsx类型400L 14F_主销险种F_MAIN_POL_SALEcdspj_script_cube_incr_f_main_pol_sale.dtsx（升级）类型160L15D_关注点D_FOCUScdspj_flat_cube_incr_d_focus.dtsx（升级）cubedb_dim_data.xls类型116P_计划数P_PLANcdspj_flat_cube_incr_p_plan.dtsx（升级）类型1说明：根据ETL流程处理的特征，将本任务相关ETL分为4个类型：类型1：每次都是清空目标表，装载全部数据。类型2：第1次做存量，以后每次做增量，按主键先删后插。（D_AGENT_RANK按时间戳，D_AGENT_BRANCH和D_AGENT_SALER按ID_NUM）类型3：第1次做存量，以后每次做增量，增量按统计日期回退取增量数据。Amis4和amis5都抽取到营销员、营销机构公用对应amis5的（11）。事实表里的数据源标识4.2 调度策略4.2.1 总公司注：具体任务调度流程请参考前面的流程图（1）每日凌晨1点定时启动cdspj_flat_cube_incr_d_saler_event.dtsxcdspj_colectdb_cubedb_incr_f_agent_cont.dtsxcdspj_colectdb_cubedb_incr_agent_prov_rank.dtsxcdspj_flat_cube_incr_d_agent_rank.dtsxexport_down_agent _cubedb_0001（2）每日凌晨4点定时启动import_up_agent_cubedb_0001运行成功后触发cdspj_cube_asmddb_f_snap_agent.dtsx。4.2.2 省公司注：具体任务调度流程请参考前面的流程图（1）每日凌晨1点30分定时启动import_down_agent _cubedb_0001import_down_agent _cubedb_0001运行成功后自动触发维表抽取及事实表抽取：amis4_incramis_cube_incr_d_agent_branch.dtsxamis4_incramis_cube_incr_d_agent_team.dtsxamis4_incramis_cube_incr_d_agent_saler.dtsxamis4_incramis_cube_incr_f_agent_trans.dtsxamis4_incramis_cube_incr_f_agent_hold.dtsxamis4_incramis_cube_incr_f_agent_achive.dtsxamis5_incramis5_cube_incr_d_agent_branch.dtsxamis5_incramis5_cube_incr_d_agent_team.dtsxamis5_incramis5_cube_incr_d_agent_saler.dtsxamis5_incramis5_cube_incr_f_agent_trans.dtsxamis5_incramis5_cube_incr_f_agent_hold.dtsxamis5_incramis5_cube_incr_f_agent_achive.dtsxsz_incrsz_cube_incr_d_agent_branch.dtsxsz_incrsz_cube_incr_d_agent_team.dtsxsz_incrsz_cube_incr_d_agent_saler.dtsxsz_incrsz_cube_incr_f_agent_trans.dtsxsz_incrsz_cube_incr_f_agent_hold.dtsxsz_incrsz_cube_incr_f_agent_achive.dtsxcdspj_cube_cube_incr_f_snap_d_agent_hr.dtsxcdspj_cube_cube_incr_f_snap_m_agent_hr.dtsx抽取成功完成后触发上传任务：export_up_agent_cubedb_0001其中cdspj_cube_cube_incr_f_snap_m_agent_hr.dtsx在每月2日、季度月的15日运行。4.3 任务参数各类型的ETL与参数的使用关系如下表：变量名说明类型AMIS4：84LAMIS5：89L收展：8BLcollectDB：90L快照：80L BATCH_FLAG存增量标志stringYYYYCubeDbConnStr汇总库连接串stringYYYYYEND_STAT_DATE增量控制的统计结束日期stringYYYYIncrAmis5ConnStrAMIS5增量库连接串stringYIncrAmis4ConnStrAMIS4增量库连接串stringYIncrSZConnStr收展增量库连接串stringYSTART_STAT_DATE增量控制的统计开始日期stringYYYYCollectionDBConnStrCollectionDB连接串stringY4.4 类型1的处理流程基本流程：基本流程分为3个环节，包括初始化审核元数据、生成表中数据、更新审核元数据。流程说明：“初始化审核元数据”、“更新审核元数据”需要更新目标表的表名。 “生成表中数据”环节的实现参考如下： 清空XXXX：这2个表中的数据每次抽取时全部重新生成，因此使用“清空目标表”来首先清空。 Truncate table XXXX。 生成XXXX： l 进行基本的数据清理及转换，包括去掉字符类型字段的空格、空值处理、调用数据库的函数进行代码与描述的转换。 、l 在select语句中使用别名来对应最终目标表字段。l 通过派生列增加审核键、时间戳、数据源l 如果数据在excel文件内，则从excel数据源读取数据时，必须通过“数据转换”将unicode类型转为非unicode，才能插入目标表，除非目标表的字段类型是nvarchar。要求在列名后用“_U”标识unicode列，当通过“数据转换”后再恢复为原列名。 插入未知成员：HDYW_D_BRANCH为维表，在本步骤中插入未知的成员，其他表的抽取不需要该步骤。SET IDENTITY_INSERT D_XXX ONGOif not exists (select 1 from D_XXX where KEY_XXX_ID=-1 ) insert into D_XXX(KEY_XXX_ID)values (-1)GOSET IDENTITY_INSERT D_XXX OFFGO说明：维表主键不为identity时，去掉2个set语句。4.5 类型2的处理流程基本流程：基本流程分为5个环节，包括初始化审核元数据、预处理、生成表中数据、后续处理、更新审核元数据。流程说明：基本过程同4.4，增加了“预处理”、“后续处理”2个环节，区别在于以下几点： 预处理首先从接口表中取出表的最大时间戳作为增量数据的上限END_STAMP，然后从C_INCR_CTRL读取上次成功的最大时间戳做为本次增量数据的下限START_STAMP。如果是存量，则START_STAMP=1900-01-01。 “生成表中数据”的“清空XXXX”如果 BATCH_FLAG=Y 则清空XXXX，否则不处理。 “生成表中数据”的“生成XXXX”根据增量数据的上下限时间戳读取数据。转换后写入目标时，判断每条数据的业务主键是否存在，不存在则插入，存在则更新，必须要保证原代理键不变。 “生成表中数据”的“插入未知成员”保留。 后续处理：更新源端的C_INCR_CTRLupdate C_INCR_CTRLset SUC_FLAG=Y, EXT_ROW_NUM=?, STAMP=getdate()where KEY_AUDIT_ID=?说明： （1）按时间戳处理增量，使用START_STAMP和END_STAMP保存时间戳范围（2）按ID_NUM处理增量，使用START_ID和END_ID保存时间戳范围。4.6 类型3的处理流程基本流程：基本流程分为5个环节，包括初始化审核元数据、预处理、生成表中数据、后续处理、更新审核元数据。流程说明：基本过程同4.4，增加了“预处理”、“后续处理”2个环节，区别在于以下几点： 预处理按一定规则计算STRAT_STAT_DATE和END_STAT_DATE。2个日期参数都是1900-01-01，计算从1900-01-01到昨天的数据。以后做增量，2个日期参数都是1900-01-01，计算从昨天回退1个月的数据。 BATCH_FLAGSTRAT_STAT_DATEEND_STAT_DATE日回退日快照月快照（快照日期以快照月的1日表示）Y1900-1-11900-1-1删除目标表本系统的数据，重算1900-01-01至昨天快照日期取1900-01-011到昨天的每一天快照日期取自1900年01月至当前日期的上个月的每个月Y1900-1-12008-8-31删除目标表本系统的数据，重算1900-01-01至2008-08-31快照日期取1900-01-011到2008-8-31的每一天快照日期取自1900年01月至2008年8月的每个月Y2008-1-12008-8-31删除目标表本系统的数据，重算2008-01-01到2008-08-31快照日期取2008-01-01到2008-08-31的每一天快照日期取2008年01月到2008年8月的每个月N1900-1-11900-1-1按日期范围关联删除目标表，重算回退1个月至昨天快照日期取昨天快照日期取当前日期的上个月N1900-1-12008-8-31按日期范围关联删除目标表，重算2008-07-31至2008-08-31快照日期取2008-8-31快照日期取2008年8月N2008-1-12008-8-31按日期范围关联删除目标表，重算2008-01-01到2008-08-31快照日期取2008-01-01到2008-08-31的每一天快照日期取2008年01月到2008年8月的每个月 日回退declare batch_flag char(1)declare start_stat_date char(19)declare end_stat_date char(19)-内部的日期类型declare d_start_date datetimedeclare d_end_date datetimeset batch_flag=?set start_stat_date=?set end_stat_date=?-如果不带时间，则补充时间if len(start_stat_date)10 set d_start_date=convert(datetime,start_stat_date) else set d_start_date=convert(datetime,start_stat_date+ 00:00:00)if len(end_stat_date)10 set d_end_date=convert(datetime,end_stat_date) else set d_end_date=convert(datetime,end_stat_date+ 23:59:59)-如果结束日期小于开始日期，则开始日期=结束日期if d_end_date10 set d_start_date=convert(datetime,start_stat_date) else set d_start_date=convert(datetime,start_stat_date+ 00:00:00)if len(end_stat_date)10 set d_end_date=convert(datetime,end_stat_date) else set d_end_date=convert(datetime,end_stat_date+ 23:59:59)-如果结束日期小于开始日期，则开始日期=结束日期if d_end_date10 set d_start_date=convert(datetime,start_stat_date) else set d_start_date=convert(datetime,start_stat_date+ 00:00:00)if len(end_stat_date)10 set d_end_date=convert(datetime,end_stat_date) else set d_end_date=convert(datetime,end_stat_date+ 23:59:59)-如果结束日期小于开始日期，则开始日期=结束日期if d_end_dated_start_date set d_start_date=d_end_date-计算结束日期if (datediff(dd,d_end_date,1900-01-01)=0 or d_end_date is null) set d_end_date=convert(char(7),dateadd(mm,-1,getdate(),120)+-01 else set d_end_date=convert(char(7),d_end_date,120)+-01if batch_flag=N and (datediff(dd,d_start_date,1900-01-01)=0 or d_start_date is null) set d_start_date=convert(char(10),dateadd(dd,0,d_end_date),120) else set d_start_date=convert(char(7),d_start_date,120)+-01select convert(char(19),d_start_date,120),convert(char(19),d_end_date,120) “生成表中数据”的“清空XXXX”如果 BATCH_FLAG=Y ，则清空XXXX，否则按STRAT_STAT_DATE和END_STAT_DATE删除数据。 “生成表中数据”的“生成XXXX”根据STRAT_STAT_DATE和END_STAT_DATE读取数据。转换后直接写入目标表。 “生成表中数据”的“插入未知成员”去掉。 后续处理：更新源端的C_INCR_CTRLupdate C_INCR_CTRLset SUC_FLAG=Y, EXT_ROW_NUM=?, STAMP=getdate()where KEY_AUDIT_ID=?说明：使用说明： 按统计日期处理增量，使用START_STAT_DATE和END_STAT_DATE保存统计日期范围。4.7 类型4的处理流程全量更新OLAP。4.8 ETL详细说明4.8.1 cdspj_flat_cube_incr_d_saler_event.dtsx4.8.2 cdspj_flat_cube_incr_d_agent_rank.dtsx（1） 初始包的审核数据。（2） 初始化D_ AGENT_RANK的审核数据，获取D_agent_RANK的表处理键、审核键。（3） 清空D_ AGENT_RANK，然后从cubedb_dim_data_YXY.xls导入。（4） 更新D_ AGENT_RANK的审核数据。（5） 初始化D_ AGENT_PROV_RANK的审核数据，获取D_ AGENT_PROV_RANK的表处理键、审核键。（6） 如果是存量，清空D_ AGENT_PROV_RANK（7） 根据M_CODE_ AGENT_PROV_RANK、D_AGENT_RANK参考ETL规则生成D_ AGENT_PROV_RANK。写入D_ AGENT_PROV_RANK时按如下规则：判断数据流的业务键是否存在，存在则更新各维度属性，否则插入。D_ AGENT_PROV_RANK的业务键PROV_BRANCH_CODE,SYS_TYPE_NAME,YXY_RANK_PROV_CODE，即M_CODE_ AGENT _PROV_RANK的主键。（8） 更新D_AGENT_PROV_RANK的审核数据。（9） 更新包的审核数据说明：M_CODE_ AGENT_PROV_RANK由分公司用户上载，各分公司要运行事实表前要先下载到D_AGENT_PROV_RANK的维度数据。4.8.3 cdspj_cube_cube_incr_f_snap_d_agent_hr.dtsx（1）实有人力select trans.KEY_AGENT_SALER_ID,trans.DATA_SRC_ID,1 FACT_ON_HR,0 FACT_HOLD_HRfrom F_AGENT_TRANS trans join D_AGENT_SALER b on trans.KEY_AGENT_SALER_ID=b.KEY_AGENT_SALER_IDjoin D_SALER_EVENT g on trans.KEY_SALER_EVENT_ID=g.KEY_SALER_EVENT_IDwhere trans.KEY_EVENT_DATE = ? or trans.KEY_STAT_END_DATE=0)（2）持证人力select hold.KEY_AGENT_SALER_ID,hold.DATA_SRC_ID,0 FACT_ON_HR,1 FACT_HOLD_HRfrom F_AGENT_HOLD hold join D_SALER_EVENT gg on hold.KEY_SALER_EVENT_ID=gg.KEY_SALER_EVENT_IDwhere hold.KEY_EVENT_DATE = ? or hold.KEY_STAT_END_DATE=0)（3）更新持证人力FACT_ON_HR 0 & FACT_HOLD_HR 0 ? FACT_HOLD_HR : 04.8.4 cdspj_cube_cube_incr_f_snap_m_agent_hr.dtsx（1）月末实有人力select KEY_AGENT_SALER_ID,DATA_SRC_ID,sum(FACT_ON_HR) FACT_M_ON_HRfrom dbo.F_SNAP_D_AGENT_HRwhere KEY_SNAP_DATE=?group by KEY_AGENT_SALER_ID,DATA_SRC_ID快照日期=月末（2）季初持证人力select KEY_AGENT_SALER_ID,DATA_SRC_ID,sum(FACT_HOLD_HR) FACT_Q_HOLD_HRfrom dbo.F_SNAP_D_AGENT_HRwhere KEY_SNAP_DATE=?group by KEY_AGENT_SALER_ID,DATA_SRC_ID快照日期=季初（3）月末持证人力select KEY_AGENT_SALER_ID,DATA_SRC_ID,sum(FACT_HOLD_HR) FACT_M_HOLD_HRfrom dbo.F_SNAP_D_AGENT_HRwhere KEY_SNAP_DATE=?group by KEY_AGENT_SALER_ID,DATA_SRC_ID快照日期=月末（4）季度举绩人力select achive.KEY_AGENT_SALER_ID,achive.DATA_SRC_ID,sum(achive.FACT_NEW_NUM) FACT_NEW_NUM,sum(achive.FACT_CANCEL_NUM) FACT_CANCEL_NUMfrom F_AGENT_ACHIVE achive where achive.KEY_STAT_DATE =? and achive.KEY_STAT_DATE sum(achive.FACT_CANCEL_NUM)（5）月度举绩人力select achive.KEY_AGENT_SALER_ID,achive.DATA_SRC_ID,sum(achive.FACT_NEW_NUM) FACT_NEW_NUM,sum(achive.FACT_CANCEL_NUM) FACT_CANCEL_NUMfrom F_AGENT_ACHIVE achive where achive.KEY_STAT_DATE =? and achive.KEY_STAT_DATE sum(achive.FACT_CANCEL_NUM)4.8.5 cdspj_script_cube_incr_f_main_pol_sale.dtsx（升级）根据分表9的内容修改f_main_pol_sale的抽取规则4.8.6 cdspj_flat_cube_incr_d_focus.dtsx（升级）cubedb_dim_data.xls增加分表9关注的内容。4.8.7 cdspj_flat_cube_incr_p_plan.dtsx增加2009年个险渠道预算计划（格式）.xls的导入功能。4.8.8 cdspj_cube_asmddb_f_cw_prem.dtsx改变原来的分区策略增加“处理机构维度”增加“统计F_CW_PREM的数据A_SNAP_TAB_STAT” ，逻辑如下declare int_yesterday intdeclare str_stamp char(19)set int_yesterday=datepart(yyyy,getdate()-1)*10000+datepart(mm,getdate()-1)*100+datepart(dd,getdate()-1)set str_stamp=convert(char(19),getdate(),120)insert into A_SNAP_TAB_STAT(TAB_NAME,KEY_SNAP_DATE,DATA_SRC_ID,PROV_BRANCH_CODE,KEY_DATE_ID,FACT_ROW_NUM,KEY_AUDIT_ID,STAMP)select F_CW_PREM TAB_NAME,int_yesterday KEY_SNAP_DATE,DATA_SRC_ID,convert(char(4),KEY_BRANCH_ID/)+00 PROV_BRANCH_CODE,max(KEY_CW_DATE) KEY_DATE_ID,-每个省每个系统的最大截止日期count(*) FACT_ROW_NUM,? KEY_AUDIT_ID,str_stamp STAMPfrom V_F_CW_PREM with (nolock)where KEY_CW_DATE0 and KEY_CW_DATE0group by DATA_SRC_ID,convert(char(4),KEY_BRANCH_ID/)+00报表展现使用以下SQL：select isnull(left(convert(char(8),min(KEY_DATE_ID),4)+-+substring(convert(char(8),min(KEY_DATE_ID),5,2)+-+right(convert(char(8),min(KEY_DATE_ID),2),convert(char(19),getdate(),120)from A_SNAP_TAB_STAT where TAB_NAME=F_CW_PREMand STAMP=(select max(stamp) from A_SNAP_TAB_STAT where TAB_NAME=F_CW_PREM)4.9 省公