中小型企业BI从0-1建设——4数据库、ETL工具的使用

李翃晅

于 2023-01-30 15:27:26 发布

阅读量245

点赞数

分类专栏： ETL 数据库文章标签：数据库 etl oracle

本文链接：https://blog.csdn.net/qq_44395284/article/details/128801858

版权

ETL 同时被 2 个专栏收录

6 篇文章 1 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

数据库

源系统为Oracle数据库。
中转服务器兼数仓也安装了Oracle数据库。
帆软的服务器上仅安装了内置的数据库，供存放帆软自己的数据。

服务器的操作系统都是windows server版

ETL工具

使用了比较稳定的Kettle 7.1版本。
实际企业级运行的时候，需要在默认安装后，进行性能调优，主要是把运行内存尽可能扩大，避免跑死程序。

同时，我是把日志记录到数据库中，以便后续查询日志。
这块不做详细介绍，在CSDN上能找到比较多的资料。

ETL搭建数仓的操作步骤

1、ODS层

建立转换的顺序和原则是：
先基础档案，再业务单据；
先主表，再子表；（实际上同时放一个转换也OK，毕竟新建的表，约束关系也没有了）；
数据量小的表，先删除，后新增；删除用delete 效率低，可以直接truncate；
数据量大的表，进行新增/更新。

按照这个原则，在ODS层建立了如下图的job和转换。
ODS层的job
转换1

转换2

这里设置了定时，每天在跑后面2层之前的时间，先跑这层。
这里是为了测试，数据量也相对比较小，层次间预留了足够的时间。
实际在生产环境，可以在3个job的上面，再做一个大的job，然后顺序是1、2、3、成功。这样可以避免某一层没有跑完导致后续层跑数据有错漏的情况。

2、DWD层和ADS层

job见下图：
在这里插入图片描述

每个转换为了区分清楚作用，这里写了具体实现的功能。
ODS1基础数据：把ODS的数据进行了更新。
ADS1

ODS2门店更新：对门店信息进行更新
删除STORE_EACH:这里没有使用帆软的同比去年的公式，思路是直接用Oracle里面的函数进行处理。
然后为了取之前365行数据，就构建了门店+日期的全量表，方便Oracle现场的函数进行去年同比数据的抓取。实际上也可以用其他方法。

插入DWB_TRUNOVER_FULL和STORE：
调用了2个存储过程
ADS4
DWB+TURNOVER_STORE_P的代码如下：

create or replace procedure DWB_TURNOVER_STORE_P
is
begin
delete from DWD_TURNOVER_STORE  WHERE BILLDATE>=
(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss')  FROM DUAL);
commit;

insert into DWD_TURNOVER_STORE
(BILLDATE,PK_ORG,PK_DEPTID,MONEY)
(
select billdate,pk_org,pk_deptid,sum(money) money
from
(
select   a.billdate, a.money,
(select distinct(pk_deptid)  from ar_recitem  b where b.pk_recbill=a.pk_recbill) pk_deptid,
pk_org
from  ar_recbill a
where a.pk_org in
(
select pk_org  from  org_orgs  where enablestate=2 and  isbusinessunit ='Y'  and dr=0 and  (pk_fatherorg='0001A110000000003G7C'  or code='1601')  and  def1<>'~'
)
and  a.billdate>=(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss')  FROM DUAL)
and a.dr=0
)
group by (pk_deptid,billdate,pk_org)
);
COMMIT;
end DWB_TURNOVER_STORE_P;

DWB_TURNOVER_STORE_FULL_P的代码如下：

create or replace procedure DWB_TURNOVER_STORE_FULL_P
is
begin
delete from DWD_TURNOVER_STORE_FULL  WHERE BILLDATE>=
(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss')  FROM DUAL);
commit;

INSERT INTO DWD_TURNOVER_STORE_FULL (BILLDATE,PK_ORG,PK_DEPTID,MONEY)
(
select A.DAY BILLDATE,B.PK_ORG PK_ORG,A.PK_DEPT PK_DEPTID,B.MONEY from
DWB_STORE_EACHDAY A
LEFT join DWD_TURNOVER_STORE B
ON A.DAY=B.BILLDATE AND A.PK_DEPT=B.PK_DEPTID
where A.DAY>=(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss')  FROM DUAL)
);
COMMIT;
end DWB_TURNOVER_STORE_FULL_P;

执行DWD_TURNOVER_STORE_FULL_AN_P的转换也非常简单：就是执行一个存储过程：DWD_TURNOVER_STORE_FULL_AN_P。
其代码如下：

create or replace procedure DWD_TURNOVER_STORE_FULL_AN_P
is
begin
delete from DWD_TURNOVER_STORE_FULL_AN  WHERE BILLDATE>=
(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss')  FROM DUAL);
commit;

insert into DWD_TURNOVER_STORE_FULL_AN
(BILLDATE,PK_DEPTID,MONEY,PK_ORG,SDLY,MTD,YTD)
(
SELECT * FROM
(
select BILLDATE,pk_deptid,money,pk_org,
LAG(money,365,0) OVER (PARTITION BY pk_deptid ORDER BY BILLDATE)  AS SDLY,
sum(money) over (partition by to_char(to_date(BILLDATE,'yyyy-MM-dd hh24:mi:ss'),'YYYYMM') ,pk_deptid   ORDER BY BILLDATE) AS MTD,
sum(money) over (partition by to_char(to_date(BILLDATE,'yyyy-MM-dd hh24:mi:ss'),'YYYY') ,pk_deptid   ORDER BY BILLDATE) AS YTD
from
DWD_TURNOVER_STORE_FULL
)
where 
billdate>=(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss')  FROM DUAL)

);
COMMIT;
end DWD_TURNOVER_STORE_FULL_AN_P;

至此，我们把这个简单的数仓做好了。
然后后面就是通过这个最终加工后的表，通过帆软来展视出来。
由于已经做过数据加工，即便我用了一台性能非常差的笔记本来充当帆软的服务器（All in One），且上面同时还运行了其他程序，报表展示出来的速度也能控制在5秒以内。