1.概述
1.1  应用场景

一张单据，每次填报时有需要固定填写的内容，例如填报各个门店每月的销售目标，目前共有10个门店，则每次填报时，都需要固定填写这10个门店的销售目标，年月也需要重复录入，能否每次填写时只用填写门店每月的销售目标？同时需要考虑，如果出现年、月和门店重复的记录，如何获取填报的最新数据？需要如何设计填报表和相应的处理流程？

简单分析上述需求，可以划分为三个点：

①每次填报时有需要固定录入的数据，可采用固定行填报的方式，填报时固定数据会自动展现出来，无需再手工录入这些固定数据。

②以门店每月销售目标填报为例，门店是固定填写的内容，从门店表中获取需要固定填写的字段内容，采用数据集填充并开启固定内容填报，则可以达成需求。

③重复记录的处理，可参考ETL设计场景：获取填报最新数据 (powerbi.com.cn)

2.操作流程

ETL设计--按年月填报销售目并获取最新数据操作流程图

2.1  填报表设计

（1）新增填报表单

在后台创建好门店填报表d_department_tb、销售目标中间表t_sale_target和销售目标最终表f_sale_target（本例，两个目标表结构一致），在BI前端创建填报表，并设计填报表单。

门店填报表结构创建语句参考：

CREATE TABLE IF NOT EXISTS `d_department_tb` (  

    `tb_date` DATE NULL COMMENT '目标年月',  

    `departmentid` VARCHAR(250) NOT NULL COMMENT '门店id',  

    `departmentname` VARCHAR(250) NULL COMMENT '门店名',  

    CONSTRAINT d_department_tb_PK PRIMARY KEY (`departmentid`)  

) COMMENT = '门店销售目标填报中间表';

 销售目标表-表结构创建语句参考：

CREATE TABLE IF NOT EXISTS `f_sale_target` (

`fdate` DATETIME  NULL COMMENT '销售日期',

`fdeptid` INT  NULL COMMENT '门店id',

`fdeptname` VARCHAR(250) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL COMMENT '门店名称',

`famount` DECIMAL(16,2)  NULL COMMENT '目标收入')

 COMMENT = '门店销售目标填报最终表';

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

①日期、门店id、门店名由用于填报的门店维度表中获取，自动带出，不可编辑。

②无法加行和减行。

③只需填报销售目标。

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

2.2 明确流程和ETL设计

①获取当前日期的年月、门店表编码和名称，作为固定填报内容

truncate table d_department_tb;

insert into d_department_tb(tb_date, departmentid, departmentname)

select DATE_FORMAT(NOW(), '%Y-%m-01'), departmentid, departmentname from d_department ;

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

delete t1 from f_sale_target t1 join

(

select  a.fdate,a.fdeptid

 from t_sale_target a

 join (

select powerbireview_user from t_sale_target

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

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

)d on a.powerbireview_user=d.powerbireview_user

 group by a.fdate,a.fdeptid

 )t2 on t1.fdate=t2.fdate  and  t1.fdeptid=t2.fdeptid ;

③插入最新填报数据  

insert into f_sale_target(fdate, fdeptid, fdeptname, famount,powerbireview_date,powerbireview_user)

select a.fdate,a.fdeptid, a.fdeptname, a.famount, a.powerbireview_date,a.powerbireview_user

from t_sale_target a

 join (

select powerbireview_user from t_sale_target

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_sale_target as a

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

2.3  创建ETL作业流程

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

2.4  设置填报执行ETL

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

2.5  ETL数据逻辑验证

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