第四章第四章第四章

(1)

第四章第四章第四章

第四章系統規劃與實作系統規劃與實作系統規劃與實作系統規劃與實作

本研究欲證實所設計方法，可以建置一個有效率及穩定的超大型資料倉儲，

利用中華電信固網通聯當作實驗標的進行實作及驗證以建置符合需求的電信固網通聯資料倉儲，不僅能涵蓋原來舊系統功能，而且要較之更有效率、更穩定、

及提供更完整的資訊。因此在系統規劃與實作的過程中，我們必須以舊系統既有功能為依據。本研究先以舊系統開發人員為對象來做需求分析，再以本研究所提出方法來進行資料倉儲的設計及建置。

第一節第一節

第一節第一節需求分析需求分析需求分析需求分析

訪談舊系統開發人員得知現行電信固網通聯需求大約可分兩大類，一是通聯記錄查詢，再者是各營運單位各種業務通話量及時間的統計。另舊系統是分成 32 台資料庫伺服器處理資料載入，這些伺服器在資料倉儲伺服器建置完成後將廢止，所以原資料載入作業亦須重新設計；再加上效能與穩定性必須勝過舊系統。

此即新電信固網通聯資料倉儲的主要需求及目標。電信固網通聯系統需求，再進一步摘要分項整理如下：

1. 現有固網通聯資料載入

原始資料檔案以 FTP 方式傳遞－相關傳輸協定

原始資料檔案規格

固網通聯記錄查詢或 CDR 資料分析需要所需載入資料欄位

資料濾除原則 2. 現有固網通聯記錄查詢

查詢及回報以檔案方式進行－檔案命名規則等

輸出入檔案規格及路徑

資料未到齊回報檔案規格及路徑

(2)

被叫/主叫電話號碼所有可能的形式－

所儲存之電話為交換機執行時形式

3. 現有固網通聯所有實體視域及更新程式，約 15 個如下：

每日通聯統計（day_summary_count）

通聯月報統計（monthly_count）

各交換機長途通聯統計（180x_node_sum）

各交換機撥接通聯統計（dial_node_sum）

02 字頭 IN（全名 I II Intelligent N NN Network）通聯統計（in02_node_sum）

08 字頭 IN 通聯統計（in08_node_sum）

中華電信 IN 通聯統計（chtin_node_sum）

當地、撥接、行動、呼叫器通聯統計（telecom_day_sum）

各類通聯前百大統計（daytop100）

撥接通聯前百大統計（dialuptop）

各營運處長途通聯統計（180x_sum）

各營運處國際通聯統計（IntlTelSum）

國際通聯前百大統計（FixIntlSum）

各營運處當地通聯統計（LocalSum）

各類通聯月報統計（INYYMMreport）

4. 補充現有固網通聯所有實體視域需求規格的釐清步驟如下：

(1)先自研發伺服器取得所有相關程式，確認是最新版本無誤

(2)完成全部相關程式碼詳讀

(3)

(3)自現有固網通聯實體視域程式分析所需要的資料表及資料內容組成方式 (4)利用工具取得現有固網通聯的 schema

第二節第二節

第二節第二節資料倉儲設計資料倉儲設計資料倉儲設計資料倉儲設計

資料倉儲之設計規劃，係依據「通聯系統基本需求」加上「本研究歸納得到之各項原則」，逐項說明如下：

通聯明細 (亦即大部分實體視域之主要基底關聯表)－通聯記錄查詢

各統計資料表(即各實體視域)－各營運單位各種業務通話量及時間的統計

前兩項係根據需求分析的結果，新電信固網通聯資料倉儲涵蓋舊系統兩大類所需資料。

CRTC_LOCK－32 台伺服器資料載入工作改採集中處理後，為平行處理及利於監控，利用 Database Primary Key 唯一限制特性做為 Critical Section Control（圖 4.2.1 中 CRTC_LOCK 之系統別及 MUTEX 欄位），以達到可任意依系統資源使用情形即時調整 Process 數目。此設計之附帶好處是查詢資料庫即可得知資料載入情形（此依據本研究所提出之方法－ Metadata 設計 Mutex 資訊，避免資料重複載入造成錯誤，且有助於掌握系統目前運作情形）。

系統異常－為利於監控，設計系統異常資料表格及所有系統錯誤。無論是資料載入、各營運單位各種業務通話量及時間的統計、甚至是通聯紀錄查詢作業，所有作業執行發生異常時，分作業別區分嚴重等級詳細紀錄錯誤訊息（此依據本研究所提出之方法－Metadata 錯誤資訊之記錄設計，利於系統監控及異常時資料補救）。

XLOG－為有效掌握 5TB 龐大資料量通聯記錄查詢執行效能，乃增加資料

表記載處理起/迄時間、擷取筆數、查詢內容等資訊，以利監控及系統調

校；資料載入及實體視域更新，也都有執行記錄之設計（圖 4.2.1 中通

(4)

聯明細 LOG 及統計 LOG）（即本研究方法所提 Metadata 記載 Process、

執行起/迄時間、筆數等資訊，利於了解執行效能及進行調校）。

通聯明細 LOG－資料萃取載入時，儲存足以更新實體視域之資訊。通聯明細每日新增 5 千萬筆，針對這些資料計算出對各實體視域的影響；避免重新由半年 90 億筆的通聯明細計算。因通聯紀錄數量極為龐大，資料更新要如何及時且快速反應到「各營運單位各種業務通話量及時間的統計」？在資料載入過程中，同時進行萃取出更新會影響實體視域的資訊，

設計成 Metadata 儲存，如此可達快速更新有影響的統計數目。為達此目的，資料萃取載入時，設計一些 Metadata，儲存足以識別各批資料載入實體視域之資訊 (如：在電信業固網通聯，可以用通話時間，但自行設計批號效果更好)。Metadata 對各批資料載入時產生唯一可識別的序號

（圖 4.2.1 中通聯明細 LOG 之序號欄位），同時在主要基底關聯表之每筆資料記錄增加序號欄位來儲存（圖 4.2.1 中通聯明細之序號欄位）。

Metadata 需能提供各實體視域尚有哪些序號未處理的資訊（圖 4.2.1 中統計參數之統計時間欄位及通聯明細 LOG 之執行起時欄位）（即本研究方法所提實體視域維護，當基底關聯表資料量龐大時，避免用重新計算方式更新，應使用 Incremental maintenance，針對異動資料來求算出視域新內容）。

統計參數資料表－為能快速因應多維度資料庫開發新需求，增加系統表格儲存多維度資料庫的相關資訊，用輸入資料方式取代撰寫程式，既可快速完成又可簡化開發、測試、及維護工作，目標撰寫查詢指令即能自動更新各實體視域（圖 4.2.1 中統計參數之 SQL 指令欄位）（即本研究方法所提 Metadata 所提供資訊甚至能自動產生各實體視域異動方法，以利加速及簡化實體視域建立與維護）。

Level1/2/3 選單－動態產生網頁樹狀選單

連線控制－多站台單一登入

網站使用者及作業權限－動態顯現可執行網頁作業

(5)

綜合以上考量，所設計出的新電信固網資料倉儲， Schema 概要圖如下：

圖 4.2.1 固網通聯系統 Schema 概要圖

FK_L1

FK_L1_L2 FK_UMDUSER_RELAT IONS_OMC

FK_USER_ID 載載載載序序檔檔 BLOCK代序 BLOCK數最最最最 ST D筆數 MAC筆數 LOC筆數 ISD筆數 OTHER筆數

NUMBER VARCHAR2 CHAR NUMBER DATE NUMBER NUMBER NUMBER NUMBER NUMBER

發發發發發發通發最最營營營交交交發發發發筆數

CHAR CHAR CHAR VARCHAR2 NUMBER

通通通通代通0 通聯類別

通通通通檔通通通通通通通檔通通通通通 CALL_TYPE_SPECIAL T RAILER_GROUP_CODE

CHAR VARCHAR2 VARCHAR2 CHAR CHAR CHAR

<pk>

發發LOG 程序通執執執最執執執最動動

VARCHAR2 DATE DATE VARCHAR2 CRTC_LOCK

系統別 MUTEX 執起最最

VARCHAR2 VARCHAR2 DAT E

<pk>

國國發發發發0 發發最最營營營通發通通發發筆數最最最最數

CHAR CHAR CHAR VARCHAR2 NUMBER NUMBER NUMBER 發發統數程序名發發最最更更最最停停停停 SQL指指

VARCHAR2 DAT E DAT E VARCHAR2 VARCHAR2

<pk>

系發系系系發通程序通錯錯最最動動錯錯嚴嚴嚴嚴通通通

VARCHAR2 VARCHAR2 DATE VARCHAR2 VARCHAR2 CHAR VARCHAR2

網網網網LOG 網網使代序 IP 功功執最執最動動

VARCHAR2 VARCHAR2 VARCHAR2 DAT E DAT E VARCHAR2 通通通通LOG 序號執執執最執執執最檔檔檔檔檔檔檔檔最最最最最最最最最最總筆數錯錯筆數載載筆數營營營程序代序

NUMBER DATE DATE VARCHAR2 NUMBER DATE CHAR CHAR NUMBER NUMBER NUMBER VARCHAR2 VARCHAR2

<pk>

通通通通檔通通通通通通通檔通通通通通 CALL_T YPE_SPECIAL TRAILER_GROUP_CODE

<pk>

通通通通檔通通通通通通通檔通通通通通 CALL_TYPE_SPECIAL T RAILER_GROUP_CODE

<pk>

通通通通代通通聯類別

通通通通檔通通通通通通通檔通通通通通 CALL_T YPE_SPECIAL TRAILER_GROUP_CODE

<pk>

國國發發發發2 發發最最營營營通發通通發發筆數最最最最數

CHAR CHAR CHAR VARCHAR2 NUMBER NUMBER NUMBER 國國發發發發3 發發最最營營營通發通通發發筆數最最最最數

CHAR CHAR CHAR VARCHAR2 NUMBER NUMBER NUMBER 國國發發發發發發最最營營營通發通通發發筆數最最最最數

CHAR CHAR CHAR VARCHAR2 NUMBER NUMBER NUMBER

通通通通序號交交交發發序通受發序通起發始始起發最最發話最最通發通數通發通通特特特特營特停通

NUMBER VARCHAR2 VARCHAR2 VARCHAR2 CHAR CHAR CHAR NUMBER CHAR CHAR CHAR

<pk>

LEVEL1選選 L1 檔通連連

NUMBER VARCHAR2 VARCHAR2

<pk>

LEVEL2選選 L1

L2 檔通連連

NUMBER NUMBER VARCHAR2 VARCHAR2

<pk,fk>

<pk>

LEVEL3選選 L1

L2 L3 檔通連連

NUMBER NUMBER NUMBER VARCHAR2 VARCHAR2

<pk,fk>

<pk>

營營營代通 OMC代碼 OMC英通檔 OMC通通檔 VALID 公公代序

CHAR VARCHAR2 VARCHAR2 CHAR CHAR

<pk>

網網網網使使用者代號員員代序姓檔性通電發 OMC 選單單嚴

VARCHAR2 VARCHAR2 VARCHAR2 CHAR VARCHAR2 CHAR CHAR

<pk>

<fk>

動營作作使用者代號 L1 L2 L3

VARCHAR2 NUMBER NUMBER NUMBER

<pk,fk>

<pk>

連連載載 ID 網網使代序 IP 更更最最

VARCHAR2 VARCHAR2 VARCHAR2 date

各各種種統統計計資資料料表表

各各種種代代碼碼資資料料表表

WW eebb 使使

用用

統計統計快快速速更更新新平行平行處處理理

(6)

_FACT 發發發發通發始始營營營代通交交交代通發發發發發發發數

VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER

通發通通_DIM 通話種類代碼通發通通英通通發通通通通

CHAR(2) VARCHAR2(50) VARCHAR2(50)

<pk>

發發發檔通通_DIM 通話種類代碼通通通通通通

VARCHAR2 VARCHAR2 VARCHAR2

<pk>

執動營使_DIM 業者代碼營使通通營使英通營運處代碼 ORDERS

VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2

<pk>

始始_DIM 日期年季月假始通

DATE CHAR(4) CHAR CHAR(2) CHAR

<pk>

國國通發通通_DIM 通發通通代通通發通通通通 ORDERS

_FACT 國國國國最國通發最最分公公代通通發通通代通通發通數

VARCHAR2 VARCHAR2 VARCHAR2 NUMBER _FACT 國國通通通發始始營營營代通通發通通代通通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER _FACT 檔長通通通發始始營營營代通營使代通通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER

_Aggregate 各通通通月各通發年月通通營營營代通營使通發發數通發通數最最通發通數

VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER 營營營_DIM

營運處代碼營營營英通營營營通通有有分公公通

<pk>

_FACT 當當通通通發始始營營營代通通發通通代通通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER _FACT 撥撥通通通發始始營營營代通通發通通代通通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER _FACT 執動通通通發始始營營營代通營使代通通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER

_FACT 檔長發發通發年月營營營代通營使代通發發序通通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER

_FACT 國國發發通發年月營營營代通通發通通代通發發序通通發發數通發通數最最通發通數

_Aggregate 國國發發通發年月營營營代通通發通通代通設設數通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER NUMBER

_FACT 呼呼呼通通通發始始營營營代通營使代通通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER _Aggregate

檔長發發通發年月營營營代通營使代通設設數通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER NUMBER

_FACT 撥撥通通撥撥最通發始始營營營代通發發序通通發通數

DATE VARCHAR2 VARCHAR2 NUMBER

_FACT 非撥撥各通通通撥撥最通發始始營營營代通通通發發序通通發發數通發通數最最通發通數

IN通通_FACT 通發始始營營營代通營使代通發發代通通發發數通發通數最最通發通數

DATE VARCHAR2 VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER _DIM2

營營營營運處代碼營營營英通營營營通通有有分公公通

<pk>

_DIM2 始始日期年季月假始通

<pk>

_DIM3 營營營營運處代碼營營營英通營營營通通有有分公公通

<pk>

<pk> 營營營_DIM4

<pk>

_DIM2 國國通發通通通發通通代通通發通通通通 ORDERS

<pk>

<pk> 營營營_DIM13

<pk>

圖 4.2.2 固網通聯系統統計 Star Schema 概要圖

(7)

第三節第三節第三節

第三節系統設計及建置系統設計及建置系統設計及建置系統設計及建置

新系統以位於北中南分公司的三台資料庫伺服器（IBM p570 主機，如圖 4.3.2）

取代原 32 台分散在各營運單位的資料庫伺服器（HP 9000 主機：3 顆 CPU/1GB RAM/資料庫為 Oracle8，如圖 4.3.1）。北區設備有 6 顆 CPU/12GB RAM，負責處理北區分公司所轄 15 個營運處資料；中區設備有 4 顆 CPU/8GB RAM，負責處理中區分公司所轄 9 個營運處資料；南區設備有 4 顆 CPU/8GB RAM，負責處理南區分公司所轄 8 個營運處資料。資料庫系統沿襲舊系統仍採用 Oracle，版本不同 8→9，但資料萃取載入、實體視域更新、通聯明細查詢、及 CDR 資料分析網頁等受資料庫架構改變影響，均需要大幅調整。由於個人對 Oracle 資料庫開發頗具經驗，就由個人做系統重新設計的重大工作。硬體採購、系統建置（Unix 方面，

如 Disk 劃分 Logical Volume、資料庫軟體 Oracle 安裝、Web 軟體 WebSphere 安裝）、系統備份，由中華電信本研究室系統組人員支援。除此之外，所有資料庫及新系統規劃設計和開發建置，幾乎全由個人獨立完成，除了資料表分割的設計

－由於固網通聯資料量龐大，資料庫需採用 Partitioned Table 方式，此技術為舊系

統所採用，故仍由原創者負責繼續沿用。還有一些資料庫存取程式，放在 Shell

Script 中結合度不錯，也是繼續使用。另在 Web 網頁開發部份，由於舊系統 Perl

全部作廢改用 JSP，UI 美化頗費功夫且時程緊迫需於 95 年底前完工，故於 95 年

8 月增派員額二名投入開發（新系統已於 95 年 10 月底提前完工）。

(8)

圖 4.3.1 舊固網通聯系統架構圖

圖 4.3.2 新固網通聯系統架構圖

(9)

系統開發歷程：系統開始進行大約在 94 年 4 月，交通部規定電信業者提供半年通聯明細自 94 年 7 月起施行，當時設備尚未採購，暫時借用中分公司一部 IBM 主機，只有 4 CPU 卻建立三個資料庫（因新系統資料庫架構規劃如此），效能可想而知。在這樣克難環境下，94 年 5 月完成資料載入雛型，其後陸續增加平行處理及系統監控等設計，94 年 7 月完成通聯明細查詢重設計，資料比對及系統修正反覆進行，至 94 年 11 月開始設計開發統計模組。直到 95 年 2 月底新設備驗收完成，95 年 3 月開始 5TB 的資料庫搬遷工作，由於南分公司設備 Switch Gigabyte Port 有問題，異常排除後，搬遷工作隨即完成。95 年 5 月開始 CDR 資料分析網頁重新開發工作。後端資料庫效能調校、實體視域更新引擎強化及修正、統計 Aggregate 設計、及前端網頁改寫…諸多工作，真是令人忙得不可開交。

直到 95 年 8 月增加兩名生力軍幫忙網頁開發，尤其其中一名委外人員，開發經驗豐富，許多想法只需說明一下，她就能瞭解，是系統提前兩個月完工的主因。

系統開發歷程如圖 4.3.3。

圖 4.3.3 固網通聯系統開發歷程

為有助於瞭解新系統各模組之間的關係及整體架構，先就其資料流加以說

(10)

明。程序是交換機 CDR 經過濾除無效記錄和簡單格式化，FTP 到資料庫伺服器，

由三區 UMD 資料庫載入作業篩選有意義資料及匯入資料庫，過程中記錄 mutex

（平行處理用）、 Metadata(統計 Aggregate 更新用)、error 及 log（監控用），三區統計作業利用 Metadata 以 Incremental 方式更新統計 Aggregate，這些資料透過

「通聯明細查詢模組」及三區可客製化的 Web UI「CDR（全名 Call Detail Record）

資料分析網頁」提供給使用者。資料流程圖中各項圖示如下：

圖 4.3.4 固網通聯系統資料流程圖

再進一步將開發工作細分「資料萃取載入」、「通聯紀錄查詢」、「各營運單位各種業務通話量及時間的統計」、「資料庫搬遷」、「固網 CDR 資料分析網站建置」五大部份，逐項說明如下：

第一項第一項第一項

第一項、、、、資料資料資料資料萃取萃取萃取載入萃取載入載入載入

資料庫表格空間－使用大小適中 Raw Device，可加快 I/O 及避免 OS cache

造成異常，故固網通聯資料庫採用 Raw Device 設計資料庫表格空間。另通聯資

料只保留 6 個月，過時資料利用 Oracle 資料庫 Partition 可以直接 Drop 特性，快

(11)

速刪除，故採用分割資料表之設計。Partition 依日期＋OMC（全名 Operation

Maintenance Center）建立，因各 OMC 資料量多寡不一，設計程式於新增 Partition

時，平均分配到各 Tablespaces。如 20060101 使用 T01~T15，20060102 使用 T16~T30…，如此可令 Tablespaces T01~T31 彼此使用率接近。資料刪除時，可直接 Drop 某特定日期之分割區，不需逐筆刪除。此乃沿襲舊系統設計。資料表格空間與 raw device 對映及分割資料表分割區之分佈，如圖 4.3.5：

圖 4.3.5 固網通聯系統 Tablespace 及分割示意圖[中華電信內部資料]

資料來源係各交換機記錄，有許多種規格，甚至某些種類交換機記錄需解碼，經過濾除未接通記錄，萃取出有意義欄位後，傳送到北中南三區固網通聯資料庫伺服器。舊系統有 32 台資料庫伺服器，一台負責處理一個營運處資料載入，

凡送達此設備資料一定是某個營運處的資料，此設備資料庫存放的資料也一定是某個營運處的，不需額外存放營運處資訊，但新系統就不能如此，需依營運處分資料夾擺放。檔名含交換機日期序號等資訊，但無法識別屬於哪一個營運處。新系統資料庫也必需增加記載資料所屬營運處的資訊。

T31 (80GB) T30 (80GB) T29 (80GB) T28 (80GB) T27 (80GB) T26 (80GB) T25 (80GB) T24 (80GB) T23 (80GB) T22 (80GB) T21 (80GB) T20 (80GB) T19 (80GB) T18 (80GB) T17 (80GB) T16 (80GB) n32_01.dbf (8G)

n32_02.dbf (8G) : n32_09.dbf (8G) n32_10.dbf (8G) 統計性報表資料統計性報表資料統計性報表資料統計性報表資料

T31 (80GB) T30 (80GB)

. . . . . . . . T02 (80GB) T01 (80GB) T32 (80GB)

2624GB 資料庫資料庫資料庫資料庫

n34_01.dbf (2G) n34_02.dbf (2G)

: n34_15.dbf (2G) n34_16.dbf (2G) 統計性報表資料統計性報表資料統計性報表資料統計性報表資料 T33 (32GB)

T34 (32GB)

北區分公司北區分公司北區分公司

北區分公司 UMD_DB Tablespace 分割示意圖分割示意圖分割示意圖分割示意圖 2006/07/28

六六六六~七個月份七個月份七個月份七個月份

的通話明細的通話明細的通話明細的通話明細

T15 (80GB)

F20060102_2230 T14 (80GB)

T13 (80GB) T12 (80GB) T11 (80GB) T10 (80GB) T09 (80GB) T08 (80GB) T07 (80GB) T06 (80GB) T05 (80GB) T04 (80GB) T03 (80GB) T02 (80GB) T01 (80GB)

F20060102_2250

F20060102_2900 F20060102_2270 F20060102_2422 F20060102_2500

F20060102_2910 F20060102_2920 F20060102_2950 F20060102_2970 F20060102_3300 F20060102_3400 F20060102_3500

TableSpace

FixCDR table partitioned by date+omc

F20060102_3800 F20060102_3900 F20060101_2230 F20060101_2250

F20060101_2900 F20060101_2270 F20060101_2422 F20060101_2500

F20060101_2910 F20060101_2920 F20060101_2950 F20060101_2970 F20060101_3300 F20060101_3400 F20060101_3500 F20060101_3800 F20060101_3900

F20060103_2230

F20060103_2250

F20060103_2900 F20060103_2270 F20060103_2422 F20060103_2500

F20060103_2910 F20060103_2920 F20060103_2950 F20060103_2970 F20060103_3300 F20060103_3400 F20060103_3500 F20060103_3800 F20060103_3900 F20060104_2230 F20060104_2250

F20060104_2900 F20060104_2270 F20060104_2422 F20060104_2500

F20060104_2910 F20060104_2920 F20060104_2950 F20060104_2970 F20060104_3300 F20060104_3400 F20060104_3500 F20060104_3800 F20060104_3900 F20060105_2230 F20060105_2250

F20060105_2900 F20060105_2270 F20060105_2422 F20060105_2500

F20060105_2910 F20060105_2920 F20060105_2950 F20060105_2970 F20060105_3300 F20060105_3400 F20060105_3500 F20060105_3800 F20060105_3900

: : :

對對對對

對對對

對

映映

映映映映到

映映

到到U

到到到

到到

Unniixx 之之

之之

之之之

之r

raaww ddeevviiccee

資資資資料

資資資資

料料

料料料

料料表表表表分

表表表表

分分

分分分

分分

割割

割割割割：

割割

：：

：：：

：：

日日

日日日日期

日日

期期

期期期

期期

＋＋

＋＋＋＋營

＋＋

營營營營

營營營運運

運運運運處

運運

處處處處

處處處

(12)

由於資料量由 32 台資料庫伺服器集中成三台，如果像舊系統只用一支 Process，每日總計約五千萬筆通聯，可能處理不完。新系統如何達成更佳效率？

例如：如何避免不同 Process 同時處理到相同資料檔? 如何能夠機動視資源及需要隨意增減 Process 數目？如何可以隨時暫停某營運處資料載入、甚至所有 Process 執行後，不會產生資料漏失，也無需額外處理？如何使三區雖資料夾等建置不同但程式能自動調整？這些功能新系統均需具備。

舊系統使用 C 語言呼叫 Oracle API 設計的程式來處理所有資料載入。新系統大量資料載入改採用資料庫系統提供的 Utility－資料庫採用 Oracle，故使用 SQL*Loader，能自動偵測伺服器狀況分散到多個 CPU 同時處理。原本舊系統資料載入仍有使用，但只限於少量資料，例如：當載入完成寫通聯明細 LOG、載入失敗寫系統異常、程序啟動寫 CRTC_LOCK…。新系統載入係使用 Shell 串接一些 Utility 而成，快速匯入資料庫引擎－演算法則如下：

圖 4.3.6 固網通聯系統資料載入引擎演算法則

圖 4.3.6 演算法則，可達成下列目標，說明如下：

(13)

平行處理

常駐程式利用作業系統排程啟動，如已存在則不處理（行 1、21）

一個交換機資料同時只能一支常駐程式做载入，如已存在則處理下一個交換機資料（行 5、6、18）

確保資料正確性及完整性

正確性：利用資料庫的 PK 檢查，確保相同資料不會重複載入

完整性：傳送 CDR 檔時，先送 x.dat（存放欲載入資料），成功後送 x.trl（存放該 CDR 檔之統計數據）；載入程式則先讀取 x.trl，成功再讀取 x.dat，以確保資料的完整性

即時快速載入

使用資料庫提供的大量載入 Utility，以加速處理效能（行 15）

載入前先將 x.trl CDR 檔之統計數據寫入資料庫，檢查是否已存在？

若已存在，代表該 CDR 檔已載入過，直接跳過不處理，以避免 PK 逐筆檢查之時間耗費（行 13）。

可機動增加載入 Process 數目－只需將 x.stop 搬移，Process x 便可以執行（行 12）。

修改參數可縮短休眠時間－常駐程式會隨時讀取參數檔（行 20）。

監控機制

CRTC_LOCK（行 1、5 新增記錄）、通聯明細 LOG（行 14 新增記錄、行 17 更新記錄）、系統異常（行 16 新增記錄）－查詢這些資料表即可掌握系統狀況。

使實體視域最小更新之設計

通聯明細 LOG（行 14 寫序號）、通聯明細（行 15 寫序號）－利用

(14)

序號可達成實體視域最小更新

視系統資源可調整常駐程式的數量（不需修改程式）

常駐程式利用作業系統排程啟動，如已存在則不處理（行 1）。

假設預先設定 20 個排程，當系統資源不足，只需產生 x.stop，Process x 便會停止執行，如此可機動控制載入 Process 數目（行 2）。

必要時可隨時停止執行－x.stop ，不使用 Kill 避免造成資料異常修補

只需產生 Cnn.stop，Process Cnn 便會停止執行（行 2）

只需產生 nnnn.stop，OMC nnnn 便會停止執行資料載入（行 2、12）

系統研發過程中，一些較重大的設計、建置、及異動，列舉如下：

1. 平行運算設計

Critical Region：OMC(營運處)→OMC+MONTH→OMC+DAY→OMC+交換局

Process 數目：

半年資料大量載入時，曾經達北:26(宜蘭:10) 中:8 南:10 / 目前北:9 中:9 南:8

優先順序：上線及新資料優先 2. 異常警告設計

儘量收集系統異常之資訊寫入系統異常資料表

陸續增加寫入的 events

a.作業系統參數(user 可使用的最大 process 數)初始值太小異常 b.系統路徑下空間不足（/var）,無法執行 sort 指令異常

c.資料庫未做 Partition 造成無法載入的異常

(15)

3. 資料庫完整性及正確性檢查

比對「資料檔案數目」及「載入成功及失敗筆數」新舊差異：完成資料驗證工作

撰寫 Function（dayamt）：查詢舊系統某一 OMC 某一天資料檔案數目

撰寫 Procedure（gen_dim_date）：產生日期資料

撰寫 SQL：比對新舊系統各資料檔案載入成功及失敗筆數

撰寫 SQL：查詢新舊系統資料檔案數目差異

新增資料表(diff_fts、diff_dayamt)：儲存前兩項比對差異，以利追蹤及後續處理

找出資料檔「數目」及「載入成功及失敗筆數」差異原因，系統修正及資料補正

撰寫 SQL ：找出新系統檔案短少清單，及 OAYP （全名

OOOperator O AAA

Assistant YYYYellow PPPPage）、VoIP（全名VVVoice V oooover IIIInternet PPProtocol）P

每日資料檔案數目清單

4. 因應現況的調整

SQL*loader 檔名為固定長度，檔名異常會造成「固網通聯明細」資料表序號不正確，將此欄位調到最後面

為避免控制檔為空檔，error log 暴增，程式發現此狀況，將.trl 自動更名為.trlx

避免中間檔案被誤殺，集中目錄控管，且加強處理邏輯控制

發現檔案太多出不來問題，修改為按月讀取以解決此問題

增加跨日關閉檔案另起 Process 之設計

將處理過 CDR 備份目錄改到新增加的磁碟/fbmscdrs/CDRS/complete

(16)

比對新舊系統差異，修改調整設計，以達一致性

為中區營運處（OMC 4500、OMC 4700）切換到新資料庫，完成修改資料載入程式，以提高其處理優先順序

修改資料載入程式，以達「新資料庫需載入舊資料，但新資料需優先處理」的原則

5. 系統調校

SQL*loader 嘗試改用 direct path，因有不能存在 index 限制，不適用（曾嘗試 direct=true，造成北南資料異常 776 筆，撰寫程式補救）

三區資料庫表格空間（UNDOTBS Tablespace）擴增至 40GB，執行速度稍有改善

三區資料庫表格空間（TEMP Tablespace）需擴充至－

北：120GB 中/南：60GB

Oracle 開機參數 parallel_max_servers 調整至－

北：240 中/南：160

6. 每日例行性系統監控及異常處理

資料庫:查系統異常資料表有無異常

作業系統:執行程式（testtrl）看資料檔有無異常 7. 查詢需求；

防止誤刪除或改錯資料，新增資料庫 ROLE 及 USER 專供查詢使用，並 CREATE 相關的 SYNONYM

第二項第二項第二項

第二項、、、、支援支援支援支援隨意隨意隨意查詢－隨意查詢－查詢－查詢－雙向通聯查詢作雙向通聯查詢作雙向通聯查詢作業工作摘要雙向通聯查詢作業工作摘要業工作摘要業工作摘要

固網雙向通聯查詢，事實上只是「客戶資料及通聯紀錄查詢系統（CRIS，全

名 Call Record Inquiring System）」的一小部分，此外還有行動通聯查詢、工作分

派模組、使用者者管理、Web UI、其他系統介接…。新固網通聯資料庫架構更改，

(17)

固網雙向通聯查詢必須跟著調整。舊系統採用 C 語言撰寫，不如 Java 有豐富的元件可使用。目前硬體效能越來越好，據了解 Java 執行速度已不輸給 C，而且只需編譯一次，到處皆可執行。所以，使用它來重新設計。以下說明新系統的設計與建置：

1. 查詢模組設計

設計資料庫連線重複使用方法，避免一再重新 Connect、Disconnect，

不僅浪費系統資源且造成查詢效能低落（即本研究方法所提應用程式遠端查詢效能提升）。

除「查電話所屬機構」及「將 layout 轉成 Client 需要的格式」仍沿用舊系統程式外，全部採用跨平台的語言 java 改寫

將查詢個別 token 檔元件包成 class，利於將來提供立即查詢功能

查詢指令加 Hint（Oracle 用來指定所使用索引方式），雙向通聯資料量極龐大，務必使其使用索引查詢（即本研究方法所提對大型資料表使用快速索引查詢，避免陷入 Full Table Scan）。

2. 測試摘要

自行設計完整 Test Case－當成「白箱測試」

（QueryType1/2/3）x（到齊/未到齊）6 筆及輸入錯誤 4 筆

（內含人工單 3 筆需優先處理）

任意選取一些實際的檔案測試－當成「黑箱測試」

擬定及進行雙軌測試

(1)撰寫程式每日自動拷貝舊系統處理完成的 token 檔給新系統 (2)撰寫程式自動進行新舊系統處理結果比對(列出不一致者) (3)查詢模組雙軌測試比對結果：

a.發現舊系統因空間不夠只能保留三個月；

b.測試正常(但詳查 log--反向查詢，無國際、行動及其它電話案例)

(18)

負載測試

同時啟動 1~9 支 Process，觀察其平均查詢時間之變化。

第三項第三項第三項

第三項、、、、各實體視域設計－各實體視域設計－各實體視域設計－各實體視域設計－統計作業工作摘要統計作業工作摘要統計作業工作摘要統計作業工作摘要

舊系統為每一實體視域更新撰寫一支程式處理，維運負擔沉重；新系統撰寫一支程式處理所有實體視域的更新，修改或增加新功能容易。舊系統使用「通話日期」做增減異動，新系統使用「序號」更準確（異動範圍較小）。新系統使用 Oracle PL-SQL 撰寫，對 Transaction 掌控較佳（舊系統使用 C 語言）。先比照舊系統建立既有的實體視域，視效能情況再增減實體視域數目，設計及建置相關工作如下：

1. 進行系統負載測試，評估新統計作業架構是否可行

以三日通聯資料作測試，不採用 parallel，北：18 hr. 中：4 hr. 南：3 hr.

內可處理完畢。採用 parallel 方式效能差。

2. 異動資訊萃取載入時自動化蒐集及利用最小異動原則自動化更新，資料可迅

速完成異動。全部通聯明細 90 億筆，每日增加 5 千萬筆，依據萃取載入時

所蒐集到的異動資訊，可只就每日增加部分求算更新。本研究之設計能具體

實現「資料倉儲中實體化視域自我維護」。而且只需定義參數表即可增加統

計 Cube/Aggregate，以設計擷取資料之 SQL 指令取代撰寫實體視域更新程

式，以簡化及加速開發。以簡化的流程圖說明快速有效的統計多維度 Cube

處理器的設計。如圖 4.3.7 所示。

(19)

圖 4.3.7 固網通聯系統統計多維度 Cube 處理器簡化的流程圖

(20)

3. 資料萃取載入、實體視域更新及 CDR 資料分析之間關係，以圖 4.3.8 表示。

圖 4.3.8 資料萃取載入、實體視域更新及 CDR 資料分析關係圖

4. 統計 Cube/Aggregate 參數表定義，一個統計一筆，舉例說明如表 4.3.1。

表 4.3.1 固網通聯系統統計主要系統表格－參數表

程序名程序名程序名

程序名 統計日統計日 統計日統計日更新日更新日 更新日更新日停停停停止止止止標標標標識識識識

SQL 指令指令指令指令

sta_calling _prefix

08/22/2006 01:01:17

08/22/2006 01:03:39

SELECT --+ INDEX(a I_SN)

TO_CHAR(a.answer_date_time,'yyyymmdd') answer_date,

a.omc_code omc,node_name,

substr(calling_number,1,length(calling_number)-3) prefix, TO_CHAR(count(*),'999999999999999999999999999999') call_count

FROM fixcdr a WHERE a.sn = :sn AND a.uui_count != 'AA' GROUP BY

TO_CHAR(a.answer_date_time,'yyyymmdd'), a.omc_code,node_name,

substr(calling_number,1,length(calling_number)-3)

發發發發發發通發最最營營營交交交發發發發筆數

CHAR CHAR CHAR VARCHAR2 NUMBER

0 國國發發發發發發最最營營營通發通通發發筆數最最最最數

2 國國發發發發發發最最營營營通發通通發發筆數最最最最數

CHAR CHAR CHAR VARCHAR2 NUMBER NUMBER NUMBER 國國發發發發3 發發最最營營營通發通通發發筆數最最最最數

CHAR CHAR CHAR VARCHAR2 NUMBER NUMBER NUMBER 國國發發發發發發最最營營營通發通通發發筆數最最最最數

按統計名稱讀出參數記錄按統計名稱讀出參數記錄按統計名稱讀出參數記錄按統計名稱讀出參數記錄一個統計一筆

一個統計一筆一個統計一筆一個統計一筆

CDR CDR CDR CDR檔檔檔檔

LOG 通通通通序號執執執最執執執最檔檔檔檔檔檔檔檔最最最最最最最最最最總筆數錯錯筆數載載筆數營營營程序代序

NUMBER DATE DATE VARCHAR2 NUMBER DATE CHAR CHAR NUMBER NUMBER NUMBER VARCHAR2 VARCHAR2

<pk>

通通通通序號

交交交發發序通受發序通起發始始起發最最發話最最通發通數通發通通特特特特營特停通

NUMBER VARCHAR2 VARCHAR2 VARCHAR2 CHAR CHAR CHAR NUMBER CHAR CHAR CHAR

<pk>

CDR CDR CDR CDR檔檔檔檔

… …

逐筆寫入逐筆寫入逐筆寫入

逐筆寫入，，，每筆加寫序號，每筆加寫序號每筆加寫序號每筆加寫序號一個一個

一個一個CDRCDRCDR檔一筆寫序號CDR檔一筆寫序號檔一筆寫序號檔一筆寫序號

發發統數程序名發發最最更更最最停停停停 SQL指指

VARCHAR2 DATE DATE VARCHAR2 VARCHAR2

<pk>

統統統統計計計計

萃取載入萃取載入萃取載入萃取載入

CDR分析

分析分析分析

更新統計表更新統計表更新統計表更新統計表

直接讀取統計直接讀取統計直接讀取統計直接讀取統計

(21)

sta_long_calling _omc_m

08/22/2006 01:05:17

08/22/2006 01:24:59

TO_CHAR(a.answer_date_time,'yyyymm') answer_month,

a.omc_code omc,

decode(i.fcompany_code,null,' ',i.fcompany_code) fcompany_code,

calling_number calling,

TO_CHAR(count(*),'999999999999999999999999999999') call_count,

TO_CHAR(sum(a.duration_time),'9999999999999999999 99999999999') call_sum,

TO_CHAR(max(a.duration_time),'9999999999999999999 99999999999') call_max

FROM fixcdr a, prefix_180x i WHERE a.sn = :sn

AND a.call_type = '01'

AND substr(a.called_number,1,4) = i.prefix_code(+) GROUP BY

TO_CHAR(a.answer_date_time,'yyyymm'), a.omc_code,

fcompany_code, calling_number

sta_intl_calling _omc_m

08/22/2006 01:06:12

08/22/2006 01:10:40

TO_CHAR(a.answer_date_time,'yyyymm') answer_month,

a.omc_code omc, a.call_type,

calling_number calling,

TO_CHAR(count(*),'999999999999999999999999999999') call_count,

TO_CHAR(sum(a.duration_time),'9999999999999999999 99999999999') call_sum,

TO_CHAR(max(a.duration_time),'9999999999999999999 99999999999') call_max

FROM fixcdr a WHERE a.sn = :sn

AND a.call_type in ('75','76','77','11','21','85','79','29') AND a.uui_count != 'AA'

GROUP BY

TO_CHAR(a.answer_date_time,'yyyymm'), a.omc_code,

a.call_type, calling_number

(22)

第四項第四項第四項

第四項、、、、搬遷計劃搬遷計劃搬遷計劃搬遷計劃

由於系統建置時，設備尚在採購中，故先向其他計畫借用設備，也因而面臨如何快速及穩定性搬遷如此龐大資料庫，對此先進行各搬遷方案評估分析，以選擇最佳方案進行。可能搬遷方案及各搬遷方案評估如下：

可能搬遷方案如下

(1)EXPORT/IMPORT TRANSPORT_TABLESPACE＋dd

(2)EXPORT/IMPORT TABLES

(3)CDR 原始檔重 Load

Data Base 搬移方案時間，區分共同程序及個別差異程序估算如下：

(1)共同－FTP：測試取得兩種數據

a. 1.348GB：20 min.＝＞5TB：52 Days | Process：12＝＞ 5 Days

b. 0.646GB：28 min.＝＞5TB：151 Days | Process：12＝＞ 13 Days

(2)個別：資料格式處理

a. EXPORT/IMPORT TRANSPORT_TABLESPACE＋dd Process：12(N6/C3/S3)

約 7 Days

b. EXPORT/IMPORT TABLES

i. Export

Process: 3(N1/ C1/ S1) N/C/S：2.5/1/1 Day

ii.Import

1.4GB：0.037 Days=＞5TB：132 Days | Process：12=＞11 Days

(23)

c. CDR 原始檔重 Load

參考中區測試主機(topscdb8)數據

CPU：4/PROCESS 數目(北：5 中：1 南：2)

北：0.000291002450044546*185837=54 days(約 2 個月)

中：0.000165084317409197*64569=11 days

南：0.000229560093690528*77958=18 days

根據實驗結果選擇「EXPORT/IMPORT TRANSPORT_TABLESPACE＋dd」

方案進行搬遷，此方案適用之前提：作業系統版本相近且資料庫版本相同，對應 raw device 之大小適中以利傳送。故如有資料庫搬遷需要，事前規劃好可以省時省力。為搬遷設計一套自動處理機制，可判斷系統硬碟空間暫停或處理，及會自動進行直到完成，以及匯入方偵測成功才將檔案刪除。搬遷自動處理機制之匯出、入 Shell Script，請參考附錄 A。

實際作業從中區測試主機將北中南三資料庫搬至北區主機，再由北區主機將中南資料庫搬到中南區主機（原本擬以北區主機備援中南區主機但因各分公司意見不同而作罷，若非此一波折及南分公司設備異常，採用此自行設計的自動化處理機制，搬遷工作約一個多禮拜即可完成，詳附錄 B 搬遷記錄）。

第五項第五項第五項

第五項、、、、網站建置網站建置網站建置網站建置

原先舊有網站使用 Perl 設計且後端原先利用批次作業自 32 台資料庫擷取檔案，需全部重新設計（圖 4.3.9 中灰色部份），且需增加和其他相關站台整合設計，如登入身份驗證結果傳遞等，改採用 JSP 開發，原預計將於 95 年底前完成。

由於增加人手，已提前於 95 年 10 月底完工。Web 網站架構圖如下：

(24)

圖 4.3.9 固網通聯系統 Web 網站架構圖使用者權限管理使用者權限管理使用者權限管理使用者權限管理

CDR CDR CDR

CDR 編輯作業編輯作業編輯作業編輯作業國際

國際國際

國際 ISD ISD ISD ISD 檔案管理作業檔案管理作業檔案管理作業檔案管理作業交換機話務統計作業交換機話務統計作業交換機話務統計作業交換機話務統計作業

發話號碼未出發話號碼未出發話號碼未出

發話號碼未出 CDR CDR CDR 查詢作業 CDR 查詢作業查詢作業查詢作業

檔案處理作業檔案處理作業檔案處理作業檔案處理作業驗証報表查詢作業驗証報表查詢作業驗証報表查詢作業驗証報表查詢作業檔案檢查查詢作業檔案檢查查詢作業檔案檢查查詢作業檔案檢查查詢作業送批價報表查詢作業送批價報表查詢作業送批價報表查詢作業送批價報表查詢作業

每日字頭比對作業每日字頭比對作業每日字頭比對作業每日字頭比對作業

障礙匯總障礙匯總障礙匯總障礙匯總

當日障礙匯總當日障礙匯總當日障礙匯總當日障礙匯總交換機房代碼維護交換機房代碼維護交換機房代碼維護交換機房代碼維護 UMD

UMD UMD

UMD 主網頁主網頁主網頁主網頁

統計作業統計作業統計作業統計作業

查詢作業查詢作業查詢作業查詢作業

系統資訊系統資訊系統資訊系統資訊

CDR CDR CDR CDR 作業作業作業作業日常維護作業日常維護作業日常維護作業日常維護作業異常案件處理異常案件處理異常案件處理異常案件處理

查看使用者使用記錄查看使用者使用記錄查看使用者使用記錄查看使用者使用記錄

一般統計作業一般統計作業一般統計作業一般統計作業固網電話統計圖表固網電話統計圖表固網電話統計圖表固網電話統計圖表

市話撥接電信業者通話統計市話撥接電信業者通話統計市話撥接電信業者通話統計市話撥接電信業者通話統計

統計月報表統計月報表統計月報表統計月報表計費磁帶檔瀏覽作業

計費磁帶檔瀏覽作業計費磁帶檔瀏覽作業計費磁帶檔瀏覽作業

交換局局情查詢交換局局情查詢交換局局情查詢交換局局情查詢發話統計排名查詢發話統計排名查詢發話統計排名查詢發話統計排名查詢通話明細紀錄查詢通話明細紀錄查詢通話明細紀錄查詢通話明細紀錄查詢

統計作業

(25)

CDR 資料分析網站設計重點如下：

1. 使用連線儲存池方式處理資料庫連線

減少連線時間，提升查詢效能

快速整合三區資訊，完成新需求－

只需更改三區不同的 JNDI，不必增加後端資料彙總及更新處理例：約一週完成「不分區 CDR 資料分析」站台建設

2. 調整最少連線數目：可能不只一個（系統預設值為一），需設定足夠數目。

方法：觀察站台平時所需連線數目

減少連線時間，提升查詢效能 3. 嚴密權限控管

採用 LDAP 及系統雙重驗證

另加作業權限控制

使用者依授權決定可見作業

執行任何作業，均檢查使用者權限 4. 完整使用記錄

執行任何功能，均完整記錄執行時間、動作、IP 5. Web 公用模組快速產生統計圖表－滿足客製化需要

利用元件化設計，加快程式撰寫速度

利用資料表作業權限、Level 1/2/3 選單，

可動態對不同使用者，產生不同的網頁

利用資料表連線控制－達成多站台單一登入

(26)

第四章 第四章 第四章