厲雨 邵天龍 王浩

[摘要]本文介紹了確定源端業(yè)務系統(tǒng)與全業(yè)務數(shù)據(jù)中心數(shù)據(jù)表對應關系的方式方法，進一步將全業(yè)務數(shù)據(jù)中心數(shù)據(jù)字典本地化，并討論如何將查詢數(shù)據(jù)表提取至電子表格，通過應用Excel的ADO和VBA技術對數(shù)據(jù)進行清洗和轉換，得到加工處理過的數(shù)據(jù)，進而通過BI軟件Tableau連接至該數(shù)據(jù)源并進行可視化分析與展現(xiàn)，使內部審計透過可視化發(fā)現(xiàn)疑點、分析原因、提出建議。

[關鍵詞]全業(yè)務? ? 數(shù)據(jù)表? ? 數(shù)據(jù)轉換? ? 可視化分析

國網(wǎng)公司于2016年初啟動全業(yè)務統(tǒng)一數(shù)據(jù)中心建設，將源端業(yè)務系統(tǒng)的存量數(shù)據(jù)及增量數(shù)據(jù)高度融合，充分共享至貼源歷史層（以下簡稱貼源層）。貼源層采用的數(shù)據(jù)庫為Oracle數(shù)據(jù)庫，由此，審計人員想要獲取數(shù)據(jù)除了通過源端業(yè)務系統(tǒng)進行前端查詢之外，還可以通過訪問貼源層來獲取數(shù)據(jù)。只要擁有貼源層相應的訪問權限，就可以直接訪問貼源層并查詢相關數(shù)據(jù)，相對于審計人員從單一源端系統(tǒng)查詢數(shù)據(jù)更便捷也更強大。本文通過PL/SQL軟件訪問貼源層Oracle數(shù)據(jù)庫，PL/SQL是一款可以訪問、操作、維護Oracle數(shù)據(jù)庫的軟件，輔以SQL語句即可訪問貼源層各源端系統(tǒng)及其數(shù)據(jù)表，并可將查詢結果導出為xls、csv等Excel文件。在Excel中通過ADO及VBA技術進行多表、跨表數(shù)據(jù)提取匯總、清洗及數(shù)據(jù)分析等操作，將Tableau鏈接處理以后的電子表格作為數(shù)據(jù)源進行數(shù)據(jù)可視化分析，進而從可視化展示中發(fā)現(xiàn)問題，解決問題。

一、Tableau簡介

相對于其他BI軟件，Tableau學習成本低而功能產(chǎn)出卻更為強大。Tablueu的使用分為三步：鏈接數(shù)據(jù)源、制作可視化圖表、使用儀表板講述可視化故事。連接好數(shù)據(jù)源以后，Tableau會智能地將字段轉換為度量或維度值，一般會將文本型和日期型字段轉換為維度值，用于分類或闡明數(shù)據(jù);而數(shù)值類型的字段一般會轉換為度量值，可根據(jù)維度值進行一些聚合計算，如求和、計數(shù)、平均值等。有了這些度量及維度值，只需在Tableau中通過一些基本的拖拽即可做出美觀的可視化圖表。Tabluea會根據(jù)用戶拖拽的字段智能生成系統(tǒng)最佳匹配的圖表。

二、結合源端系統(tǒng)在貼源層定位并查詢數(shù)據(jù)

（一）貼源層數(shù)據(jù)字典本地化

為方便日后查詢數(shù)據(jù)表以及表字段，首先，將貼源層數(shù)據(jù)字典本地化。通過SQL語句進行數(shù)據(jù)表ALL_COL_COMMENTS及ALL_TAB_COMMENTS聯(lián)合查詢，即可查詢出表名、表注釋、字段名、字段注釋等數(shù)據(jù)表信息。將查詢所得數(shù)據(jù)導出到Excel表格，考慮到Excel儲存能力及計算速度有限，采用將Access作為本地數(shù)據(jù)字典的儲存載體。Access相比Excel不僅數(shù)據(jù)更為嚴謹，而且儲存量達到2G內存，查詢速度也比Excel快。雖然Access更適合作為儲存載體，但其操作界面相較于Excel不夠人性化，也不符合大多數(shù)審計人員的操作習慣，因此在Excel里通過UI設計結合VBA語言開發(fā)一套前端查詢系統(tǒng)。數(shù)據(jù)字典本地化不占用服務器資源，數(shù)據(jù)庫在本地相對查詢速度比在貼源層直接查詢要快。此外，這樣一份數(shù)據(jù)字典為審計人員的日常工作帶來一定的便利。

（二）源端系統(tǒng)Html網(wǎng)頁結構分析

通過源端系統(tǒng)可以查詢數(shù)據(jù)，但是查詢方式必須按照前端所設定的固定查詢條件進行。如果可以直接在貼源層查詢的話，就可以按照自己想要的條件編寫SQL語句進行查詢，從而大大提高數(shù)據(jù)查詢的靈活性。但靈活查詢的前提是要梳理清楚源端系統(tǒng)前端頁面與貼源層數(shù)據(jù)表之間的對應關系。

為理清源端數(shù)據(jù)表與貼源層數(shù)據(jù)表的對應關系，最初筆者采用的方法是字段注釋模糊檢索法，但此方法無異于大海撈針，不僅工作量大而且缺乏針對性。而采用Html網(wǎng)頁結構分析法，可以快速找到源端系統(tǒng)與貼源層數(shù)據(jù)表的對應關系，此方法使得工作量大大縮短，而且更有針對性。

（三）查詢貼源層數(shù)據(jù)到本地

已知數(shù)據(jù)表名，接下來的數(shù)據(jù)查詢工作就變得簡單很多。通過以下SQL語句查詢FC_GC數(shù)據(jù)表中的數(shù)據(jù)：SELECT * FROM BUF_CMS_ALL.FC_GC，然后將查詢結果導出為Excel格式的數(shù)據(jù)。如果數(shù)據(jù)量過大超出Excel的行數(shù)限制，可以在上述SQL基礎上加一個限制條件：ROWNUM<1，000，000，這樣就可以查詢1，000，000條以下的記錄數(shù)。通過Html網(wǎng)頁結構分析法，很快將發(fā)電客戶檔案里的其他幾個表數(shù)據(jù)也查詢到本地Excel文件中。

三、應用Excel ADO技術進行數(shù)據(jù)處理

（一）Office ADO技術介紹

ADO是微軟提供的一項技術手段，為用戶訪問數(shù)據(jù)庫中的數(shù)據(jù)提供了API接口。本文基于微軟提供的Access和Excel的驅動程序，得以實現(xiàn)在Excel中調用ADO接口訪問Access或Excel數(shù)據(jù)庫。ADO的工作原理是先創(chuàng)建ADO的類，然后通過一串連接字符串，通過所創(chuàng)建的類來實現(xiàn)對數(shù)據(jù)庫的連接。

連接Excel的字符串如下："provider=Microsoft.ACE.OLEDB.12.0;extended properties=Excel 12.0; data source="，

“data source”等于所要連接的本地Excel文件的完整路徑。

連接Access的字符串如下："provider=Microsoft.ace.OLEDB.12.0; Data source="

“data source”等于所要連接的本地Access文件的完整路徑。

在連接成功的前提下，通過ADO提供的Execute方法執(zhí)行SQL語句，并得到查詢記錄集對象。最終通過Copy From Recordset方法將查詢記錄集對象輸出到本地的Excel文件中。

由于強大的ADO接口，可以使用該接口訪問Excel和Access文件。使用SQL語句查詢本地數(shù)據(jù)庫不僅速度快，且可提供許多聚合計算的函數(shù)，既保證審計人員仍然可以使用Excel作為數(shù)據(jù)分析工具，又兼得數(shù)據(jù)處理的速度與能力。

（二）無效數(shù)據(jù)刪除

當Excel數(shù)據(jù)量達到數(shù)十萬甚至百萬量級時，數(shù)據(jù)處理速度就成為審計人員關切的問題。為突破普通篩選以及函數(shù)處理速度慢的現(xiàn)狀，本文采用ADO技術作為無效數(shù)據(jù)篩查的手段。如在發(fā)電客戶檔案表中，發(fā)電方式字段的有效識別符包括01、02、03、04，而在前面查詢所得的FC_GC表內卻出現(xiàn)了07、11、99以及空白等不應當出現(xiàn)的識別符。在這種情況下，這些無法識別的識別符應作為無效數(shù)據(jù)刪除。在發(fā)電客戶檔案表中，相當一部分字段都出現(xiàn)了這種情況，因此需要逐一檢查并排除。

通過SQL語句排除無效識別符，只需在SQL語句里加幾個限制查詢條件即可，具體SQL語句為：SELECT * FROM FC_GC WHERE GC_TYPE_CODE =01AND GC_TYPE_CODE =02AND GC_TYPE_CODE =03AND GC_TYPE_CODE =04

（三）數(shù)據(jù)格式化

數(shù)據(jù)格式化指將數(shù)據(jù)表中的數(shù)據(jù)進行整理使其樣式統(tǒng)一，便于后續(xù)數(shù)據(jù)分析。如在FC_GC表中的客戶建檔日期字段內出現(xiàn)了空白日期或日期格式不規(guī)范的情況。對于空白的日期，應考慮是否作為無效數(shù)據(jù)進行刪除。日期格式出現(xiàn)了“YYYY-MM-DD HH：MM：SS”和“YYYY-MM-DD”兩種格式，應根據(jù)業(yè)務需求，如將日期統(tǒng)一格式化為“YYYY-MM-DD”格式。這一操作同樣可通過SQL語句中的FORMAT函數(shù)實現(xiàn)，具體的SQL語句為：SELECT FORMAT（BUILD_DATE，YYYY-MM-DD） FROM FC_GC。通過此方法將FC_GC數(shù)據(jù)表中的其他日期類字段統(tǒng)一格式化為“YYYY-MM-DD”格式。

四、通過Tableau對電力銷售進行可視化分析

使用VBA技術對電力銷售明細表進行多表及跨表數(shù)據(jù)提取匯總，將匯總數(shù)據(jù)作為數(shù)據(jù)源連接至Tableau，最終通過Tableau進行數(shù)據(jù)可視化展示。

（一）數(shù)據(jù)匯總

通過源端系統(tǒng)導出的數(shù)據(jù)為多個Excel表格，其中包括國網(wǎng)遼寧公司整體數(shù)據(jù)以及各地市公司數(shù)據(jù)。首先使用Excel VBA技術將遼寧省及各地市數(shù)據(jù)匯總到一個電子表格內。語句如下：

fileToopen = Application.GetOpenFilename（"

Excel Files（*.xl*）， * .xl*"， "請選擇文件"， True）

If IsArray（fileToopen） = False Then

Exit Sub

Else

For i = 1 To UBound（fileToopen）

Set wb = Workbooks.Open（fileToopen（i））

Next

End If

通過上述語句可以選擇多個Excel文件并將它們逐個打開，再通過以下語句獲取各個工作表的數(shù)據(jù)至一個匯總表：

Sheet4.Range（"d" & MaxRow1）.Value = sht.Range（"E11"）.Value

Sheet4.Range（"e" & MaxRow1）.Value = sht.Range（"G11"）.Value

Sheet4.Range（"f" & MaxRow1）.Value = sht.Range（"H11"）.Value

Sheet4.Range（"g" & MaxRow1）.Value = sht.Range（"I11"）.Value

最終，將遼寧省及各地市的售電量、峰平谷電量、應收電費等數(shù)據(jù)進行匯總，如表1所示。

（二）售電量可視化分析

將上述數(shù)據(jù)作為數(shù)據(jù)源與Tableau進行連接。在Tableau軟件中將“地市”字段轉換為地理角色——縣，Tableau即可自動創(chuàng)建各地市的經(jīng)緯度度量值，進而通過拖拽經(jīng)緯度創(chuàng)建遼寧省售電量地圖，如圖1所示。

在上述售電量地圖中，可以看到Tableau創(chuàng)建的遼寧省各地市地圖分界線以及各地市名稱。每個地市用不同顏色的氣泡表示，氣泡大小與售電量總和大小成正比。通過該圖可以看出，沈陽、鞍山、營口、大連4個地市的售電量總和最大，丹東、阜新等售電量總和相對較小。

Tableau可以創(chuàng)建的圖表類型非常多，不僅僅局限于地圖可視化，通過業(yè)務分析創(chuàng)建各種類型的可視化圖表，還提供了儀表盤工具，可將各種可視化圖表放置到儀表盤內進行布局設計。另外，還可通過關聯(lián)數(shù)據(jù)創(chuàng)建動態(tài)可視化圖表，如圖2所示。

通過一系列可視化圖表展示，可直觀清晰地對比各地市售電量數(shù)據(jù)、峰平谷數(shù)據(jù)、應收電費數(shù)據(jù)大小、走向趨勢等信息，以達到透過可視化找異常、透過異常找原因、透過原因直達病灶解決問題的審計目的。

五、Tableau與Power BI及Excel的對比

Power BI是微軟開發(fā)的BI軟件，Power BI的組件較多，其中最主要的有Power Query、Power Pivot和Power BI Desktop。Power BI與Tableau相比，劣勢在于組件多，每種組件的學習成本也相對較高，并且目前國內相關的學習資料相對較少。審計人員想要得心應手地運用還需學習M語言及DAX語言。Tabluea不需要審計人員具備任何編程基礎，甚至通過半天的短暫培訓就可以快速上手數(shù)據(jù)可視化制作。本文用到的版本為Tableau Public，該版本為免費版，其所鏈接的數(shù)據(jù)源有Excel文件和CSV文件，足以完成大部分可視化分析工作。在實際工作中，審計人員大多運用Excel進行數(shù)據(jù)處理。因此，Tableau Public不僅省去了使用成本，而且更適用于數(shù)據(jù)分析師進行一些基本的可視化工作。

（作者單位：國網(wǎng)遼寧省電力有限公司，郵政編碼：110006，電子郵箱：liy@ln.sgcc.com.cn）