基于數據倉庫的行政事業單位收支管理內部控制評價

程平 楊霽莞

【摘要】數據倉庫作為能夠為內部控制評價提供各種類型海量數據支持的戰略集合，可為行政事業單位收支管理內部控制的全面、科學、智能和可視化評價提供有效的技術支撐。以重慶海事局為例，依據財政部《行政事業單位內部控制規范（試行）》及收支管理相關規范和制度，設計面向收支管理內部控制評價的數據倉庫體系結構，分析數據源和數據的ETL過程，詳細闡述從主題域確定、數據粒度劃分、事實表和維度表確定到模型設計的收支管理內部數據倉庫構建過程，最后運用Hadoop Hive數據倉庫的HiveQL語言和OLAP技術、結合案例分析基于數據倉庫的收支管理內部控制評價的具體實施。

【關鍵詞】行政事業單位;收支管理;數據倉庫;內部控制;評價

【中圖分類號】F233，C931

【文獻標識碼】A

【文章編號】1004-0994（2019）14-0092-6

一、引言

2012年11月29日，財政部印發的《行政事業單位內部控制規范（試行）》（以下簡稱《內控規范》）指出，對經濟活動業務層面的收支管理進行風險評估時要重點關注以下內容：收入是否實現歸口管理，是否按照規定及時向財會部門提供收入的有關憑據，是否按照規定保管和使用印章和票據等;發生支出事項時是否按照規定審核各類憑據的真實性、合法性，是否存在使用虛假票據套取資金的情形。這為行政事業單位的收支管理內部控制評價指明了方向。

數據倉庫是一個面向主題的、集成的、相對穩定的、反映歷史變化的數據集合，它能夠把所有信息系統和外部的數據進行集成，保證數據的一致性，并且能反映出業務系統的變化，滿足企業的深度分析需求[1]。其與數據庫的區別主要在于數據庫是以數據的增刪改查為主，而數據倉庫是以數據分析為主。在大數據時代，基于數據倉庫構建的行政事業單位收支管理內部控制評價體系，能夠對收支管理的效率和效果目標、相關信息真實完整目標、資產安全目標、合法合規目標等單個或整體控制目標的實現進行全面、科學、智能和可視化的評價。

從2012年開始，重慶海事局在持續推進財務云平臺的建設過程中，對收支管理內部控制進行信息化優化，設計了基于財務云平臺的收支管理內部控制審計指標體系[2]，并構建了審計實施流程，為基于數據倉庫的收支管理內部控制評價研究奠定了良好的基礎。鑒于此，本文以重慶海事局為例，設計了行政事業單位收支管理內部控制數據倉庫體系結構，分析了數據源和數據的抽取、轉換和加載過程，詳細闡述了收支管理內部控制數據倉庫的構建過程，最后探討了HiveQI語言數據查詢分析和OLAP多維分析在收支管理內部控制評價中的具體應用。

二、面向收支管理內部控制評價的數據倉庫體系結構設計

1.設計思路。《內控規范》從收入控制、支出控制、票據管理、歸檔控制、職責權限管理、債務管理等方面制定了七條具體的行政事業單位收支業務控制條例。依據收支管理相關規范進行內部控制評價，本質上是面向收支管理內部控制評價進行數據倉庫體系結構設計的過程，其設計思路是收支業務相關數據首先通過數據抽取、轉換和加載（ETI）到數據倉庫中進行集中存儲和管理，再按照星型模型或雪花模型組織數據建立若干數據集市，構建收支管理內部控制數據倉庫，然后利用HiveQL查詢語言或者OLAP工具從數據倉庫中讀取數據進行內部控制有效性分析和評價，進而形成面向收支管理內部控制評價的數據倉庫體系結構。

2.設計過程。行政事業單位收支管理內部控制評價數據倉庫體系結構的設計包括數據源分析與數據ETL、內部控制評價數據倉庫構建、內部控制數據分析與評價、內部控制評價結果輸出等四個過程。以重慶海事局為例，其收支管理內部控制評價數據倉庫體系結構設計過程圖參考《財會月刊》2019年第13期文章《基于數據倉庫的行政事業單位單位層面內部控制評價——以重慶海事局為例》相關內容[3]。

在收支管理內部控制評價數據倉庫體系結構設計過程中，收支管理內部控制評價數據源主要來自海事局收支業務管理和內部控制有關的制度規范、海事局財務云平臺的收支業務數據、金蝶K3系統的會計憑證賬表數據、各類報銷標準、各種票據掃描影像等，為數據倉庫的構建提供最基礎的原始數據;原始數據經過Sqoop數據庫ETL過程后，基于Hadoop架構的收支管理內部控制評價大數據平臺，構建形成包括收入管理、支出管理、債務管理3大類共7個主題數據集市的收支管理內部控制評價數據倉庫（Hive）;數據分析與評價則利用HiveQL語言對數據倉庫的數據進行查詢和分析，運用OLAP工具和數據挖掘算法（Mahout）對收支管理內部控制數據進行多維分析與智能評價;內部控制的分析可以通過餅狀圖、直方圖、折線圖等統計圖形進行可視化展示，實時評價結果可以通過圖文并茂的形式進行動態呈現和風險預警，并自動生成多粒度、多維度的收支管理內部控制評價報告。

三、收支管理內部控制評價數據源分析與ETL

收支管理內部控制評價數據倉庫的構建，首先需要分析收支管理內部控制評價涉及的數據來源，再進行ETL數據抽取、轉換和加載，才能為數據倉庫構建做好最基礎的數據準備。

1.數據源分析。重慶海事局收支管理內部控制評價的數據來源較多，數據量較大，包括結構化、半結構化和非結構化三種數據類型。結構化數據主要包括來自財務云平臺中收支管理模型的付款記錄表、報銷記錄表、借款記錄表等數據庫管理系統中的數據，來自金蝶K3財務軟件系統的收付款憑證和賬表數據，來自費收管理系統的業務數據，以及來自Excel的收支相關管理數據。非結構化數據主要包括海事局收支管理過程中相關的規章制度、報銷標準、會議紀要、收支票據掃描影像、合同影像等數據結構不規則、不完整，不方便用數據庫二維邏輯表直接存儲的數據，主要以視頻、音頻、圖像、文檔、文本等形式存儲。半結構化數據不同于關系型數據庫或列表中具有特定數據模型結構的數據，但可以用數據標記或其他元素來分隔語義元素以及對記錄和字段進行分層，主要包括郵件、HTML網頁、資源庫等。

2.數據抽取、轉換和加載（ETL）。收支管理內部控制ETL是將數據通過抽取、清洗轉換之后再加載到數據倉庫的過程，目的是將分散、零亂、標準不統一的收支管理內部控制相關數據整合到一起，為分析評價提供依據。數據的抽取是從各個不同的數據源抽取到ODS（Operational Data Store，操作型數據存儲）中，這個過程也可以做一些數據的清洗和轉換，在抽取的過程中為了保證以及提高ETL的運行效率，需要根據數據源數據的特點，選用合理的抽取方法。數據清洗是對數據進行重新審查和校驗的過程，過濾掉缺損的數據、錯誤的數據、冗余的數據等不符合要求的數據，保證數據的一致性。ETL的實現方法有多種，包括借助ETL工具實現、以SQL方式實現、以ETL工具和SQL相結合的方式實現等。

數據ETL工具Sqoop可以實現Hadoop和關系型數據庫中數據的相互轉換，將關系型數據庫（如MySQL、Oracle、Postgres等）中的數據導入Hadoop的HDFS中。對于結構化數據，可以通過Sqoop ETL工具直接從海事局財務云平臺和費收管理系統的SQL Server數據庫中進行抽取，運用該工具進行轉換、加載，解決數據冗余、語義矛盾、格式不統一等問題，最終形成能被Hive數據倉庫存儲和集成的，結構標準、格式規范的結構化數據，例如對于類型為文本格式的時間字段，需要將其轉換為統一的時間類型格式，方便對其從時間維度進行分析。

半結構化和非結構化數據的ETL，針對PDF、Rtf. Word、Excel和PowerPoint等格式的文件主要是通過自然語言分詞技術、針對圖像格式的文件主要是通過OCR光學字符識別技術、針對影音格式的文件主要是通過語義識別和自然語言分詞技術，提取形成Excel和數據庫等類型的結構化數據，然后通過Sqoop等ETL工具進行轉換、加載到數據倉庫中。例如，與報銷有關的各項報銷標準主要是通過Word格式存儲，需要將其處理形成Excel格式文件或者數據庫文件后才能導入數據倉庫中。

四、收支管理內部控制評價數據倉庫構建

行政事業單位收支管理內部控制評價數據倉庫的構建主要包括確定主題域、劃分數據粒度、確定事實表和維度表、模型設計等環節。

1.確定主題域。主題域通常是聯系較為緊密的數據主題的集合。根據《內控規范》對收支管理內部控制的具體規定以及收支管理內部控制審計指標設計[2]，再結合海事局收支業務處理的邏輯關系、數據勾稽關系以及關聯關系，將收支管理內部控制評價劃分為7大主題域，具體見表1。

2.劃分數據粒度。數據粒度是數據倉庫中存儲數據的細化或綜合程度。數據倉庫中數據表包含的海量數據用何種粒度進行存儲，將進一步影響查詢分析和數據挖掘的效果，最終影響收支管理內部控制評價的效果。

數據倉庫粒度劃分主要考慮是采用單一粒度還是多重粒度，以及粒度的劃分層級。粒度層級越低，細化程度越高，反之則反。例如，針對行政事業單位支出核算處理及時性的評價需求，時間維度數據粒度需要劃分為“年”“月”“日”三個粒度層級。

3.確定事實表和維度表。收支管理內部控制數據倉庫由多個事實表和維度表組成，一個事實表必須與一個或多個維度表建立關聯關系。事實表和維度表中的數據必須根據具體的主題域確定。

事實表用來存儲事實的度量值和指向各個維度表的外鍵值，主要存儲數字類型數據標記而非詳細的描述性信息。根據確定的主題域，收支管理內部控制數據倉庫可以建立收入歸口管理執行性事實表、票據保管規范性事實表、票據使用范圍合理性事實表、報銷審批流程完整性事實表、支出范圍遵循性事實表、支出核算處理及時性事實表、債務控制流程完整性事實表。

維度表用于描述維度層次及成員類別等元數據信息，用以記錄事實表中特性描述以及事實記錄信息，以便為內部控制評價提供有用的信息。例如，為了評價支出核算處理的及時性，可以通過建立時間維度表、報銷類型維度表和人員維度表來分析和評價支出業務發生時間和核算時間的時間間隔。

4.模型設計。根據事實表和維度表之間的關系，在設計具體模型時，根據評價需求，應當考慮是選用星型模型還是雪花模型進行數據的組織。

數據倉庫用于為內部控制評價提供決策支持手段，而數據集市是數據倉庫的子集，用于滿足特殊應用需求的數據倉庫通常包含較少的數據量和主題域。出于評價需求，根據主題域內容，將收支管理內部控制數據倉庫劃分為七個數據集市。

分析收支管理內部控制確定的主題域，各個主題均具有多維特性，且在不同的主題域中存在著相互關聯與數據共享，事實表共享多個維度表，不同維度表之間也存在關聯關系，基于此特性，收支管理內部控制評價數據倉庫選用雪花模型進行數據組織，其模型描述及其數據組織見表2。

以支出核算處理及時性內部控制評價主題為例，采用雪花模型進行數據組織，主要包括報銷類型、時間、人員維度表，其中，人員維度又關聯部門維度和權限維度，具體如圖1所示。

根據支出核算處理及時性的評價需求，分析事實表和維度表的數據特征，再進行事實表和維度表具體的物理結構設計，支出核算處理及時性事實表和維度表以及表中字段見表3。

五、基于數據倉庫的收支管理內部控制評價應用

查詢分析、OLAP聯機分析處理和數據挖掘是基于數據倉庫進行收支管理內部控制評價的重要方法。Hive是基于Hadoop的一個數據倉庫，其通過HiveQL語言進行數據查詢分析。OLAP聯機分析處理允許在被稱為多維數據集的多維結構中訪問聚合并組織后的數據，使分析人員能快速、一致和交互地分析各個維度的信息，以達到獲取分析數據價值的目的。數據挖掘通常是指通過決策樹、隨機森林等算法從大量數據中提取有價值的隱含信息的過程。因篇幅有限，下面主要闡述HiveQL數據查詢和OLAP多維分析在支出業務內部控制評價中的具體應用。

1.基于HiveQL語言的內部控制情況查詢分析評價。原始數據經過ETL過程構建數據倉庫后，可以根據評價主題確定分析點，然后編寫HiveQL語句進行數據查詢分析。

（1）報銷審批流程完整性。考察是否具有完整的報銷審批流程，是否對不同的崗位層級、人員、報銷金額、報銷業務設置不同的報銷方案。運用HiveQL語句，提取出報銷審批流程完整性事實表需要分析的字段[“方案名稱”“報銷（金額）標準”“適用對象”等]進行如下詳細分析。

查找報銷審批流程是否涵蓋所有報銷類目，首先提取事實表中涉及的報銷類型，與報銷類型維度表數據進行比對，找出報銷類型不完整的數據列，對應的HiveQL分析語句如下：

Hive>

SELECT DISTINCT I__Bxlx FROM NK YW—SZ—SS__ BXSPLCWZX

WHERE I Bxlx NOT IN

（SEIECT ID FROM NK YW_SZ_WD_BXLX）

篩查每一條報銷方案任何一個字段出現空值的列，找出流程內容不完整的數據列，對應的HiveQL分析語句如下：

Hive>

SELECT*FROM NKj YW_SZ_SS__ BXSPLCWZX

WHERE VC_Title =-OR I_BusinessID =-ORVC_Business=-OR VC_Note=～

OR M_Begin =-OR MEnd 2-OR I_Role 2"OR VC_Role =-OR I_NAME =-OR VC_NAME= -

篩查每一條報銷方案的適用對象，查看相同業務類型下不同的報銷方案是否覆蓋所有的崗位層級（適用于任何崗位人員、一般部門人員、部長、局長），找出未能覆蓋到的對象，對應的HiveQL語句如下：

Hive>

SELECT DISTINCT I__ Role FROM NK YW—SZ—SS__ BXSPLCWZX

WHERE I_Role NOT IN （SEIECT ID FROMNK YW_SZ_WD_SYDX）

（2）支出范圍遵循性。對比支出的報銷內容下對應的金額范圍是否遵循報銷標準。運用HiveQL語句提取出支出范圍遵循性事實表需要分析的字段，具體分析如下：

以差旅費報銷為例，查找出每條報銷記錄中“人數”“往返地”“住宿費”“住宿標準”“往返交通費”“往返交通費標準”“伙食費”“伙食費標準”等字段，找出實際費用超出“人數”×對應報銷標準的報銷金額超標的數據列，對應的HiveQL語句如下：

Hive>

SELECT 4 FROM NK__ YW—SZ__ SS__. ZCFWZXX aJOIN NK_YW_SZ_WD_BXSTAND b on （a.I_Bxlx=b.ID） WHERE b.VC_Bxlx=‘差旅費’

AND a.M_Food>a.I_People*b.M_FoodStand OR a.M_Hode>a.I_People*b.M_HoteIStand OR a.M_City-in+a.M_Cityout>a.LPeople*b.M_City

其他報銷類型的內部控制評價數據分析參照差旅費報銷，在此不做贅述。

（3）支出核算處理及時性。對比支出業務的發生時間與支出核算時間的差值，即報銷憑證錄入中的“業務結束日期”和“報銷日期”的時間間隔是否超出規定期限。運用HiveQL語句，提取出支出核算處理及時性事實表中的“業務結束日期”和“報銷日期”，計算每一條記錄的時間間隔是否超時（如按照有關規定支出核算業務處理天數不得超過半個月），對應的HiveQL語句如下：

Hive>

SELECT * FROM NK_ YW_SZ_SS_BXCLJSX

WHERE DATEDDIFF （d， D-Llnput， DT_Bx》15

2.基于OLAP的內部控制多維分析評價。在數據倉庫中存儲的數據不僅能像關系數據庫中存儲二維關系數據，通常還需要反映數據多維的信息甚至查看某一維度下一級維度的信息，例如收支管理內部控制評價數據倉庫中支出核算處理及時性主題對應的多維數據集（報銷類型維一時間維一人員維）可以表示為：（報銷類型維度，時間維度，人員維度，3），根據多維數據子集構建的多維立方體是邏輯上的數據組織形式，是進行OLAP的分析主體，支出核算處理及時性OLAP評價模型如圖2所示。

通過擴展Hive的OLAP驅動建立OLAP多維數據分析模型后，可以通過對多維模型的切片、切塊、上卷、下鉆等操作進行收支管理內部控制數據分析和評價。

（1）切片。數據切片操作用于選取和定義收支管理內部控制評價主題多維數組的一個二維子集，例如，可以從報銷類型、時間、人員三個維度組織建立的三維數據結構中選取報銷類型維度和時間維度進行二維切片。

（2）切塊。數據切塊是將多個切片進行疊加，進而形成特定維度區間的操作。例如，可以從報銷類型、時間和人員組成的三維數據結構中選取報銷類型、時間和人員維度進行三維切塊。

切片和切塊后的模型如圖3所示。

時間維度：如按日、月、年對數據進行分組匯總，不僅可以按不同長度時間點查看數據，還可以統計不同時間段的數據，從而更好地從短期到長期對報銷數據進行統計分析，以及評價其中可能存在的風險。

報銷類型維度：按不同報銷類型對報銷數據進行分組匯總，按類別統計，可以對平時風險較多的報銷類型進行重點分析。

人員維度：根據人員ID對報銷數據進行分組匯總統計，重點關注存在較多風險的報銷核算處理數據的錄入人員。

（3）鉆取。鉆取包括上卷和下鉆兩種操作。上卷是從明細級數據向上匯總到高級數據視圖;下鉆是從高級數據向下鉆取到明細級數據視圖。對數據鉆取的能力取決于數據結構以及粒度層級。

主要參考文獻：

[1]王珊，王會舉，覃雄派等.架構大數據：挑戰、現狀與展望[J].計算機學報，2011（10）：1741～1752.

[2]程平，尹赤.數據視角下基于財務云平臺的收支管理內部控制審計——以重慶海事局為例[J].財會月刊，2018（2）：129～134.

[3]程平，范洵.基于數據倉庫的行政事業單位單位層面內部控制評價——以重慶海事局為例[J].財會月刊.2019（ 13）：71～76.