PowerBI在內(nèi)部審計(jì)中的應(yīng)用

薛曉儒 儲(chǔ)文勝

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

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

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

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

一、Power BI 簡(jiǎn)介

對(duì)于Excel，大多數(shù)審計(jì)人員都能夠熟練應(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用來(lái)創(chuàng)建報(bào)表和交互式數(shù)據(jù)可視化分析視圖；Power Map則在Excel中體驗(yàn)標(biāo)注地理空間數(shù)據(jù)的3D地圖，見(jiàn)圖1。

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

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

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

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

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

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

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

（四）Power BI可以免費(fèi)使用

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

庫(kù)存賬齡=∑（批次入庫(kù)金額÷分析時(shí)點(diǎn)庫(kù)存總額×批次在庫(kù)時(shí)間）

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

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

（2）新建計(jì)算列。因?yàn)樵谄谀?kù)存表中分別記錄了不同批次庫(kù)存物資的入庫(kù)日期，因此，還需要計(jì)算出每個(gè)物料不同批次物資入庫(kù)日至分析日的庫(kù)存天數(shù)，這就需要通過(guò)新建列操作來(lái)進(jìn)行計(jì)算。在Power BI中選擇“建模”-“新建列”菜單，在新建列編輯框中錄入：

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

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

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

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

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

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

四、Power BI與Excel、Sql的對(duì)比

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

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

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

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

主要參考文獻(xiàn)

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

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