1.概述
1.1  应用场景

填报表由多用户进行数据填报时，受填报操作、录入数据内容等影响，获取到的数据不一定都是能直接用于数据分析，可能会存在数据重复、不全等情况，需要经过一定的数据清洗和加工。

本例以销售目标填报为例，以组织名称、年份和月份为判断是否重复录入的依据，且单据为提交或审核状态，将最新填报的记录更新至目标表。

2.操作流程

ETL设计--获取填报最新数据操作流程图

2.1  填报表设计

在后台创建好中间表f_salebudget_tb和最终表f_salebudget（本例，两表结构一致），在BI前端创建填报表，并设计填报表单。

表结构创建语句参考：

CREATE TABLE  IF NOT EXISTS   `f_salebudget_tb`  (

 `fbr_number` varchar(80)  NULL DEFAULT NULL COMMENT '组织编码',

 `fbr_name` varchar(80)  NULL DEFAULT NULL COMMENT '组织',

`fyear` varchar(20)  NULL DEFAULT NULL COMMENT '年份',

`fmonth` varchar(20)  NULL DEFAULT NULL COMMENT '月份',

  `famount` decimal(20, 8) NULL DEFAULT NULL COMMENT '金额',

  `fqty` decimal(20, 8) NULL DEFAULT NULL COMMENT '数量',

`fprofit` decimal(20, 8) NULL DEFAULT NULL COMMENT '毛利'

) COMMENT'销售目标填报表';

填报表前端设计字段的数据匹配和校验，从源头上杜绝异常数据，从而减少设计etl语句时所需要考虑的各种情况。

①名称开启下拉框，编码和名称绑定组织维度表的编码和名称，编码开启不可编辑，确保编码准确；

②年份设置正整数校验；

③月份开启自定义枚举，选择范围设定为1-12。

具体操作参考普通填报 (powerbi.com.cn) 、填报设计技巧 (powerbi.com.cn)

2.2 明确流程和ETL设计

  ①获取最新填报数据，删除与最新填报数据匹配的内容

     第一步，需要从填报中间表获取最新的填报数据，与填报目标表内的数据进行匹配，如匹配成功，则将目标表的旧数据删除掉，为后续插入最新数据做准备。

delete t1 from f_salebudget t1 join

(

select  fyear,fmonth,c.fitemid as fbr

 from f_salebudget_tb a

 left join  (select  fnumber,max(fitemid) fitemid from   d_company  group by  fnumber)  c on a.fbr_number=c.fnumber

 join (

select powerbireview_user from f_salebudget_tb

where powerbireviewstate in (1,2) -- 提交状态审核

order by powerbireview_date desc LIMIT 1 -- 取最新日期的批次

)d on a.powerbireview_user=d.powerbireview_user -- 填报单据批次相同

 group by fyear,fmonth,fbr_number,c.fitemid

 )t2 on year(t1.fdate)=t2.fyear and month(t1.fdate)=t2.fmonth  and  t1.fbr=t2.fbr ;

-- 目标表日期字段的年份、月份，以及组织编码相同

②在中间表设计数据查询的处理流程，并将查询的字段内容写入到目标表。

insert into f_salebudget(fbr, fdate, famount, fqty, fprofit,powerbireview_date,powerbireview_user)

select c.fitemid as fbr,

CONCAT(cast(a.fyear as CHAR),'-',case when a.fmonth<10 then CONCAT('0',cast(a.fmonth as CHAR)) else cast(a.fmonth as CHAR) end,'-01') as fdate, -- 年份、月份和‘-01’拼接，作为日期

a.famount, a.fqty,a.fprofit,a.powerbireview_date,a.powerbireview_user

from f_salebudget_tb a

  left join  (select  fnumber,max(fitemid) fitemid from   d_company  group by  fnumber)  c on a.fbr_number=c.fnumber

join (

select powerbireview_user from f_salebudget_tb

where powerbireviewstate in (1,2) -- 提交状态审核

order by powerbireview_date desc LIMIT 1 -- 取最新日期的批次

)t2 on a.powerbireview_user=t2.powerbireview_user;

③删除事实表内填报数据被清空后的残留页数据

删除事实表存在填报数据被清空的数据，用填报批次去识别，如填报已经没有的填报批次ID，但是事实表中存在，就判断为被删除的页，对应事实表也要删除该填报批次的记录    

delete a from f_salebudget as a

where not EXISTS (select distinct powerbireview_user from  f_salebudget_tb b where a.powerbireview_user=b.powerbireview_user);

2.3  创建ETL作业流程

在所有表中，找到存储过程，并添加至视图设计窗口。

参考以上sql代码，结合数据实际清洗需求调整好后，创建一个作业流程，将代码封装至sql任务内。

2.4  设置填报执行ETL

返回填报表设计页，选择需要执行的ETL作业流程及执行方式

存储过程定义，根据数据库类型也会有所不同，可参考窗口内的语句定义。

2.5  ETL数据逻辑验证

结合ETL设计逻辑，填报数据并执行ETL做数据验证，检验没问题后即可正式投入使用