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