可視化分析在電力營銷審計中的應用

厲雨 邵天龍 王浩

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

[關鍵詞]全業務? ? 數據表? ? 數據轉換? ? 可視化分析

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

一、Tableau簡介

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

二、結合源端系統在貼源層定位并查詢數據

（一）貼源層數據字典本地化

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

（二）源端系統Html網頁結構分析

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

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

（三）查詢貼源層數據到本地

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

三、應用Excel ADO技術進行數據處理

（一）Office ADO技術介紹

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

連接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方法執行SQL語句，并得到查詢記錄集對象。最終通過Copy From Recordset方法將查詢記錄集對象輸出到本地的Excel文件中。

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

（二）無效數據刪除

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

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

（三）數據格式化

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

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

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

（一）數據匯總

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

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文件并將它們逐個打開，再通過以下語句獲取各個工作表的數據至一個匯總表：

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

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

（二）售電量可視化分析

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

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

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

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

五、Tableau與Power BI及Excel的對比

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

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