维度模型数据仓库（三） —— 准备数据仓库模拟环境

用户1148526

发布于 2022-12-02 14:00:29

9570

发布于 2022-12-02 14:00:29

（二）准备数据仓库模拟环境 上一篇说了很多数据仓库和维度模型的理论，从本篇开始落地实操，用一个小而完整的示例说明维度模型及其相关的ETL技术。示例数据库和ETL的SQL实现是在《Dimensional Data Warehousing with MySQL: A Tutorial》基础上做了些修改，增加了Kettle实现的部分。本篇详细说明数据仓库模拟实验环境搭建过程。操作系统：Linux 2.6.32-358.el6.x86_64 数据库：MySQL 5.6.14 for Linux 64位 Kettle：GA Release 5.1.0 实验环境搭建过程： 1. 设计ERD 2. 建立源数据数据库和数据仓库数据库 3. 建立源库表 4. 建立数据仓库表 5. 建立过渡表 6. 生成源库测试数据 7. 生成日期维度数据源数据数据库初始ERD如图（二）- 1所示数据仓库数据库初始ERD如图（二）- 2所示执行清单（二）- 1里的SQL脚本完成2-7步的任务

图（二）- 1

图（二）- 2

-- 建立源数据数据库
DROP DATABASE IF EXISTS source;
CREATE DATABASE source;

-- 建立数据仓库数据库
DROP DATABASE IF EXISTS dw;
CREATE DATABASE dw;

-- 建立源库表
USE source;

-- 建立客户表
CREATE TABLE customer (
    customer_number INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '客户编号，主键',
    customer_name VARCHAR(50) comment '客户名称',
    customer_street_address VARCHAR(50) comment '客户住址',
    customer_zip_code INT(5) comment '邮编',
    customer_city VARCHAR(30) comment '所在城市',
    customer_state VARCHAR(2) comment '所在省份'
);

-- 建立产品表
CREATE TABLE product (   
    product_code INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '产品编码，主键',
    product_name VARCHAR(30) comment '产品名称',
    product_category VARCHAR(30) comment '产品类型'   
);

-- 建立销售订单表
CREATE TABLE sales_order (
    order_number INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '订单号，主键',
    customer_number INT comment '客户编号',
    product_code INT comment '产品编码',
    order_date DATE comment '订单日期',
    entry_date DATE comment '登记日期',
    order_amount DECIMAL(10 , 2 ) comment '销售金额',
    foreign key (customer_number)
        references customer (customer_number)
        on delete cascade on update cascade,
    foreign key (product_code)
        references product (product_code)
        on delete cascade on update cascade
);

-- 建立数据仓库表
USE dw;

-- 建立客户维度表
CREATE TABLE customer_dim (
    customer_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键，代理键',
    customer_number INT comment '客户编号，业务主键',
    customer_name VARCHAR(50) comment '客户名称',
    customer_street_address VARCHAR(50) comment '客户住址',
    customer_zip_code INT(5) comment '邮编',
    customer_city VARCHAR(30) comment '所在城市',
    customer_state VARCHAR(2) comment '所在省份',
    effective_date DATE comment '生效日期',
    expiry_date DATE comment '到期日期'
);

-- 建立产品维度表
CREATE TABLE product_dim (
    product_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键，代理键',
    product_code INT comment '产品编码，业务主键',
    product_name VARCHAR(30) comment '产品名称',
    product_category VARCHAR(30) comment '产品类型',
    effective_date DATE comment '生效日期',
    expiry_date DATE comment '到期日期'
);

-- 建立订单维度表
CREATE TABLE order_dim (
    order_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键，代理键',
    order_number INT comment '订单号，业务主键',
    effective_date DATE comment '生效日期',
    expiry_date DATE comment '到期日期'
);

-- 建立日期维度表
CREATE TABLE date_dim (
    date_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键，代理键',
    date DATE comment '日期',
    month_name VARCHAR(9) comment '月份名称',
    month INT(1) comment '月份',
    quarter INT(1) comment '季度',
    year INT(4) comment '年',
    effective_date DATE comment '生效日期',
    expiry_date DATE comment '到期日期'
);

-- 建立销售订单事实表
CREATE TABLE sales_order_fact (
    order_sk INT comment '订单维度主键',
    customer_sk INT comment '客户维度主键',
    product_sk INT comment '产品维度主键',
    order_date_sk INT comment '日期维度主键',
    order_amount DECIMAL(10 , 2 ) comment '销售金额',
    foreign key (order_sk)
        references order_dim (order_sk)
        on delete cascade on update cascade,
    foreign key (customer_sk)
        references customer_dim (customer_sk)
        on delete cascade on update cascade,
    foreign key (product_sk)
        references product_dim (product_sk)
        on delete cascade on update cascade,
    foreign key (order_date_sk)
        references date_dim (date_sk)
        on delete cascade on update cascade
);

-- 建立过渡表
USE dw;

-- 建立产品过渡表
CREATE TABLE product_stg (
    product_code INT,
    product_name VARCHAR(30),
    product_category VARCHAR(30)
);

-- 建立客户过渡表
CREATE TABLE customer_stg (
    customer_number INT,
    customer_name VARCHAR(30),
    customer_street_address VARCHAR(30),
    customer_zip_code INT(5),
    customer_city VARCHAR(30),
    customer_state VARCHAR(2)
);

-- 生成源库测试数据
USE source;

-- 生成客户表测试数据
INSERT INTO customer 
	(customer_name,
    customer_street_address,
    customer_zip_code,
    customer_city,
    customer_state)
VALUES  
('Really Large Customers', '7500 Louise Dr.',17050, 'Mechanicsburg','PA'),
('Small Stores', '2500 Woodland St.',17055, 'Pittsburgh','PA'),
('Medium Retailers','1111 Ritter Rd.',17055,'Pittsburgh','PA'),
('Good Companies','9500 Scott St.',17050,'Mechanicsburg','PA'),
('Wonderful Shops','3333 Rossmoyne Rd.',17050,'Mechanicsburg','PA'),
('Loyal Clients','7070 Ritter Rd.',17055,'Pittsburgh','PA'),
('Distinguished Partners','9999 Scott St.',17050,'Mechanicsburg','PA');

-- 生成产品表测试数据
INSERT INTO product
	(product_name,
    product_category )
VALUES 
('Hard Disk Drive', 'Storage'),
('Floppy Drive', 'Storage'),
('LCD Panel', 'Monitor');

INSERT INTO sales_order VALUES
  (1, 1, 1, '2013-02-01', '2013-02-01', 1000)
, (2, 2, 2, '2013-02-10', '2013-02-10', 1000)
, (3, 3, 3, '2013-03-01', '2013-03-01', 4000)
, (4, 4, 1, '2013-04-15', '2013-04-15', 4000)
, (5, 5, 2, '2013-05-20', '2013-05-20', 6000)
, (6, 6, 3, '2013-07-30', '2013-07-30', 6000)
, (7, 7, 1, '2013-09-01', '2013-09-01', 8000)
, (8, 1, 2, '2013-11-10', '2013-11-10', 8000)
, (9, 2, 3, '2014-01-05', '2014-01-05', 1000)
, (10, 3, 1, '2014-02-10', '2014-02-10', 1000)
, (11, 4, 2, '2014-03-15', '2014-03-15', 2000)
, (12, 5, 3, '2014-04-20', '2014-04-20', 2500)
, (13, 6, 1, '2014-05-30', '2014-05-30', 3000)
, (14, 7, 2, '2014-06-01', '2014-06-01', 3500)
, (15, 1, 3, '2014-07-15', '2014-07-15', 4000)
, (16, 2, 1, '2014-08-30', '2014-08-30', 4500)
, (17, 3, 2, '2014-09-05', '2014-09-05', 1000)
, (18, 4, 3, '2014-10-05', '2014-10-05', 1000)
, (19, 5, 1, '2015-01-10', '2015-01-10', 4000)
, (20, 6, 2, '2015-02-20', '2015-02-20', 4000)
, (21, 7, 3, '2015-02-28', '2015-02-28', 4000);

COMMIT;

-- 建立日期维度数据生成的存储过程
USE dw;

DELIMITER // ;

DROP PROCEDURE IF EXISTS pre_populate_date //

CREATE PROCEDURE pre_populate_date (IN start_dt DATE, IN end_dt
       DATE)
BEGIN
       WHILE start_dt <= end_dt DO
             INSERT INTO date_dim(
               date_sk
             , date
             , month_name
             , month
             , quarter
             , year
             , effective_date
             , expiry_date
             )
             VALUES(
               NULL
             , start_dt
             , MONTHNAME(start_dt)
             , MONTH(start_dt)
             , QUARTER(start_dt)
             , YEAR (start_dt)
             , '0000-00-00'
             , '9999-12-31'
     )
             ;
             SET start_dt = ADDDATE(start_dt, 1);
      END WHILE;
END
//

DELIMITER ; //

-- 生成日期维度数据
SET FOREIGN_KEY_CHECKS=0;
truncate table date_dim;
call pre_populate_date('2000-01-01', '2020-12-31');
commit;
SET FOREIGN_KEY_CHECKS=1;

清单（二）- 1

说明： 1. 在实际数据仓库项目中一般会有一个独立的过渡区（有时也称operational data store，ODS），用于临时存储源数据，这里为了简化将过渡表建立在DW库里。 2. sales_order.entry_date表示订单登记的日期，一般情况下应该等同于订单日期（sales_order.order_date），但有时两者是不同的，等实验进行到“迟到的事实”时会详细说明。 3. 关于日期维度数据装载日期维度在数据仓库中是一个特殊角色。日期维度包含时间，而时间是最重要的，因为数据仓库的主要功能之一就是存储历史数据，所以每个数据仓库里的数据都有一个时间特征。装载日期数据有三个常用方法：

预装载
每日装载一天
从源数据装载日期

在三种方法中，预装载最容易，也是被示例所采用的方法。使用预装载插入一个时间段里的所有日期。比如，本示例预装载21年的日期维度数据，从2000年1月1日到2020年12月31日。使用这个方法，在数据仓库生命周期中，只需要预装载日期维度一次。也可以按需添加数据。预装载的缺点是：

提早消耗磁盘空间
可能不需要所有的日期（稀疏使用）

本示例使用MySQL存储过程和Kettle两种方法生成日期维度数据。图（二）- 3到图（二）- 8显示Kettle转换的具体步骤。清单（二）- 2里面是JavaScript步骤的代码。图（二）- 9显示生成的date_dim表的数据。

图（二）- 3

图（二）- 4

图（二）- 5

图（二）- 6

图（二）- 7

图（二）- 8

//Create a Locale according to the specified language code
var locale = new java.util.Locale(
    language_code.getString()
,   country_code.getString()
);

//Create a calendar, use the specified initial date
var calendar = new java.util.GregorianCalendar(locale);
calendar.setTime(initial_date.getDate());

//set the calendar to the current date by adding DaySequence days
calendar.add(calendar.DAY_OF_MONTH,DaySequence.getInteger() - 1);

var simpleDateFormat   = java.text.SimpleDateFormat("D",locale);

//get the calendar date
var date = new java.util.Date(calendar.getTimeInMillis());
simpleDateFormat.applyPattern("MM");
var month_number       = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("MMMM");
var month_name         = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("yyyy");
var year4              = "" + simpleDateFormat.format(date);
var quarter_number;
switch(parseInt(month_number)){
    case 1: case 2: case 3: quarter_number = "1"; break;
    case 4: case 5: case 6: quarter_number = "2"; break;
    case 7: case 8: case 9: quarter_number = "3"; break;
    case 10: case 11: case 12: quarter_number = "4"; break;
}
var date_key = DaySequence;

清单（二）- 2