基于異構數據存儲的車務數據分析系統開發方法研究

李勝蘭　上海鐵路局金華車務段

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　展望

當然，目前只是針對一、兩年內近期的數據進行分析與呈現。其實我們還可以進一步深入挖掘多年的運營數據，對于身處大數據時代的企業而言，成功的關鍵還在于找出大數據所隱含的真知灼見。“以前，人們總說信息就是力量，但如今，對數據進行分析、利用和挖掘才是力量之所在。”