基于异构数据存储的车务数据分析系统开发方法研究

李胜兰　上海铁路局金华车务段

1　背景

随着铁路信息化的迅速发展，各业务部门都设有专属的信息系统，业务内部逐渐开始信息共享，如清算运统一可以从十八点系统和TMIS系统获取数据；但不同业务之间的信息系统是异构的，各系统的数据存储分散独立：客票用的是Sybase数据库且与我们办公网网络物理隔离，管内的运营数据分布在车务和货运中心的两台Oracle服务器上，运输中转数据分布在金东站和中小站两台Oracle服务器上，形成一个个信息孤岛。对于车务管理岗位来说，最好能把管内各业务情况同时一览无遗，即一张报表总览客运的、货运的、中转停留情况等数据，从全局分析、判断、做决策。

那么如何集成这些信息孤岛中所需的数据？如何在多个异类数据存储区中提取数据并合并到单个一致的数据集中？这是开发我们的车务数据分析系统的关键所在。

之前，本单位曾经做个一个信息共享系统，它定时用第三方软件PowerBuilder的数据管道工具来收集各个异构数据存储区的数据，并全部复制存储在本地数据库。这样大规模地多次重复复制，既占用网络带宽又浪费本地存储资源；加上PB近年来已经基本淘汰；且也成为新的64位服务器高性能的瓶颈。所以，找出一种新的解决方案势在必行。

2　ETL技术

充分整合各种需要的数据资源进行再构建并呈现，是信息系统建设一个重要领域。技术实现上，ETL（Extract,Transform,Load）抽取、转换及装载技术是一种方法。

不同业务系统不同时期的系统之间数据模型不一致，各个异构存储中抽取出来的数据在集成前必须进行转换后才能装载，这就需要应用ETL转换。常用的转换有以下种：

（1）空值处理：捕获字段空值，进行加载或替换为其他含义数据，并可根据字段空值实现分流加载到不同目标库。

（2）规范化数据格式：实现字段格式约束定义，对于数据源中时间、数值、字符等数据，可重新自定义加载格式。

（3）拆分数据：依据业务需求对字段可进行分解，提取字段某部分信息。

（4）验证数据正确性：利用Lookup及拆分功能进行数据验证。

（5）数据替换：对于因业务因素，可实现无效数据、缺失数据的替换。

市场上，ETL产品有微软的SSIS（SQLServerIntegration Services）、Informatica 的 PowerCenter，SAPBusinessObjects DataIntegrator等等。其中SSIS是一种高性能的数据集成解决方案，它可以连接各种各样的数据源，包括各种关系型数据库及文档数据库。我们的车务数据分析系统是基于.NET框架，数据库管理工具采用的是SQLServer，所以SSIS的确是个不错的选择。下面将详细介绍SSIS。

3　SSIS介绍

SSIS（SQLServerIntegrationServices）就是 SQLServer集成服务的简称。安装SQLServer时勾选上SSIS，就有SSIS开发工具。

SSIS包括数据集成的一条龙服务：有用于提取和加载数据的数据源和目标；用于清理、聚合、合并和复制数据的转换；用于对 IntegrationServices对象模型编程的应用程序编程接口 (API)，以及用于生成和调试包的图形工具和向导；用于执行工作流函数（如 FTP操作）、执行 SQL语句或发送电子邮件的任务等。

SSIS可以连接到各种各样的数据源。可以使用 .NET和OLEDB访问接口连接到关系数据库，还可以使用 ODBC驱动程序连接到多个早期数据库。包还可以连接到平面文件、Excel文件和 AnalysisServices项目。

SSIS包含一些源组件：内置任务、容器、转换和数据适配器，您无需编写一行代码，就可以创建 SSIS解决方案来使用ETL和商业智能解决复杂的业务问题。这些组件负责从所连接的数据源中的平面文件、Excel电子表格、XML文档和关系数据库中的表及视图提取数据。再用SSIS包含的转换功能对数据进行转换。数据转换为兼容格式后，就可以将其物理合并到一个数据集中。这个数据集可以是平面文件、原始文件和关系数据库时所用的目标。数据也可以加载到内存中的记录集中，供其他包元素访问。

4　技术实践

我们利用SSIS来实时增量抽取业务数据，保证业务数据的快速和时效性。具体过程为建立序列容器，并且添加数据流任务，TMIS数据定时为每 5分钟增量抽取一次，18点统计数据每日七点后增量抽取一次。

SSIS的工作区见图1。

图1　SSIS设计界面图

4.1　构成

SSIS最基本的组成是包，每个包主要由以下几部分构成：

（1）连接管理器，位于图1的中下方。

（2）控制流任务（ControlFlowTasks）及容器（Container）等，位于图1的中间主窗口。多个控制流任务可以放在一个容器内，可以以容器为单位测试执行情况。

（3）数据流的源、转换、目标处理任务(DataFlowTasks)等，双击控制流任务可以进入具体的数据流设计页面，图1左边的工具箱也换成数据流相关的工具（如图2）。

图2　数据流设计界面图

（4）语法：变量、表达式、单/多分支、循环、连接线、作用域等，在数据流设计中具体应用。

（5）日志提供程序。SSIS包含日志记录功能，可在包或各个任务以及容器中添加日志记录功能来监控包执行情况以及进行故障排除，可选择我记录的时间，也可以对单个包创建多个日志。

4.2　具体应用步骤

（1）新建项目和包：在VisualStudio中创建一个IntegrationServices项目，也可在SQLServer2008中打开SSIS并新建项目。在该项目中创建包，后缀名为.dtsx。

（2）建立连接管理器：在连接管理器中，添加、配置外部数据源新建数据源，即建立到各业务的多个数据库的连接，TMIS数据相关的有三个：中小站TMIS、金东TMIS、衢化TMIS，18点统计数据有两个：本段18点(客收、客发、停时、中时、运用车、金东办理辆数等指标)和货运中心18点（涉及一些装卸车数和货物发送）。这些都是Oracle数据库，所以需安装32位的Oracle客户端软件，配好本地服务名后，新建MicrosoftOLEDBProviderforOracle类型连接管理器。

（3）在连接管理器中，添加、配置目标数据源，由于车务数据分析系统采用的数据库是SQLServer，所以新建OLEDB类型连接管理器。

（4）在“控制流”ControlFlow面板中，新增、配置“数据流任务”DataFlowTask。

（5）双击该数据流任务，进入“数据流”DataFlow面板，添加数据源、数据流转换及数据流目标过程，如图2。

（6）调试包，全为绿色即为成功。

（7）部署包。推荐修改配置属性启用包配置，然后再生成安装部署文件。接着执行xxx.SSISDeploymentManifest弹出包的安装向导一步步设置。SSIS包一般有两种部署方式：部署到文件系统和部署到SQLServer。部署到文件系统，操作更简单，配置修改也比较容易；而部署到SQLServer安全性更高。

（8）包的调度。通过SQLServeAgentJOB调用类型“SQLServerIntegrationService包”，并选择该包的依赖配置文件（非常重要，以后包无论通过什么用户运行，都会重新加载这个配置文件，使用这个数据库连接密码，否则包可能执行不成功。）执行JOB，查询是否成功，并查看包执行后的结果。

5　实践总结

通过SSIS连接Oracle数据库时走了些许弯路，也积累了一些经验，在此与大家分享：

（1）必须注意SSIS是32位开发工具，即使安装64位的SQLServer。而 32位的工具只能 load32位的 oracle provider。所以相应的Oracle客户端工具也必须安装32位，在SSIS中才能连接上数据源。

（2）Linkedserver去 连接Oracle的时候，首先会读取tnsname.ora文件，然后读取sqlnet.ora文件，然后用SQLServr.exe直接去连oracle,连接成功以后会再次读取tnsname.ora文件，然后开始载入oracleOLEDB的DLL文件。在64位操作系统上安装的32位SQLServer中，读到的路径是这样的：(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.137.90.10)(PORT=1521))(CONNECT_DATA=(SID=express)(CID=(PROGRAM=D：ProgramFiles(x86)MicrosoftSQL ServerMSSQL.1MSSQLBinnsqlservr.exe)(HOST=SCT-ITSQLTEST)(USER=SYSTEM))))

（3）在64bit的操作系统上安装32位的SQLServer服务器以及32位的oracle客户端，是可以创建linkedserver和SSIS程序包的。但是，oracle在处理包含括号文件全路径名称时有个bug，这个bug在8i,9i和10g的版本上都存在，会报错ORA-12154或者 ORA-6413，必须通过打oracle的补丁（Bug 3807408）来解决，所以推荐安装Oracle的11g。

（4）报“无法从OLEDB访问接口检索列代码页信息”时，将数据流任务属性中AlwaysUseDefaultCodePage设为True。

（5）注意 varchar类型（非 unicode）装载到 nvarchar(unicode)类型字段时，要加个中间转换插件，把字段类型转换下再插入，或是把源表的字段类型先转换成nvarchar。在SSIS中字符串类型的对应关系如表1。

表1　SSIS中字符串类型对应表

（6）32位的 SSIS包在64位的SQLServer环境执行时要做修改：可以在SSDT里面修改debug选项(Run64BitRuntime=false)，还可以建立job执行的时强制以32位方式运行。

6　展望

当然，目前只是针对一、两年内近期的数据进行分析与呈现。其实我们还可以进一步深入挖掘多年的运营数据，对于身处大数据时代的企业而言，成功的关键还在于找出大数据所隐含的真知灼见。“以前，人们总说信息就是力量，但如今，对数据进行分析、利用和挖掘才是力量之所在。”