本项目进行电信计费系统开发，基于ETL工具Informatica，SQL，数据库Oracle和PL/SQL进行开发，数据来源微信公众号数据爱分享的数据。
小王子
业务需求分析：
电信运营情况分析：从各业务系统数据(用户表、地区表、通话表)分析公司运营情况（本项目只分析通话，暂不分析流量）
报表结果：
统计各维度与各指标的用户量与运营收入情况
维度（时间，地区，产品）
指标（用户数，运营收入）
电信计费系统数据
用户表(oltp)
地区表(oltp)
通话表(oltp)
产品表(file_csv)
开发流程
OLTP------->ODS-------->EDW--------->DM
计费系统开发流程及开发逻辑：
A：地区表处理
B：开发过程
1： 先计算每个号码每个月的累计通话时长
SQ 组件来进行计算
2： 关联客户表（得到用户所使用的产品及地区）,
关联产品表（得到免费通话时长，及通话计算标准）
计算每个号码每个月的费用
C: 根据地区维度，产品维度，来统计用户数及整个收入费用
业务系统数据抽取至 ODS 层
Oracle用户下用户表(oltp)、地区表(oltp)、通话表(oltp)以及产品表(file_csv)抽取至ODS层
创建任务，修改任务映射源表与目标表的连接数据库层，通过任务启动工作流
A：地区表处理
地区维度表进行相应的处理
将地区分为 区局--->端局(镇)--->社区
SQL开发思路分析
select * from ods_department where dept_type_no=5;--社区
select * from ods_department where dept_type_no=4;--端局
select * from ods_department where dept_type_no=2;--区局
将区局、端局、社区整合到地区维度表
select quju.dept_id,quju.dept_name,
duanju.dept_id,duanju.dept_name,
shequ.dept_id,shequ.dept_name
from
(select dept_id,dept_name,level_no
from ods_department where dept_type_no=2)quju,--区局
( select dept_id,dept_name,level_no
from ods_department where dept_type_no=4)duanju,--端局(镇)
( select dept_id,dept_name,level_no
from ods_department where dept_type_no=5)shequ--社区
WHERE substr(duanju.level_no,1,3)=quju.level_no
and substr(shequ.level_no,1,6)=duanju.level_no
ETL工具informatica开发过程
创建地区维度表，导入到informatica的目标源
CREATE TABLE edw_department_dim
(
quju_id VARCHAR2(40),
quju_name VARCHAR2(40),
duanju_id VARCHAR2(40),
duanju_name VARCHAR2(40),
shequ_id VARCHAR2(40),
shequ_name VARCHAR2(40)
)
将源表与目标表拖入mapping，编辑SQ组件，保留和目标表相同的字段(保留id和name)，记住端口字段顺序与SQL输出字段保持一致(即目标表quju端口是第一顺序，与SQL的输出字段quju是第一顺序保持一致)，连接映射关系，ctrl+s保存
创建任务，修改任务映射连接相应的数据库层，保存，连接任务，启动工作流
检测工作流执行完毕，检查目标数据
B:用户费用开发过程
SQL开发思路分析
在通话记录表中取其中的一个用户的一个月的通话记录来做测试计算，使用使用窗口函数的聚合函数sum()，以电话号码、月份进行分组，以日期和开始通话时间进行排序，统计出用户每月的累计通话时长
SELECT call_id,
call_date,
phone_no,
begin_time,
time_long,
SUM(time_long)OVER(PARTITION BY phone_no,to_char(call_date,'yyyy-mm')
ORDER BY to_char(call_date,'yyyy-mm-dd'),begin_time)leiji
--使用窗口函数的聚合函数sum()，以电话号码、月份进行分组
，以日期和开始通话时间进行排序，统计出每个用户每月的累计通话时长
FROM ods_Call_Record
WHERE phone_no = '13421728761' AND to_char(call_date,'yyyy-mm')='2018-03'
--测试，一个用户一个月的通话时长
查看数据，可以知道号码为13421728761的用户，在2018-03月份中的通话时长为218分钟
该号码的用户在用户表(ods_cust_info)中查询套餐为4G58，在产品表中查询该套餐免费通话时长为30分钟和每分钟0.19的话费计费
获取该号码用户累计的通话时长和话费，知道该用户的免费时长为30分钟和每分钟计费为0.19/min
WITH CALL_time_long AS (SELECT call_id,
call_date,
phone_no,
begin_time,
time_long,
SUM(time_long)OVER(PARTITION BY phone_no,to_char(call_date,'yyyy-mm')
ORDER BY to_char(call_date,'yyyy-mm-dd'),begin_time)leiji
FROM ods_Call_Record
WHERE phone_no = '13421728761' AND to_char(call_date,'yyyy-mm')='2018-03')
SELECT phone_no,
call_date,
begin_time,
time_long,
leiji,
CASE WHEN leiji>30 THEN --免费通话时长为30分钟
CASE WHEN (leiji-30-time_long)<0 THEN (leiji-30)*0.19
ELSE time_long*0.19 END --累计时长-30-当次通话时长小于0,说明当次通话时长还有免费时长，大于0说明当次通话时长没有免费时长
ELSE 0 END call_fee --在免费时长里没有产生费用
FROM CALL_time_long
ETL工具Informatica开发流程核心思想与步骤
1、计算累计通话时长
创建EDW层通话话费表edw_call_fee，导入目标表
create table EDW_CALL_FEE
(
phone_no VARCHAR2(20),
month_id VARCHAR2(20),
product VARCHAR2(20),
adress VARCHAR2(40),
fee NUMBER(20,2),
basic_fee NUMBER(10)
)
需要源表ods_call_record、ods_cust_info、ods_product，同目标表一起拖入mapping进行开发，编辑ods_call_record的SQ组件，实现通话时长累计功能。
2、实现多表连接，获取相应需求字段
关联客户表（得到用户所使用的产品、地区),
关联产品表（得到免费通话时长、通话收费标准）
计算每个号码每个月的费用
获取月份
转化号码为字符串格式
第一步ods_call_record与ods_cust_info相连接，获取用户的地区(社区)和其使用的产品，使用joiner组件，并添加连接条件。
第二步与ods_product相连，获取产品的套餐以及套餐的免费时长与通话费用，使用joiner组件，并添加连接条件，
特别注意连接条件的字段要数据类型一致
连接表时只保留需要的字段，不需要就不相连
3、计算每次通话的费用
通过获取免费通话时长MF_TIME、通话费用CALL_MINUTE_FEE计算每次通话费用
iif(LEIJI>MF_TIME,
iif((LEIJI-MF_TIME-TIME_LONG)<0,(LEIJI-MF_TIME)*CALL_MINUTE_FEE,TIME_LONG*CALL_MINUTE_FEE),0)
下一步通过汇总组件按照(用户、月份、地区、套餐、基本月租)进行分组，计算每个用户每月的通话费用
sum(fee)+BASIC_FEE --当月的话费费用加上月租
连接映射，完成mapping
创建任务，修改任务的源表与目标表连接相应的数据库层，创建工作流，启动工作流
监控工作流流程，执行成功，检查数据
DM层 根据地区维度，产品维度，来统计用户数及整个收入费用
和已经处理的地区表EDW_DEPARTMENT_DIM关联，求每个地区每个月各套餐使用用户数及合计消费
在DM层创建DM_DEPT_CT_COMM表，导入目标源
create table DM_DEPT_CT_COMM
(
month_id VARCHAR2(10),
area VARCHAR2(40),
prod_id VARCHAR2(15),
num_total NUMBER(10),
basic_fee NUMBER(10),
total_call_fee NUMBER(15,2)
)
将源表与目标表导入mapping
地区维度表与已完成的edw_call_fee表关联，实现地区是区局的用户数及收入费用的统计
对月份、区局、套餐进行分组，统计出用户数、总的收入费用
创建任务，修改源表与目标表的数据库层，创建工作流，通过任务启动工作流
检查数据
ODS层、EDW层、DM层开发全部完成，创建工作流，把各层的任务导入工作流，按照执行顺序连接任务ODS-->EDW-->DM
监控执行情况，查看日志
任务执行成功，数据全部导入数据库
到此ODS层，EDW层，DM层已开发成功，后续可根据不同维度，指标进一步分析！
网友评论