2023.11.29 -hmzx电商平台建设项目 -核销主题阶段总结
目录
1.准备源数据
2.准备数仓工具进行源数据同步到ods层,本项目使用Datax
3.使用Datax完成数据同步前建表时的方案选择
3.1同步方式区别:
3.2存储格式和压缩区别:
4.在hive中创建表,共31个表
5.数仓概念 和 数仓建模方案
5.1数仓的基本概念
5.2 数仓建模方案
关系建模:
维度建模:
两张表关系:
数仓发展的三种模型:
5.3数仓建设方案:
6.使用海豚调度完成ODS层到DWD层数据导入
6.1 先在DWD层建表
6.2 DWD层导入数据准备
6.3使用海豚调度平台创建导入数据的工作流,部署上线
编辑
7.核销主题数仓建模
7.1核销主题需求
7.1.1 商品销售情况(核销)分析
7.1.2经营情况(已核销)分析
7.1.3 门店营销(核销)情况分析
销售渠道需求：
日清活动需求：
涉及表：
1.准备源数据
本项目的源数据层,由本地的Mysql和Sqlserver中而来,共31张表
销售表,会员表,订单表,库存表都称为指标/事实表,记录实际的业务数据
2.准备数仓工具进行源数据同步到ods层,本项目使用Datax
datax的介绍:离线数据同步工具
3.使用Datax完成数据同步前建表时的方案选择
3.1同步方式区别:
a.全量覆盖:建表时不需要构建分区表,每次一都是将之前的数据全部删除,重新导入 ,弊端是会没有历史数据
b.全量同步:建表时要构建分区表,分区字段与更新周期保持一致,每一次导入都是将表中所有数据导入到新的分区,弊端是如果新增的量很少,每次都是完全同步,会极大占用存储空间,以及有非常多的重复数据.
c.仅新增:建表时需要构建分区表,分区字段与更新周期保持一致,每一次导入都是将新增的数据导入到新的分区中
d.新增并更新:建表时需要构建分区表,分区字段与更新周期保持一致,每一次导入都是将新增的数据和更新的数据导入到一个新的分区中
e.缓慢渐变维:为了维护变化的数据, 有以下方法 1.直接覆盖 2.将新增和更新的数据放置到一个新的分区. 3.拉链表新增两个字段,一个开始一个结束,当数据发送变化的时候,将之前的数据结束时间调整为上一天的日期,新增一条新变更后的数据,开始时间为上一条结束时间
3.2存储格式和压缩区别:
存储格式一般选择orc , 压缩方案:读多写少为snappy / 写多读少为zlib或者gz
4.在hive中创建表,共31个表
构建的31张表分为8个维度表,23个业务数据表(事实表,指标表)
全量覆盖:适用于一般数据量较少,并且长时间不会改变,也不需要维护历史变化. 用于维度表,销售额目标等
全量同步:适用于保留历史快照, 用于每天门店商品库存天表.
仅新增:适用于数据量比较大,不需要维护历史变化的, 用于各种信息表,调货表
新增且更新:适用于数据量大,又需要维护历史变化,比如会员的信息表,商城订单明细表
5.数仓概念 和 数仓建模方案
5.1数仓的基本概念
5.2 数仓建模方案
关系建模:
维度建模:
两张表关系:
1.事实表:根据分析的主题,主体所对应的表一般为事实表;事实表一般是由一堆其他表主键聚集的,事实表一般是由用户某种行为而产生的
2.维度表:在对事务进行分词处理的时候,在统计某个维度的时候,需要关联其他的表,这些表就是维度表.
数仓发展的三种模型:
5.3数仓建设方案:
6.使用海豚调度完成ODS层到DWD层数据导入
6.1 先在DWD层建表
DWD层: 明细层
-
作用: 根据要分析的主题, 从ODS层抽取相关的数据, 对数据进行清洗转换处理工作, 然后将数据加载到DWD层, 一般将此层称为 大聚合层, 一般将所有相关的数据全部糅杂在一个表中, 在此过程中, 可以进行一定的维度退化操作
-
什么叫转换处理呢?
比如说: 对于时间而言, 在ODS表中有一个时间字段, 字段数据为: 2020-12-10 15:30:30
说明:
在ODS层这个时间字段上, 糅杂了太多字段数据, 包含 年 月 日 小时 分钟 秒
此时, 需要将字段导入到DWD层时候, 将其转换为 年 月 日 小时 ...
维度层数据都是系统基础数据, 数据质量比较高, 顾一般不需要进行清洗处理操作
ods_dim_category_f进行了分类拉平操作
ods_dim_goods_info_f将分类ID替换为对应一二三级分类ID,编码和名称
ods_dim_store_f 门店表进行了降维操作 变成 dwd_dim_store_i
门店表: 在门店宽表构建时，就添加了门店面积信息和区域名称信息，门店面积信息可以用来计算坪效等，区域名称信息可以用来上卷时显示区域名称。
其中门店面积信息可以从分店面积明细表中获取。先取实际经营面积，如果取不到（实际经营面积为空或0）再取经营面积。
其中区域名称信息从店组信息表中取，store_group_type_no = ‘04’即对应区域的编码和名称。
其中 store_type_code和management_type_code 需要转换为整数类型
新增了dwd_dim_store_clear_goods_i门店商品日清表,在门店商品表dwd_dim_store_goods_i的基础上筛选出日清数据,结构与门店商品表一致
6.2 DWD层导入数据准备
DATE_SUB()函数从DATE或DATETIME值中减去时间值(或间隔)。
1.dim.dwd_dim_date_f时间维度表:不需要任何处理,直接将ODS层数据导入到DWD层即可
2.dim.dwd_dim_category_statistics_i 商品分类表进行拉宽处理
3.dim.dwd_dim_goods_i 商品表 ,将ods商品表和dwd分类表根据分类id进行关联
4.dim_dwd_dim_store_goods_i 门店商品表 , 将ods门店商品表和dwd分类表 根据分类id进行关联
5.dim_dwd_dim_store_clear_goods_i 日清门店商品表, 在门店商品表处理完后,通过条件where is_clear = 1 即可
6.dim_dwd_dim_source_type_map_i 交易类型映射表,直接从ods导入dwd即可
7.dwd_dim_store_i 门店表
6.3使用海豚调度平台创建导入数据的工作流,部署上线
DS的架构
7.核销主题数仓建模
核销主题:分为售卖主题-下单时间 , 核销主题-订单完成时间/库存处理时间
目前dwd层共有30张表,核销主题共涉及到21张表
维度:dim_date_f , dim_source_type_map_f ,dim_store_f,dim_goods_f,dim_store_f
销售:store_sale_dtl,store_sale_info,store_sale_pay,shop_order,shop_order_item,shop_sale_pay,shop_refund,shop_refund_item
会员:member_union
订单:store_receive , store_return_to_vendor,store_return_to_dc,store_alloc_in,store_alloc_out,store_require
库存:store_stock_adj
7.1核销主题需求
7.1.1 商品销售情况(核销)分析
需求:了解不同城市,各个门店以及各个品类商品每天的销售情况
指标:销售单量、销售数量、销售金额、折扣金额、销售成本、余额支付金额、取消商品销售金额、退款商品销售金额、线上单量、线下单量、线上销售金额、线下销售金额、线上销售成本、线下销售成本、损耗金额、收货金额（收货-退货-退配+调入-调出）、要货金额
维度:时间,区域,类别
粒度:时间维度(天,下钻至刻),区域维度(城市,门店),类别维度(商品,第一品类,第二品类,第三品类)
涉及库:sale,member,order,stock
事实表:
sale: store_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_itemmember: member_unionstock: store_stock_adjorder: store_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require维度表:
dim: dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f7.1.2经营情况(已核销)分析
需求:了解不同城市,各个门店每天的销售情况(已核销)
指标:销售单量、销售数量、销售金额、折扣金额、销售成本、余额支付金额、取消商品销售金额、退款商品销售金额、线上单量、线下单量、线上销售金额、线下销售金额、线上销售成本、线下销售成本、损耗金额、收货金额（收货-退货-退配+调入-调出）、要货金额、线上会员单量、实体卡会员单量、线上会员销售金额、实体卡会员销售金额、线上会员销售成本、实体卡会员销售成本、线上会员下单人数、实体卡会员下单人数、使用余额销售金额、使用余额单量、使用余额的销售成本、使用余额的下单人数
维度:时间,区域
粒度:时间维度(天,下钻至刻),区域维度(城市,门店)
涉及库:sale,member,order,stock
涉及表:
-
1) 事实表：
a) sale：store_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_item
b) member：member_union
c) stock：store_stock_adj
d) order：store_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require
-
2) 维度表：
dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f
7.1.3 门店营销(核销)情况分析
说明：了解各个门店营销情况，包括不同销售渠道（已核销）的销售情况以及日清活动的效果。【日清活动指的是，为了保证果蔬的新鲜度，一些商品当日出清，为了能出清商品，会采用打折的方式出售，并且随着时间的推移，越晚折扣越低。】
销售渠道需求：
指标：订单量、退款订单量、取消订单量、商品销售金额、商品销售成本、商品折扣金额
维度：订单渠道
日清活动需求：
指标：销售SKU数、销售单量、销售数量、销售金额、折扣金额、销售成本、销售利润、线上单量、线下单量、线上销售金额、线下销售金额、损耗金额、收货金额（收货-退货-退配+调入-调出）、要货金额、
维度：区域、品类
粒度：区域维度（门店）、品类维度（第一品类）
涉及库：sale、member、order、stock
涉及表：
-
1) 事实表：
a) sale：store_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_item
b) member：member_union
c) stock：store_stock_adj
d) order：store_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require
-
2) 维度表：dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f