【数据仓库】|5 维度建模设计和实施过程

人们普遍认为，在数据仓库和商业智能中，维度模型事给用户显示信息的首选结构，它更易于理解和使用。又因为大数据和Hadoop的出现，允许了数据的高冗余，维度建模便越发多公司使用。

上一节《2 – 到底哪种数仓设计模型更合适》中，我们简单了解了业界用的最多的四种数仓建模方法。

本节我们继续整合《阿里巴巴大数据之路》一书中提出的维度建模相关概念和方法，一步一步去践行维度建模方法论，实践的最后有相应的资料下载。

注：本节只作为demo，目的只为说明维度建模的实施过程，所以暂时不考虑实时数据加工。

划分数据域

数据域是指面向业务分析，将业务过程或者维度进行抽象的集合。业务过程可以概括为个个不可拆分的行为事件，如下单、支付、退款。为保障整个体系的生命力，数据域需要抽象提炼，并且长期维护和更新，但不轻易变动。在划分数据域时，既能涵盖当前所有的业务需求，又能在新业务进入时无影响地被包含进已有的数据域中或者扩展新的数据域。 —— 《阿里巴巴大数据之路》

数据域，通常是联系较为紧密的业务动作的集合，也就是说，数据域是针对事实表划分的。

划分方法

主题域的确定必须由最终用户和数据仓库的设计人员共同完成的，而在划分主题域时，大家的切入点不同可能会造成一些争论、重构等的现象，考虑的点可能会是下方的某些方面：

1、按照业务或业务过程划分：比如一个靠销售广告位置的门户网站主题域可能会有广告域，客户域等，而广告域可能就会有广告的库存，销售分析、内部投放分析等主题；

2、根据需求方划分：比如需求方为财务部，就可以设定对应的财务主题域，而财务主题域里面可能就会有员工工资分析，投资回报比分析等主题；

3、按照功能或应用划分：比如微信中的朋友圈数据域、群聊数据域等，而朋友圈数据域可能就会有用户动态信息主题、广告主题等；

4、按照部门划分：比如可能会有运营域、技术域等，运营域中可能会有工资支出分析、活动宣传效果分析等主题；

总而言之，切入的出发点逻辑不一样，就可以存在不同的划分逻辑。在建设过程中可采用迭代方式，不纠结于一次完成所有主题的抽象，可先从明确定义的主题开始，后续逐步归纳总结成自身行业的标准模型。

典型的电商行业数据域划分如下：

数据域	业务过程
买家域和卖家域	注册、登录、装修、开店、关店等
商品域	发布、上架、下架、重发、SKU库存管理等
交易域	加购、下单、支付、确认收货、退款等
事件域	曝光、浏览、点击、滑动等
营销活动域	限时购、秒杀、满减、团购、砍价助力等
互动域	评论、发帖、回帖等
仓储配送域	商品采购、发货、入库、物流信息跟踪等
财务域	买家账户、卖家账户、平台账户、推广账户等
售后域	退款退货、申诉、投诉、纠纷等
直播域	直播间开通、注销、点赞、弹幕、打赏、关注等

我们围绕着电商的基本三要素：人、货、场，细分和展开业务过程，在传统业务的基础上，增加了直播电商域。

总线矩阵

总线矩阵是根据事件域作维度，把业务过程和相关的维度描述出来，形成矩阵表。如：

域英文缩写：buyer/saller		公共维度
数据域	业务过程	日期	买家	卖家	店铺	商品	订单	活动	地区
买家域和卖家域	注册	√	√	√	×	×	×	×	×
买家域和卖家域	登录	√	√	√	×	×	×	×	×
买家域和卖家域	装修	√	×	√	√	×	×	×	×
买家域和卖家域	开店	√	×	√	√	×	×	×	×
买家域和卖家域	关店	√	×	√	√	×	×	×	×
买家域和卖家域	收藏	√	√	√	√	×	×	×	×

数仓架构

以下是数仓模型架构，每个公司都不要求一样，所以请按照实际需求建层。

ODS：Operational Data Store，操作数据层，在结构上其与源系统的增量或者全量数据基本保持一致。它相当于一个数据准备区，同时又承担着基础数据的记录以及历史变化。
CDM：Common Data Model，公共维度模型层，又细分为DWD和DWS。它的主要作用是完成数据加工与整合、建立一致性维度、构建可复用的面向分析和统计的明细事实表以及汇总公共粒度的指标。

DWD：Data Warehouse Detail，明细数据层。
DWS：Data Warehouse Summary，汇总数据层。

ADS：Application Data Service，应用数据层。

该数据分类架构在ODS层分为三部分：数据准备区、离线数据和准实时数据区。在进入到CDM层后，由以下几部分组成：

公共维度层DIM：基于维度建模理念思想，建立整个企业的一致性维度。
明细粒度事实层DWD：以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表。您可以结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当的冗余，即宽表化处理。
公共汇总粒度事实层DWS：以分析的主题对象为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段来物理化模型。

数据域，主要是用在CDM层中。

物理实现

以下，我们根据一个需求来实现从dws到dws到ads的表的物理设计，完整的pdm文件会在最后放出。

求所有商品最近三十天的浏览人数和交易金额

数据域：商品域

数据粒度：商品

维度：商品

事实：支付和商品浏览

DIM 层设计

商品维度表

drop table dim.dim_item;

/*==============================================================*/
/* Table: 商品维度表                                             */
/*==============================================================*/
create table dim.dim_item (
   item_id              bigint comment '商品ID',
   item_name            string comment '商品名称',
   img_url              string comment '商品图片链接',
   properties           Map<string,string> comment '商品属性(颜色、尺寸、尺码)',
   status               bigint comment '商品状态',
   price                bigint comment '商品价格',
   class_id_1           bigint comment '一级类目ID',
   class_id_1_name      string comment '一级类目名称',
   class_id_2           bigint comment '二级类目ID',
   class_id_2_name      string comment '二级类目名称',
   class_id_3           bigint comment '三级类目ID',
   class_id_3_name      string comment '三级类目名称',
   brand_id             bigint comment '品牌ID',
   brand_name           string comment '品牌名称',
   create_tm            string comment '创建时间 (yyyy-MM-dd hh:mm:ss)',
   chk_tm               string comment '审核时间 (yyyy-MM-dd hh:mm:ss)',
   seller_id            bigint comment '卖家ID',
   saller_name          string comment '卖家名称',
   shop_id              bigint comment '店铺ID',
   shop_name            string comment '店铺名称',
   is_self_support      bigint comment '是否店铺自营',
   supplier_id          bigint comment '供应商ID',
   supplier_name        string comment '供应商名称',
   deli_country_id      bigint comment '发货国家ID',
   deli_country_name    string comment '发货国家名称',
   deli_province_id     bigint comment '发货省ID',
   deli_province_name   string comment '发货省名称',
   deli_city_id         bigint comment '发货市ID',
   deli_city_name       string comment '发货市名称',
   deli_area_id         bigint comment '发货区ID',
   deli_area_name       string comment '发货区名称',
   dw_create_tm         string comment '数仓-记录创建时间 (yyyy-MM-dd hh:mm:ss)',
   dw_update_tm         string comment '数仓-记录更新时间 (yyyy-MM-dd hh:mm:ss)',
   dw_source_sys        string comment '数仓-来源系统缩写',
   dw_source_tabs       string comment '数仓-来源表列表(系统ID1.表名1,系统ID2.表名2... ...)',
   constraint PK_DIM_ITEM primary key (item_id)
)
   comment '商品维度表-全站'
   partitioned by (parent_id bigint comment '母商品ID');

DWD层设计

1. 新建【交易域】的订单多事务事实表，包含下单和支付事实。

drop table dwd.dwd_sale_order_fct;

/*==============================================================*/
/* Table: 订单交易多事务事实表（下单、支付，当前事务事实需要置零处理）*/
/*==============================================================*/
create table dwd.dwd_sale_order_fct (
   id                   bigint comment 'ID',
   order_id             bigint comment '订单号',
   big_order_id         bigint comment '大订单ID',
   sku_id               bigint comment 'SKUID',
   sku_name             string comment 'SKU名称',
   item_id              bigint comment '商品ID',
   parent_id            bigint comment '母商品ID',
   item_name            string comment '商品名称',
   class_id_1_name      string comment '商品一级类目名称',
   class_id_2_name      string comment '商品二级类目名称',
   class_id_3_name      string comment '商品三级类目名称',
   item_price           bigint comment '商品价格',
   buyer_id             bigint comment '买家ID',
   buyer_name           string comment '买家名称',
   saller_id            bigint comment '卖家ID',
   saler_name           string comment '卖家名称',
   shop_id              bigint comment '店铺ID',
   shop_name            string comment '店铺名称',
   order_tm             string comment '下单时间 （yyyy-MM-dd hh:mm:ss）',
   order_num            bigint comment '下单数量',
   order_amt            bigint comment '下单金额',
   share_order_amt      bigint comment '下单分摊金额',
   discount_order_amt   bigint comment '下单优惠金额',
   pay_tm               string comment '支付时间（yyyy-MM-dd hh:mm:ss）',
   pay_num              bigint comment '支付数量',
   buy_amt              bigint comment '支付金额',
   share_buy_amt        bigint comment '支付分摊金额',
   discount_buy_amt     bigint comment '支付优惠金额',
   share_ship_amt       bigint comment '运费分摊',
   rev_address_id       bigint comment '收货地址行政区域',
   ship_address_door_num string comment '收货地址门牌号',
   dw_create_tm         string comment '数仓-记录创建时间 (yyyy-MM-dd hh:mm:ss)',
   dw_update_tm         string comment '数仓-记录更新时间 (yyyy-MM-dd hh:mm:ss)',
   dw_source_sys        string comment '数仓-来源系统缩写',
   dw_source_tabs       string comment '数仓-来源表列表(系统ID1.表名1,系统ID2.表名2... ...)',
   constraint PK_DWD_SALE_ORDER_FCT primary key (id)
)
   comment '订单交易多事务事实表（下单、支付，当前事务事实需要置零处理）'
   partitioned by (dt string comment '业务日期 （yyyy-MM-dd）');

2. 新建【事件域】的页面浏览事实表，里面有商品详情页的浏览事实（可计算出商品的UV）

drop table dwd.dwd_event_page_view_fct;

/*==============================================================*/
/* Table: 页面浏览事实表                               */
/*==============================================================*/
create table dwd.dwd_event_page_view_fct (
   id                   bigint comment 'ID',
   device_id            string comment '设备唯一ID',
   last_page_id         bigint comment '上一个页面ID',
   page_id              bigint comment '页面ID',
   user_id              bigint comment '用户ID',
   shop_id              bigint comment '店铺ID',
   item_id              bigint comment '商品ID',
   ip                   bigint comment 'IP',
   address_id           bigint comment '地址ID',
   channel              string comment '渠道',
   phone_model          bigint comment '手机型号',
   phone_brand          string comment '手机品牌',
   os_system            string comment '操作系统',
   app_version          string comment 'APP版本号',
   page_stay_tm         bigint comment '页面停留时长（毫秒）',
   dw_create_tm         string comment '数仓-记录创建时间 (yyyy-MM-dd hh:mm:ss)',
   dw_update_tm         string comment '数仓-记录更新时间 (yyyy-MM-dd hh:mm:ss)',
   dw_source_sys        string comment '数仓-来源系统缩写',
   dw_source_tabs       string comment '数仓-来源表列表(系统ID1.表名1,系统ID2.表名2... ...)',
   constraint PK_DWD_EVENT_PAGE_VIEW_FCT primary key ()
)
   comment '页面浏览事实表 （打开、隐藏）'
   partitioned by (dt string comment '业务日期 （yyyy-MM-dd）');

DWS层设计

关于汇总层的表建模应遵循以下的原则：

数据公用性比如，汇总的聚集表能否与他人公用？基于某个维度的聚集是否是数据分析或者报表中经常使用的？如果满足这些情况，我们就有必要把明细数据沉淀到汇总表中。
不跨数据域，数据域是在较高层次上对数据进行分类聚集的抽象，如交易统一划到交易域下，商品的新增、修改放到商品域下。
区分统计周期，表命名上要能说明数据的统计周期，如_1d 表示最近1天，_td 截止到当天，_nd 表示最近N天。
避免多个层级的数据应该避免将不同层级的数据放在一起，比如，如果存在7天和30天的事实，我们可以选择用两列存放7天和30天的事实，但是需要在列名和字段注释上说明清楚。同时我们也可以使用两张表分别存储不同统计周期的数据加以区分。
聚集是不跨越事实的，聚集是针对原始星型模型进行的汇总，为了获取和查询原始模型一致的结果，聚集的维度和度量必须与原始模型保持一致，因此聚集是不跨事实的。横向钻取(交叉探查)是针对多个事实基于一致性维度进行的分析，很多时候采用融合事实表，预先存放横向钻取的结果，从而提高查询性能。因此融合事实表是一种导出模式而不是聚集。

按照以上原则，我们设计出两张dws表：【商品粒度交易汇总事实表】、【商品粒度流量汇总事实表】。

drop table dws.dws_sales_item_info;

/*==============================================================*/
/* Table: 商品粒度交易汇总事实表                                   */
/*==============================================================*/
create table dws.dws_sales_item_info (
   item_id              bigint comment '商品ID',
   item_name            string comment '商品名称',
   class_id_1           bigint comment '一级类目ID',
   class_id_1_name      string comment '一级类目名称',
   class_id_2           bigint comment '二级类目ID',
   class_id_2_name      string comment '二级类目名称',
   class_id_3           bigint comment '三级类目ID',
   class_id_3_name      string comment '三级类目名称',
   pro_area             string comment '产地',
   order_num_sum_1w     bigint comment '自然周下单数量',
   order_amt_sum_1w     bigint comment '自然周下单金额',
   share_order_amt_sum_1w bigint comment '自然周下单分摊金额',
   discount_order_amt_sum_1w bigint comment '自然周下单优惠金额',
   pay_num_sum_1w       bigint comment '自然周支付数量',
   buy_amt_sum_1w       bigint comment '自然周支付金额',
   share_buy_amt_sum_1w bigint comment '自然周支付分摊金额',
   discount_buy_amt_sum_1w bigint comment '自然周支付优惠金额',
   gmv_sum_1w           bigint comment '自然周GMV',
   order_num_sum_1m     bigint comment '自然月下单数量',
   order_amt_sum_1m     bigint comment '自然月下单金额',
   share_order_amt_sum_1m bigint comment '自然月下单分摊金额',
   discount_order_amt_sum_1m bigint comment '自然月下单优惠金额',
   pay_num_sum_1m       bigint comment '自然月支付数量',
   buy_amt_sum_1m       bigint comment '自然月支付金额',
   share_buy_amt_sum_1m bigint comment '自然月支付分摊金额',
   discount_buy_amt_sum_1m bigint comment '自然月支付优惠金额',
   gmv_sum_1m           bigint comment '自然月GMV',
   order_num_sum_1d     bigint comment '最近一日下单数量',
   order_amt_sum_1d     bigint comment '最近一日下单金额',
   share_order_amt_sum_1d bigint comment '最近一日下单分摊金额',
   discount_order_amt_sum_1d bigint comment '最近一日下单优惠金额',
   pay_num_sum_1d       bigint comment '最近一日支付数量',
   buy_amt_sum_1d       bigint comment '最近一日支付金额',
   share_buy_amt_sum_1d bigint comment '最近一日支付分摊金额',
   discount_buy_amt_sum_1d bigint comment '最近一日支付优惠金额',
   gmv_sum_1d           bigint comment '最近一日GMV',
   order_num_sum_7d     bigint comment '最近七日下单数量',
   order_amt_sum_7d     bigint comment '最近七日下单金额',
   share_order_amt_sum_7d bigint comment '最近七日下单分摊金额',
   discount_order_amt_sum_7d bigint comment '最近七日下单优惠金额',
   pay_num_sum_7d       bigint comment '最近七日支付数量',
   buy_amt_sum_7d       bigint comment '最近七日支付金额',
   share_buy_amt_sum_7d bigint comment '最近七日支付分摊金额',
   discount_buy_amt_sum_7d bigint comment '最近七日支付优惠金额',
   gmv_sum_7d           bigint comment '最近七日GMV',
   order_num_sum_30d    bigint comment '最近三十日下单数量',
   order_amt_sum_30d    bigint comment '最近三十日下单金额',
   share_order_amt_sum_30d bigint comment '最近三十日下单分摊金额',
   discount_order_amt_sum_30d bigint comment '最近三十日下单优惠金额',
   pay_num_sum_30d      bigint comment '最近三十日支付数量',
   buy_amt_sum_30d      bigint comment '最近三十日支付金额',
   share_buy_amt_sum_30d bigint comment '最近三十日支付分摊金额',
   discount_buy_amt_sum_30d bigint comment '最近三十日支付优惠金额',
   gmv_sum_30d          bigint comment '最近三十日GMV',
   order_num_sum        bigint comment '累积下单数量',
   order_amt_sum        bigint comment '累积下单金额',
   share_order_amt_sum  bigint comment '累积下单分摊金额',
   discount_order_amt_sum bigint comment '累积下单优惠金额',
   pay_num_sum          bigint comment '累积支付数量',
   buy_amt_sum          bigint comment '累积支付金额',
   share_buy_amt_sum    bigint comment '支付分摊金额',
   discount_buy_amt_sum bigint comment '累积支付优惠金额',
   gmv_sum              bigint comment '累积GMV',
   dw_create_tm         string comment '数仓-记录创建时间 (yyyy-MM-dd hh:mm:ss)',
   dw_update_tm         string comment '数仓-记录更新时间 (yyyy-MM-dd hh:mm:ss)',
   dw_source_sys        string comment '数仓-来源系统缩写',
   dw_source_tabs       string comment '数仓-来源表列表(系统ID1.表名1,系统ID2.表名2... ...)',
   constraint PK_DWS_SALES_ITEM_INFO primary key ()
)
   comment '商品粒度交易汇总事实表'
   partitioned by (dt string comment '业务日期 （yyyy-MM-dd）');


drop table dws.dws_event_item_info;

/*==============================================================*/
/* Table: 商品粒度流量汇总事实表                                  */
/*==============================================================*/
create table dws.dws_event_item_info (
   item_id              bigint comment '商品ID',
   item_name            string comment '商品名称',
   class_id_1           bigint comment '一级类目ID',
   class_id_1_name      string comment '一级类目名称',
   class_id_2           bigint comment '二级类目ID',
   class_id_2_name      string comment '二级类目名称',
   class_id_3           bigint comment '三级类目ID',
   class_id_3_name      string comment '三级类目名称',
   pro_area             string comment '产地',
   uv_sum_1w            bigint comment '自然周浏览人数',
   pv_sum_1w            bigint comment '自然周浏览次数',
   uv_sum_1m            bigint comment '自然月浏览人数',
   pv_sum_1m            bigint comment '自然月浏览次数',
   uv_sum_1d            bigint comment '最近一日浏览人数',
   pv_sum_1d            bigint comment '最近一日浏览次数',
   uv_sum_7d            bigint comment '最近七日浏览人数',
   pv_sum_7d            bigint comment '最近七日浏览次数',
   uv_sum_30d           bigint comment '最近三十日浏览人数',
   pv_sum_30d           bigint comment '最近三十日浏览次数',
   uv_sum               bigint comment '累积浏览人数',
   pv_sum               bigint comment '累积浏览次数',
   dw_create_tm         string comment '数仓-记录创建时间 (yyyy-MM-dd hh:mm:ss)',
   dw_update_tm         string comment '数仓-记录更新时间 (yyyy-MM-dd hh:mm:ss)',
   dw_source_sys        string comment '数仓-来源系统缩写',
   dw_source_tabs       string comment '数仓-来源表列表(系统ID1.表名1,系统ID2.表名2... ...)',
   constraint PK_DWS_EVENT_ITEM_INFO primary key ()
)
   comment '商品粒度流量汇总事实表'
   partitioned by (dt string comment '业务日期 （yyyy-MM-dd）');

ADS层设计

要满足需求，必须要从交易域和事件域抽出两张汇总事实表进行交叉探查才能满足需求

drop table ads.ads_item_info_30d;

/*==============================================================*/
/* Table: 商品最近三十日的成交和流量信息                           */
/*==============================================================*/
create table ads.ads_item_info_30d (
   item_id              bigint comment '商品ID',
   item_name            string comment '商品名称',
   uv                   bigint comment '浏览人数',
   pay_amt              bigint comment '支付金额',
   dw_create_tm         string comment '数仓-记录创建时间 (yyyy-MM-dd hh:mm:ss)',
   dw_update_tm         string comment '数仓-记录更新时间 (yyyy-MM-dd hh:mm:ss)',
   constraint PK_ADS_ITEM_INFO_30D primary key ()
)
   comment '商品最近三十日的成交和流量信息';
   
-- 伪加工代码为：
inert overwrite table ads.ads.ads_item_info_30d
select
   a.item_id,
   a.item_name,
   sum(a.uv_sum_30d) as uv, -- 浏览人数
   sum(b.pay_amt_sum_30d) as pay_amt,  -- 支付金额
   from_unixtime(unix_timestamp(),"yyyy-MM-dd HH:mm:ss") create_tm,
   from_unixtime(unix_timestamp(),"yyyy-MM-dd HH:mm:ss") update_tm
from dws.dws_event_item_info a
left join dws.dws_sales_item_info b
  on a.item_id = b.item_id
 and a.dt = b.dt
where a.dt = '2021-06-01';

总结

数仓的构建，工作量最大的还是需求的整理和数据域的处理；其次才是CDM层的表加工，再次是如何才能让开发人员按照一定的规则和共识生产和丰富CDM层。一般来说ADS取数从直接的上游DWS中取，如果DWS中没有，要看这个需求是否经常会用，能否报表化，如果能就在DWS中建公共汇总，如果不能就直接从DWD甚至是ODS层出便可，但一定要防止烟囱式开发。

完整的项目设计过程资料和pdm文档等资料链接：设计过程文档

本文链接：https://www.cnblogs.com/yongjian/p/14858897.html