【总结】Data Vault数据建模过程及举例

数据仓库2.png

1. 摘要

本文介绍数据仓库中Data Vault建模的过程描述，并举一个示例以加深对相关概念的理解。

2. 内容

2.1 关于Data Vault数据模型

Data Vault（DV）模型用于企业级的数据仓库建模，是Dan Linstedt在20世纪90年代提出的。在最近几年，Data Vault模型获得了很多关注。

Data Vault是一种数据仓库建模方法，用来存储来自多个操作型系统的完整的历史数据。

Data Vault方法需要跟踪所有数据的来源，因此其中每个数据行都要包含数据来源和装载时间属性， 用以审计和跟踪数据值所对应的源系统。

Data Vault不区分数据在业务层面的正确与错误， 它保留操作型系统的所有时间的所有数据， 装载数据时不做数据验证、清洗等工作， 这点明显有别于其他数据仓库建模方法。

Data Vault建模方法显式地将结构信息和属性信息分离， 能够还原业务环境的变化。 Data Vault允许并行数据装载，不需要重新设计就可以实现扩展。

Data Vault是面向细节的，可追踪历史的，一组有连接关系的规范化的表的集合。 这些表可以支持一个或多个业务功能。

它是一种综合了第三范式（3NF）和星型模型优点的建模方法。 其设计理念是要满足企业对灵活性、 可扩展性、 一致性和对需求的适应性要求， 是一种专为企业级数据仓库量身定制的建模方式

2.2 Data Vault 模型各类表说明

对于Data Vault主要分为3种类型的表：中心表，链接表，卫星表（附属表）。

2.2.1 中心表(Hub)

对于构建Data Vault模型的，第一件事就是构建中心表，中心表示DV模型中的核心。如果设计得当，将可以兼容整合各种数据源。 为了达到这点，就应该假设系统源是不可知的，所以中心表应该依赖于实际的业务逻辑标识，而不是使用代理键。

中心表的表结构：

字段	说明
hub_key	代理主键，通过对业务主键进行MD5计算所得
business_key	业务主键,唯一标识业务主键，来之源系统
load_dts	数据第一次转载的时间,只记录第一次转载时间
rec_src	数据源系统

2.2.2 链接表(Link)

链接表的目的是为了灵活性和易扩展，通过链接表可以在不改变原有的构架和转载条件下进行扩展。在Data Vault模型中所有的 关系和事件都是通过链接表来表示。在DV模型中，中心表没有外键，对于中心表间的连接是通过链接表。所以链接表至少要有两个父中心表。

链接表表结构：

字段	说明
link_key	代理主键，使用相关的父Hub表的业务主键拼接后计算MD5值
hub_keys	hubs的代理键
hub_business_keys	hubs的业务主键
load_dts	第一次装载数据的时间
rec_src	源系统信息

2.2.3 卫星表（附属表Satellite）

卫星表示所有的原始数据系统，在这个表中也捕获数据的变化，所以这种表有点像维度模型中的渐变维度表。 一个附属表总有一个且唯一一个外键引用到中心表或链接表。

字段	说明
sta_key	代理主键，相关的hub或link表的主键和数据载入时间的MD5值
hub_or_link_key	父hub或Link的代理主键
attribute_columns	属性数据列
hash_diff	各列拼接后的MD5值计算
sat_load_dts	数据装载时间
sat_rec_src	数据来源信息

2.2.4 总结

表	关键字	作用
Hubs中心表	business_key业务主键	使其以业务为导向, 并允许跨系统集成
Links链接表	Associations/Transactions关联和转换	提供了在无需重新设计的情况下吸收结构和业务规则更改的灵活性
Satellites附属表	Descriptors描述性信息	提供在任何想要的时间间隔内记录历史记录的适应性, 以及对源系统的无可争辩的可审核性和可追溯性

总之, 通过Data Vault模型可以获得敏捷性、灵活性、适应性、可审核性、可扩展性.

2.3 Data Vault建模实践

本示例源数据库是一个订单销售的普通场景，共有省、市、客户、产品类型、产品、订单、订单明细7个表。ERD如下图所示。

使用下面的脚本建立源数据库表：

代码语言：javascript复制

CREATE TABLE province (
  province_id varchar(2) NOT NULL COMMENT '省份编码',
  province_name varchar(20) DEFAULT NULL COMMENT '省份名称',
  PRIMARY KEY (province_id)
) ;
 
CREATE TABLE product_catagory (
  product_catagory_id varchar(2) NOT NULL COMMENT '产品分类编码',
  product_catagory_name varchar(20) DEFAULT NULL COMMENT '产品分类名称',
  PRIMARY KEY (product_catagory_id)
) ;
 
CREATE TABLE city (
  city_id varchar(4) NOT NULL COMMENT '城市编码',
  city_name varchar(20) DEFAULT NULL COMMENT '城市编码',
  province_id varchar(2) DEFAULT NULL COMMENT '省份编码',
  PRIMARY KEY (city_id),
  FOREIGN KEY (province_id) REFERENCES province (province_id)
) ;
 
CREATE TABLE customer (
  customer_id int(11) NOT NULL AUTO_INCREMENT COMMENT '客户ID',
  customer_name varchar(20) DEFAULT NULL COMMENT '客户名称',
  city_id varchar(4) DEFAULT NULL COMMENT '城市ID',
  cust_post_code varchar(6) DEFAULT NULL COMMENT '客户邮编',
  cust_address varchar(50) DEFAULT NULL COMMENT '客户地址',
  ship_post_code varchar(6) DEFAULT NULL COMMENT '送货邮编',
  ship_address varchar(50) DEFAULT NULL COMMENT '送货地址',
  PRIMARY KEY (customer_id),
  FOREIGN KEY (city_id) REFERENCES city (city_id)
) ;
 
CREATE TABLE product (
  product_id int(11) NOT NULL AUTO_INCREMENT COMMENT '产品ID',
  product_name varchar(20) DEFAULT NULL COMMENT '产品名称',
  unit_price decimal(10,4) DEFAULT NULL COMMENT '产品单价',
  product_catagory_id varchar(2) DEFAULT NULL COMMENT '产品分类编码',
  PRIMARY KEY (product_id),
  FOREIGN KEY (product_catagory_id) REFERENCES product_catagory (product_catagory_id)
) ;
 
CREATE TABLE sales_order (
  sales_order_id int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  order_time datetime DEFAULT NULL COMMENT '下单时间',
  entry_time datetime DEFAULT NULL COMMENT '录入时间',
  customer_id int(11) DEFAULT NULL COMMENT '客户ID',
  amount decimal(12,4) DEFAULT NULL COMMENT '订单金额',
  allocate_time datetime DEFAULT NULL COMMENT '分配库房时间',
  packing_time datetime DEFAULT NULL COMMENT '出库时间',
  ship_time datetime DEFAULT NULL COMMENT '配送时间',
  receive_time datetime DEFAULT NULL COMMENT '收货时间',
  PRIMARY KEY (sales_order_id),
  FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
) ;
 
CREATE TABLE sales_order_item (
  sales_order_item_id int(11) NOT NULL AUTO_INCREMENT COMMENT '订单明细ID',
  sales_order_id int(11) DEFAULT NULL COMMENT '订单ID',
  product_id int(11) DEFAULT NULL COMMENT '产品ID',
  unit_price decimal(10,4) DEFAULT NULL COMMENT '产品单价',
  quantity int(11) DEFAULT NULL COMMENT '数量',
  PRIMARY KEY (sales_order_item_id),
  FOREIGN KEY (sales_order_id) REFERENCES sales_order (sales_order_id),
  FOREIGN KEY (product_id) REFERENCES product (product_id)
) ;

将示例转换成Data Vault模型。

1. 转换中心表

（1）确定中心实体 示例中的客户、产品类型、产品、订单、订单明细这5个实体是订单销售业务的中心实体。省、市等地理信息表是参考数据，不能算是中心实体，实际上是附属表。 （2）把第一步确定的中心实体中有入边的实体转换为中心表，因为这些实体被别的实体引用。 把客户、产品类型、产品、订单转换成中心表 （3）把第一步确定的中心实体中没有入边且只有一条出边的实体转换为中心表，因为必须至少有两个Hub才能产生一个有意义的Link。 示例中没有这样的表。下表列出了所有中心表

实体	业务主键
hub_product_catagory	product_catagory_id
hub_customer	customer_id
hub_product	product_id
hub_sales_order	sales_order_id

2. 转换链接表

（1）把源库中没有入边且有两条或两条以上出边的实体直接转换成链接表 把订单明细转换成链接表 （2）把源库中除第一步以外的外键关系转换成链接表。 订单和客户之间建立链接表，产品和产品类型之间建立链接表。注意Data Vault模型中的每个关系都是多对多关系。 下表列出了所有链接表

链接表	被链接的中心表
link_order_product	hub_sales_order，hub_product
link_order_customer	hub_sales_order，hub_customer
link_product_catagory	hub_product，hub_product_catagory

3. 3. 转换附属表

附属表为中心表和链接表补充属性。所有源库中用到的表的非键属性都要放到Data Vault模型中。 下表列出了所有附属表

附属表	描述
sat_customer	hub_customer
sat_product_catagory	hub_product_catagory
sat_product	hub_product
sat_sales_order	hub_sales_order
sat_order_product	link_order_product

使用下面的脚本建立Data Vault数据库表：

代码语言：javascript复制

create table hub_product_catagory (
    hub_product_catagory_id int auto_increment primary key,
    product_catagory_id varchar(2),
    load_dts timestamp,
    record_source varchar(100)
);
 
create table hub_customer (
    hub_customer_id int auto_increment primary key,
    customer_id int,
    load_dts timestamp,
    record_source varchar(100)
);
 
create table hub_product (
    hub_product_id int auto_increment primary key,
    product_id int,
    load_dts timestamp,
    record_source varchar(100)
);
 
create table hub_sales_order (
    hub_sales_order_id int auto_increment primary key,
    sales_order_id int,
    load_dts timestamp,
    record_source varchar(100)
);
 
create table link_order_product (
    link_order_product_id int auto_increment primary key,
    hub_sales_order_id int,
    hub_product_id int,
    load_dts timestamp,
    record_source varchar(100),
    foreign key (hub_sales_order_id)
        references hub_sales_order (hub_sales_order_id),
    foreign key (hub_product_id)
        references hub_product (hub_product_id)
);
 
create table link_order_customer (
    link_order_customer_id int auto_increment primary key,
    hub_sales_order_id int,
    hub_customer_id int,
    load_dts timestamp,
    record_source varchar(100),
    foreign key (hub_sales_order_id)
        references hub_sales_order (hub_sales_order_id),
    foreign key (hub_customer_id)
        references hub_customer (hub_customer_id)
);
 
create table link_product_catagory (
    link_product_catagory_id int auto_increment primary key,
    hub_product_id int,
    hub_product_catagory_id int,
    load_dts timestamp,
    record_source varchar(100),
    foreign key (hub_product_id)
        references hub_product (hub_product_id),
    foreign key (hub_product_catagory_id)
        references hub_product_catagory (hub_product_catagory_id)
);
 
create table sat_customer (
    sat_customer_id int auto_increment primary key,
    hub_customer_id int,
    load_dts timestamp,
    load_end_dts timestamp,
    record_source varchar(100),
    customer_name varchar(20),
    city_name varchar(20),
    province_name varchar(20),
    cust_post_code varchar(6),
    cust_address varchar(50),
    ship_post_code varchar(6),
    ship_address varchar(50),
    foreign key (hub_customer_id)
        references hub_customer (hub_customer_id)
);
 
create table sat_product_catagory (
    sat_product_catagory_id int auto_increment primary key,
    hub_product_catagory_id int,
    load_dts timestamp,
    load_end_dts timestamp,
    record_source varchar(100),
    product_catagory_name varchar(20),
    foreign key (hub_product_catagory_id)
        references hub_product_catagory (hub_product_catagory_id)
);
 
create table sat_product (
    sat_product_id int auto_increment primary key,
    hub_product_id int,
    load_dts timestamp,
    load_end_dts timestamp,
    record_source varchar(100),
    product_name varchar(20),
    unit_price decimal(10 , 4 ),
    foreign key (hub_product_id)
        references hub_product (hub_product_id)
);
 
create table sat_sales_order (
    sat_sales_order_id int auto_increment primary key,
    hub_sales_order_id int,
    load_dts timestamp,
    load_end_dts timestamp,
    record_source varchar(100),
    order_time datetime,
    entry_time datetime,
    amount decimal(12 , 4 ),
    allocate_time datetime,
    packing_time datetime,
    ship_time datetime,
    receive_time datetime,
    foreign key (hub_sales_order_id)
        references hub_sales_order (hub_sales_order_id)
);
 
create table sat_order_product (
    sat_order_product_id int auto_increment primary key,
    link_order_product_id int,
    load_dts timestamp,
    load_end_dts timestamp,
    record_source varchar(100),
    unit_price decimal(10 , 4 ),
    quantity int,
    foreign key (link_order_product_id)
        references link_order_product (link_order_product_id)
);

Data Vault模型如下图所示：

3. 参考

（1）Data Vault 数据仓库模型构建-1 https://www.jianshu.com/p/df3684c20092

（2）Data Vault初探（三） —— 建立Data Vault模型 https://blog.csdn.net/wzy0623/article/details/50222269

data key md5 product src

0 人点赞