【数据库管理】|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 '网页页面滞留时间（ms）',
   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_2w     bigint comment '自然周提交订单总数',
   order_amt_sum_2w     bigint comment '自然周提交订单额度',
   share_order_amt_sum_2w bigint comment '自然周提交订单平摊额度',
   discount_order_amt_sum_2w bigint comment '自然周提交订单特惠额度',
   pay_num_sum_2w       bigint comment '自然周付款总数',
   buy_amt_sum_2w       bigint comment '自然周付款额度',
   share_buy_amt_sum_2w bigint comment '自然周付款平摊额度',
   discount_buy_amt_sum_2w bigint comment '自然周付款特惠额度',
   gmv_sum_2w           bigint comment '自然周GMV',
   order_num_sum_1米     bigint comment '当然月提交订单总数',
   order_amt_sum_1米     bigint comment '当然月提交订单额度',
   share_order_amt_sum_1米 bigint comment '当然月提交订单平摊额度',
   discount_order_amt_sum_1米 bigint comment '当然月提交订单特惠额度',
   pay_num_sum_1米       bigint comment '当然月付款总数',
   buy_amt_sum_1米       bigint comment '当然月付款额度',
   share_buy_amt_sum_1米 bigint comment '当然月付款平摊额度',
   discount_buy_amt_sum_1米 bigint comment '当然月付款特惠额度',
   gmv_sum_1米           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_2w            bigint comment '自然周访问 总数',
   pv_sum_2w            bigint comment '自然周访问 频次',
   uv_sum_1米            bigint comment '当然月访问 总数',
   pv_sum_1米            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文档等材料连接：设计过程文本文档