PowerBI在內部審計中的應用

薛曉儒 儲文勝

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

[關鍵詞] Power BI 大數據 審計方法 可視化

互聯網大數據相繼催生出諸如R語言、IBM

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

一、Power BI 簡介

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

二、Power BI的數據分析優勢

（一）可快速處理大數據，實現審計數據分析全覆蓋

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

（二）可快速實現數據可視化

應用Power BI可快速實現數據的可視化，從視覺上直觀地呈現數據分析結果，并實現數、表間的聯動。

（三）建立的分析工具可重復使用

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

（四）Power BI可以免費使用

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

三、Power BI的審計應用實踐

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

（一）數據的采集

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

以中石油為例，應用SQL查詢語言從企業的SAP HANA數據平臺中提取需要分析數據的操作如下：

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

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

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

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

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

（二）數據的清洗

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

（三）數據模型的建立

完成數據的清洗后，在關系視圖下，進行數據建模。

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

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

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

2.理論模型在Power BI中的實現。

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

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

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

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

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

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

（四）分析結果的呈現

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

四、Power BI與Excel、Sql的對比

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

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

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

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

主要參考文獻

馬世權.從Excel到Power BI 商業智能數據分析[M].北京：電子工業出版社， 2018

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