薛曉儒 儲文勝

[摘要]本文通過對Microsoft Power BI功能的介紹以及與Excel、Sql的關(guān)聯(lián)性進行實例印證，指出Power BI具有快速實現(xiàn)數(shù)據(jù)可視化及數(shù)據(jù)分析全覆蓋、可重復(fù)并免費使用的優(yōu)勢，旨在豐富內(nèi)部審計方法體系。

[關(guān)鍵詞] Power BI 大數(shù)據(jù) 審計方法 可視化

互聯(lián)網(wǎng)大數(shù)據(jù)相繼催生出諸如R語言、IBM

Hadoop、HP Vertica、分布式實時計算系統(tǒng)Storm等許多較成熟的大數(shù)據(jù)分析工具，從而為內(nèi)部審計技術(shù)的創(chuàng)新和方法體系的完善提供了有力支撐。毋庸諱言，這些工具性能的確強大，但因為大部分數(shù)據(jù)分析是通過編程實現(xiàn)的，對于非計算機專業(yè)背景的審計人員來說，學(xué)習(xí)和掌握這些工具難度較大。因此，尋找一款不那么“高大上”、不需要編程或僅需較少程序語句的專門工具，就顯得非常必要，微軟的Power BI即是符合條件、滿足需要的選擇之一。

一、Power BI 簡介

對于Excel，大多數(shù)審計人員都能夠熟練應(yīng)用。而Power BI脫胎于Excel，兩者的基本操作是相通的。具體而言，Power BI由Power Query、Power Pivot、Power View、Power Map四部分組成。其中Power Query可以輕松地鏈接公眾數(shù)據(jù)或企業(yè)數(shù)據(jù)源；Power Pivot能夠直接在Excel中創(chuàng)建復(fù)雜的數(shù)據(jù)模型；Power View用來創(chuàng)建報表和交互式數(shù)據(jù)可視化分析視圖；Power Map則在Excel中體驗標注地理空間數(shù)據(jù)的3D地圖，見圖1。

二、Power BI的數(shù)據(jù)分析優(yōu)勢

（一）可快速處理大數(shù)據(jù)，實現(xiàn)審計數(shù)據(jù)分析全覆蓋

由于Power BI采用新的算法和數(shù)據(jù)存儲方式，其處理數(shù)據(jù)量的多少只取決于計算機內(nèi)存的大小，并且運算速度快，使得審計人員可以對大數(shù)據(jù)進行全面分析，彌補審計抽樣的缺憾。

（二）可快速實現(xiàn)數(shù)據(jù)可視化

應(yīng)用Power BI可快速實現(xiàn)數(shù)據(jù)的可視化，從視覺上直觀地呈現(xiàn)數(shù)據(jù)分析結(jié)果，并實現(xiàn)數(shù)、表間的聯(lián)動。

（三）建立的分析工具可重復(fù)使用

使用Power BI對數(shù)據(jù)進行分析后會形成pbix文件，這個文件可作為以后對此類問題進行數(shù)據(jù)分析的工具。在分析新的相同類型數(shù)據(jù)時，審計人員只需在pbix文件中替換相同格式的數(shù)據(jù)源，刷新即可得到分析結(jié)果，極大地提高了工作效率。

（四）Power BI可以免費使用

雖然免費軟件本身對信息共享功能進行了限制，但應(yīng)用Power BI對數(shù)據(jù)分析絲毫不會受到影響。

三、Power BI的審計應(yīng)用實踐

一般而言，欲對某一事項進行分析，需要根據(jù)分析的目的，合理確定需要采集的數(shù)據(jù)，這是正確進行數(shù)據(jù)分析的基礎(chǔ)。審計中，數(shù)據(jù)分析需要的數(shù)據(jù)不僅包含企業(yè)各類信息系統(tǒng)數(shù)據(jù)，也可能包含系統(tǒng)外數(shù)據(jù)，如來自企業(yè)外部諸如國家機關(guān)、行業(yè)協(xié)會公布的一些執(zhí)行標準、公告等數(shù)據(jù)。由于數(shù)據(jù)來源不一、格式不同，為保證分析數(shù)據(jù)的正確性、一致性和完整性，在采集數(shù)據(jù)后，審計人員應(yīng)首先對數(shù)據(jù)進行加載、清洗，再進行數(shù)據(jù)分析。

（一）數(shù)據(jù)的采集

Power BI提供了幾十種數(shù)據(jù)采集方式，可對電子表格、文本文件、數(shù)據(jù)庫、網(wǎng)頁、微軟云計算平臺等幾乎所有數(shù)據(jù)類型進行采集。但要用Power BI直接對服務(wù)器或數(shù)據(jù)庫進行數(shù)據(jù)采集，則需要系統(tǒng)管理員進行相應(yīng)的審批程序后授權(quán)，存在一定的困難。而作為關(guān)系型數(shù)據(jù)庫通用查詢語言，具有強大數(shù)據(jù)查詢功能的SQL語言，能夠方便地在相關(guān)系統(tǒng)查詢編輯器中獲取需要分析的數(shù)據(jù)。因此，實踐中，一般直接用SQL語言在查詢編輯器中查詢數(shù)據(jù)后，生成Excel表格或TXT文件，然后通過Power BI的“獲取數(shù)據(jù)”菜單把數(shù)據(jù)導(dǎo)入Power BI中。

以中石油為例，應(yīng)用SQL查詢語言從企業(yè)的SAP HANA數(shù)據(jù)平臺中提取需要分析數(shù)據(jù)的操作如下：

1.取期末庫存數(shù)據(jù)。運行SAP HANA Studio，進入HANA數(shù)據(jù)平臺，在catalog下尋找物資采購FX_KT子目錄，在views視圖下，選擇“SJS_KT_06_期末庫存表”，點擊SQL按鈕，在右邊SQL編輯區(qū)輸入：

SELECT * FROM"FX_KT"."SJS_KT_06_期末庫存表 "where"工廠"='18B4'and"按本幣計的金額 "<>0

值得注意的是，實踐中利用上述SQL語句對公司期末庫存的所有數(shù)據(jù)進行采集，由于計算機內(nèi)存小，當(dāng)采集到260萬條信息時，計算機死機。因此，對采集的數(shù)據(jù)，“工廠”字段限定在為“18B4”（某采油廠代碼）且金額不為0的記錄，見圖2。

2.取物料主數(shù)據(jù)。進入HANA數(shù)據(jù)平臺，在catalog下的FX_WC_V子目錄中，選擇“SJS_KT_06_期末庫存表”，點擊SQL按鈕，在右邊SQL編輯區(qū)輸入：SELECT * FROM"FX_WC_V"."SJS_WC_物料主數(shù)據(jù)"，見圖3。

通過上述SQL語句，采集公司所有的物料主數(shù)據(jù)導(dǎo)入到Excel表，并經(jīng)過粗略檢查剔除明顯錯誤后，應(yīng)用“開始-獲取數(shù)據(jù)-Excel”功能菜單，導(dǎo)入Power BI。

（二）數(shù)據(jù)的清洗

數(shù)據(jù)導(dǎo)入Power BI后，需要進行數(shù)據(jù)清洗，主要任務(wù)是刪除不需要的數(shù)據(jù)、剔除不正確的字符、規(guī)范數(shù)據(jù)類型、完善數(shù)據(jù)信息。通過數(shù)據(jù)清洗，可為后續(xù)數(shù)據(jù)建模和數(shù)據(jù)分析奠定基礎(chǔ)。在Power BI中對數(shù)據(jù)進行清洗，主要通過功能模塊Power Query中的“編輯查詢”完成，該“編輯查詢”可實現(xiàn)對數(shù)據(jù)的類型轉(zhuǎn)換、分組、分列、填充、逆透視等，見圖4。

（三）數(shù)據(jù)模型的建立

完成數(shù)據(jù)的清洗后，在關(guān)系視圖下，進行數(shù)據(jù)建模。

1.確立分析所需的理論模型。庫存賬齡的計算原理是對某一分析時點形成的庫存進行分析，確定該物資的在庫時間，即其每一批入庫時點至分析時點的時間間隔，同時確定該批次庫存占該物資總庫存的權(quán)數(shù)，對該物資全部庫存的在庫時間進行加權(quán)計算，即得到該物資分析時點的庫存賬齡。

本例分析所需的庫存賬齡理論公式為：

庫存賬齡=∑（批次入庫金額÷分析時點庫存總額×批次在庫時間）

2.理論模型在Power BI中的實現(xiàn)。

（1）建立表間關(guān)系。Power BI中的數(shù)據(jù)建模，就是在多個分析所需的數(shù)據(jù)表之間，通過相同的字段，建立表間關(guān)系。本例中，期末庫存表只有物料編碼、物料組、物料小類，在分析結(jié)果中要體現(xiàn)物料的大類、中類、小類以及名稱，就需要期末庫存表與物料主數(shù)據(jù)表建立關(guān)系。而在兩個表中，都有物料編碼字段，因此，可以通過兩個表的物料編碼字段建立關(guān)聯(lián)，見圖5。建立關(guān)聯(lián)一定要保證維度表（物料主數(shù)據(jù)表）一方關(guān)聯(lián)字段的值具有唯一性，否則會出現(xiàn)錯誤提示。

（2）新建計算列。因為在期末庫存表中分別記錄了不同批次庫存物資的入庫日期，因此，還需要計算出每個物料不同批次物資入庫日至分析日的庫存天數(shù)，這就需要通過新建列操作來進行計算。在Power BI中選擇“建模”-“新建列”菜單，在新建列編輯框中錄入：

庫存天數(shù) = "2017/12/31"-'期末庫存'[采購日期]

（3）新建度量值。在獲取每個物料的庫存天數(shù)、庫存金額后，就可通過度量值計算出每個物料的庫存賬齡。在Power BI選擇“建?！?“新建度量值”菜單，在度量值編輯框中錄入：

庫存賬齡 = SUMX（'期末庫存'，'期末庫存'[按本幣計的金額]*'期末庫存'[庫存天數(shù)]）/SUM（'期末庫存'[按本幣計的金額]）

以上建立的計算列和計算度量值公式，稱為Data Analysis Expressions（DAX）語言，Power BI即通過DAX語言對數(shù)據(jù)進行計算和分析。DAX計算功能強大，應(yīng)用靈活，但需要審計人員熟悉常用的函數(shù)，并在使用中不斷積累一些常用表達式的書寫方式。

（四）分析結(jié)果的呈現(xiàn)

在報表模式下，從可視化欄選擇需要形成的圖表，然后從字段欄選擇需要顯示的字段，快速形成物資大類、中類、小類以及物資明細的庫存賬齡和庫存物資金額的圖表。

四、Power BI與Excel、Sql的對比

從上述簡單的數(shù)據(jù)分析，可以看到Power BI功能的強大，如果需要進行多個表的大數(shù)據(jù)處理，從多維度對數(shù)據(jù)進行分析，對Power BI強大的功能就會有更深體會。

但“寸有所長，尺有所短”。雖然Power BI在大數(shù)據(jù)分析中具有速度快、圖標可視化、生成工具可重復(fù)應(yīng)用等優(yōu)點，但Power BI是列存儲式表，數(shù)據(jù)存儲的形式?jīng)Q定了其相對于Excel在應(yīng)用中缺少靈活性。因此，在數(shù)據(jù)分析中，特別是對于表格的處理和少量數(shù)據(jù)的分析，利用Excel更方便靈活。而對于數(shù)據(jù)查詢，目前使用的信息管理系統(tǒng)，都是基于關(guān)系型數(shù)據(jù)庫建立的系統(tǒng)，SQL語言作為關(guān)系型數(shù)據(jù)庫查詢的通用語言，對于關(guān)系數(shù)據(jù)庫的查詢有著天然的優(yōu)勢。因而，審計人員在進行數(shù)據(jù)分析時，要根據(jù)具體情況，結(jié)合使用不同工具，才能更有效提高數(shù)據(jù)分析效率。

盡管Power BI容易操作，形成的分析工具可重復(fù)使用，并可有效提高數(shù)據(jù)分析效率，但要熟練掌握，也需長期的技術(shù)積累。同時應(yīng)看到，Power BI畢竟是數(shù)據(jù)分析工具，前提是使用者必須明確想要獲得的結(jié)果需要哪些數(shù)據(jù)；數(shù)據(jù)之間存在哪些邏輯關(guān)系；如何通過這些數(shù)據(jù)和邏輯關(guān)系，一步步“推”出想要的結(jié)果。

（作者單位：中國石油長慶油田公司，郵政編碼：710021，電子郵箱：xxx1_cq@petrochina.com.cn）

主要參考文獻

馬世權(quán).從Excel到Power BI 商業(yè)智能數(shù)據(jù)分析[M].北京：電子工業(yè)出版社， 2018

Alberto Ferrari， Marco Russo，劉凱.微軟Excel2013：用Powerpivot建立數(shù)據(jù)模型[M].北京：清華大學(xué)出版社， 2015