电商行业数据仓库建模实践（一）

一、为什么要数据建模

什么是数据仓库？数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。
   什么是数据建模？如何将数据仓库的数据进行有序、有结构地分类组织和存储，这便是数据建模。
   好的数据建模方案，应该具备哪些特性：
   1）使用：良好的数据模型能帮助我们快速查询所需数据，改善用户使用数据的体验，提高使用数据的效率；
   2）质量：良好的数据模型能改善统计口径的不一致性，减少数据计算错误的可能性；
   3）成本：良好的数据模型能极大减少不必要数据的冗余，实现计算结果复用，降低数据存储和计算的成本；
   4）性能：良好的数据模型能加速数据加工进程，缩短数据产出时间。

二、有哪些数据建模方法论

ER建模、维度建模、DataVault建模等等。
   本文主要遵循维度建模思想，以电商普遍的分析决策需求出发来构建公共数据层模型。
   

三、数据仓库建模原则

1）高内聚低耦合：将业务相近的放在一起，将高概率使用的放一起，遵循软件设计开发的高内聚低耦合原则
   2）公共处理逻辑下沉：越是底层公用的处理逻辑越应该在数据调度依赖的底层进行封装，公共逻辑不应该多处同时存在
   3）数据可回滚：处理逻辑不变，在不同时间多次运行数据结果不变
   4）一致性：相同含义的字段在不同表中必须使用相同的命名以及类型，表名必须清晰，包含层次结构，易于理解
   5）评审机制：模型设计必须进行评审，以确保模型实施过程的正确性

四、数据仓库模型层次划分

1）操作数据层ODS（Operational Data Store）：贴源设计，存放各源系统数据，只做简单清洗；结构化数据增量或全量同步，非结构化数据结构化处理
   2）公共数据层CDS（Common Data Store）：维度建模设计，存放明细数据、公共指标汇总数据以及维表数据
       2-1）明细数据层CDDS（Detail Data Store）：存放细粒度事实表数据（dwd）及维表数据（dim）
       2-2）汇总数据层CSDS（Summary Data Store）：存放粗粒度事实表数据(dws)
   3）应用数据层ADS（Application Data Store）：应用驱动设计，存放数据产品个性化的指标数据；基于应用的数据组装
   

五、数据仓库规范定义

   名词定义：
   1）数据域：
       指面向业务分析，将业务过程或者维度进行进行抽象的集合。
       数据域需要长期维护和更新，但不轻易变动。
       在划分数据域时，既能涵盖当前所有的业务需求，又能在新业务进入时无影响的被包含进已有的数据域中和扩展新的数据域。
   2）业务过程：指企业的业务活动事件，如下单、支付都是业务过程。业务过程是一个不可拆分的行为事件。
   3）度量：某一业务事件行为下的度量，是业务定义中不可再拆分的指标，具有明确业务含义的名词，如支付金额
   4）维度：维度是度量的环境，用来反映业务的一类属性。

   命名规范：
   1）物理模型表名命名规范：
       ods：ods.源数据库名_源数据库表名
       dwd：dw.dwd_数据域_业务过程
       dws：dw.dws_数据域_业务过程_汇总周期
       dim：dw.dim_数据域_维度名
       ads：ads.分析主题_分析内容
   2）物理模型字段命名规范：
       命名约定：
            优先使用英文简写来命名，其次是英文全称，如英文全称过于复杂，使用汉语拼音首字母替代
       维度属性：
            尽可能使用英文缩写
       度量命名：
           原子指标：动作_度量，如销售数量 sales_num
           派生指标：原子指标[时间周期][修饰词]，如3C品类最近7天商品销售数量 sales_num_7d_3c

六、数据仓库数据域划分

  1）用户域（user）：注册、存量等
   2）商品域（goods）：上架、下架、存量等
   3）交易域（trans）：下单、支付等
   4）日志域（log）：访问、曝光、点击、下单、加购等
   5）客服域（cs）：咨询、投诉等
   6）互动域（in）：评论、抽奖等
   7）供应链域（sc）：采购、物流等
   8）公共域（com）：时间维度、地理维度等

七、维度与事实表设计原则

    1）尽可能生成丰富的维度属性
    2）尽量沉淀出通用的维度属性
    3）尽可能包含所有与业务过程相关的事实
    4）只选择与业务过程相关的事实
    5）分解不可加性事实为可加的组件
    6）同一个事实表中不能存在多种不同粒度的事实
    7）事实表的单位要保持一致
    8）对事实表的null值要处理

八、用户域数据建模

   1）用户注册事实表 dw.dwd_user_register 无事实的事实表
   
    主键    用户编号    user_id
    外键    来源编号    source_id
    外键    设备编号    eqpt_id
    日期维度    注册时间    reg_time   
    杂项维度    注册方式(手机、邮箱等)    reg_type   
    杂项维度    注册平台（app、m站、pc站等）    platform
    杂项维度    注册IP    ip
    杂项维度    推荐人用户编号    parent_id
    分区键    注册日期    reg_date
   
    2）用户维表 dw.dim_user 维度表
   
    分区主键    用户编号    user_id
    用户维度    用户账号    user_name
    用户维度    用户邮箱    email
    用户维度    用户手机    mobile
    用户维度    用户昵称    nickname
    用户维度    用户等级    user_lvl
    用户维度    用户余额    balance
    用户维度    用户积分    integral
    分区键    数据日期    data_date
   
    可考虑使用拉链表形式进行存储
   

九、商品域数据建模

       1）商品上下架事实表    dw.dwd_goods_on_off_sale 多事务事实表：商品上架、商品下架

    商品维度    商品编号    goods_id
    日期维度    上架时间    on_sale_time
    日期维度    下架时间    off_sale_time
    杂项维度    下架原因    off_sale_reason
    杂项维度    上下架类型    operation_type
    分区键    数据日期    data_date    nvl(off_sale_time, on_sale_time)

    off_sale_time - on_sale_time 可以计算商品在架时间
   
    2）商品维表 dw.dim_goods 维度表

    分区主键    商品编号    goods_id
    外键    类目编号    cat_id
    外键    供应商编号    supplier_id
    商品维度    商品名称    goods_name
    商品维度    商品货号    goods_sn
    商品维度    商品链接    goods_link
    商品维度    商品季节    goods_season
    商品维度    品牌名称    brand_name
    商品维度    风格名称    style_name
    商品维度    商品属性    goods_attr    以key-value拼接成json形式存储
    商品维度    首次上架时间    first_on_sale_time
    商品维度    最近下架时间    last_off_sale_time
    商品维度    最近下架原因    last_off_sale_reason
    商品维度    累计在架天数    on_sale_days
    商品维度    是否在架    is_on_sale
    分区键    数据日期    data_date
   
    可考虑使用拉链表形式进行存储
   
    3）商品SKU维表 dw.dim_goods_sku 维度表

    分区主键    SKU编号    sku_id
    外键    商品编号    goods_id
    SKU维度    是否启用    is_enable
    SKU维度    SKU颜色    sku_color
    SKU维度    SKU尺寸    sku_size
    SKU维度    SKU重量    sku_weight
    SKU维度    SKU长    sku_length
    SKU维度 SKU宽    sku_width
    SKU维度    SKU高    sku_height
    SKU维度    SKU成本价    cost_price
    SKU维度    SKU吊牌价    shop_price
    SKU维度    SKU售价    promote_price
    SKU维度    SKU促销开始时间    promote_start_time
    SKU维度    SKU促销结束时间    promote_end_time
    分区键    数据日期    data_date
   
    可考虑使用拉链表形式进行存储
   
    4）商品类目维表 dw.dim_goods_cat 维度表

    分区主键    类目编号    cat_id
    类目维度    类目名称    cat_name
    类目维度    类目描述    cat_desc
    类目维度    类目层级    cat_lvl
    类目维度    是否叶子节点    is_leaf
    类目维度    父级类目编号    parent_id
    类目维度    父级类目名称    parent_name
    类目维度    是否启用    is_enable
    类目维度    一级类目编号    cat_lvl1_id
    类目维度    一级类目名称    cat_lvl1_name
    类目维度    二级类目编号    cat_lvl2_id
    类目维度    二级类目名称    cat_lvl2_name
    类目维度     三级类目编号    cat_lvl3_id
    类目维度    三级类目名称    cat_lvl3_name
    分区键    数据日期    data_date
   
    可考虑使用拉链表形式进行存储
   

十、交易域数据建模
   

    1）交易事实表 dw.dwd_trans_order 多事务事实表：下单、支付

    主键    子订单编号    sub_order_id
    外键    父订单编号    order_id
    外键    用户编号    user_id
    外键    商品编号    goods_id
    外键    SKU编号    sku_id
    外键    类目编号    cat_id
    外键    优惠编号    discount_id
    日期维度    下单时间    order_time   
    日期维度    支付时间    pay_time   
    杂项维度    是否当天下单    is_order
    杂项维度    是否当天支付    is_paid
    杂项维度    下单时SKU吊牌价    shop_price
    杂项维度    下单时SKU售价    promote_price
    度量    购买数量    order_num
    度量    购买全额    order_amt
    度量    购买优惠金额    order_discount
    分区键    数据日期    data_date    nvl(pay_time, order_time)
        
    2）订单维表 dw.dim_order 维度表
   
    主键    订单编号    order_id
    外键    优惠编号    discount_id
    订单维度    下单平台（app、m站、pc站等）    platform
    订单维度    支付方式编号    pay_id
    订单维度    支付方式名称    pay_name
    订单维度    支付平台（app、m站、pc站等）    platform
    订单维度    收货人姓名    name
    订单维度    收货人电话    mobile
    订单维度    收货人省份    province
    订单维度    收货人城市    city
    订单维度    收货人详细地址    address
    订单维度    物流商编号    lp_id
    订单维度    物流商名称    lp_name
    订单维度    物流单号    logistics_id
    分区键    数据日期    data_date
   
    可考虑使用拉链表形式进行存储
   

十一、日志域数据建模

    持续更新，后续文章中阐述

来源：种豆大叔