构建审计分析数据仓库中的数据清理及转换技术

文章编号：1672-5913(2009)08-0129-04

摘要：数据仓库是进行多角度数据分析的基础，在审计中可借助数据仓库技术分析数据，用以辅助发现问题和锁定审计重点。数据仓库中的数据结构是面向数据分析设计的，在构建数据仓库时需要对原始操作型的数据进行适当的清理和转换，使其更适合分析的需求。本文介绍了构建审计分析数据仓库中常见的数据清理和数据转换问题以及相应的解决技术。

关键词：数据仓库；数据清理；数据转换

中图文分类号：G642

文献标识码：B

1数据仓库技术及在审计中的作用

随着计算机及数据库技术的飞速发展，人们对数据的处理提出了更高的要求，希望计算机能更多的参与数据分析与决策的制定等领域。因此数据处理的发展相应地形成了两大类型，一类是操作型处理，即我们日常的事务型处理，比如银行业务、商品零售业务等；另一类是分析型处理，比如分析数据之间潜在的关联关系，分析一段时间内数据的变化情况等。数据仓库技术是为第二类应用而产生的，其主要目的是构建适合数据分析的数据结构，并将事务型操作中的业务数据，经过适当的清理、转换，加载到数据仓库数据库中，从而更利于进行数据分析。目前常用的数据分析技术有联机分析处理和数据挖掘，联机分析处理是从多个角度分析数据，也称为多维分析，而数据挖掘是发现数据之间潜在的关联关系。联机分析处理和数据挖掘针对的对象都是数据仓库中的数据，因此构建数据仓库的结构和数据是进行数据分析的基础。

事务数据、数据仓库、联机分析处理以及数据挖掘之间的关系示意图如图1所示。

图1事务数据、数据仓库及数据分析间的关系

近几年我国的审计技术水平发展非常迅速，审计方法也从过去的手工翻账，发展到今天的利用计算机进行审计。目前用计算机进行审计主要有两种方法，一种是直接针对数据库数据编写审计分析的SQL语句，这种方法的好处是灵活方便，缺点是不利于快速锁定审计重点，发现问题；另一种就是构建审计分析的数据仓库，然后利用多维分析技术对数据进行多角度分析，这种方法便于快速锁定审计重点，发现数据之间内在的关系和问题。

审计工作的好坏关系到国家的财政安全、金融安全等诸多方面，做好国家各方面的审计工作有助于揭示存在的风险，提出防范和化解风险的对策性建议，维护国家的稳定和安全。因此，正确地构建适合审计分析的数据仓库在审计工作是非常重要的。

随着被审计单位的数据规模的不断扩大，尤其是在金融、海关、证券等领域，其数据量非常大，业务也很复杂，单纯依靠传统的SQL语句进行审计分析的方法已经不能完全满足要求，因此应借助数据仓库和多维数据分析的优势。

为在审计中利用多维分析技术，首先要做的是构建适合多维分析的数据仓库。由于被审计单位的数据并不都是规范的，因此在构建数据仓库时，必须先对原始数据进行适当的清理和转换，才能更方便地进行数据分析。

下面先讨论在构建审计分析数据仓库时，比较常见的数据清理问题以及清理办法，然后介绍一些常见的数据转换技术。

本文主要是以SQL Server数据库管理系统为例，介绍在这个系统中可利用的数据清理和转换技术。

2数据清理技术

在构建用于数据分析的数据仓库时，由于从源系统中采集到的数据来源众多、种类繁杂以及数据不规范等原因，这些源数据可能存在两种情况：第一种是有些列的数据对审计分析是无意义的；第二种是对那些有意义的数据，可能又存在某些数据值定义不完整、数据冗余等情况。这种不规范的、不完整的数据会影响后续数据分析的结果。因此，数据清理是将数据加载到数据仓库之前必须要进行的工作。

在构建审计分析数据仓库时比较常见的数据清理问题有如下几类：

●数据冗余。主要指采集到的数据表中存在着对数据分析没有意义的字段或记录。

●空值。在数据库中，空值并不等同于“0”值(对于数值型)或者空字符串(对于字符型)，空值不能进行数值型数据的加减以及比较大小等运算，大部分聚合函数在进行计算时也忽略空值，但对审计人员来说，这有可能导致某些数据分析结果不正确。因此需要对这些空值进行清理。

●数据不规范。指数据表中存在妨碍数据分析的其他情况，如数据前的空格、录入时人为省略的字段值等。

2.1冗余数据的清理

2.1.1重复行数据的清理技术

数据表中的重复行数据是指由于原始数据库设计上的缺陷(如，没有定义主码)，使数据表中存在数据值完全相同的记录，这些重复数据会影响数据分析的结果。

对重复行数据的清理方法：通过SQL语言的SELECT … INTO … 语句将原始表中的非重复行数据直接存储到一个新的分析表中。其语句格式为：

SELECT DISTINCT * INTO 新分析表名 FROM 有重复行数据的原始表名

2.2.2无用字段的清理技术

无用字段是指对审计分析没有意义的字段，这些字段的存在会增加系统的存储和处理开销，同时也可能会影响分析人员的分析思路。因此，在构建数据仓库时，应清除掉这些无用字段。

消除无用字段可以使用ALTER TABLE语句实现，具体格式为：

ALTER TABLE 表名 DROP COLUMN 无用列名1, 无用列名2，…

2.2空值的清理

空值在数据库中是一个特殊的值，它既不是0值也不是空串，它代表一个不确定的值。因此空值在与确定值进行运算时，其结果还是空值。这意味着对含有空值的列进行统计分析时，其结果可能与人们期望的不一样，因此，在构建数据分析的数据仓库时，可先对这些空值进行清理。

根据审计分析的需要，一般对空值的清理方式是将其替换为一个确定值，比如0(对数值型数据)或空串(对字符型数据)，这可利用SQL语言中的UPDATE语句实现，具体格式为：

UPDATE 表名 SET 列名 = 0 WHERE 列名 IS NULL --置为0

UPDATE 表名 SET 列名 = ' ' WHERE 列名 IS NULL --置为空串

2.3不规范数据的清理

在操作型数据库中，有可能存在由于操作人员录入数据时的不小心，在实际数据前输入了一个或多个空格，这些不起眼的空格很可能会严重影响数据分析的准确性。例如对图2所示的“支行基本表”中的数据，如果审计人员要查看“F市26支行”的贷款情况，如果按下述条件子句查询：

图2存在多余空格的数据

Where 支行名称 = 'F市26支行'

则图2中圆圈所标记的数据行将不会出现在查询结果中。为避免这种情况，在构建数据仓库时应消除数据前的无用空格。这也可以使用UPDATE语句实现，具体格式为：

UPDATE 表名 SET 列名 = LTRIM(列名)

例如：消除“支行基本表”中“支行名称”列前的无用空格的语句为：

UPDATE 支行基本表 SET 支行名称 = LTRIM(支行名称)

3数据转换技术

在将数据加载到数据仓库之前，通常需要将操作型数据转换成另一种更加适合数据分析的格式。在数据加载时，应保证数据从传统的面向操作的数据模式转换到面向分析的数据模式。

数据转换是构建面向分析的数据模式的关键。根据审计人员在构建审计分析数据仓库时比较常见的问题，我们将数据转换技术归为如下几类：

●数据类型的转换。比较常见的是非日期类型数据向日期类型数据的转换。在很多被审计单位的数据库中，经常将日期值存储为字符串类型或整型类型，这些类型不利于审计人员对数据按日期含义进行分析，特别是在需要构建时间维度时，非日期类型的数据将无法达到这个目标。

●对象名的转换。很多操作型数据库经常将表名、列名用编码或拼音缩写来命名，这样的名字不利于审计人员阅读和理解，因此，在构建数据仓库时，应将对象名转换为分析者易懂的名称。

●数据编码的转换。出于数据规范化和节省空间等目的，在被审计单位的数据库中，经常将数据的值以编码的方式存储在数据库中，比如对“经营规模”，经常用“1”表示“特大型”企业、“2”表示“大型”企业。这样的数据也不利于对数据进行直观的分析。因此，在构建数据仓库时，可将这些编码转换为易懂的内容。

●表结构的转换。这种类型的转换是指对被审计单位数据库中的表结构进行更改，使其更符合分析需求。

下面分别介绍这四种常见转换的实现技术。

3.1数据类型的转换

3.1.1直接转换的数据类型

有些数据类型的转换是可以通过系统提供的隐式类型转换规则实现的(如整型向字符类型的转换)，但有些数据类型虽然系统没有提供隐式类型转换规则，但也可以直接进行转换，这种情况可通过ALTER TABLE 语句实现。一般来说，如下的类型转换可以直接进行(以SQL Server数据库管理系统为例)：

●日期时间型转换为小日期时间型，反之亦可。

●整型类型转换为定点小数类型或浮点类型。

●存放格式为“yyyymmdd”的定长字符类型转换为日期时间型或小日期时间型。

●数值类型转换为字符类型。

●值全部为数字和小数点的字符类型转换为数值类型。

进行这类转换时，使用的ALTER TABLE语句格式为：

ALTER TABLE 表名 ALTER COLUMN 列名 新数据类型

例如，对按“yyyymmdd”格式存储日期的字符串类型，若要转换为日期类型，可使用如下语句：

ALTER TABLE表名 ALTER COLUMN 列名 SMALLDATETIME

3.1.2间接转换的数据类型

不能直接进行转换的数据类型主要是将非日期类型的数据转换为日期类型。被审计单位数据库中对日期数据的存储格式各不相同，转换的方法也不尽相同，下面分情况介绍各种日期存储格式的转换方法。

(1) 将存储格式为“mm/dd/yy”的字符串类型的日期值转换为日期类型日期值

“mm/dd/yy”格式的字符串一般不能利用上边介绍的修改列定义的方法直接将其转换为日期类型，因为系统默认的前两位代表的是年，中间两位代表的是月，最后两位代表的是日。

对这种格式的日期数据的转换方法为：

●首先将数据从“mm/dd/yy”格式的存储转换为“yyyymmdd”格式的存储，这可通过UPDATE语句实现；

●然后进行直接的数据类型转换。

将数据从“mm/dd/yy”格式转换为“yyyymmdd”格式的代码为：

UPDATE表名 SET 列名 =CASE

-- 假设此列的值均为1960年之后的日期

WHEN RIGHT(列名,2) LIKE '[6789]%' THEN

'19' + RIGHT(列名,2) + LEFT(列名,2) + SUBSTRING(列名,4,2)

ELSE-- 若是2000年及之后的日期

'20' + RIGHT(列名,2) + LEFT(列名,2) + SUBSTRING(列名,4,2)

END

之后可以用直接类型转换方法将其转换为日期类型。

(2) 将整型类型的日期值转换为日期类型的日期值

有时操作型数据库中的日期数据是用整型类型存储的，这种情况也不能直接转换为日期类型，而需要间接进行转换。将整型格式的日期值转换为日期类型的日期值的转换方法为：

●将整型类型转换为字符类型，其方法是使用如下ALTER TABLE语句：

ALTER TABLE表名 ALTER COLUMN 列名 CHAR(8)

●再用直接类型转换方法将字符串类型的日期值转换为日期类型的日期值。

3.2对象名的转换

被审计单位经常将数据库中的表名和字段名用英文字母或拼音缩写来表示，这不利于审计人员阅读和理解表及字段的含义。在构建数据仓库时，可将这些符号转换为易于理解的中文。

对表名的转换有多种方式，比较简单的方法是使用SQL Server提供的系统存储过程sp_rename，执行此存储过程的格式为：

EXEC sp_rename '原表名', '新表名'

例如，将企业数据库中的“acloanvch”表名转换为“借款凭证表”，可使用如下语句：

EXEC sp_rename ' acloanvch ', '借款凭证表'

对字段名的转换也可通过sp_rename系统存储过程实现，具体执行格式为：

EXEC sp_rename '表名.原列名', '新列名', 'COLUMN'

3.3数据编码的转换

在操作型数据库中，出于存储空间等方面的考虑，很多数据都是以编码形式保存的，而编码的具体含义则保存在数据字典中，这给数据分析造成了一定的困难，因此在构建数据仓库时需要将这些编码数据转换为易于理解的中文文字。可以通过UPDATE语句实现此功能，具体格式为：

UPDATE表名 SET 编码列名 =

CASE编码列名

WHEN 编码值1 THEN 中文含义字符串1

WHEN 编码值2 THEN中文含义字符串2

WHEN 编码值3 THEN中文含义字符串3

...

END

例如，在某企业的“法人基本信息表”中，“经营规模”字段的取值是1到5的数字编码，其对应的含义如表1所示。

对其进行转换的SQL语句为：

UPDATE 法人基本信息表 SET 经营规模 =

CASE 经营规模

WHEN '1' THEN '特大型'

WHEN '2' THEN '大型'

WHEN '3' THEN '中型'

WHEN '4' THEN '小型'

WHEN '5' THEN '其他'

END

在进行数据编码转换的过程中需要特别注意的是，转换前后的数据值是否符合列的定义。比如，如果“经营规模”字段的数据类型是整型类型而不是字符串类型的，则在执行上述语句之前，应先将数据类型转换为字符类型，而且长度要足够容纳转换后的中文字。

3.4表结构的转换

3.4.1用列数据构建新字段

有时在被审计单位的数据库表中，某些字段的代码值中蕴含着分析需要的信息，例如，图3所示为“贷款台账表”，其中的“企业代码”字段的前4位代表该企业所属的银行分行代码。这种情况下可把这些需要的值提取出来构建新的分析字段。

图3 贷款台账表部分数据示例

转换的过程为：

●为表增加新的列。可通过如下语句实现：

ALTER TABLE 表名 ADD 新列名 数据类型

●为新增列赋值。可通过如下语句实现：

UPDATE 表名 SET 新列名 = 值

例如，将图3的“企业代码”字段的前4位提取出来，构成 “分行代码”列，实现过程为：

首先在贷款台账表中添加“分行代码”列，语句如下：

ALTER TABLE 贷款台账表 ADD 分行代码 CHAR(4)

然后为新添加的列赋予合适的值：

UPDATE 贷款台账表 SET 分行代码 = LEFT (企业代码, 4 )

3.4.2为扩展分析内容构建新字段

图4所示为“担保方式表”中的部分数据，其“担保方式编码”字段是3位长的字符串。如果希望对担保方式进行更高层的概括分析，例如，对各个担保大类别(比如，“信用”或“抵押”)进行分析，则在构建数据仓库时就需要对这个表的结构进行修改，比如将“担保方式”分解为“担保方式”和“担保明细方式”，以便更多角度多层次的数据分析。

图4 担保方式表数据示例

实现此功能的SQL语句如下：

SELECT LEFT(担保方式编码,1) AS 担保方式编码,

LEFT(担保方式名称,2) AS 担保方式名称,

担保方式编码 AS 担保方式明细编码,

担保方式名称 AS 担保方式明细名称

INTO 担保方式分析表 FROM 担保方式表

转换后的表数据如图5所示。

图5 转换后的担保方式分析表数据示例

4结束语

本文简要介绍了在构建审计分析数据仓库时比较常见的数据清理和数据转换问题，介绍了这些问题的解决方法。计算机审计的目的是快速、准确地查找出问题的所在，而基于数据仓库技术的多维分析技术恰好能很好地满足审计的这个目的。要运用好多维分析技术，首先必须构建利于进行数据分析的数据仓库。在构建数据仓库时，数据从面向操作的数据库中加载到面向分析的数据仓库中，其间的清理和转换是必不可少的工作。

参考文献：

[1] 刘汝焯. 审计数据的多维分析技术[M]. 北京:清华大学出版社,2006.

[2] 何玉洁，张俊超. 数据仓库与OLAP实践教程[M]. 北京:清华大学出版社,2008.

[3] Peter GULUTZAN,Trudy PELZER. SQL-3参考大全[M]. 北京:机械工业出版社,2000.

[4] 金立钢. SQL Server 2005 BI概述——数据挖掘与多维分析模型增强(2006.9)[EB/OL]. http://www.microsoft.com/china/ technet/webcasts/class/sql_2005.mspx.

The Data Cleaning and Transformation Technology in Construction Audit Analysis Data Warehouse

HE Yu-jie

(Computer School, Beijing Information Science & Technology University, Beijing 100101, China)

Abstract: The data warehouse is the foundation of carrying on the multiple perspectives data analysis. The data warehouse technology can be used in the computer audit to analysis data, find the problem and fixes the audit key in assistent . The data structure in data warehouse is constructed for data analysis. When data warehouse is constructed , it needs to clean up and transformate the primitive operation data, which makes it more suitable for the analysis. This article introduced the common question in data cleaning up and conversion as well as the corresponding technical solution in constructing the audit analysis data warehouse.

Key words: data warehouse; data cleaning; data transformation