1.概述
1.1  应用场景

在数仓中的销售单据表，有些销售记录由于实际业务的变动需要由人工手动修改。因为单据记录比较多，设想是可以通过单据编码、日期和门店筛选查看相关的单据记录，并在相关行上做更新。需要如何设计填报表和相应的ETL流程？

简单分析上述需求，可以拆分为两个点：

①单据的填报字段中需要录入的数据，受到其他表数据的联动，表单之间存在明显的填报先后顺序，可采用多表填报，也就是填报表中存在多个简表。多表填报中，较为常用的设计方式为表头表体填报。

②以表头表体填报为例。利用单据头表（含单据编码、日期、门店名）中的数据内容，联动带出单据表中符合的记录→在这些记录上做新纪录的增加→将重复的旧数据通过设计的etl流程删除掉，确保数据是最新的。

2.操作流程

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

2.1  填报表设计

（1）新增填报表单

在后台创建好联动单据头表d_sales、销售单据中间表f_sales_temp和销售单据最终表f_sales（本例，两个单据表结构一致），在BI前端创建填报表，并设计填报表单。

单据头表-表结构创建语句参考：

CREATE TABLE IF NOT EXISTS `d_sales` (  

    `sno` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单据ID',  

    `sdate` datetime NULL DEFAULT NULL COMMENT '销售日期',  

    `sdepartment` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '门店名'  

);

 销售单据表-表结构创建语句参考：

CREATE TABLE IF NOT EXISTS `f_sales_temp` (

    `sno` VARCHAR(250) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL COMMENT '单据编号',

    `sdate` DATETIME  NULL COMMENT '日期',

    `sarea` VARCHAR(250) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL COMMENT '区域',

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

    `sgoodsclass` VARCHAR(250) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL COMMENT '品类',

    `sgoods` VARCHAR(250) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL COMMENT '商品',

    `sincome` DOUBLE(22,0)  NULL COMMENT '收入',

    `snum` INT  NULL COMMENT '数量',

    `sprice` DOUBLE(22,0)  NULL COMMENT '单价',

    `scost` DOUBLE(22,0)  NULL COMMENT '成本',

    `sprofit` DOUBLE(22,0)  NULL COMMENT '利润'

 COMMENT = 'f_sales_temp');

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

①单据头表格开启表头

②单据体表格，数值型字段开启校验和自定义公式

③单据体设置联动筛选来源和绑定数据列，读取单据现有数据

④假定只能修改数值型字段，则文本型字段开启不可编辑，数值型字段有变动时则有重复记录。

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

2.2 明确流程和ETL设计

  ①获取最新填报数据

     需要从填报中间表获取最新的填报数据，与填报目标表内的数据进行匹配，如匹配成功，则将最新记录直接覆盖更新至对应行。

update  f_sales t1

join f_sales_temp a on t1.sno=a.sno and   t1.sdate=a.sdate and  t1.sarea=a.sarea and t1.sdepartment=a.sdepartment and t1.sgoodsclass=a.sgoodsclass and t1.sgoods=a.sgoods

join (

select powerbireview_user from f_sales_temp

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

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

)t2 on a.powerbireview_user=t2.powerbireview_user

set  t1.sincome=a.sincome, t1.snum=a.snum, t1.sprice=a.sprice, t1.scost=a.scost,  t1.sprofit=a.sprofit; -- 本例中，数值型字段才需要更新

②将最新的单据最终表数据，拷贝到中间表。

TRUNCATE table f_sales_temp;

INSERT into f_sales_temp(sno,sdate,sarea,sdepartment,sgoodsclass,sgoods,sincome,snum,sprice,scost,sprofit)

select sno,sdate,sarea,sdepartment,sgoodsclass,sgoods,sincome,snum,sprice,scost,sprofit from f_sales;

③从销售最终表中，获取单据头数据作为联动筛选来源（本例中，一张单据有多个商品的销售记录，取唯一值）

    TRUNCATE table d_sales;

    INSERT INTO d_sales(sno,sdate,sdepartment) select distinct(sno),sdate,sdepartment from f_sales;

2.3  创建ETL作业流程

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

2.4  设置填报执行ETL

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

2.5  ETL数据逻辑验证

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