审计分析：从关系到大数据

本书将多年计算机审计培训中的知识技术总结为教材，既包括关系数据库，又关注大数据在现代审计关系中的应用 

本书针对计算机在审计中数据分析所需要的技术、方法和工具,按照技术发展的脉络,介绍基于关系数据库的以结构化查询语言(SQL)为工具的查询分析，基于数据仓库的可视化途径的多维分析，基于模型训练的机器学习途径的挖掘分析，以及基于大数据的相关分析,力图使读者能够应用本书介绍的方法、工具和技术完成审计目标。
全书共有7章。第1章介绍审计数据分析的基本概念;第2章以TSQL为例介绍结构化查询语言,包括基本的DR、DDL、DML、DCL等,以及结构化查询语言在财务审计与业务数据结合的财务审计中的应用技术;第3章介绍数据库用户以及授权、数据导入导出等技术,以及这些技术在审计中的应用;第4章介绍高级查询分析技术,包括游标、触发器、视图、索引等;第5章介绍如何通过多维分析发现审计线索;第6章介绍数据挖掘途径的审计数据分析;第7章介绍基于大数据的审计分析。
本书可作为计算机审计工作者的技术参考书。

目录
第1章审计数据分析1
1.1审计1
1.2计算机审计2
1.3审计数据分析2
1.3.1基于关系数据库的审计分析3
1.3.2基于数据仓库的审计分析8
1.3.3基于数据挖掘的审计分析9
1.3.4基于大数据的审计分析9
1.3.5审计方法模型10第2章结构化查询技术及其应用12
2.1概述12
2.2基本查询14
2.2.1在Management Studio中设计和执行查询15
2.2.2基本的查询语句17
2.3区分不同的数据类型18
2.4字面量的格式要求20
2.5使用表达式21
2.6应用内置函数完成通用功能30
2.6.1聚合函数与聚合查询30
2.6.2日期和时间函数33
2.6.3数学函数35
2.6.4字符串函数36
2.6.5系统函数38
2.7基于单表的查询技术38
2.7.1WHERE子句39
2.7.2ORDER BY子句40
2.7.3GROUP BY子句41
2.7.4HAVING子句44
2.7.5持久化查询结果45
2.8多表查询技术45
2.8.1交叉连接46
2.8.2内连接46
2.8.3自连接50
2.8.4外连接50
2.9子查询技术54
2.9.1使用返回单个值的子查询55
2.9.2使用返回多个值的子查询56
2.9.3应用子查询进行存在性测试56
2.10合并58
2.11修改数据59
2.11.1插入行59
2.11.2修改行61
2.11.3删除行62
2.12应用DDL管理表62第3章数据导入导出技术65
3.1用户以及授权65
3.1.1SQL Server的安全体系结构65
3.1.2安全认证模式66
3.1.3用户管理66
3.1.4数据控制语句70
3.2从SQL Server数据库导入表71
3.2.1利用数据库的分离/附加功能实现数据导入71
3.2.2直接复制数据库中的文件71
3.2.3备份/还原71
3.2.4导入导出72
3.3从其他数据库导入表72
3.3.1把Access数据导入SQL Server73
3.3.2把文本文件导入SQL Server80
3.3.3Visual FoxPro数据表导入SQL Server84第4章高级查询分析技术85
4.1视图85
4.2应用索引加快查询89
4.2.1索引的类型89
4.2.2索引的创建90
4.3数据字典90
4.3.1数据文件和事务日志文件91
4.3.2表定义91
4.4临时表91
4.4.1客户与数据库服务器的连接91
4.4.2临时表的创建与删除93
4.5设计脚本完成计算94
4.5.1案例: 计算个人所得税94
4.5.2标识符、语句和注释96
4.5.3变量96
4.5.4流控制语句IFELSE97
4.5.5BEGIN…END97
4.5.6IF ELSE语句98
4.5.7CASE表达式99
4.5.8WHILE语句104
4.6存储过程108
4.6.1系统存储过程108
4.6.2用户自定义存储过程108
4.7自定义函数109
4.8触发器113
4.9游标115
4.10事务与并发控制119
4.10.1事务的概念119
4.10.2事务类型120
4.10.3并发操作可能产生的问题123
4.10.4隔离级别125
4.11在审计脚本语言中应用SQL语句129
4.11.1ASL中的运算符130
4.11.2ASL中的分支语句131
4.11.3ASL中的循环语句134
4.11.4从脚本中访问数据库139第5章多维数据分析技术142
5.1多维分析案例——延期纳税143
5.2多维数据集的设计148
5.3多维分析案例——烟草公司纳税150
5.3.1创建多维数据集151
5.3.2在Excel中浏览该多维数据集153第6章挖掘型分析155
6.1数据挖掘155
6.2审计数据挖掘分析157
6.3数据挖掘算法158第7章大数据分析165
7.1大数据165
7.2大数据审计分析166
7.3大数据可视化167参考文献172

前言
2000年的春天,河北省审计厅陈金如副厅长来到河北师范大学,就计算机教育与培训问题与我进行了第1次“聊天”。当时没有想到,那次“聊天”却是十八年合作的开始。
当时,河北省审计系统面临如何提升全省审计工作者的计算机审计水平,以适应信息技术的迅猛发展问题。从事审计的人员如果不懂信息技术,那么在信息化环境下就进不了被审单位的门，打不开被审单位的账。审计署指出,开展计算机审计要靠审计人员素质的提高,靠审计机关知识结构的改善。根据审计署的要求,河北省审计厅认为计算机审计的发展和审计事业的发展已经密不可分,应从事关全省审计事业发展的高度思考计算机审计问题,强力推进计算机审计。应切实增强危机感和紧迫感,下大力量将计算机辅助审计工作搞上去。
2000—2005年,我应邀为审计厅的几次短期培训授课。从2006年开始,河北省审计厅每年都与河北师范大学联合举办“河北省审计机关计算机审计中级培训”,目前仍然在进行。参训学员大部分已成为各单位审计信息化工作骨干,并多次在审计署组织的AO应用实例评选和计算机审计专家经验评选中获奖。
在培训项目中,我不仅负责组织工作,而且主讲了“数据库应用技术”“审计数据高级分析技术”两门课程。为了把审计业务与计算机技术结合起来,让非计算机专业的审计师能够理解、掌握和应用计算机技术,在省审计厅安排下,我还深入审计现场,实地调研,学习审计业务,了解审计人员的需求;钻研审计一线人员提出的技术问题,提出解决方案;不断研究数据仓库、数据挖掘、大数据等技术在河北省计算机审计业务中的应用等问题,并将研究结果应用于培训。2014年,受河北省审计厅计算机中心委托,编写了“技巧系列丛书”中的《数据挖掘技巧》一书的初稿,并根据审计署、省审计厅的意见进行了修改,于2017年出版。十八年来累积的讲义以及其他技术资料经过整理,形成了本书。本书的内容不仅涉及与计算机审计有关的技术,还包括如何规避常见的技术错误。
本书初稿由河北师范大学的董东老师完成。河北师范大学的王艳君、陈玉哲、张朝昆等老师在历年培训过程中对前4章进行过修订。王艳君对本书的章节组织以及第1～4章内容提出了修改意见;陈玉哲对第5～7章提出了修改意见。董东最后对全稿进行了精简。
本书得到河北师范大学应用开发基金(L2013K01)和河北省审计厅2018年重点科研课题(201805)资助,在此表示感谢。
囿于学识,在思想、方法或者技术等方向定有不当之处,望读者批评指正。

董东
2018年12月于河北师范大学

第5章 多维数据分析技术
多维数据分析基于多维数据库观察数据间关联和趋势。多维数据库以事实、维度或者度量值对数据进行建模。分析服务是对多维数据库进行数据管理和分析的引擎。其体系结构如图51所示。该体系结构的核心是分析服务,如Microsoft SQL Server OLAP Service。来自财务库和业务库的会计事务和业务事务数据经过数据清洗、转换和验证,形成新的数据库。基于新的数据库,建立多维分析模型。通过分析服务运行多维分析模型,通过数据透视表等工具浏览和分析,发现审计线索。应用多维分析的优势在于能够直观地把握总体、分析趋势、发现异常、选择重点。
一个多维数据库中可以有多个多维数据集,通过分析服务访问多维数据集。通过Microsoft Excel能够观察透视图和钻取,分析趋势和发现异常更加方便。
图51分析服务的体系结构
建立多维分析模型的关键是根据业务需要,设计维和度量值。凡是能够对数据进行分组的实体,如部门,就可以作为维。事实表中的数值列可以按照某个维进行分组汇总,这样的列称为度量值,来自同一个事实表的若干度量值形成一个度量值组。对事实的计数也可以作为度量值。
多维数据库的数据源通常是关系型数据库。该数据库中的数据模式(库结构)往往被转换成OLAP多维数据库中的星型或者雪花型模式。只要能通过ODBC或者OLE DB连接,多维数据库中的数据也可以是非关系型数据库。一般情况下,OLE DB和ODBC Provider作为数据访问组件的一部分与SQL Server一起安装。
分析服务是对多维数据库进行数据管理和分析的引擎。审计人员往往需要一些统计汇总信息,而在大规模关系数据库中通过SQL 语句进行统计汇总是一个复杂而且耗时的工作。为了解决这个问题,需要建立一个独立的数据库更好地存储用来进行分析的业务数据。这个数据库的数据来源于操作型数据库,但必须转换成星型或者雪花型。在这种架构下,通常有一个事实表。事实表记录了业务活动数据,如延期纳税审批活动。这张表会有上万，甚至几十万行数据。维表定义了维的属性、级别和各级别的成员。
典型的星形模式有以下特点: 只有一个事实表,事实表中含有数值列和外键列。每个维只有一个维表,表中包含了该维的主键及其他属性。主键用来作为事实表的参照,其他属性一般是对主键的进一步分组。例如,“产品”维一般有“品牌”属性,“品牌”也是对产品的一种分组。
雪花架构将一个维拆成若干个维。可将维表中成员之间的多对一关系定义为一个独立的有层次体系的维表。某一维的分离使得整个数据模型看起来像雪花,因此称为雪花模型。
分析服务对数据的一致性十分敏感。当定义好多维模型,分析服务就要从关系数据库中读入数据,按照多维数据集结构的要求进行分组汇总,此时需要数据满足以下一致性规则: 无空值(null)和无效值(invalid)、参照完整、不允许删除事实表中的行。
维有3种类型: 常规维、父子维和具有层次结构的维。表示常规维的维表通常有偶数个编码和名称相对应的列。父子维与常规维不同的是,其维表只有3列,其中两列表达了父子关系。例如，会计科目中包含3列: 科目编码、科目名称、上级科目编码。其中，科目编码和上级科目编码两列中包含了父子关系。
多维数据模型对数据的要求有
(1) 度量值必须是数值型。因为只有数值型列，才能被计算汇总。在Access中,数值型数据类型有数字型和货币型。SQL Server中的数值型数据有bigint、int、smallint、tinyint、numeric、decimal、smallmoney、money、real和float。
(2) 事实表和维度表间具有参照关系的列必须为相同数据类型。
(3) 维成员是字符型。
(4) 时间维也是常规维,其对应的列必须是日期时间型。Access中的列数据类型选“日期/时间”。SQL Server中的列数据类型选择smalldatetime或者datetime。
5.1多维分析案例——延期纳税
按照税收征管法及实施细则规定,只有当企业出现以下情况时,税务机关才能批准企业延期纳税: 因不可抗力,导致纳税人发生较大损失,正常生产经营活动受到较大影响的;当期货币资金在扣除应付职工工资、社会保险费后,不足以缴纳税款的。本案例的审计目标就是查证有无非法批准企业延期纳税。
审前调查得知,某市国税局所属各区县的延期纳税审批数据全部集中在市局的数据库服务器中。通过终端查询和后台转换采集了数据,并进行了代码转换,最后将其导入Access数据库中: X市国税局批准延期纳税.mdb(图52)。其中有3个表: 延期纳税批件、税务机关代码和征收项目代码。3个表的表结构及代表性或全部数据如下。
延期纳税批件表:
税务机关代码表:
征收项目代码表:
图52X市国税局批准延期纳税数据库
审计人员希望以图53所示方式在不同维上浏览数据: 指定所有或某个征收项目,列出各区(县)税务机关在各年度或者某年度各月的审批延期纳税额情况，并对可疑数据项可以立即调出其在延期纳税批件表中的相关行。
图53在不同维上浏览数据
以Microsoft Visual Studio为开发工具,以Microsoft Analysis Services为分析服务,以Microsoft Excel为客户端工具,建立和浏览多维分析模型的过程如下。
首先以“Microsoft Jet 4.0 OLE DB Provider”作为驱动程序定义分析服务与“X市国税局批准延期纳税.mdb”的数据源。其中,延期纳税批件为事实表,征收项目代码和税务机关代码为维表。
然后定义事实表和维表之间的参照关系如图54所示。多维分析模型中的各个表必须定义主键。如果在数据源中没有定义,则需定义逻辑主键。
图54事实表和维表之间的参照关系
接下来设置“延期纳税批件”为事实表，并把其中的“税额”设置为多维数据集中的一个度量值。事实表参照的外键表作为维表。多维数据模型如图55所示。该模式是一个星型架构。标题条是黄色的表是事实表;标题条是蓝色的表是维表。
图55多维数据模型
让分析服务处理该多维数据模型后，就可以按照图53所示形式浏览数据了。增加“审批日期”维,可按照“审批日期”重新进行处理和浏览。若要通过“审批日期”实现按年、按月、按日期进行数据浏览,则要创建维的级别实现此目标。在维上可以定义多个级别,如“审批日期”维的级别可以有年、季度、月份、星期、日期等。每个级别一般有若干成员,如“星期”级别中的成员有星期一、星期二、星期三、星期四、星期五、星期六和星期日,“月份”级别中的成员有1月、2月、3月、4月、5月、6月、7月、8月、9月、10月、11月、12月;“季度”级别中的成员有1季度、2季度、3季度、4季度。通常，维的所有成员组成了一个层次结构。图56以时间维为例展示了维的级别以及各级别的成员(矩形框)。
图56级别
在多维数据集浏览器窗口可观察指定所有或某个税务机关、指定所有或某个征收项目在各个时间维级别(年、月)上的税额(度量值,MeasuresLevel)情况。例如,双击2012年,就展开了该年的下级维度(月)。
又如,从“征收项目”维的下拉列表中选择“企业所得税”,可以发现2012年和2014年所有税务机关没有征收企业所得税,2013年的企业所得税征收集中在4月份和10月份。由此可见,通过多维观察数据,审计人员可以很容易发现一些线索。例如，可以看到不少税额为整数,而纳税人应该缴纳的税额很少出现整数,尤其L市国家税务局从2013年2月起连续出现审批金额为整数(35400000)的情况,这是一条重要审计线索。
增加“纳税人”维度,并通过“钻取”操作可以发现,L市国税局连续对“某某大陆钢铁有限公司”进行批缓。采取同样的方法,还可以发现其他涉嫌企业。通过延伸审计,很容易确认国税局的审批行为是否恰当。
通过Excel数据透视表从不同的角度查看数据。数据透视表的名字来源于它具有“透视”数据的能力,这使其成为非常强大的数据分析工具。通过Excel浏览延期纳税多维数据集的步骤如下。
创建Excel数据透视表。单击Excel“数据”菜单,选择“自其他来源”命令,然后选择“来自分析服务”,设置分析服务器名称为“.”,表示本地服务器。在“选择数据库和表”对话框中选择数据库“延期纳税”。设置Excel进行数据库连接的文件名字,单击“完成”按钮,此时Excel如图57所示。
图57初始的数据透视表
从图57中可以看到,“数据透视表字段列表”中列出了多维数据集中的3个维度(审批日期、税务机关和征收项目)和一个度量值(税额)。通过把维度拖放到下方的3个不同区域(行标签、列标签和报表筛选)建立透视表。
Excel的优势在于，它可以将数据透视表和数据透视图联动展示。在Excel中选择一个新的工作单(sheet)或者新建一个工作单。在“数据”功能区中选择“现有连接”。从中选择先前建立的“X市国税局批准延期纳税”,在导入数据对话框中选择“数据透视图和数据透视表”,单击“确定”按钮后,在Excel工作单上既出现了透视表区域(左上角),又出现了透视图区域(中间空白处)。“数据透视表字段列表”的上半部分列出了来自多维数据集的度量值和维度;其下半部分是数据透视表或者数据透视图的参数设置,如图58所示。
图58数据透视图
如果单击数据透视图区域,则显示“数据透视图筛选窗格”,窗格中包括“报表筛选”“轴字段(分类)”“图例字段(系列)”和“∑数值”;如果单击数据透视表,则相应“轴字段(分类)”变成了“行标签”,相应“图例字段(系列)”变成了“列标签”。这个对应变化反映了数据透视图和数据透视表间的一致性: 数据透视表中的行标签对应于数据透视图的轴字段;数据透视表的列标签对应于数据透视图的图例字段。
在“选择要添加到报表的字段”中选择度量值“税额”,该度量值就出现在“∑数值”框中,意思是在图中显示对该度量值的各种汇总。如果想按2013年度观察各税务机关批缓情况,就把“审批日期”拖放到“轴字段(分类)”,把“税务机关”拖放到“图例字段”。右击数据透视图的空白区域,可选择图表类型,如条形图、折线图等。