数据仓库搭建流程
基于Oracle数据库的传统数仓一、数仓的概念（一）业务系统—>ODS层（二）ODS—>DW层（三）DW—>DM层二、涉及知识点（一）ODS层搭建1. Kettle2. Datastage（二）DW层搭建（三）DM层搭建（四）核心SQL—>Oracle1. oracle基本语法三、...
·
基于Oracle数据库的传统数仓搭建
先大概讲以下，是什么原因写这篇文章！
在这个大数据的时代，全国各地、各大大中型企业都陆续进入数据中台、数据仓库开发的过程中。
其中以深圳起步比较早，在逐步进入数据流建设的下一个阶段。当我们把所有的数据通道建设OK后，不难看出中国将进入真正的数据时代。
所以在本行业的自己，就简单写下最基本的数仓搭建流程，给想要往这个方向发展的大家一个引导！
文章目录
一、数仓架构
本次介绍的是最简单的数仓搭建流程。
该数仓主要分为三层：ODS--->DW---->DM三层。每一层在该数仓都起着不可或缺的作用。
数据流向
数据仓库流程：业务系统——>ODS(贴源层)——>DW(数仓层)——>DM(集市层)——>报表
（一）业务系统—>ODS层
该层主要实现从业务系统复制数据到我们的数仓中。
该过程分为以下几步：
第一步：我们先连接业务系统和ODS层数据库系统
第二步：在ODS层创建目标表
第三步：将数据从业务系统抽取到ODS层
该过程还可以称为ETL过程(ETL--Extract-Transform-Load)
将业务系统的数据抽取、转换、加载加载到目标端。
（二）ODS—>DW层
该层主要实现将抽取到数仓的数据进一步转换。
DW层涉及到的三个必须知道的名词：事实表、维度表、模型。
--模型：主要反映事实表和维度表之间的关系，常见的两种：星型模型、雪花模型
--事实表：表内主要包含各个维度表关联字段和我们关注的内容
--维度表：我们需要的数据信息
该层主要是实现对事实表和维度表内数据的填充。
一般通过编写存储过程的方式，数据同步的方式：增量同步和全量同步。
（三）DW—>DM层
该层主要实现对我们所关注的指标进行计算。
通过将DW层中我们关注的内容，输出到同一张宽表中，通过各种计算
方式，得出我们所需要的指标。
同时，通过FineReport实现报表的输出。
二、各层搭建Demo
（一）ODS层搭建
该层一般通过库与库之间的连接工具实现数据的ETL。
本文介绍下Kettle实现的过程，常见的还有datastage......
1.连接数据库
在Kettle中点击connect,依次连接业务系统数据库(Mysql)和数仓数据库(Oracle)
2.创建目标表
在我们的数仓数据库中建立目标表来接收业务系统中的数据。
--创建表的代码
-- 建表的相关操作
------------------ 创建表结构.sql
--1 创建表结构
CREATE TABLE ODS_SAL_0705();
--2 给表添加注释
COMMENT ON TABLE ODS_SAL_0705 IS 'TABLE_NAME' ;
COMMENT ON COLUMN ODS_SAL_0705 .EMPNO IS 'COLUMN_NAME' ;
--3 给表创建 主键 约束
ALTER TABLE ODS_SAL_0705 ADD CONSTRAINT PK_EMP_0615 PRIMARY KEY(EMPNO);
在创建目标表的时候，主要参考对象是业务系统中的业务表。
3.数据抽取
数据抽取的过程主要是通过Kettle工具，简单实现的。
***参考kettle使用教程：***Jmayday大佬
（二）DW层搭建
1.数仓模型设计
数仓模型的设计，一般使用**PowerDesigner**来实现。
模型包括很多，常见的是：星型模型、雪花模型。主要规范事实表和
维度表之间的关系。
--星型模型：事实表直接和所有维度表字段相连
--雪花模型：事实表和维度表之间存在间接连接
2.创建目标表
该层的目标表包括：事实表和维度表。
创建主要是确认以下几项内容：表内字段、字段类型、取数逻辑。
主要通过制作数据字典的方式，通过源表来确认所需的表内数据。
3.实现数据同步
数据的同步主要分为两种：全量同步、增量同步。
a.全量同步
--全量同步：
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1
IS
/*全量同步的逻辑：
1 先删除目标表的数据
2 将源表的数据全量插入到目标表中 */
BEGIN
/* 1 先删除目标表的数据*/
-- 每次更新目标表之前，先清空目标表中的原有数据 ，这样可以支持重跑
DELETE FROM EMP_BAK1 WHERE 1 = 1 ; -- 如果没有这一步，那么表中有主键的话就会报 主键冲突/ 违反唯一约束 。
/*2 将源表的数据全量插入到目标表中*/
INSERT INTO EMP_BAK1(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
LAST_UPDATE_DATE)
SELECT
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
SYSDATE
FROM EMP ;
COMMIT ; -- TCL事务控制语言 ，对 DML语句（增，删，改）的操作都需要做 TCL的操作
END SP_EMP_BAK1 ;
--3 开发完成以后 ，调用 并测试逻辑是否正确 。
BEGIN
SP_EMP_BAK1 ;
END ;
SELECT * FROM EMP_BAK1 ;
b.增量实现
1）游标实现
--增量同步：游标实现
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1_CURSOR
IS
CURSOR C_EMP IS
SELECT * FROM EMP ;
/* 增量同步的逻辑：
1 先判断目标表中是否包含 源表中的数据 ，一般是通过 主键字段比较的。
2 如果源表的数据在目标表中 存在 ，那么就用 源表的数据 更新 目标表的数据 。
如果源表的数据在目标表中 不存在 ，，那么就将 源表的数据 直接插入到目标表中。*/
V_CT NUMBER(1) ;
BEGIN
FOR X IN C_EMP LOOP
/*1 先判断目标表中是否包含 源表中的数据 ，一般是通过 主键字段比较的。*/
SELECT COUNT(*) INTO V_CT FROM EMP_BAK1 WHERE EMPNO = X.EMPNO ;
/*2 如果源表的数据在目标表中 存在 ，那么就用 源表的数据 更新 目标表的数据 。
*/
IF V_CT = 1 THEN
UPDATE EMP_BAK1 M
SET -- 更新的字段中 不包括 比较字段 EMPNO ，这里不更新
M.ENAME = X.ENAME ,
M.JOB = X.JOB ,
M.MGR = X.MGR ,
M.HIREDATE = X.HIREDATE ,
M.SAL = X.SAL ,
M.COMM = X.COMM ,
M.DEPTNO = X.DEPTNO ,
M.LAST_UPDATE_DATE = SYSDATE
WHERE M.EMPNO = X.EMPNO ;
/* 如果源表的数据在目标表中 不存在 ，那么就将 源表的数据 直接插入到目标表中。 */
ELSIF V_CT = 0 THEN
INSERT INTO EMP_BAK1(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
LAST_UPDATE_DATE)
VALUES(
X.EMPNO ,
X.ENAME ,
X.JOB ,
X.MGR ,
X.HIREDATE ,
X.SAL ,
X.COMM ,
X.DEPTNO ,
SYSDATE
);
END IF ;
END LOOP;
COMMIT ;
END SP_EMP_BAK1_CURSOR;
2) Merge实现
--增量同步：merge实现
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1_MERGE
IS
/* 增量同步的逻辑：
1 先判断目标表中是否包含 源表中的数据 ，一般是通过 主键字段比较的。
2 如果源表的数据在目标表中 存在 ，那么就用 源表的数据 更新 目标表的数据 。
如果源表的数据在目标表中 不存在 ，，那么就将 源表的数据 直接插入到目标表中。*/
BEGIN
MERGE INTO EMP_BAK1 M -- 通过 MERGE 方法更新 EMP_BAK1 表
USING ( SELECT * FROM EMP ) X -- 使用 X 这个结果集
/*1 先判断目标表中是否包含 源表中的数据 ，一般是通过 主键字段比较的。 */
ON (M.EMPNO = X.EMPNO) -- 比较字段是2个表的主键字段 EMPNO
/*2 如果源表的数据在目标表中 存在 ，那么就用 源表的数据 更新 目标表的数据 。*/
WHEN MATCHED THEN
-- 这里 UPDATE SET 中间没有表名 ，因为要更新的目标表在上面 MERGE 后面已经指定了
UPDATE SET
M.ENAME = X.ENAME ,
M.JOB = X.JOB ,
M.MGR = X.MGR ,
M.HIREDATE = X.HIREDATE ,
M.SAL = X.SAL ,
M.COMM = X.COMM ,
M.DEPTNO = X.DEPTNO ,
M.LAST_UPDATE_DATE = SYSDATE
-- 这个后面不用写 WHERE 条件 ，同时也不能写 分号 ;
/* 如果源表的数据在目标表中 不存在 ，，那么就将 源表的数据 直接插入到目标表中。 */
WHEN NOT MATCHED THEN
-- 这里的 INSERT 后面不跟表名，因为上面已经指定了。
INSERT (
M.EMPNO ,
M.ENAME ,
M.JOB ,
M.MGR ,
M.HIREDATE ,
M.SAL ,
M.COMM ,
M.DEPTNO ,
M.LAST_UPDATE_DATE
)
VALUES(
X.EMPNO ,
X.ENAME ,
X.JOB ,
X.MGR ,
X.HIREDATE ,
X.SAL ,
X.COMM ,
X.DEPTNO ,
SYSDATE
);
COMMIT ;
END SP_EMP_BAK1_MERGE ;
（三）DM层搭建
DM层主要是对我们关注的指标进行计算，
例如：常见的电商系统中，年/月/日销售额、销量等...
1. 建立目标宽表
该层数据输出方式分为:OLAP和OLTP两种方式。
OLTP常见报表软件：FineReport
OLAP常见报表软件：Tableau
2.计算指标
a.FineReport
当我们使用FineReport时，既可以在FineReport这个软件中直接写
sql取数，来实现指标的计算，也可以先在oracle编辑器中将指标计算
好然后直接在FineReport中取数就好了。
b.Tableau
OLAP类偏重于对历史数据进行分析，一般使用Tableau时，指标的
计算过程在Oracle中完成，输出数据到Tableau实现报表的绘制。
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
11
2- 0
已为社区贡献3条内容
所有评论(0)