ETL

資料處理 Database Marketing (1)

基本介紹

教學目標

初步了解資料庫行銷 (Database Marketing) 的基本概念。

重點概念

資料庫行銷主要是透過客戶資料庫進行分析,以在不同通路產生針對專屬個人化的行銷訊息。其中客戶資料庫主要包括了消費客戶和潛在客戶的個人資料和交易資料,接著藉由資料探勘的應用更進一步達成精準行銷的目的。然而在金融業中許多客戶相關的大量資料皆會匯整至資料倉儲 (Data Warehouse) 中,因此資料庫行銷通常會與資料倉儲進行整合應用,主要會藉由撰寫 ETL 批次程式針對消費客戶的個人資料與交易資料匯入至資料庫行銷的相關系統中進行資料處理,產生不同通路精準行銷的客戶名單。

接著許多客戶資料主要是由互動產生,例如: 開啟電子直效行銷 (eDM) 閱讀內容,並且點擊內容中的超連結。此時若要針對目標族群進行一對一行銷的效果時,就必須將互動產生的回應資料寫入至資料庫中,藉由這些回應資料再進行資料處理與資料探勘等應用,隨時修正消費客戶的相關資料,並且找出潛在客戶的相關資料,也因此又稱此類型的行銷模式為學習型行銷,當客戶回應資料越多則就越了解客戶,客戶就是市場,所以當掌握客戶資料時就是掌握了市場。然而行銷通路非常多元化,例如: 電子直效行銷、簡訊、網站、APP、…等,金融業通常會由許多不同的合作廠商進行相關應用的程式開發,此時藉由撰寫 ETL 批次程式將回應資料寫入至資料倉儲中,以利未來進行客戶整合分析,了解產品的目標族群。

此外金融業除了可以透過資料倉儲了解到產品的目標族群,更進一步以資料倉儲為基礎建立主資料管理 (Master Data Management,MDM) 的架構,針對系統客戶關係管理系統、金融交易系統 、…等與客戶相關資料系統進行主資料整合確保資料品質,以利計算出客戶取得成本、服務成本、貢獻度、…等價值。為了達到此目的就需要藉由撰寫 ETL 批次程式將相關系統中的資料寫入至資料倉儲中,以利計算客戶實際交易的終生價值。

最後目前金融業的行銷考量大部份逐漸由產品佔有率轉換成個人佔有率,簡單來說就是客戶買了任何金融產品,就能藉由資料分析出適當的行銷通路推薦適合該客戶其它金融產品。此外在金融業中每檔行銷活動皆會由許多人合力完成,此時就必須藉由 ETL 批次程式自動化流程加速檢驗與驗證行銷活動的市場成效,立即在第一時間進行調整,以利達到精準行銷。

相關資源

資料處理 Data Integration (1)

基本介紹

教學目標

初步了解資料整合 (Data Integration,DI) 的概念。

重點概念

資料整合主要可以分為企業應用與互聯網應用,有很多的原因會導致企業資料分散在多個不同的資料庫中,例如: 企業併購、部門合併,特定需求的專案、…等,因此在企業進行內部重組時,應該適時的進行調整與對應。此外互聯網中有成千上萬的網站提供特定主題領域的資訊,例如: 影劇、音樂、美食、旅遊、新聞、…等,此時面臨最大的困難為大規模資料庫的整合,資料會由不同的人用不同的語言建立,質次提取資料相當困難,因此會藉由爬蟲的功能擷取出有意義的資訊,當然資料不一定正確且適用,因此需要透過不同的方法針對資料進行組合和排序。所以以資料整合為基礎建立的系統就是為了自行治理和差異結構的資料來特提供統一的存取入口,其中包括查詢處理、差異結構性、自行治理和資料來源的數量等重點處理應用。

接著資料整合目前面臨三個挑戰分別為系統原因、邏輯原因和管理原因,其中系統原因主要為不同的關聯式資料庫系統雖然已經支援 SQL 標準和 ODBC/JDBC 連線,但不同供應商的實作方式還是會有些差異,因此在整合過程中,這些差異就需要進行協調,再著如何有效地針對分散式資料庫和不同效能資料庫執行查詢更是一大挑戰。接著邏輯原因主要為在面對完全相同的資料庫應用需求時,不同人會計計出非常不同的資料庫模型,因此資料來自於多個資料來源時就會產生差異,並且資料的表達格式也有可能不同,例如: 貨幣單化為多國單位、語言為多國語系、…等,因此若把多個不同資料來源進行整合,必須解決彼此之間的語義差異結構的問題。再來管理問題主要為資料存取的共享和匿名等問題,所以資料整合應用是個很難的問題 永遠不會被徹底解決,因此實務上最重要的是設置適當的預期,此外越精確的資料花費的時間越多,但若能讓使用者以較小的時間代價從資料整合系統中獲得更精確的結果,將會是資料整合的目標。

然而資料整合的架構主要為資料倉儲與中介平台<->包裝器<->不同資料來源,在企業中會將多個獨立資料來源的資料載入至資料倉儲中,然後使用者就能以這些資料為基礎進行查詢操作,其中資料主要是透過 ETL 工具定期進行擷取、轉換和載入。然而資料倉儲開發之初並不是以資料整合為目的,而是進行更深入的分析而開發的工具,例如: 將交易系統中的資料經由 ETL 工具進行清理和匯整,並且載入至資料倉儲中以利決策支援查詢之應用。中間平台則是資料仍然儲存於原本的資料來源中,只有在需要查詢時才被存取,其關鍵在於語義對應,主要是將資料來源與中間平台進行關聯,此時資料整合系統就能讓使用者透過中間平台進行查詢之應用。此外資料整合系統的查詢與資料庫的查詢主要有兩個差別,第一為查詢需要從中介平台重寫成對應於資料來源的格式,第二為查詢執行需要根據不同的突發事件自動適應不同的查詢優化與執行。

總結資料整合主要分為企業應用和互聯網應用,企業應用也就是資料倉儲相關應用,例如: 銀行業針對核心系統的資料匯整至資料倉儲中,以利解決跨部門整合查詢分析的問題,重點在於專業領域的知識。互聯網應用也就是網站爬蟲相關應用,例如: 藉由爬蟲處理將互聯網中所有影劇連結和相關資訊匯整至看劇 App 中,重點在網路駭客的技術。以利解決人們看劇的龐大需求,然而未來最有趣的應用則是社交媒體的資料整合應用,不僅僅資料龐大且又要能即時進行分析將會是一大挑戰,例如: 應用社群網站的 API 存取每個人的社交資料進行分析,以利解決個人隱私、趨勢話題、…等問題,重點在於數學理論的應用。

相關資源

資料處理 Operational Data Store (1)

基本介紹

教學目標

初步了解營運資料儲存 (Operational Data Store,ODS) 的概念。

重點概念

企業有許多不同的應用系統,每個系統皆有不同的資料格式,可能是結構化或非結構化的資料,為了業務的需要就必須透過營運資料儲存 (Operational Data Store,ODS) 將應用系統中的相關資料轉換成適當的檔案格式,例如: 平面檔案 (Flat File) 以最小的空間儲存結構化資料,可分為固定長度或可變長度,也就是所謂 CSV 檔,接著載入至資料倉儲進行企業的分析應用與資料管理。

在大多數的企業中主要是由營運環境和資料倉儲環境建構應用於每天進行處理與決策的骨架,其中經常性的資料市集會從資料倉儲出取出以利進行分析。在不同的環境有不同資料結構的類型需求,其本上可以分成線上交易處理資料、整合資料和歷史資料,一般來說線上交易處理資料會經由 ETL 處理轉換成歷史資料和整合資料,其中整合資料主要是以系統記錄 (System of record) 或單一的事實版本 (Single version of truth) 為主進行建立基礎以利分析處理之應用。

然而當我們需要線上交易處理的整合這種不同類型的資料結構時,就會需要營運資料儲存 (Operational Data Store,ODS),企業對需要營運資料儲存主要是因為其應用操作系統無法有效進行整合和難處理,當企業需要整合資料卻無法修改已經存在的相關系統環境時就需要營運資料儲存。此外也能夠透過營運資料儲存將線上資料更新至資料倉儲中,此時營運資料儲存就必須具備高效能的線上交易處理環境。

至於營運資料儲存與資料倉儲皆是以主題導向的整合資料為主,但是營運資料儲存能夠獨立進行更新、刪除和增加的短暫當時的資料,而資料倉儲主要儲存不同時間的長期歷史的資料。因此營運資料儲存根據處理時間的不同主要可以分為三種類型,類型一以毫秒 (Milliseconds) 為時間單位進行資料同步,類型二以小時 (Hours) 為時間單位進行資料同步,類型三以天 (Days) 為時間單位進行資料同步,根據不同的需求選擇適合類型的營運資料儲存的系統環境,一般來說企業皆以類型三的營運資料儲存為主。此外不同來源資料可能會需要進行整合會有前後相依關係的問題,此時在營運資料儲存和資料倉儲之間就會有臨時區域 (The Staging Area) 等待資料完整後才會進行整合轉換。

比較項目 資料倉儲 營運資料儲存 應用操作系統
建立目的 決策支援 即時監控 業務操作
服務對象 企業管理 業務管理 業務處理
儲存週期 長期 短期 即時
處理頻率 非即時 非即時/即時 即時
主要功能 分析功能 分析功能/事務處理 事務處理
技術實作 OLAP OLAP/OLTP OLTP
功能結構 集中 相對集中 分散
資料類型 匯整資料/明細資料 明細資料 明細資料
資料大小

表一、DW、ODS 和 AP 比較表

最後銀行有許多核心系統,例如: Temenos T24 Core Banking、Misys FusionCapital Summit 等針對不同業務處理的核心系統,若要整合出應用分析的戰略報表就必須必過營運資料儲存將核心系統中的原始資料轉換為正規化後資料格式再導入至資料倉儲中,以避免資料倉儲成為垃圾場 (Garbage Dump) 影響分析決策之應用,也就是所謂 ETL 轉換而非 ELT 轉換之資料處理的差別。

相關資源

資料處理 ETL Development (3)

基本介紹

教學目標

初步了解 ETL 批次程式如何委外廠商進行開發。

重點概念

ETL 批次程式開發是能夠進行委外廠商進行開發,前提是要有特殊且可被完整定義的轉換,每個 ETL 專案計劃皆可分成八個階段,分別為:

  1. 建立開發環境
  2. 分析業務需求
  3. 設計邏輯資料對應
  4. 管理資料品質
  5. 建立 ETL 處理
  6. 測試 ETL 流程
  7. 進行 ETL 部署
  8. 維護 ETL 批次

建立開發環境主要會請 DBA 團隊建立一個獨立的開發環境保證資料分析和 ETL 不會受到交易系統的影響,並且在專案開始的階段要記錄建立開環境所需的工具的安全步驟,透過標準文化可以最大化降低未來錯誤的機會和最小化後續增加系統時所帶來的風險。接著儘管在資料建模透過分析已經有很詳細的業務規則,但是 ETL 架構師的職責是分析業務需求實作這些規則,基本上 ETL 架構師和系統分析師會審閱所有的文件,並且和資料建模專家一起討遇到的問題。因此相關人員必須對來源系統和系統內的資料有全面的了解,一定不要縮短業務分析的完成時間,並且在沒有對所有來源系統進行徹底的分析之前,不要開始創建邏輯資料的對應。此外我們可以透過下表記錄業務規則並且分別進行追蹤資料清理或 ETL 的細節處理,不僅是為了程式開發,更多原因是相關文件將會是單元測試、系統測試、QA 測試和使用者接受測試的案例基礎。

編號 業務邏輯 處理方式 來源資料清理 ETL 處理 審核報告 報告傳送
1 員工 ID 必須唯一 ETL處理 N/A - 沒有發現不唯一的員工編號 將會檢查不唯一的員工 ID,若發現相關記錄則會進行處理 報告包括名字、顧用日期和部門等相關員工資訊 員工系統管理團隊

理論上重要的資訊皆要被記載,並且要即時更新保持最新狀態,透過文件可以確保了解資料關係,避免在 ETL 批次處理中找錯誤更有效率,並且要先在文件中定義範圍,其中包括決定 ETL 處理階段分別包括什麼內容,以及相關的業務邏輯、轉換和清理的策略。可是需求一直在變動,因此針對範圍的改變必須透過談判和區分優先等級再進行處理。若分析業務需求階段有缺少細節資訊,則 ETL 架構帥就要針對來源系統進行手工分析,當所有問題都已經解決之後就能設計邏輯資料對應。

接著資料倉儲除了能夠方便的進行查詢之外,其資料是否一致和可信賴,因此定義資料品質評估標準非常重要,主要是分析由 ETL 主管和資料品質專家負責定義資料品質規劃,主要任務為分析來源系統的品質,並且記錄所有確認資料遺漏值,以利確保最大效用的資料經由清理載入至資料倉儲中,一般來說資料的清理可分為二種,第一種是在來源系統中清理資料,第二種是在 ETL 中轉換資料,可是受限於資源所以一般會採用第二種方式進行資料的清理,此時 ETL 處理要能夠提供審核報告列出有問題資料的報表有助於追蹤資料和來源的差異。

再來 ETL 架構師會與 ETL 開發人員一起預演整個邏輯資料對應確保 ETL 開發人員在開發之前就能夠理解完整的需求,接著進行 ETL 批次程式的開發,此時不論是寫 SQL 或專門的 ETL 工具皆必須經過開發和測試,並且在轉移之前所有最終資料必須進行驗證成功。
此外若要同時交付 ETL 批次程式給開發人員時,ETL 架構師通常需要準備 ETL 的建立順序文件作為開發指南,其中包括資料表名稱、ETL 處理順序和相關描述,此時理應就能外包給廠商進行開發。然而 ETL 專案開發每個階段分應用進行三種測試,分別為單元測試 (UT)、品質保證測試 (QA) 和使用者接受測試 (UAT),當在測試新的 ETL 處理過程的時候,需要確保對已知資料問題和來源系統異常的情況進行使用者接受測試,以利提高使用者滿意度。

最後將 ETL 批次程式部署至正式環境中,並且進行維護 ETL 批次定義透過 E-mail 將審核報告傳送給相關系統人員,此外 ETL 主管則需要有能夠追蹤需求改進、Bug 修改或確認範圍修改的處理流程,因此 ETL 管理工具最好提供下述資訊提供 ETL 主管參考以利進行批次程式追蹤管理。

  • 主題範圍
  • 請求日期
  • 變更描述
  • 優先權
  • 變更類型
  • 需求狀態
  • 開啟狀態
  • 交付人
  • 負責人
  • 問題版本
  • 修正後版本
  • 關閉日期
  • 功能描述
  • 技術性描述

總結若能夠以專案方式提供特殊且可被完整定義的轉換文件理應就能請委外廠商進行 ETL 批次程式的開發、測試與監督。

相關資源

資料處理 Perl (1)

基本介紹

教學目標

了解如何透過 Perl 程式語言撰寫 ETL 批次程式。

重點概念

企業主要是以 Windows 平台之個人電腦為主,因此會透過編輯工具 (例如: UltraEdit) 以 SFTP 方式進行遠端程式碼的撰寫,再透過遠端工具 (例如: Putty) 連線至 Unix 主機直接執行 Perl 批次程式碼並且進行除錯。Perl 全名為 Practical Extraction and Report Language ,也就是實務擷取與報表語言,其開發者為 Larry Wall 是一位語言學家,語言學直接想到不是單字就是句子,透過單字和句子拆解和組合就能在日常生活中表達更豐富的內容,其所代表的意義完全取決於句子的結構、語意和文章中實際的位置,因此 Perl 能夠以言簡意賅地方式表達想法。

在 1987 年 Perl 被發表目前已經能夠支援 Unicode 萬國碼,其最大的特色性就是以 C 語言為基礎開發的腳本語言 (Script),不需編譯直接執行,此外 Perl 不需要宣告變數即可使用,可是建議使用 use strict 強制使用變數一定要宣告,此外 Perl 可以搭配 DBI 函式庫依據資料表動態產生變數,變數只是便於保存某些東西的地方,必須有個名字,好讓我們回來取用時知道何處尋找某個特定物件,並且為了要能立即有用的方法就是按照變數所能存放資料的類別分類,類似英文名詞中的單數與複數之分,在 Perl 中會將數字和字串以單數變數儲存,單數變數稱為純量 (Scalar),將數字列和字串以複數變數儲存,複數變數稱為陣列 (Array) ,且在宣告變數時會以「$」前置符號告訴 Perl 為單數變數,以及會以「@」前置符號告訴 Perl 為複數變數。

然而若要透過 Perl 設計以 Teradata 資料倉儲為基礎的 ETL 批次程式框架,必須具備藉由 Perl 透過 Teradata BTEQ 命令執行 SQL 陳述式和藉由 Perl 將批次程式進行模組化的功能,也就是能夠將程式邏輯與查詢語法獨立個別檔案,彼此之間的關聯可以透過檔案存取的方式處理,宣告共用的自動依環境轉之變數藉由子程式的方式載入應用,以及針對不同處理階段獨立個別檔案再透過 ETL 工具整合為可行的批次程式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# Perl 透過 BTEQ 命令執行 SQL 陳述式
#!/usr/bin/perl

use strict;

my $bteq_rc = open(BTEQ, "| bteq");

unless ($bteq_rc) {
print "無法正常驅動 BTEQ 命令。\n";
return -1;
}

print BTEQ << END_OF_BTEQ;

.SET ERROROUT STDOUT

# 相關 SQL 陳述式,其中可以透過 ${變數名稱} 自動置換 SQL 陳述式的內容
SELECT * FROM DBC.TABLES;

END_OF_BTEQ;

此外 Perl 還能夠搭配 DBI 函式庫執行 SQL 陳述式,並且透過資料表為基礎動態建立變數,更進一步動態修改批次中的 SQL 陳述式,也就是說 SQL 陳述式會根據資料表中的對應值進行動態調整,如此一來就能夠減少因為報表需求的頻繁更新,導致因為上線流程繁鎖所產生的困擾,但此應用只建議用於報表用途的批次程式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# Perl 透過 DBI 執行 SQL 陳述式
#!/usr/bin/perl

use strict;

my $hostname = "主機名稱";
my $username = "帳號";
my $password = "密碼";

my $dbh = DBI->connect("dbi:Teradata:${hostname}", $username, $password)

unless ( defined($dbh) ) {
print "無法正常連線至資料庫。\n";
return -1;
}

$sql = " SQL 陳述式";

$sth = $dbh->prepare(${sql});

unless ($sth) {
print "無法正常準備 SQL 陳述式。\n";
return -1;
}

$sth->execute();

# 透過資料表為基礎動態建立變數
while (@row = $sth->fetchrow()) {
my $key = trim($row[0]);
my $value = trim($row[1]);
$value =~ s/\"/\'/ig;
eval ("\${$key} = \"$value\"");
}

$sth->finish();

$dbh->disconnect();

最後 Perl 也支援正規表示法 (Regular Expressions) 和直接執行 Unix 指令等實用功能,因此能夠因應不同系統針對資料進行更強大的擷取應用,接著再搭配 Teradata BTEQ 進行快速載入和應用需求調整「動態」轉換為報表資料表的應用。雖然初學者來說的確很難上手,但在面對企業中龎大且複雜的系統時,以 Perl 為基礎撰寫的批次的確有能力解決所有複雜的問題,並且穩定執行,可惜維護與更新管理將會是其所面臨最大的挑戰。

相關資源