構(gòu)建審計(jì)分析數(shù)據(jù)倉(cāng)庫(kù)中的數(shù)據(jù)清理及轉(zhuǎn)換技術(shù)

文章編號(hào)：1672-5913(2009)08-0129-04

摘要：數(shù)據(jù)倉(cāng)庫(kù)是進(jìn)行多角度數(shù)據(jù)分析的基礎(chǔ)，在審計(jì)中可借助數(shù)據(jù)倉(cāng)庫(kù)技術(shù)分析數(shù)據(jù)，用以輔助發(fā)現(xiàn)問(wèn)題和鎖定審計(jì)重點(diǎn)。數(shù)據(jù)倉(cāng)庫(kù)中的數(shù)據(jù)結(jié)構(gòu)是面向數(shù)據(jù)分析設(shè)計(jì)的，在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)需要對(duì)原始操作型的數(shù)據(jù)進(jìn)行適當(dāng)?shù)那謇砗娃D(zhuǎn)換，使其更適合分析的需求。本文介紹了構(gòu)建審計(jì)分析數(shù)據(jù)倉(cāng)庫(kù)中常見的數(shù)據(jù)清理和數(shù)據(jù)轉(zhuǎn)換問(wèn)題以及相應(yīng)的解決技術(shù)。

關(guān)鍵詞：數(shù)據(jù)倉(cāng)庫(kù)；數(shù)據(jù)清理；數(shù)據(jù)轉(zhuǎn)換

中圖文分類號(hào)：G642

文獻(xiàn)標(biāo)識(shí)碼：B

1數(shù)據(jù)倉(cāng)庫(kù)技術(shù)及在審計(jì)中的作用

隨著計(jì)算機(jī)及數(shù)據(jù)庫(kù)技術(shù)的飛速發(fā)展，人們對(duì)數(shù)據(jù)的處理提出了更高的要求，希望計(jì)算機(jī)能更多的參與數(shù)據(jù)分析與決策的制定等領(lǐng)域。因此數(shù)據(jù)處理的發(fā)展相應(yīng)地形成了兩大類型，一類是操作型處理，即我們?nèi)粘５氖聞?wù)型處理，比如銀行業(yè)務(wù)、商品零售業(yè)務(wù)等；另一類是分析型處理，比如分析數(shù)據(jù)之間潛在的關(guān)聯(lián)關(guān)系，分析一段時(shí)間內(nèi)數(shù)據(jù)的變化情況等。數(shù)據(jù)倉(cāng)庫(kù)技術(shù)是為第二類應(yīng)用而產(chǎn)生的，其主要目的是構(gòu)建適合數(shù)據(jù)分析的數(shù)據(jù)結(jié)構(gòu)，并將事務(wù)型操作中的業(yè)務(wù)數(shù)據(jù)，經(jīng)過(guò)適當(dāng)?shù)那謇?、轉(zhuǎn)換，加載到數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)庫(kù)中，從而更利于進(jìn)行數(shù)據(jù)分析。目前常用的數(shù)據(jù)分析技術(shù)有聯(lián)機(jī)分析處理和數(shù)據(jù)挖掘，聯(lián)機(jī)分析處理是從多個(gè)角度分析數(shù)據(jù)，也稱為多維分析，而數(shù)據(jù)挖掘是發(fā)現(xiàn)數(shù)據(jù)之間潛在的關(guān)聯(lián)關(guān)系。聯(lián)機(jī)分析處理和數(shù)據(jù)挖掘針對(duì)的對(duì)象都是數(shù)據(jù)倉(cāng)庫(kù)中的數(shù)據(jù)，因此構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)的結(jié)構(gòu)和數(shù)據(jù)是進(jìn)行數(shù)據(jù)分析的基礎(chǔ)。

事務(wù)數(shù)據(jù)、數(shù)據(jù)倉(cāng)庫(kù)、聯(lián)機(jī)分析處理以及數(shù)據(jù)挖掘之間的關(guān)系示意圖如圖1所示。

圖1事務(wù)數(shù)據(jù)、數(shù)據(jù)倉(cāng)庫(kù)及數(shù)據(jù)分析間的關(guān)系

近幾年我國(guó)的審計(jì)技術(shù)水平發(fā)展非常迅速，審計(jì)方法也從過(guò)去的手工翻賬，發(fā)展到今天的利用計(jì)算機(jī)進(jìn)行審計(jì)。目前用計(jì)算機(jī)進(jìn)行審計(jì)主要有兩種方法，一種是直接針對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)編寫審計(jì)分析的SQL語(yǔ)句，這種方法的好處是靈活方便，缺點(diǎn)是不利于快速鎖定審計(jì)重點(diǎn)，發(fā)現(xiàn)問(wèn)題；另一種就是構(gòu)建審計(jì)分析的數(shù)據(jù)倉(cāng)庫(kù)，然后利用多維分析技術(shù)對(duì)數(shù)據(jù)進(jìn)行多角度分析，這種方法便于快速鎖定審計(jì)重點(diǎn)，發(fā)現(xiàn)數(shù)據(jù)之間內(nèi)在的關(guān)系和問(wèn)題。

審計(jì)工作的好壞關(guān)系到國(guó)家的財(cái)政安全、金融安全等諸多方面，做好國(guó)家各方面的審計(jì)工作有助于揭示存在的風(fēng)險(xiǎn)，提出防范和化解風(fēng)險(xiǎn)的對(duì)策性建議，維護(hù)國(guó)家的穩(wěn)定和安全。因此，正確地構(gòu)建適合審計(jì)分析的數(shù)據(jù)倉(cāng)庫(kù)在審計(jì)工作是非常重要的。

隨著被審計(jì)單位的數(shù)據(jù)規(guī)模的不斷擴(kuò)大，尤其是在金融、海關(guān)、證券等領(lǐng)域，其數(shù)據(jù)量非常大，業(yè)務(wù)也很復(fù)雜，單純依靠傳統(tǒng)的SQL語(yǔ)句進(jìn)行審計(jì)分析的方法已經(jīng)不能完全滿足要求，因此應(yīng)借助數(shù)據(jù)倉(cāng)庫(kù)和多維數(shù)據(jù)分析的優(yōu)勢(shì)。

為在審計(jì)中利用多維分析技術(shù)，首先要做的是構(gòu)建適合多維分析的數(shù)據(jù)倉(cāng)庫(kù)。由于被審計(jì)單位的數(shù)據(jù)并不都是規(guī)范的，因此在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)，必須先對(duì)原始數(shù)據(jù)進(jìn)行適當(dāng)?shù)那謇砗娃D(zhuǎn)換，才能更方便地進(jìn)行數(shù)據(jù)分析。

下面先討論在構(gòu)建審計(jì)分析數(shù)據(jù)倉(cāng)庫(kù)時(shí)，比較常見的數(shù)據(jù)清理問(wèn)題以及清理辦法，然后介紹一些常見的數(shù)據(jù)轉(zhuǎn)換技術(shù)。

本文主要是以SQL Server數(shù)據(jù)庫(kù)管理系統(tǒng)為例，介紹在這個(gè)系統(tǒng)中可利用的數(shù)據(jù)清理和轉(zhuǎn)換技術(shù)。

2數(shù)據(jù)清理技術(shù)

在構(gòu)建用于數(shù)據(jù)分析的數(shù)據(jù)倉(cāng)庫(kù)時(shí)，由于從源系統(tǒng)中采集到的數(shù)據(jù)來(lái)源眾多、種類繁雜以及數(shù)據(jù)不規(guī)范等原因，這些源數(shù)據(jù)可能存在兩種情況：第一種是有些列的數(shù)據(jù)對(duì)審計(jì)分析是無(wú)意義的；第二種是對(duì)那些有意義的數(shù)據(jù)，可能又存在某些數(shù)據(jù)值定義不完整、數(shù)據(jù)冗余等情況。這種不規(guī)范的、不完整的數(shù)據(jù)會(huì)影響后續(xù)數(shù)據(jù)分析的結(jié)果。因此，數(shù)據(jù)清理是將數(shù)據(jù)加載到數(shù)據(jù)倉(cāng)庫(kù)之前必須要進(jìn)行的工作。

在構(gòu)建審計(jì)分析數(shù)據(jù)倉(cāng)庫(kù)時(shí)比較常見的數(shù)據(jù)清理問(wèn)題有如下幾類：

●數(shù)據(jù)冗余。主要指采集到的數(shù)據(jù)表中存在著對(duì)數(shù)據(jù)分析沒(méi)有意義的字段或記錄。

●空值。在數(shù)據(jù)庫(kù)中，空值并不等同于“0”值(對(duì)于數(shù)值型)或者空字符串(對(duì)于字符型)，空值不能進(jìn)行數(shù)值型數(shù)據(jù)的加減以及比較大小等運(yùn)算，大部分聚合函數(shù)在進(jìn)行計(jì)算時(shí)也忽略空值，但對(duì)審計(jì)人員來(lái)說(shuō)，這有可能導(dǎo)致某些數(shù)據(jù)分析結(jié)果不正確。因此需要對(duì)這些空值進(jìn)行清理。

●數(shù)據(jù)不規(guī)范。指數(shù)據(jù)表中存在妨礙數(shù)據(jù)分析的其他情況，如數(shù)據(jù)前的空格、錄入時(shí)人為省略的字段值等。

2.1冗余數(shù)據(jù)的清理

2.1.1重復(fù)行數(shù)據(jù)的清理技術(shù)

數(shù)據(jù)表中的重復(fù)行數(shù)據(jù)是指由于原始數(shù)據(jù)庫(kù)設(shè)計(jì)上的缺陷(如，沒(méi)有定義主碼)，使數(shù)據(jù)表中存在數(shù)據(jù)值完全相同的記錄，這些重復(fù)數(shù)據(jù)會(huì)影響數(shù)據(jù)分析的結(jié)果。

對(duì)重復(fù)行數(shù)據(jù)的清理方法：通過(guò)SQL語(yǔ)言的SELECT … INTO … 語(yǔ)句將原始表中的非重復(fù)行數(shù)據(jù)直接存儲(chǔ)到一個(gè)新的分析表中。其語(yǔ)句格式為：

SELECT DISTINCT * INTO 新分析表名 FROM 有重復(fù)行數(shù)據(jù)的原始表名

2.2.2無(wú)用字段的清理技術(shù)

無(wú)用字段是指對(duì)審計(jì)分析沒(méi)有意義的字段，這些字段的存在會(huì)增加系統(tǒng)的存儲(chǔ)和處理開銷，同時(shí)也可能會(huì)影響分析人員的分析思路。因此，在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)，應(yīng)清除掉這些無(wú)用字段。

消除無(wú)用字段可以使用ALTER TABLE語(yǔ)句實(shí)現(xiàn)，具體格式為：

ALTER TABLE 表名 DROP COLUMN 無(wú)用列名1, 無(wú)用列名2，…

2.2空值的清理

空值在數(shù)據(jù)庫(kù)中是一個(gè)特殊的值，它既不是0值也不是空串，它代表一個(gè)不確定的值。因此空值在與確定值進(jìn)行運(yùn)算時(shí)，其結(jié)果還是空值。這意味著對(duì)含有空值的列進(jìn)行統(tǒng)計(jì)分析時(shí)，其結(jié)果可能與人們期望的不一樣，因此，在構(gòu)建數(shù)據(jù)分析的數(shù)據(jù)倉(cāng)庫(kù)時(shí)，可先對(duì)這些空值進(jìn)行清理。

根據(jù)審計(jì)分析的需要，一般對(duì)空值的清理方式是將其替換為一個(gè)確定值，比如0(對(duì)數(shù)值型數(shù)據(jù))或空串(對(duì)字符型數(shù)據(jù))，這可利用SQL語(yǔ)言中的UPDATE語(yǔ)句實(shí)現(xiàn)，具體格式為：

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

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

2.3不規(guī)范數(shù)據(jù)的清理

在操作型數(shù)據(jù)庫(kù)中，有可能存在由于操作人員錄入數(shù)據(jù)時(shí)的不小心，在實(shí)際數(shù)據(jù)前輸入了一個(gè)或多個(gè)空格，這些不起眼的空格很可能會(huì)嚴(yán)重影響數(shù)據(jù)分析的準(zhǔn)確性。例如對(duì)圖2所示的“支行基本表”中的數(shù)據(jù)，如果審計(jì)人員要查看“F市26支行”的貸款情況，如果按下述條件子句查詢：

圖2存在多余空格的數(shù)據(jù)

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

則圖2中圓圈所標(biāo)記的數(shù)據(jù)行將不會(huì)出現(xiàn)在查詢結(jié)果中。為避免這種情況，在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)應(yīng)消除數(shù)據(jù)前的無(wú)用空格。這也可以使用UPDATE語(yǔ)句實(shí)現(xiàn)，具體格式為：

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

例如：消除“支行基本表”中“支行名稱”列前的無(wú)用空格的語(yǔ)句為：

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

3數(shù)據(jù)轉(zhuǎn)換技術(shù)

在將數(shù)據(jù)加載到數(shù)據(jù)倉(cāng)庫(kù)之前，通常需要將操作型數(shù)據(jù)轉(zhuǎn)換成另一種更加適合數(shù)據(jù)分析的格式。在數(shù)據(jù)加載時(shí)，應(yīng)保證數(shù)據(jù)從傳統(tǒng)的面向操作的數(shù)據(jù)模式轉(zhuǎn)換到面向分析的數(shù)據(jù)模式。

數(shù)據(jù)轉(zhuǎn)換是構(gòu)建面向分析的數(shù)據(jù)模式的關(guān)鍵。根據(jù)審計(jì)人員在構(gòu)建審計(jì)分析數(shù)據(jù)倉(cāng)庫(kù)時(shí)比較常見的問(wèn)題，我們將數(shù)據(jù)轉(zhuǎn)換技術(shù)歸為如下幾類：

●數(shù)據(jù)類型的轉(zhuǎn)換。比較常見的是非日期類型數(shù)據(jù)向日期類型數(shù)據(jù)的轉(zhuǎn)換。在很多被審計(jì)單位的數(shù)據(jù)庫(kù)中，經(jīng)常將日期值存儲(chǔ)為字符串類型或整型類型，這些類型不利于審計(jì)人員對(duì)數(shù)據(jù)按日期含義進(jìn)行分析，特別是在需要構(gòu)建時(shí)間維度時(shí)，非日期類型的數(shù)據(jù)將無(wú)法達(dá)到這個(gè)目標(biāo)。

●對(duì)象名的轉(zhuǎn)換。很多操作型數(shù)據(jù)庫(kù)經(jīng)常將表名、列名用編碼或拼音縮寫來(lái)命名，這樣的名字不利于審計(jì)人員閱讀和理解，因此，在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)，應(yīng)將對(duì)象名轉(zhuǎn)換為分析者易懂的名稱。

●數(shù)據(jù)編碼的轉(zhuǎn)換。出于數(shù)據(jù)規(guī)范化和節(jié)省空間等目的，在被審計(jì)單位的數(shù)據(jù)庫(kù)中，經(jīng)常將數(shù)據(jù)的值以編碼的方式存儲(chǔ)在數(shù)據(jù)庫(kù)中，比如對(duì)“經(jīng)營(yíng)規(guī)?！?，經(jīng)常用“1”表示“特大型”企業(yè)、“2”表示“大型”企業(yè)。這樣的數(shù)據(jù)也不利于對(duì)數(shù)據(jù)進(jìn)行直觀的分析。因此，在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)，可將這些編碼轉(zhuǎn)換為易懂的內(nèi)容。

●表結(jié)構(gòu)的轉(zhuǎn)換。這種類型的轉(zhuǎn)換是指對(duì)被審計(jì)單位數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)進(jìn)行更改，使其更符合分析需求。

下面分別介紹這四種常見轉(zhuǎn)換的實(shí)現(xiàn)技術(shù)。

3.1數(shù)據(jù)類型的轉(zhuǎn)換

3.1.1直接轉(zhuǎn)換的數(shù)據(jù)類型

有些數(shù)據(jù)類型的轉(zhuǎn)換是可以通過(guò)系統(tǒng)提供的隱式類型轉(zhuǎn)換規(guī)則實(shí)現(xiàn)的(如整型向字符類型的轉(zhuǎn)換)，但有些數(shù)據(jù)類型雖然系統(tǒng)沒(méi)有提供隱式類型轉(zhuǎn)換規(guī)則，但也可以直接進(jìn)行轉(zhuǎn)換，這種情況可通過(guò)ALTER TABLE 語(yǔ)句實(shí)現(xiàn)。一般來(lái)說(shuō)，如下的類型轉(zhuǎn)換可以直接進(jìn)行(以SQL Server數(shù)據(jù)庫(kù)管理系統(tǒng)為例)：

●日期時(shí)間型轉(zhuǎn)換為小日期時(shí)間型，反之亦可。

●整型類型轉(zhuǎn)換為定點(diǎn)小數(shù)類型或浮點(diǎn)類型。

●存放格式為“yyyymmdd”的定長(zhǎng)字符類型轉(zhuǎn)換為日期時(shí)間型或小日期時(shí)間型。

●數(shù)值類型轉(zhuǎn)換為字符類型。

●值全部為數(shù)字和小數(shù)點(diǎn)的字符類型轉(zhuǎn)換為數(shù)值類型。

進(jìn)行這類轉(zhuǎn)換時(shí)，使用的ALTER TABLE語(yǔ)句格式為：

ALTER TABLE 表名 ALTER COLUMN 列名 新數(shù)據(jù)類型

例如，對(duì)按“yyyymmdd”格式存儲(chǔ)日期的字符串類型，若要轉(zhuǎn)換為日期類型，可使用如下語(yǔ)句：

ALTER TABLE表名 ALTER COLUMN 列名 SMALLDATETIME

3.1.2間接轉(zhuǎn)換的數(shù)據(jù)類型

不能直接進(jìn)行轉(zhuǎn)換的數(shù)據(jù)類型主要是將非日期類型的數(shù)據(jù)轉(zhuǎn)換為日期類型。被審計(jì)單位數(shù)據(jù)庫(kù)中對(duì)日期數(shù)據(jù)的存儲(chǔ)格式各不相同，轉(zhuǎn)換的方法也不盡相同，下面分情況介紹各種日期存儲(chǔ)格式的轉(zhuǎn)換方法。

(1) 將存儲(chǔ)格式為“mm/dd/yy”的字符串類型的日期值轉(zhuǎn)換為日期類型日期值

“mm/dd/yy”格式的字符串一般不能利用上邊介紹的修改列定義的方法直接將其轉(zhuǎn)換為日期類型，因?yàn)橄到y(tǒng)默認(rèn)的前兩位代表的是年，中間兩位代表的是月，最后兩位代表的是日。

對(duì)這種格式的日期數(shù)據(jù)的轉(zhuǎn)換方法為：

●首先將數(shù)據(jù)從“mm/dd/yy”格式的存儲(chǔ)轉(zhuǎn)換為“yyyymmdd”格式的存儲(chǔ)，這可通過(guò)UPDATE語(yǔ)句實(shí)現(xiàn)；

●然后進(jìn)行直接的數(shù)據(jù)類型轉(zhuǎn)換。

將數(shù)據(jù)從“mm/dd/yy”格式轉(zhuǎn)換為“yyyymmdd”格式的代碼為：

UPDATE表名 SET 列名 =CASE

-- 假設(shè)此列的值均為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

之后可以用直接類型轉(zhuǎn)換方法將其轉(zhuǎn)換為日期類型。

(2) 將整型類型的日期值轉(zhuǎn)換為日期類型的日期值

有時(shí)操作型數(shù)據(jù)庫(kù)中的日期數(shù)據(jù)是用整型類型存儲(chǔ)的，這種情況也不能直接轉(zhuǎn)換為日期類型，而需要間接進(jìn)行轉(zhuǎn)換。將整型格式的日期值轉(zhuǎn)換為日期類型的日期值的轉(zhuǎn)換方法為：

●將整型類型轉(zhuǎn)換為字符類型，其方法是使用如下ALTER TABLE語(yǔ)句：

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

●再用直接類型轉(zhuǎn)換方法將字符串類型的日期值轉(zhuǎn)換為日期類型的日期值。

3.2對(duì)象名的轉(zhuǎn)換

被審計(jì)單位經(jīng)常將數(shù)據(jù)庫(kù)中的表名和字段名用英文字母或拼音縮寫來(lái)表示，這不利于審計(jì)人員閱讀和理解表及字段的含義。在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)，可將這些符號(hào)轉(zhuǎn)換為易于理解的中文。

對(duì)表名的轉(zhuǎn)換有多種方式，比較簡(jiǎn)單的方法是使用SQL Server提供的系統(tǒng)存儲(chǔ)過(guò)程sp_rename，執(zhí)行此存儲(chǔ)過(guò)程的格式為：

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

例如，將企業(yè)數(shù)據(jù)庫(kù)中的“acloanvch”表名轉(zhuǎn)換為“借款憑證表”，可使用如下語(yǔ)句：

EXEC sp_rename ' acloanvch ', '借款憑證表'

對(duì)字段名的轉(zhuǎn)換也可通過(guò)sp_rename系統(tǒng)存儲(chǔ)過(guò)程實(shí)現(xiàn)，具體執(zhí)行格式為：

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

3.3數(shù)據(jù)編碼的轉(zhuǎn)換

在操作型數(shù)據(jù)庫(kù)中，出于存儲(chǔ)空間等方面的考慮，很多數(shù)據(jù)都是以編碼形式保存的，而編碼的具體含義則保存在數(shù)據(jù)字典中，這給數(shù)據(jù)分析造成了一定的困難，因此在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)需要將這些編碼數(shù)據(jù)轉(zhuǎn)換為易于理解的中文文字?？梢酝ㄟ^(guò)UPDATE語(yǔ)句實(shí)現(xiàn)此功能，具體格式為：

UPDATE表名 SET 編碼列名 =

CASE編碼列名

WHEN 編碼值1 THEN 中文含義字符串1

WHEN 編碼值2 THEN中文含義字符串2

WHEN 編碼值3 THEN中文含義字符串3

...

END

例如，在某企業(yè)的“法人基本信息表”中，“經(jīng)營(yíng)規(guī)?！弊侄蔚娜≈凳?到5的數(shù)字編碼，其對(duì)應(yīng)的含義如表1所示。

對(duì)其進(jìn)行轉(zhuǎn)換的SQL語(yǔ)句為：

UPDATE 法人基本信息表 SET 經(jīng)營(yíng)規(guī)模 =

CASE 經(jīng)營(yíng)規(guī)模

WHEN '1' THEN '特大型'

WHEN '2' THEN '大型'

WHEN '3' THEN '中型'

WHEN '4' THEN '小型'

WHEN '5' THEN '其他'

END

在進(jìn)行數(shù)據(jù)編碼轉(zhuǎn)換的過(guò)程中需要特別注意的是，轉(zhuǎn)換前后的數(shù)據(jù)值是否符合列的定義。比如，如果“經(jīng)營(yíng)規(guī)模”字段的數(shù)據(jù)類型是整型類型而不是字符串類型的，則在執(zhí)行上述語(yǔ)句之前，應(yīng)先將數(shù)據(jù)類型轉(zhuǎn)換為字符類型，而且長(zhǎng)度要足夠容納轉(zhuǎn)換后的中文字。

3.4表結(jié)構(gòu)的轉(zhuǎn)換

3.4.1用列數(shù)據(jù)構(gòu)建新字段

有時(shí)在被審計(jì)單位的數(shù)據(jù)庫(kù)表中，某些字段的代碼值中蘊(yùn)含著分析需要的信息，例如，圖3所示為“貸款臺(tái)賬表”，其中的“企業(yè)代碼”字段的前4位代表該企業(yè)所屬的銀行分行代碼。這種情況下可把這些需要的值提取出來(lái)構(gòu)建新的分析字段。

圖3 貸款臺(tái)賬表部分?jǐn)?shù)據(jù)示例

轉(zhuǎn)換的過(guò)程為：

●為表增加新的列?？赏ㄟ^(guò)如下語(yǔ)句實(shí)現(xiàn)：

ALTER TABLE 表名 ADD 新列名 數(shù)據(jù)類型

●為新增列賦值?？赏ㄟ^(guò)如下語(yǔ)句實(shí)現(xiàn)：

UPDATE 表名 SET 新列名 = 值

例如，將圖3的“企業(yè)代碼”字段的前4位提取出來(lái)，構(gòu)成 “分行代碼”列，實(shí)現(xiàn)過(guò)程為：

首先在貸款臺(tái)賬表中添加“分行代碼”列，語(yǔ)句如下：

ALTER TABLE 貸款臺(tái)賬表 ADD 分行代碼 CHAR(4)

然后為新添加的列賦予合適的值：

UPDATE 貸款臺(tái)賬表 SET 分行代碼 = LEFT (企業(yè)代碼, 4 )

3.4.2為擴(kuò)展分析內(nèi)容構(gòu)建新字段

圖4所示為“擔(dān)保方式表”中的部分?jǐn)?shù)據(jù)，其“擔(dān)保方式編碼”字段是3位長(zhǎng)的字符串。如果希望對(duì)擔(dān)保方式進(jìn)行更高層的概括分析，例如，對(duì)各個(gè)擔(dān)保大類別(比如，“信用”或“抵押”)進(jìn)行分析，則在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)就需要對(duì)這個(gè)表的結(jié)構(gòu)進(jìn)行修改，比如將“擔(dān)保方式”分解為“擔(dān)保方式”和“擔(dān)保明細(xì)方式”，以便更多角度多層次的數(shù)據(jù)分析。

圖4 擔(dān)保方式表數(shù)據(jù)示例

實(shí)現(xiàn)此功能的SQL語(yǔ)句如下：

SELECT LEFT(擔(dān)保方式編碼,1) AS 擔(dān)保方式編碼,

LEFT(擔(dān)保方式名稱,2) AS 擔(dān)保方式名稱,

擔(dān)保方式編碼 AS 擔(dān)保方式明細(xì)編碼,

擔(dān)保方式名稱 AS 擔(dān)保方式明細(xì)名稱

INTO 擔(dān)保方式分析表 FROM 擔(dān)保方式表

轉(zhuǎn)換后的表數(shù)據(jù)如圖5所示。

圖5 轉(zhuǎn)換后的擔(dān)保方式分析表數(shù)據(jù)示例

4結(jié)束語(yǔ)

本文簡(jiǎn)要介紹了在構(gòu)建審計(jì)分析數(shù)據(jù)倉(cāng)庫(kù)時(shí)比較常見的數(shù)據(jù)清理和數(shù)據(jù)轉(zhuǎn)換問(wèn)題，介紹了這些問(wèn)題的解決方法。計(jì)算機(jī)審計(jì)的目的是快速、準(zhǔn)確地查找出問(wèn)題的所在，而基于數(shù)據(jù)倉(cāng)庫(kù)技術(shù)的多維分析技術(shù)恰好能很好地滿足審計(jì)的這個(gè)目的。要運(yùn)用好多維分析技術(shù)，首先必須構(gòu)建利于進(jìn)行數(shù)據(jù)分析的數(shù)據(jù)倉(cāng)庫(kù)。在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)時(shí)，數(shù)據(jù)從面向操作的數(shù)據(jù)庫(kù)中加載到面向分析的數(shù)據(jù)倉(cāng)庫(kù)中，其間的清理和轉(zhuǎn)換是必不可少的工作。

參考文獻(xiàn)：

[1] 劉汝焯. 審計(jì)數(shù)據(jù)的多維分析技術(shù)[M]. 北京:清華大學(xué)出版社,2006.

[2] 何玉潔，張俊超. 數(shù)據(jù)倉(cāng)庫(kù)與OLAP實(shí)踐教程[M]. 北京:清華大學(xué)出版社,2008.

[3] Peter GULUTZAN,Trudy PELZER. SQL-3參考大全[M]. 北京:機(jī)械工業(yè)出版社,2000.

[4] 金立鋼. SQL Server 2005 BI概述——數(shù)據(jù)挖掘與多維分析模型增強(qiáng)(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