流程概览：
从来源抽取数据（用kettle）（全量抽取，增量抽取【一般以时间为增量】）本项目使用全量抽取，
（ODS）存储到数据仓库，
对数据整合（先放在DW层），
整合成两类表，事实表，维度表。
然后可能再整合到 DM层（数据集市）整合成宽表。
做成报表（报表工具：帆软，tableau）
数据仓库包括好几部分 ODS（性能，数据备份）,DW
命名叫做ODS ：贴源层（最贴近来源的层），具有数据同构性（来源内什么样抽过来基本就什么样）
为了不影响来源的完整性，抽取速度要越快越好，否则可能会丢失数据。
数据放到DW层内。
前置操作
创建普通用户，用户名命名为ODS（ODS层）
CREATE USER ODS IDENTIFIED BY ODSTEST;
--给用户授权，授权后才可以登录连接
GRANT CREATE SESSION TO ODS;(授权创建会话)
GRANT connect,resource,dba TO ODS;（授权连接，资源，资源管理员权限）
GRANT SELECT ANY TABLE TO ODS;(查询任何表格的权限)
（能登录就可以了）
在kettle创建了新的文件资源库
右上角+新建一个文件资源库，然后用户名为project1
如果右上角消失了 就去C盘 --user–edz–.kettle–找到repositories,右键noteoad++编辑，把乱码改成project1
抽取com_brand，com_brand_ype 这两张表
抽取表的修改操作：
1.在表输入处，增加,NOW() ETL_DATE，更改表名为ODS_com_brand
2.在表输出处sql里更改time step 为DATE,更改表名为ODS_com_brand
3.新建保存目录，新建子目录project1 ，再在里面新建子目录trans，两个表都存入这个目录中
4.执行即可
5.其他表也按照上述步骤执行即可.
各种表的修改
com_area表：
store表：
表输入处改为
, FROM_UNIXTIME(signed_time,’%Y-%m-%d’) signed_time
, FROM_UNIXTIME(created_time,’%Y-%m-%d’) created_time
, FROM_UNIXTIME(closed_time,’%Y-%m-%d’) closed_time
, FROM_UNIXTIME(business_time,’%Y-%m-%d’) business_time
并且删除，tpye_id
（有一些忘记了）
创建作业，保证每次都能够快速抽取
新建–作业 --通用里拖出start，成功和9个转换，然后记得保存先–把这些按钮连起来–然后点开转换，在project1上创建子目录，命名job，一个个复制名字，按下图一个个操作，九个都要操作完成 ，保存
至此，从来源处抽取数据的步骤就完成了。
相关知识点
主键非空且唯一（表的字段内不重复）
项目开始
首先，要找到每张表的关联关系，将其保存在数据字典–表关系表内，
（这样，在后续关联的时候才能够快速关联，提高效率）
各表的内容名称
DW层相关
1.DW 门店维度表
根据DW层整合结构表结构整合出一份DW门店维度表
—DW层整合结构表–门店维度表
—DW门店维度表
在plsql里创建DW层
CREATE USER DW IDENTIFIED BY 123456;
GRANT CREATE SESSION TO DW;
GRANT CONNECT,RESOURCE,DBA TO DW;
GRANT SELECT ANY TABLE TO DW;
在DW层内创建 门店维度表
下为代码
creat table DW(
SELECT S.ID store_id,S.PROJECT_ID,S.STORE_NAME,
A.brand_name,A.group_name,
B.type_name brand_type_name ,B.category_name,
CASE WHEN ship_flag =0 then '异常店'
WHEN ship_flag =1 then '普通店'
WHEN ship_flag =2 then '插旗店'
ELSE '旗舰店' END AS ship_name,
C.area_name,C.province,C.CITY_NAME,
D.name district_name,
S.room_open, S.OPEN_DATE,S.SIGNED_TIME,S.CLOSED_TIME,
E.NAME status_name
FROM ODS.ods_t_store S
LEFT JOIN ODS.ODS_COM_BRAND A ON A.brand_id = S.brand_id
LEFT JOIN ODS.ods_com_brand_type B ON B.type_id = S.BRAND_TYPE_ID
LEFT JOIN (
SELECT s1.id,S1.NAME city_name , s2.name province,s3.name area_name
FROM ODS.ods_com_area S1--city表
JOIN ODS.ods_com_area S2 ON S1.PID = S2.ID --省份表
JOIN ODS.ods_com_area S3 ON S2.PID = S3.ID --区域表
) C ON C.ID = S.CITY_ID
LEFT JOIN ODS.ods_com_area_district D ON D.ID = S.district_id
LEFT JOIN ODS.ods_com_store_status E ON E.id = S.flag );
DW层的表就存入账户DW了 ，即放入DW层了。
DW层内的门店维度表就做完了，
接下来是DW-房间售出明细表
2.DW 房间售出明细表
根据DW层整合结构表结构整合出一份DW房间售出明细表
DW层整合结构表–房间售出明细表
DW房间售出明细表
建立DW层内的DW_ROOM_DETAIL表 命名为DW_ROOM_DETAIL
CREATE TABLE DW_ROOM_DETAIL AS
SELECT A.datekey,A.YEARSS date_year,A.MONTHSS date_month,
B.CHAINID chainid,
SUM(C.statusrmng) status_rmng,
SUM(C.statusrmng+c.roomday+c.roomhalfday+c.roomhourday) status_rm,
SUM(B.AMOUNTRMNG) AmountRmNg,
SUM(NVL(AmountRmNg,0)+NVL(AmountRmAllDay,0)+NVL(AmountRmOther,0)+NVL(AmountRmDiscount,0)) RoomAmount,
SUM(NVL(AmountRmNg,0)+NVL(AmountRmAllDay,0)+NVL(AmountRmOther,0)+NVL(AmountRmDiscount,0) +NVL(B.AmountOther,0)) revenue
FROM ODS.ODS_T_DATE A
JOIN ODS.ods_roomammount_detail B ON A.datekey = to_date(B.datekey,'yyyy/mm/dd')
JOIN ODS.ods_roomcount_detaiL C ON B.datekey = C.datekey
where B.chainid = C.chainid
and B.roomtypeid = C.roomtypeid
GROUP BY A.datekey,A.YEARSS,A.MONTHSS ,
B.CHAINID
到此DW层内操作完成。
DM层相关
整合上面的DW_ROOM_DETAIL，DW_STORE两表
创建DM层的代码
CREATE TABLE DM IDENTIFIED BY 123456;
GRANT CONNECT,RESOURCE,DBA TO DM;
GRANT CREATE SESSION TO DM;
GRANT SELECT ANY TABLE TO DM;
在DM层内创建DM_STORE_DETAIL表代码
CREATE TABLE DM_STORE_DETAIL AS
SELECT S.store_id,
S.project_id,
S.store_name,
S.brand_name,
S.group_name,
S.brand_type_name,
S.category_name,
S.ship_name,
S.area_name,
S.province,
S.city_name,
S.district_name,
S.room_open,
S.open_date,
S.signed_time,
S.closed_time,
S.status_name,
D.datekey,
D.date_year,
D.date_month,
D.status_rmng,
D.status_rm,
D.amountrmng,
D.roomamount,
D.revenue
FROM DW.DW_ROOM_DETAIL D
JOIN DW.DW_STORE S ON D.CHAINID =S.STORE_ID
随后发现 数据内的area_name字段，内部信息全为中国，是错误字段，发现是来源的信息错误，故重新抽取，顺序仍然是按照来源，ODS抽取到DW，DW抽取到DM的顺序。
利用DELETE FROM 表名来删除表内数据，但表格式仍然存在，
拖拽表格进入编辑框，点击插入，会出现
删除values后半部分，用下述文件内的代码代替values的内容
--DW_STORE表
DELETE FROM dw_store--删除表内容
insert into dw_store --加载表内容
(store_id, project_id, store_name, brand_name, group_name, brand_type_name, category_name, ship_name, area_name, province, city_name, district_name, room_open, open_date, signed_time, closed_time, status_name)
SELECT S.ID store_id,S.PROJECT_ID,S.STORE_NAME,
A.brand_name,A.group_name,
B.type_name brand_type_name ,B.category_name,
CASE WHEN ship_flag =0 then '异常店'
WHEN ship_flag =1 then '普通店'
WHEN ship_flag =2 then '插旗店'
ELSE '旗舰店' END AS ship_name,
C.area_name,C.province,C.CITY_NAME,
D.name district_name,
S.room_open, S.OPEN_DATE,S.SIGNED_TIME,S.CLOSED_TIME,
E.NAME status_name
FROM ODS.ods_t_store S
LEFT JOIN ODS.ODS_COM_BRAND A ON A.brand_id = S.brand_id
LEFT JOIN ODS.ods_com_brand_type B ON B.type_id = S.BRAND_TYPE_ID
LEFT JOIN (
SELECT s1.id,S1.NAME city_name , s2.name province,s3.name area_name
FROM ODS.ods_com_area S1--city表
JOIN ODS.ods_com_area S2 ON S1.PID = S2.ID --省份表
JOIN ODS.ods_com_area S3 ON S2.PID = S3.ID --区域表
) C ON C.ID = S.CITY_ID
LEFT JOIN ODS.ods_com_area_district D ON D.ID = S.district_id
LEFT JOIN ODS.ods_com_store_status E ON E.id = S.flag ;
commit;
---------------------------------------------
--DW_ROOM_DETAIL表
DELETE FROM dw_room_detail --删除表内容
insert into dw_room_detail --加载表内容
(datekey, date_year, date_month, chainid, status_rmng, status_rm, amountrmng, roomamount, revenue)
SELECT A.datekey,A.YEARSS date_year,A.MONTHSS date_month,
B.CHAINID chainid,
SUM(C.statusrmng) status_rmng,
SUM(C.statusrmng+c.roomday+c.roomhalfday+c.roomhourday) status_rm,
SUM(B.AMOUNTRMNG) AmountRmNg,
SUM(NVL(AmountRmNg,0)+NVL(AmountRmAllDay,0)+NVL(AmountRmOther,0)+NVL(AmountRmDiscount,0)) RoomAmount,
SUM(NVL(AmountRmNg,0)+NVL(AmountRmAllDay,0)+NVL(AmountRmOther,0)+NVL(AmountRmDiscount,0) +NVL(B.AmountOther,0)) revenue
FROM ODS.ODS_T_DATE A
JOIN ODS.ods_roomammount_detail B ON A.datekey = to_date(B.datekey,'yyyy/mm/dd')
JOIN ODS.ods_roomcount_detaiL C ON B.datekey = C.datekey
where B.chainid = C.chainid
and B.roomtypeid = C.roomtypeid
GROUP BY A.datekey,A.YEARSS,A.MONTHSS ,
B.CHAINID
commit;
DELETE FROM dm_store_detail --删除表内容
insert into dm_store_detail --加载表内容
(store_id,
project_id,
store_name,
brand_name,
group_name,
brand_type_name,
category_name,
ship_name,
area_name,
province,
city_name,
district_name,
room_open,
open_date,
signed_time,
closed_time,
status_name,
datekey,
date_year,
date_month,
status_rmng,
status_rm,
amountrmng,
roomamount,
revenue)
SELECT S.store_id,
S.project_id,
S.store_name,
S.brand_name,
S.group_name,
S.brand_type_name,
S.category_name,
S.ship_name,
S.area_name,
S.province,
S.city_name,
S.district_name,
S.room_open,
S.open_date,
S.signed_time,
S.closed_time,
S.status_name,
D.datekey,
D.date_year,
D.date_month,
D.status_rmng,
D.status_rm,
D.amountrmng,
D.roomamount,
D.revenue
FROM DW.DW_ROOM_DETAIL D
JOIN DW.DW_STORE S ON D.CHAINID =S.STORE_ID
commit;
(注）
拖拽表格进入编辑框，点击选择，会出现查询该表格全部字段的样式，
然后点击窗工具栏部分的美化SQL
代码会转变成
至此ODS,DW,DM层内各表数据抽取完毕
创建存储过程(封装代码)
create or replace procedure 存储过程名 is/as begin
–过程体
end
–存储过程的命名 ， 必须以SP_开头
create or replace procedure SP_DW_STORE is/as（任选一个） begin
--过程体（过程体内必须要有commit;）
end;---END要加分号！！！！！！!！！！！!！!！！!！!！
例：
```sql
CREATE OR REPLACE PROCEDURE SP_dw_store IS BEGIN
delete from DW_STORE;
commit; --删除
insert into dw_store --插入数据
(store_id,
project_id,
store_name,
brand_name,
group_name,
brand_type_name,
category_name,
ship_name,
area_name,
province,
city_name,
district_name,
room_open,
open_date,
signed_time,
closed_time,
status_name)
SELECT S.ID store_id,S.PROJECT_ID,S.STORE_NAME,--整体插入数据
A.brand_name,A.group_name,
B.type_name brand_type_name ,B.category_name,
CASE WHEN ship_flag =0 then '异常店'
WHEN ship_flag =1 then '普通店'
WHEN ship_flag =2 then '插旗店'
ELSE '旗舰店' END AS ship_name,
C.area_name,C.province,C.CITY_NAME,
D.name district_name,
S.room_open, S.OPEN_DATE,S.SIGNED_TIME,S.CLOSED_TIME,
E.NAME status_name
FROM ODS.ods_t_store S
LEFT JOIN ODS.ODS_COM_BRAND A ON A.brand_id = S.brand_id
LEFT JOIN ODS.ods_com_brand_type B ON B.type_id = S.BRAND_TYPE_ID
LEFT JOIN (
SELECT s1.id,S1.NAME city_name , s2.name province,s3.name area_name
FROM ODS.ods_com_area S1--city表
JOIN ODS.ods_com_area S2 ON S1.PID = S2.ID --省份表
JOIN ODS.ods_com_area S3 ON S2.PID = S3.ID --区域表
) C ON C.ID = S.CITY_ID
LEFT JOIN ODS.ods_com_area_district D ON D.ID = S.district_id
LEFT JOIN ODS.ods_com_store_status E ON E.id = S.flag ; --要有分号
COMMIT; --要有commit;
END;--要有分号
**存储过程的调用：**
BEGIN
存储过程名;
END
```sql
begin
SP_DW_STORE;
END;
（注意，都要加分号！）
既然存储了，在帆软内也能调用来使用
首先要连接数据库，因为要调用oracleDM ，DW里面的SP文件代码，所以要连接这些库，所以在资源库内添加DW,DM两个库。
然后创建转换，在转换内找到查询–调用DB过程，然后三个表都是点开方块 ，按下表输入，
（返回值删除，名称复制，保存的时候也是一样，保存在project1，trans内）
最终链路连接过程
点开方块查看
记得先保存再浏览！
记得先保存再浏览！
记得先保存再浏览！
扫一扫
1824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
