厲雨 邵天龍 王浩
[摘要]本文介紹了確定源端業(yè)務(wù)系統(tǒng)與全業(yè)務(wù)數(shù)據(jù)中心數(shù)據(jù)表對(duì)應(yīng)關(guān)系的方式方法,進(jìn)一步將全業(yè)務(wù)數(shù)據(jù)中心數(shù)據(jù)字典本地化,并討論如何將查詢數(shù)據(jù)表提取至電子表格,通過應(yīng)用Excel的ADO和VBA技術(shù)對(duì)數(shù)據(jù)進(jìn)行清洗和轉(zhuǎn)換,得到加工處理過的數(shù)據(jù),進(jìn)而通過BI軟件Tableau連接至該數(shù)據(jù)源并進(jìn)行可視化分析與展現(xiàn),使內(nèi)部審計(jì)透過可視化發(fā)現(xiàn)疑點(diǎn)、分析原因、提出建議。
[關(guān)鍵詞]全業(yè)務(wù)? ? 數(shù)據(jù)表? ? 數(shù)據(jù)轉(zhuǎn)換? ? 可視化分析
國(guó)網(wǎng)公司于2016年初啟動(dòng)全業(yè)務(wù)統(tǒng)一數(shù)據(jù)中心建設(shè),將源端業(yè)務(wù)系統(tǒng)的存量數(shù)據(jù)及增量數(shù)據(jù)高度融合,充分共享至貼源歷史層(以下簡(jiǎn)稱貼源層)。貼源層采用的數(shù)據(jù)庫(kù)為Oracle數(shù)據(jù)庫(kù),由此,審計(jì)人員想要獲取數(shù)據(jù)除了通過源端業(yè)務(wù)系統(tǒng)進(jìn)行前端查詢之外,還可以通過訪問貼源層來獲取數(shù)據(jù)。只要擁有貼源層相應(yīng)的訪問權(quán)限,就可以直接訪問貼源層并查詢相關(guān)數(shù)據(jù),相對(duì)于審計(jì)人員從單一源端系統(tǒng)查詢數(shù)據(jù)更便捷也更強(qiáng)大。本文通過PL/SQL軟件訪問貼源層Oracle數(shù)據(jù)庫(kù),PL/SQL是一款可以訪問、操作、維護(hù)Oracle數(shù)據(jù)庫(kù)的軟件,輔以SQL語句即可訪問貼源層各源端系統(tǒng)及其數(shù)據(jù)表,并可將查詢結(jié)果導(dǎo)出為xls、csv等Excel文件。在Excel中通過ADO及VBA技術(shù)進(jìn)行多表、跨表數(shù)據(jù)提取匯總、清洗及數(shù)據(jù)分析等操作,將Tableau鏈接處理以后的電子表格作為數(shù)據(jù)源進(jìn)行數(shù)據(jù)可視化分析,進(jìn)而從可視化展示中發(fā)現(xiàn)問題,解決問題。
一、Tableau簡(jiǎn)介
相對(duì)于其他BI軟件,Tableau學(xué)習(xí)成本低而功能產(chǎn)出卻更為強(qiáng)大。Tablueu的使用分為三步:鏈接數(shù)據(jù)源、制作可視化圖表、使用儀表板講述可視化故事。連接好數(shù)據(jù)源以后,Tableau會(huì)智能地將字段轉(zhuǎn)換為度量或維度值,一般會(huì)將文本型和日期型字段轉(zhuǎn)換為維度值,用于分類或闡明數(shù)據(jù);而數(shù)值類型的字段一般會(huì)轉(zhuǎn)換為度量值,可根據(jù)維度值進(jìn)行一些聚合計(jì)算,如求和、計(jì)數(shù)、平均值等。有了這些度量及維度值,只需在Tableau中通過一些基本的拖拽即可做出美觀的可視化圖表。Tabluea會(huì)根據(jù)用戶拖拽的字段智能生成系統(tǒng)最佳匹配的圖表。
二、結(jié)合源端系統(tǒng)在貼源層定位并查詢數(shù)據(jù)
(一)貼源層數(shù)據(jù)字典本地化
為方便日后查詢數(shù)據(jù)表以及表字段,首先,將貼源層數(shù)據(jù)字典本地化。通過SQL語句進(jìn)行數(shù)據(jù)表ALL_COL_COMMENTS及ALL_TAB_COMMENTS聯(lián)合查詢,即可查詢出表名、表注釋、字段名、字段注釋等數(shù)據(jù)表信息。將查詢所得數(shù)據(jù)導(dǎo)出到Excel表格,考慮到Excel儲(chǔ)存能力及計(jì)算速度有限,采用將Access作為本地?cái)?shù)據(jù)字典的儲(chǔ)存載體。Access相比Excel不僅數(shù)據(jù)更為嚴(yán)謹(jǐn),而且儲(chǔ)存量達(dá)到2G內(nèi)存,查詢速度也比Excel快。雖然Access更適合作為儲(chǔ)存載體,但其操作界面相較于Excel不夠人性化,也不符合大多數(shù)審計(jì)人員的操作習(xí)慣,因此在Excel里通過UI設(shè)計(jì)結(jié)合VBA語言開發(fā)一套前端查詢系統(tǒng)。數(shù)據(jù)字典本地化不占用服務(wù)器資源,數(shù)據(jù)庫(kù)在本地相對(duì)查詢速度比在貼源層直接查詢要快。此外,這樣一份數(shù)據(jù)字典為審計(jì)人員的日常工作帶來一定的便利。
(二)源端系統(tǒng)Html網(wǎng)頁(yè)結(jié)構(gòu)分析
通過源端系統(tǒng)可以查詢數(shù)據(jù),但是查詢方式必須按照前端所設(shè)定的固定查詢條件進(jìn)行。如果可以直接在貼源層查詢的話,就可以按照自己想要的條件編寫SQL語句進(jìn)行查詢,從而大大提高數(shù)據(jù)查詢的靈活性。但靈活查詢的前提是要梳理清楚源端系統(tǒng)前端頁(yè)面與貼源層數(shù)據(jù)表之間的對(duì)應(yīng)關(guān)系。
為理清源端數(shù)據(jù)表與貼源層數(shù)據(jù)表的對(duì)應(yīng)關(guān)系,最初筆者采用的方法是字段注釋模糊檢索法,但此方法無異于大海撈針,不僅工作量大而且缺乏針對(duì)性。而采用Html網(wǎng)頁(yè)結(jié)構(gòu)分析法,可以快速找到源端系統(tǒng)與貼源層數(shù)據(jù)表的對(duì)應(yīng)關(guān)系,此方法使得工作量大大縮短,而且更有針對(duì)性。
(三)查詢貼源層數(shù)據(jù)到本地
已知數(shù)據(jù)表名,接下來的數(shù)據(jù)查詢工作就變得簡(jiǎn)單很多。通過以下SQL語句查詢FC_GC數(shù)據(jù)表中的數(shù)據(jù):SELECT * FROM BUF_CMS_ALL.FC_GC,然后將查詢結(jié)果導(dǎo)出為Excel格式的數(shù)據(jù)。如果數(shù)據(jù)量過大超出Excel的行數(shù)限制,可以在上述SQL基礎(chǔ)上加一個(gè)限制條件:ROWNUM<1,000,000,這樣就可以查詢1,000,000條以下的記錄數(shù)。通過Html網(wǎng)頁(yè)結(jié)構(gòu)分析法,很快將發(fā)電客戶檔案里的其他幾個(gè)表數(shù)據(jù)也查詢到本地Excel文件中。
三、應(yīng)用Excel ADO技術(shù)進(jìn)行數(shù)據(jù)處理
(一)Office ADO技術(shù)介紹
ADO是微軟提供的一項(xiàng)技術(shù)手段,為用戶訪問數(shù)據(jù)庫(kù)中的數(shù)據(jù)提供了API接口。本文基于微軟提供的Access和Excel的驅(qū)動(dòng)程序,得以實(shí)現(xiàn)在Excel中調(diào)用ADO接口訪問Access或Excel數(shù)據(jù)庫(kù)。ADO的工作原理是先創(chuàng)建ADO的類,然后通過一串連接字符串,通過所創(chuàng)建的類來實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的連接。
連接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語句,并得到查詢記錄集對(duì)象。最終通過Copy From Recordset方法將查詢記錄集對(duì)象輸出到本地的Excel文件中。
由于強(qiáng)大的ADO接口,可以使用該接口訪問Excel和Access文件。使用SQL語句查詢本地?cái)?shù)據(jù)庫(kù)不僅速度快,且可提供許多聚合計(jì)算的函數(shù),既保證審計(jì)人員仍然可以使用Excel作為數(shù)據(jù)分析工具,又兼得數(shù)據(jù)處理的速度與能力。
(二)無效數(shù)據(jù)刪除
當(dāng)Excel數(shù)據(jù)量達(dá)到數(shù)十萬甚至百萬量級(jí)時(shí),數(shù)據(jù)處理速度就成為審計(jì)人員關(guān)切的問題。為突破普通篩選以及函數(shù)處理速度慢的現(xiàn)狀,本文采用ADO技術(shù)作為無效數(shù)據(jù)篩查的手段。如在發(fā)電客戶檔案表中,發(fā)電方式字段的有效識(shí)別符包括01、02、03、04,而在前面查詢所得的FC_GC表內(nèi)卻出現(xiàn)了07、11、99以及空白等不應(yīng)當(dāng)出現(xiàn)的識(shí)別符。在這種情況下,這些無法識(shí)別的識(shí)別符應(yīng)作為無效數(shù)據(jù)刪除。在發(fā)電客戶檔案表中,相當(dāng)一部分字段都出現(xiàn)了這種情況,因此需要逐一檢查并排除。
通過SQL語句排除無效識(shí)別符,只需在SQL語句里加幾個(gè)限制查詢條件即可,具體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ù)進(jìn)行整理使其樣式統(tǒng)一,便于后續(xù)數(shù)據(jù)分析。如在FC_GC表中的客戶建檔日期字段內(nèi)出現(xiàn)了空白日期或日期格式不規(guī)范的情況。對(duì)于空白的日期,應(yīng)考慮是否作為無效數(shù)據(jù)進(jìn)行刪除。日期格式出現(xiàn)了“YYYY-MM-DD HH:MM:SS”和“YYYY-MM-DD”兩種格式,應(yīng)根據(jù)業(yè)務(wù)需求,如將日期統(tǒng)一格式化為“YYYY-MM-DD”格式。這一操作同樣可通過SQL語句中的FORMAT函數(shù)實(shí)現(xiàn),具體的SQL語句為:SELECT FORMAT(BUILD_DATE,YYYY-MM-DD) FROM FC_GC。通過此方法將FC_GC數(shù)據(jù)表中的其他日期類字段統(tǒng)一格式化為“YYYY-MM-DD”格式。
四、通過Tableau對(duì)電力銷售進(jìn)行可視化分析
使用VBA技術(shù)對(duì)電力銷售明細(xì)表進(jìn)行多表及跨表數(shù)據(jù)提取匯總,將匯總數(shù)據(jù)作為數(shù)據(jù)源連接至Tableau,最終通過Tableau進(jìn)行數(shù)據(jù)可視化展示。
(一)數(shù)據(jù)匯總
通過源端系統(tǒng)導(dǎo)出的數(shù)據(jù)為多個(gè)Excel表格,其中包括國(guó)網(wǎng)遼寧公司整體數(shù)據(jù)以及各地市公司數(shù)據(jù)。首先使用Excel VBA技術(shù)將遼寧省及各地市數(shù)據(jù)匯總到一個(gè)電子表格內(nèi)。語句如下:
fileToopen = Application.GetOpenFilename("
Excel Files(*.xl*), * .xl*", "請(qǐng)選擇文件", True)
If IsArray(fileToopen) = False Then
Exit Sub
Else
For i = 1 To UBound(fileToopen)
Set wb = Workbooks.Open(fileToopen(i))
Next
End If
通過上述語句可以選擇多個(gè)Excel文件并將它們逐個(gè)打開,再通過以下語句獲取各個(gè)工作表的數(shù)據(jù)至一個(gè)匯總表:
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
最終,將遼寧省及各地市的售電量、峰平谷電量、應(yīng)收電費(fèi)等數(shù)據(jù)進(jìn)行匯總,如表1所示。
(二)售電量可視化分析
將上述數(shù)據(jù)作為數(shù)據(jù)源與Tableau進(jìn)行連接。在Tableau軟件中將“地市”字段轉(zhuǎn)換為地理角色——縣,Tableau即可自動(dòng)創(chuàng)建各地市的經(jīng)緯度度量值,進(jìn)而通過拖拽經(jīng)緯度創(chuàng)建遼寧省售電量地圖,如圖1所示。
在上述售電量地圖中,可以看到Tableau創(chuàng)建的遼寧省各地市地圖分界線以及各地市名稱。每個(gè)地市用不同顏色的氣泡表示,氣泡大小與售電量總和大小成正比。通過該圖可以看出,沈陽、鞍山、營(yíng)口、大連4個(gè)地市的售電量總和最大,丹東、阜新等售電量總和相對(duì)較小。
Tableau可以創(chuàng)建的圖表類型非常多,不僅僅局限于地圖可視化,通過業(yè)務(wù)分析創(chuàng)建各種類型的可視化圖表,還提供了儀表盤工具,可將各種可視化圖表放置到儀表盤內(nèi)進(jìn)行布局設(shè)計(jì)。另外,還可通過關(guān)聯(lián)數(shù)據(jù)創(chuàng)建動(dòng)態(tài)可視化圖表,如圖2所示。
通過一系列可視化圖表展示,可直觀清晰地對(duì)比各地市售電量數(shù)據(jù)、峰平谷數(shù)據(jù)、應(yīng)收電費(fèi)數(shù)據(jù)大小、走向趨勢(shì)等信息,以達(dá)到透過可視化找異常、透過異常找原因、透過原因直達(dá)病灶解決問題的審計(jì)目的。
五、Tableau與Power BI及Excel的對(duì)比
Power BI是微軟開發(fā)的BI軟件,Power BI的組件較多,其中最主要的有Power Query、Power Pivot和Power BI Desktop。Power BI與Tableau相比,劣勢(shì)在于組件多,每種組件的學(xué)習(xí)成本也相對(duì)較高,并且目前國(guó)內(nèi)相關(guān)的學(xué)習(xí)資料相對(duì)較少。審計(jì)人員想要得心應(yīng)手地運(yùn)用還需學(xué)習(xí)M語言及DAX語言。Tabluea不需要審計(jì)人員具備任何編程基礎(chǔ),甚至通過半天的短暫培訓(xùn)就可以快速上手?jǐn)?shù)據(jù)可視化制作。本文用到的版本為Tableau Public,該版本為免費(fèi)版,其所鏈接的數(shù)據(jù)源有Excel文件和CSV文件,足以完成大部分可視化分析工作。在實(shí)際工作中,審計(jì)人員大多運(yùn)用Excel進(jìn)行數(shù)據(jù)處理。因此,Tableau Public不僅省去了使用成本,而且更適用于數(shù)據(jù)分析師進(jìn)行一些基本的可視化工作。
(作者單位:國(guó)網(wǎng)遼寧省電力有限公司,郵政編碼:110006,電子郵箱:liy@ln.sgcc.com.cn)