某制药公司ETL项目
一、项目背景
近几年，制药行业发展迅猛，企业业务数据急剧膨胀。运用BI技术进行数据分析、提供决策支持，已成为行业内各竞争对手商海夺金的法宝。
作为国际知名品牌的某某制药公司，其机构层次和业务逻辑之复杂可想而知，牵扯到PC_ETMS、OBU_ETMS、PC_Commercial flow、OBU_Commercial Flow、HRIS、SAP、IMS、CPN等十几个业务系统，每天从全国各地汇总上来的业务信息数据量大、格式不一。要想在此基础上搭建BI平台，首先要建立一套完备、稳定、智能化的数据抽取、转换和加载流程，把这些看似独立无关的业务数据有机整合到一起，最终形成高质量、无冗余、具有商业分析价值的数据仓库。
二、解决方案
1、整体架构图
2、架构说明
(1)、SS_ODS_ETL：此环节的任务是把源系统（包括四个OLTL系统和一个平面文件源）的数据抽取到ODS（Operating Data Storage）中，整合到一起，并派生出三个字段，分别标识每一条记录的源系统标签、在源表中的位置、加载时间。这样做的目的是为了掌握数据的抽取情况，看到ODS中的每一条记录都能够马上判断出该记录来自哪个业务系统、在源表中的位置、是什么时间抽取的。例如，Brand相关数据的抽取流程如下图：
(2)、ODS_MDM_ETL：此环节的任务是把ODS中的主数据（如医院和员工的信息）抽取出来，通过程序人工的进行干预，对这些主数据及其hierarchies进行匹配和整合。
(3)、ODS_DW_ETL：此环节的任务是把ODS中的数据按照定义好的Mapping规则，将数据加载到数据仓库中。例如，在四个业务系统中都有关于“城市”的记录，但对同一个城市的描述却不尽相同，如“北京市”、“北 京”、“BeiJing”、“BJ”。如果不对这些信息进行统一，直接加载到DW中，那以后基于DW建立CUBE进行多维分析时，得出的结果必然是错误的。从ODS到DW这一环节的任务就是要对重复的信息通过Mapping表进行匹配，在DW中对同一个实体只有一个唯一的命名和ID，如上例，在DW中我们统一规范为“北京”。
ODS中的Brand表
DW中的Brand表
(4)、EXCEL_ODS_ETL：除了OLTP系统的数据源外，还有许多平面文件数据源，主要是EXCEL文件。为实现ETL流程的全自动化，此处我们编写了一个C#程序来实现数据提取。客户只需把包含新数据的EXCEL文件放到服务器的指定目录下，程序将自动检测到有新文件上传，并按照指定的抽取规则进行数据抽取，先将数据加载到ODS数据库的临时表中，再基于此临时表将数据加载到ODS的维度或事实表中。
平面文件格式图例
平面文件数据抽取流程图
三、项目特点及成效：
1、设计严谨。考虑到客户业务逻辑复杂多变，各维度信息可能每天都会有变化，为此我们将ODS和DW中的维度信息都做了缓慢变化处理，并在DW中保存每一条维度记录的历史数据，以准确的适应复杂的业务查询，为前端的数据分析和展现提供准确的底层数据。
2、数据进入DW之前先进入Mapping表，通过一个存储过程对Mapping表里的数据和DW中的数据进行匹配，可自动解决大部分的因业务系统需求差异而造成的数据不一致问题，避免了通过人工手段在Mapping表中逐行的进行数据验证，节省了大量人力资源。
3、兼容性强。直接在数据仓库中建立了MAT、YTD等业务关键绩效指标，可供SSAS、Congnos等多种前端分析工具直接访问。
4、易于配置。整个流程全部用组件实现，并且在每个Package中都使用了XML包配置和变量参数，需要移植平台或更改数据库配置时，无需打开工程文件，只要简单修改包配置文件的相关参数值即可。
5、调度自动化。可自定义抽取策略，通过SQL Server的Agent任务调度组件，可实现从数据源到数据仓库的全自动化、无人值守的数据抽取、转换、加载。
四、技术平台
1、Microsoft SQL Server 2005关系数据库引擎，用于存储源系统、ODS、DW中的数据
2、Microsoft SQL Server 2005 Integration Services组件，实现数据抽取、转换、加载功能
3、Microsoft SQL Server 2005 Agent组件，提供任务调度功能，实现IS包的自动运行
|
