数仓构建流程-jk数仓

数仓构建流程-jk数仓

一、背景

鉴于公司业务日益完善，公司启动极数BI项目，以支持业务同事与公司高层实现精准把控业务情况、优化业务问题等工作的目标。截止2022年第四季度，极数BI已经完成了全域覆盖，包括交付域、销售域、车辆域、用户域、财务域、供应链域等18个主题域；同时业务报表也接近30张，包括经营管理报表、销售报表、交付报表、财务报表、产销日报、税务报表、快乐经营体等等。为业务同事的汇报与分析工作提升极大的效率（40%~50%），也为后续建模工作奠定了坚实基础；

二、业务调研与需求分析

2.1、业务调研

本次数仓调研工作，主要负责交付域、行为域、生态域这三个主题域，现仅以交付域为例展开；

2.1.1、交付业务-流程梳理

零售单转单 -> 订单分配交付顾问 -> 用户申请配车 -> 订单匹配车辆 -> 车辆发运 -> 车辆入库 -> 车辆整备（PDI检测、车辆必备物品准备等） -> 用户开票 -> 客户确认提车 -> 订单完成（车辆交付）

说明：
1、订单匹配车辆完成前，用户均可进行改配和退订操作；
2、用户开票前，如果用户选择按揭付款，还会包含金融贷款流程；
3、如果用户选择送货上门，还会包含远程交付环节（车辆发运环节）；
4、仅未完成订单，但会有订单关闭；已完成订单不会再变更订单状态；
5、PDI检测，支持多次检测；

2.1.2、交付业务-数据调研

1、交付订单表

说明：一车一单
1、包含信息：用户信息（用户ID、手机号、性别、职业等）、销售属性（销售顾问、下单时间、下单渠道、销售车型）、配置信息（是否远程交付、是否按揭、是否参与国补等）、物流信息（发运订单、发运时间等）、车辆信息（VIN码（识别码）等）、交付属性（交付顾问、交付门店）、价格信息（原价、优惠、支付金额等）、支付属性（支付流水号、方式、金额等）、金融属性（分期订单、首付款等）、其他信息（预约交付时间）；
2、用于计算交付订单量、用户数、订单金额、当日预约交付量、7日内预约交付数（未来）等指标；

2、订单操作表

说明：记录用户状态变更操作、以及时间；
1、包含信息：订单号、状态码、操作时间（即订单变更为该状态的时间）等；
2、存在状态回退的情况，为正常业务操作；如修改车辆配置，修改开票信息；
3、后续可用于计算各交付流程的订单数、用户数、间隔时长等指标，也用于制作瀑布图；

4、开票信息表

说明：记录订单开票信息
1、包含信息：订单号、开票机构（编码、名称、所属交付门店等）、发票号码、开票时间等；
2、存在一对多关系，但同一时间只能有一个生效。即订单的开票信息（发票号码、开票时间等会变更）；
3、因开票业务是属于税务系统主导的，且税务开票信息中含订单信息，故鉴于2和3的原因，不使用该表计算开票订单量；

4、上门交车表

说明：
1、包含信息：交车单、订单号、物流状态、派单与接单、预计发运和到达、实际发运和到达、交车时间、发运地址、司机等；
2、变更时间变化，仅在物流状态变更时；
3、可以依照接单间隔变化、运输时效来判断物流的好坏；

6、风险订单表

说明：记录并跟进存在交付风险的订单
1、包含信息：订单号、提交者、解决者、风险原因、创建与解决时间；
2、用于计算风险单量、风险处理时效等；

7、金融订单表

说明：按揭的交付订单对应的贷款相关信息
1、包含信息：金融订单、交付订单、用户信息、放款时间、贷款机构、放款金额、首付款金额、金融状态；
2、用于计算订单是否收款完成，返佣金额等指标；

8、订单车辆配置表

说明：用户下订单时，选择的车辆配置；
1、包含信息：订单号、销售车型、车机、内外饰、选装包等；
2、存在多值属性，用来计各个配置的订单量、订单金额等指标；

9、用户信息表

含用户ID、手机号码、渠道来源、性别、出生年月等信息；
变更频率一般，数据量大，增量同步；更优方案：拉链表；

10、交付顾问表

含顾问编码、顾问姓名、所属组织等；
变更频率低，数据量小，可全量同步；

11、组织区划表

公共维表，数仓中需要退化为维度表
含城市信息，需要与地区维表关联；
变更频率较低，数据量小，可全量同步；

12、地区表

公共维表，数仓中需要退化成维度表；
变更频率极低，数据量小，可全量同步；
国家统一；

13、编码字典表

主要用到订单分类（普通客户、大客户等）、销售车型（WE86、YOU100等）、车辆用途（商品车、非商品车）、退款原因、支付方式（支付宝、微信等）；

14、其他

2.2、需求分析

交付报表：（日/周/月/年/累计）交付订单量、（日/周/月/年/累计）交付用户数、（日/周/月/年/累计）新增HO订单量、（未来）7日内预约交付订单量、（未来）7日以上预约交付订单量、（日/周/月/年/累计）PDI通过订单量、（累计）收款完成订单量、（累计）收款未完成订单量、平均交付时长、（日/周/月/年/累计）开票订单量、（累计）合同已签署订单量、（累计）合同未签署订单量等指标；

注意事项：

1、订单状态存在回退情况，业务时间，业务事实存在多个，不建议降低事实粒度；经与业务方讨论，确认业务时间以第一次业务时间为准，例如配车，开票等；而业务事实，要基于逻辑计算。例如开票订单量，开票金额，存在开票，红冲，再开票的情况，因此计算开票订单量时，会以``业务时间，开票金额是否大于0`来确定；

2、交付订单中，预约交付时间，是客户自己选择的，可进行变更；由于历史指标没有实际业务含义，因此预约交付量只算未来指标。如（未来）7日内预约交付订单量、（未来）7日以上预约交付订单量；

3、PDI检测流程，支持多次检测；但经过确认，只要检测通过，就不会再进行PDI检测。因此仅获取检测通过的车即可；

4、收款完成订单数指标，因交付订单中不存在收款完成的业务时间，因此仅计算累计指标，逻辑也需要与业务方讨论而定；

以上逻辑问题，均需要在数据探查时，校验数据，确认逻辑；

三、数据域划分

Version1：

2021-10月份，主要是根据部门分类，用户数字发展部门下管辖供应链系统与交付系统；

交付域：交付系统数据、税务系统数据、（车辆）供应链系统；

Version2：

2022-04前后，后续添加了车辆域，就将车辆域移出交付域了；

交付域：交付系统数据、税务系统数据；

四、构建总线矩阵与建模

1、总线矩阵

数据域	业务过程	日期	组织区划	地址	订单分类	客户	销售车型	车辆用途	订单号	金额
交付域	转交订单	✅	✅	✅	✅	✅	✅	✅	✅	✅
交付域	配车	✅					✅	✅	✅
交付域	用户尾款支付	✅				✅			✅	✅
...	...	...	...	...	...	...	...	...	...	...
交付域	用户开票	✅	✅	✅		✅			✅	✅
交付域	车辆交付	✅	✅	✅	✅	✅	✅	✅	✅	✅

2、维度建模

1、一致性事实

事务型快照事实表

1.1、交付订单事实表

包含内容：订单（ID、编号）、客户ID、组织区划ID、交付地区ID（可获取省份、城市）、交付时间、订单分类ID、订单分类名称、销售车型ID、销售车型名称、车辆用途ID、车辆用途名称、订单金额、优惠金额、国补金额等；

1、粒度：一行数据表示一个交付订单；

2、主要用来计算交付订单总量、交付订单总金额、交付用户总数；

1.2、支付订单事实表

包含内容：支付流水号、交付订单号、订单分类、销售车型、车辆用途、交付地区ID、组织区划、客户、支付类型、支付方式、支付时间、支付金额等；

1、粒度：一行数据表示一个支付流水；

2、主要用来计算支付订单总量、支付订单总金额、支付用户总数；

1.3、订单退款事实表

退款单号、退款原因、交付订单号、订单分类、销售车型、车辆用途、交付地区、组织区划、客户、退款时间；

1、粒度：一行数据表示一个交付订单的退款记录；

2、主要用来计算退款订单总量、退款总金额、退款用户总数；

1.4、税务开票事实表

发票ID、发票号码、发票分类、开户行、开具方式、原蓝字发票号、组织区划、开票地区、开票时间、交付订单号、订单分类、购方、销方、含税金额、不含税金额；

1、粒度：一行数据表示一个开票记录；

2、用来计算开票订单总量、总金额等；

1.6、风险订单事实表

风险ID、订单ID、组织区划ID、风险原因、风险描述、创建时间；

1、粒度：一行数据表示一个交付订单的风险记录；

2、仅用来计算各组织下的风险订单总数；

周期型快照事实表

暂无；

累积型快照事实表

1.1、交付订单累计快照事实表

订单号、客户、组织区划、交付地区（省份、城市）、下单渠道、订单分类名称、销售车型名称、车辆用途名称、VIN码、车辆配置信息、预计交付时间、各个流程的业务时间（如转交时间、配车时间、开票时间、交付时间等）、退款原因、退款完成时间、订单金额、优惠金额、权益抵扣金额、国补金额、开票金额、支付流水号、支付方式、支付时间、金融订单号、申请时间、放款时间、首付款、放款金额等等；

1、粒度：一行数据表示一个交付订单；

2、主要为了解决事务型事实表解决不了多事务关联的统计场景，计算一些流程效率指标，例如各流程间转化率，间隔时长；

2、一致性维度

2.1、组织区划维表

包含内容：团队（ID、编码、名称）、小区（ID、编码、名称）、大区（ID、编码、名称）、是否删除；

方案1：全量导入；每日分区全量数据；

方案2：全量导入某表，直接更新昨天的数据，不做分区；

如何选择，需要考虑业务方的使用场景；不关心历史状态，则选方案二；如果需要做业绩考核等工作，则需要方案1；

2.2、地区维表

包含内容：区域（ID、编码、名称）、城市（ID、编码、名称）、省份（ID、编码、名称）

变更频率极低，数据量小；

方案1：每日分区，全量导入，简单易行；

方案2：全量导入某分区；如果后续有变更，新增分区。但需要调整关联逻辑；

2.3、用户维表

包含内容：用户ID、电话号码（加密）、身份证号码（加密）、性别、出生年月、学历、职业、来源渠道等；

变更频率低，数据量较大；原始数据可增量同步；

方案1、维表数据可拉链表处理，适用于变化频率低的情况；

方案2：维表数据每日分区全量数据；

2.4、日期维表

包含内容：日（2022-11-11、20221111）、周（20、2022-20）、月（11、2022-11、202211）、季（04、2022-04）、年（2022）、周第一天、周最后一天、月第一天、月最后一天等等；

用来处理业务时间；一般一年仅新增一次即可；

3、退化维度

3.1、字典码值表

订单分类、销售车型、车辆用途、支付类型、支付方式、订单状态、金融状态等等；

五、明确统计指标

1、原子指标

转交订单总量（转交时间）、转交订单总金额、转交用户总数、配车订单总量（有配车时间）、发运订单总量（有发运时间）、入库订单总量（有入交付中心库时间）、支付订单总量（尾款支付）、支付总金额、支付用户总数、开票订单总量（有第一次开票时间，且金额>0）、开票总金额、交付订单总量、交付总金额、交付用户总数、交付总时长、PDI总次数、PDI通过总次数、预约交付订单总量（有预约交付时间）、收款订单总量等指标；

转交订单，是交付流程第一步；

2、派生指标

（日/月/年/累计）各组织区划转交订单总量、转交订单总金额、转交用户总数、配车订单总量、开票订单总量、交付订单总量、交付总金额；

（日/月/年/累计）订单类型为普通用户的转交订单总量、转交订单总金额、转交用户总数、配车订单总量、开票订单总量、交付订单总量、交付总金额；

（日/月/年/累计）车辆用途为商品车的转交订单总量、转交订单总金额、转交用户总数、配车订单总量、开票订单总量、交付订单总量、交付总金额；

（日/月/年/累计）各车型的转交订单总量、转交订单总金额、转交用户总数、配车订单总量、开票订单总量、交付订单总量、交付总金额；

（日/月/年/累计）各地址（省份、城市）的转交订单总量、转交订单总金额、转交用户总数、配车订单总量、开票订单总量、交付订单总量、交付总金额；

（日/月/年/累计）各车型PDI总次数、PDI通过总次数；

（未来）7日内预约交付订单总量、（未来）7日以上预约交付订单总量、累计收款完成订单总量、累计收款未完成订单总量；

（累计）各组织区划交付总时长；

等指标；

3、衍生指标

各组织区划平均交付时长（交付总时长/交付订单总量）、交付完成占比（如交付订单总量/转交订单总量）等指标；

六、数仓开发

主要是依照依照数仓开发规范进行代码开发；如分层规范、命名规范、任务（代码）规范、质量规范；

七、部署运维

数仓监控规范

八、问题与解决

发票号码重复问题

解决方案：

1、分析问题原因

1.1、是否是数仓处理的原因；如果是，说明开发逻辑有问题；如果不是，则说明源系统本身存在差异；最终确定是源系统本身的问题；

1.2、为什么会重复？发票数据存在手动录入的情况，业务方误操作，导致数据重复；

2、解决问题：

由于是业务操作的问题，因此数仓侧仅对该问题做了质量监控；如果告警，且是业务方误操作，则及时通知业务方，并跟进解决进度；

3、上报问题给到上级以及数据治理团队；给于业务方数据录入规范方面的指导；

交付订单的某维度（如：销售车型）与销售订单的不一致

解决方案：

1、分析问题原因

1.1、是否是数仓处理的原因；如果是，说明开发逻辑有问题；如果不是，则说明源系统本身存在差异；最终确定是源系统本身的问题；

1.2、为什么会有差异？最后确定是销售车型维度会由于订单改配而存在变更，而交付订单的销售车型未变更；

2、解决问题

联合业务方，分析师确认逻辑；确定应以变更后的为准，因销售车型的源头表是销售订单配置信息表，所以应关联销售订单配置信息表，获取销售车型；

3、针对订单的维度不一致问题，上报到上级以及数据治理团队；与交付系统业务方讨论是否需要进行订单更新逻辑调整；

注：

1、中间系统，容易出现某关键维度未更新的情况；需要与业务方讨论，确认最终逻辑；

2、应在开发规范中定义，维度数据建议从源头关联；

时间数据的时区不统一

解决方案：

1、找源系统开发人员确认，时区的转换方式；

2、不对贴源层（ODS）做处理，所以DWD层对时间数据进行时区转换；

3、添加时区规范方案到数据仓库开发规范中；