本篇文章进行员工考勤项目开发，基于ETL工具Informatica，数据库Oracle和PL/SQL进行开发，数据来源微信公众号数据爱分享的员工考勤表数据。
ETL项目开发步骤
第一部分：理解业务需求
根据员工考勤表数据分析以及情况分析：从各业务系统分析员工的考勤情况以及薪酬统计，开发出相应的ODS层、EDW层
统计出报表情况：DM层员工考勤月统计报表、DM层员工考勤统计状态表、DM层员工薪酬统计表
第二部分：抽取数据、导入数据
在Oracle数据库中创建bi_ods、bi_edw、bi_dw用户
以及在bi_ods层创建表导入员工考勤数据csv文件以及在bi_edw层导入工资维度表数据
第三部分：开发思路
创建ODS层，从数据源导入ODS层，从ODS层抽取数据到EDW层开发出员工考勤明细表，基于EDW层开发DM层统计报表
ETL工具Informatica开发流程
第一部分：数据源导入数据库
1、导入数据源员工考勤和维度表员工工资数据到Informatica
2、Oracle数据库的ODS层、EDW层中创建源表ods_emp_work、emp_work_sal
--创建员工考勤表
create table ODS_EMP_WORK
(
sid NUMBER(3),
cno NUMBER(4),
dn NUMBER(1),
id NUMBER(2),
name VARCHAR2(10),
status NUMBER(1),
action NUMBER(1),
apb NUMBER(1),
jobcode NUMBER(1),
datetime DATE
)
--员工工资维度表
CREATE TABLE emp_work_sal(
empid NUMBER(2),
ename VARCHAR2(10),
sal NUMBER(10,2)
)
3、将员工考勤表的时间转换为时间类型datetime
to_date(DATETIME,'yyyy-mm-dd HH24:mi')
完成员工考勤源表的映射
4、创建任务，修改任务映射的源表与目标表的来源，创建工作流连接任务，通过任务启动工作流
5、监测执行完毕，检查目标数据
第二部分： EDW层创建员工考勤明细表临时表
1、创建员工考勤明细表临时表
开发思路
实现将ODS层表打卡字段分为上班时间与下班时间以及三次打卡时间字段
考虑相关业务逻辑与口径，开发脚本程序SQL
--员工考勤明细表临时表
SELECT ID,
NAME,
DAY,
MAX(CASE WHEN rn = 1 THEN datetime ELSE NULL END) sign_in, --上班时间
MAX(CASE WHEN rn = 2 THEN datetime ELSE NULL END) sign_out, --下班时间
MAX(CASE WHEN rn = 3 THEN datetime ELSE NULL END) three --打卡三次
FROM(SELECT ID,
NAME,
to_char(datetime,'yyyy-mm-dd')DAY, --日期
datetime,
row_number()OVER(PARTITION BY ID,NAME,to_char(datetime,'yyyy-mm-dd') --打卡次数
ORDER BY datetime ASC)rn
FROM ods_emp_work)
GROUP BY ID,NAME,DAY
ORDER BY ID,DAY;
ETL开发
TO_CHAR(DATETIME,'yyyy-mm-dd')
对员工ID，员工姓名，打卡日期进行分组排序
--对每个员工每天的打卡时间进行排序
iif(RANKINDEX=1,DATETIME,null)
iif(RANKINDEX=2,DATETIME,null)
iif(RANKINDEX=3,DATETIME,null)
员工考勤明细表临时表映射完成
监测执行完毕，检查目标数据
第三部分 EDW层员工考勤明细表
创建目标表，EDW层明细表
--创建EDW层明细表
CREATE TABLE edw_emp_work_detail
( empid NUMBER(2), --员工编号
ename VARCHAR2(10), --员工姓名
day_id VARCHAR2(15), --考勤日期
sign_in DATE, --上班时间
sign_out DATE, --下班时间
is_late NUMBER(2), --是否迟到
is_overtime NUMBER(2), --是否加班
is_early NUMBER(2) --是否早退
)
EDW层员工考勤明细表口径说明：
对临时表中的数据进行口径调整
员工考勤临时表中有三次打卡情况分别调整：
A：如只打一次卡的情况（以下午3点为界限）早于下午3点的算上班时间，晚于下午3点的算下班时间
B:两次打卡情况，算正常记录
C: 三次打卡情况，分二次上班记录一次下班记录和二次下班记录与一次上班记录
A：一次打卡情况
isnull(SIGN_OUT) and to_char(SIGN_IN,'HH24mi')>'1500'
B:三次打卡情况
not isnull(THREE_TIME)
C:两次打卡（正常情况）
isnull(THREE_TIME) and to_char(SIGN_IN,'HH24mi')<'1500'
检查数据
接下来计算员工的迟到、加班、早退次数
EDW层员工考勤明细表口径说明：
D： 是否迟到口径说明
上班时间在早上9点以后，算迟到 加标识：1为迟到 0 为不迟到 ，但必须算在工作日。
迟到一分钟不算迟到。
E： 是否加班口径说明
下班时间在 晚上8点以后，算加班 加标识：1为加班 0 为不加班 ，但必须算在工作日。
F: 是否早退口径说明
下班时间在 下午5:30点以前，算早退 加标识：1为早退 0 为不早退 但必须算在工作日。
SELECT to_char(SYSDATE,'d') 星期 FROM dual
/*今天星期一显示2
星期 日 一 二 三 四 五 六
1 2 3 4 5 6 7
iif(TO_char(OUT_SIGN_IN,'d')<>'7' and to_char(OUT_SIGN_IN,'d')<>'1'
and to_char(OUT_SIGN_IN,'HH24mi')>'0901',1,0)
iif(TO_char(OUT_SIGN_OUT,'d')<>'7' and to_char(OUT_SIGN_OUT,'d')<>'1'
and to_char(OUT_SIGN_OUT,'HH24mi')>'2001',1,0)
iif(TO_char(OUT_SIGN_OUT,'d')<>'7' and to_char(OUT_SIGN_OUT,'d')<>'1'
and to_char(OUT_SIGN_OUT,'HH24mi')<'1730',1,0)
完成映射
创建任务，修改任务映射的源表和目标表。创建工作流，连接任务，通过任务启动工作流
检查数据
第四部分 DM层员工月统计报表开发
基于EDW层的员工考勤明细表统计每个员工每个月的迟到次数，早退次数，加班次数，周末加班次数
创建目标表，DM层月统计表
-----创建DM_EMP_WORK_MONTH表
create table DM_EMP_WORK_MONTH(
EMPID, NUMBER(8) ---员工编号
ENAME, VARCHAR2(20) ---员工姓名
MONTH_ID, VARCHAR2(7) ---考勤月份
Late_Cnt, NUMBER(2) ----迟到次数
overtime_Cnt, NUMBER(2) ----加班次数
Early_Cnt, NUMBER(2) ----早退次数
Weekend_Cnt, NUMBER(2) ----周末加班次数
)
IIF((TO_CHAR(SIGN_IN,'d')='7'or to_char(SIGN_IN,'d')='1')
or(TO_CHAR(SIGN_OUT,'d')='7'or to_char(SIGN_OUT,'d')='1'),1,0)
SUM(IS_LATE)
SUM(IS_OVERTIME)
SUM(IS_EARLY)
SUM(IS_WEEKEND)
检查数据
第五部分 DM层员工考勤统计表
A: 应上班天数：
计算统计当月日期数据，做日历表1为工作日，0为非工作日，统计该月工作天数，不考虑节假日
B：实际上班天数：
在工作日只要有打卡才算实际上班，不考虑一次打卡两次打卡三次打卡情况
C: 矿工天数：
应上班天数-实际上班天数=矿工天数
D: 迟到次数：
从明细表中，得到迟到标识进行统计
E：迟到时长：
在明细表，打卡时间超过9点的计算 每一次迟到时长（单位：分钟）
计算方法： 两个日期相减得到天数再* 24* 60 得到分钟 (Oracle的计算方法)
然后将该员工一个月的所有迟到时长汇总
F: 早退次数：
从明细表中，得到早退标识进行统计
G: 早退时长:
在明细表，打卡时间在下午5：30分以前算早退 计算每一次的早退时长（单位：分钟）
计算方法： 两个日期相减得到天数再* 24* 60 得到分钟 (Oracle的计算方法)
然后将该员工一个月的所有早退时长汇总
H: 周末加班次数
从明细表中，得到周末加班标识进行统计
创建日历表,并统计出当月应上班天数
-创建日历表
CREATE TABLE rili(day_id DATE);
--把每月的日期插入日历
BEGIN
FOR i IN 0..365 LOOP
INSERT INTO rili VALUES(to_date('2019-01-01','yyyy-mm-dd')+i);
END LOOP;
END;
--当月应上班天数
SELECT to_char(day_id,'yyyy-mm')DAY,
SUM(CASE WHEN to_char(day_id,'d') IN (7,1) THEN 0 ELSE 1 END)day_cnt --天数
FROM rili
GROUP BY to_char(day_id,'yyyy-mm')
ORDER BY 1;
创建目标表，DM层员工考勤统计表
-----------创建DM_EMP_WORK_STAT表
create table DM_EMP_WORK_STAT
(
EMPID NUMBER(8) ---员工编号
,ENAME VARCHAR2(20) ---员工姓名
,MONTH_ID VARCHAR2(7) ---考勤月份
,Work_Day_Cnt NUMBER(10) ---应上班天数
,Away_Day_Cnt NUMBER(10) ---旷工天数
,Late_Day_Cnt NUMBER(10) ---迟到次数
,Late_Tim_Len NUMBER(10) ---迟到时长
,Early_Day_Cnt NUMBER(10) ---早退次数
,Early_Tim_Len NUMBER(10) ---早退时长
,Is_Weekend_Cnt NUMBER(2) ----周末加班次数
)
IIF(TO_CHAR(DAY_ID,'d')='7' or to_char(DAY_ID,'d')='1',0,1)--获取工作日
to_char(DAY_ID,'yyyy-mm')--获取月份
连接EDW层的员工考勤明细表和日历表
接下来计算每个员工的迟到的次数和时长，早退的次数和时长，周末加班次数
iif(IS_EARLY=1,
DATE_DIFF(SIGN_IN,to_date(to_char(SIGN_IN,'yyyy-mm-dd')||'09:00','yyyy-mm-dd HH24:mi'),'MI')
--用于计算两个日期中间相隔多少分钟
,0)
iif(IS_LATE=1,
DATE_DIFF(SIGN_IN,to_date(to_char(SIGN_IN,'yyyy-mm-dd')||'09:00','yyyy-mm-dd HH24:mi'),'MI')
--用于计算两个日期中间相隔多少分钟
,0)
iif((to_char(SIGN_IN,'d')='7' or to_char(SIGN_IN,'d')='1')
or (to_char(SIGN_OUT,'d')='7' or to_char(SIGN_OUT,'d')='1')
,1,0)--计算周末天数
SUM(IS_LATE)
SUM(IS_EARLY)
SUM(IS_LATE_LEN)
SUM(IS_EARLY_LEN)
SUM(IS_WEEKEND)
OUT_MONTH_DAY-COUNT(EMPID)--旷工次数
完成映射
创建工作流，修改映射的源表和目标表
检验数据
第五部分 DM层员工薪酬统计
各字段口径说明如下：
(1) 迟到在1分钟内，不算迟到。
(2) 迟到扣除=30基数+分钟薪资迟到分钟。 (2个小时以内)
另：迟到超过两小时按迟到半天算（也就是迟到时长超过120分钟，但是小于4个小时，超过四小时按一天算
(3) 分钟薪资＝日薪资/8/60。
(4) 日薪资=月薪资/应上班天数。
(5) 早退扣除，计算方法和迟到扣除计算方法一样。
另：早退超过两小时按迟到半天算（也就是迟到时长超过120分钟，但是小于4个小时)，超过四小时按一天算
(6) 事假扣除＝事假天数日薪。
(7) 旷工扣除，计算方法和事假扣除计算方法一样（就是扣掉一天工资，等同于迟到超过四小时扣的工资）。
(8) 迟到早退不应该算非工作日
(9) 加班补发，是平时上班的两倍
(10) 实发工资=应发工资-迟到扣除-旷工扣除-早退扣除+周末加班补发
创建目标表
-----创建员工薪酬统计表
CREATE TABLE DM_EMP_WORK_SAL_STAT(
EMPID NUMBER(8) ---员工编号
,ENAME VARCHAR2(20) ---员工姓名
,MONTH_ID VARCHAR2(7) ---考勤月份
,fee_late NUMBER(10,2) ---迟到扣除
,fee_away NUMBER(10,2) ---旷工扣除
,fee_early NUMBER(10,2) ---早退扣除
,fee_weekend NUMBER(10,2) ---周末加班补发
,Total_Pay_Amount NUMBER(10,2) ---应发工资
,Final_Pay_Amount NUMBER(10,2) ---实发工资
);
源表来源：员工考勤明细表 工资维度表 DM层考勤统计表
然后计算每个员工每一次迟到、早退、旷工、加班的时长(单位分钟)，基于时长按照口径进行计算应扣除多少工资，统计每个员工的工资(实发工资=应发工资-迟到扣除-旷工扣除-早退扣除+周末加班补发)
IIF(IS_LATE=1,
DATE_DIFF(SIGN_IN,TO_DATE(TO_CHAR(SIGN_IN,'yyyy-mm-dd')||' 09:00','yyyy-mm-dd HH24:mi'),'mi'),0)
--每个员工每一次迟到时长
IIF(IS_OVERTIME=1,
DATE_DIFF(SIGN_OUT,TO_DATE(TO_CHAR(SIGN_OUT,'yyyy-mm-dd')||' 20:00','yyyy-mm-dd HH24:mi'),'mi'),0)
--每个员工每一次加班时长
iif(IS_EARLY=1,
DATE_DIFF(to_date(to_char(SIGN_OUT,'yyyy-mm-dd')||' 17:30','yyyy-mm-dd HH24:mi'),SIGN_OUT,'MI')
,0)
--每个员工每一次早退时长
IIF(DAY_IS_LATE_LEN>0 AND DAY_IS_LATE_LEN<120,30+(DAY_IS_LATE_LEN*SAL/WORK_DAY_CNT/6.5/60)
,IIF(DAY_IS_LATE_LEN>=120 AND DAY_IS_LATE_LEN<240,SAL/WORK_DAY_CNT/2
,IIF(DAY_IS_LATE_LEN>=240,SAL/WORK_DAY_CNT,0)))--每个员工每一次迟到应扣工资
IIF(DAY_IS_EARLY_LEN>0 AND DAY_IS_EARLY_LEN<120,30+(DAY_IS_EARLY_LEN*SAL/WORK_DAY_CNT/6.5/60)
,IIF(DAY_IS_EARLY_LEN>=120 AND DAY_IS_EARLY_LEN<240,SAL/WORK_DAY_CNT/2
,IIF(DAY_IS_EARLY_LEN>=240,SAL/WORK_DAY_CNT,0)))--每个员工每一次早退应扣工资
AWAY_DAY_CNT*SAL/WORK_DAY_CNT --每个员工每一次旷工应扣工资
WEEKEND_LEN*2*SAL/WORK_DAY_CNT/6.5/60 --每个员工每一次周末加班应扣工资
IS_OVERTIME_LEN*2*SAL/WORK_DAY_CNT/6.5/60 ----每个员工每一次平时加班应扣工资
SUM(SAL_DAY_IS_LATE_LEN) --旷工迟到工资
SUM(SAL_OUT_IS_EARLY_LEN) --旷工早退工资
MAX(SAL_AWAY_DAY_CNT) --旷工扣除工资
--总的加班工资
SUM(SAL_IS_OVERTIME_LEN)+MAX(SAL_WEEKEND_LEN)
--实发工资
SAL-SUM(SAL_DAY_IS_LATE_LEN)-SUM(SAL_OUT_IS_EARLY_LEN)-MAX(SAL_AWAY_DAY_CNT)+sum(SAL_IS_OVERTIME_LEN)+max(SAL_WEEKEND_LEN)
DM层员工薪酬统计映射关系，创建任务，修改对应的映射关系，创建工作流，通过任务启动工作流。
检验数据
到此ODS层的源表数据、EDW层的员工考勤明细表、DM层的员工考勤月统计表、员工考勤统计表、员工薪酬统计表已完成。
根据ODS层、EDW层、DM层的关系，绘制出工作流
清空表数据
TRUNCATE TABLE bi_ods.ods_emp_work
TRUNCATE TABLE bi_edw.edw_emp_work_detail_temp
TRUNCATE TABLE bi_edw.edw_emp_work_detail
TRUNCATE TABLE bi_dm.dm_emp_work_month
TRUNCATE TABLE bi_dm.dm_emp_work_stat
TRUNCATE TABLE bi_dm.dm_emp_work_sal_stat
通过任务启动工作流，监控工作状态，查看日志，检查数据
员工考勤ETL项目开发已完成(后续按照不同维度、指标作进一步需求的分析)
