基于Sqlserver2008數據庫下的報表服務方案探析

劉建軍

(吉林省教育學院 網絡信息中心，長春130022)

1 報表服務方案

1.1 報表服務方案

圖1 報表服務整體流程

針對業務數據庫報表數據情況不同，采用兩種方式進行數據同步。

(1)同步增量報表。對數據基數大且數據更新頻繁的業務表，則采用同步增量統計分析結果的方式，即同步增量報表。即在中間數據庫上，對業務表的cdc數據的同義詞按業務邏輯進行報表分析匯總，獲得增量報表，同步該增量報表到報表數據。同步過程主要為合并過程，可理解為兩個二維統計表的邏輯加。

(2)同步報表。對數據基數比較小或數據更新頻率較低的業務表，則采用同步統計分析結果，直接同步報表的方式。即在中間數據庫上，對業務表的同義詞直接按業務邏輯進行分析匯總后，同步報表到報表數據。同步過程主要為替換過程，可理解為先刪除再新增。

圖2 同步增量報表

圖3 同步增量報表服務業務流程

圖4 同步報表

圖5 同步報表服務業務流程

1.2 報表服務表結構

1.3 報表服務過程及其描述

表2 報表服務存儲過程

2 報表服務使用步驟

2.1 初始準備

(1)啟用sqlserver數據庫的SQL Server Agent代理服務。

(2)對sqlserver數據庫的對應業務數據庫啟動CDC即變更數據捕捉功能。

執行SQL代碼為：EXEC sys.sp_cdc_enable_db。

(3)分析原報表服務統計分析SQL語句中涉及的對象范圍、數據量級別來確定是否對該對象啟用CDC。

例如：

報表服務對A1,A2,A3表統計匯總，A1、A2表數據量很大，A3表數據量很少，數據均有頻繁變動，則需要對A1,A2,A3表均啟用變更數據捕捉。

報表服務對A1,A2,A3表統計匯總，A1、A2表數據量很大并有數據變化，A3表數據量很少且數據變化不頻繁，則只需對A1,A2表均啟用變更數據捕捉.

報表服務對A1,A2,A3表統計匯總，A1,A2,A3表數據量均很小，A1,A2,A3數據變化頻率低且變化數據范圍較小，則不需要啟用變更數據捕捉，而采用報表服務方式二處理.

(4)確定變更捕捉字段，確定捕捉源表中哪些字段的數據變化到變更捕獲表中。

(5)執行EXEC sys.sp_cdc_enable_table并調整相應參數對表及相應字段啟用變更數據捕捉，隨即在系統表下生成cdc架構下的對應該表的變更數據捕獲表。

(6)在報表數據庫下建立報表服務涉及表的同義詞,啟用cdc有變更捕獲表則建立變更捕獲表的同義詞，沒有啟用cdc，則直接對該表建立同義詞。

(7)在報表數據庫下建立報表數據存儲表,用作存儲執行報表、或者增量報表數據。報表服務采用MERGE方法同步數據該報表數據存儲表。

2.2 報表服務實現步驟

(1)調用過程Prcd _Reg，注冊報表服務信息到日志記錄表。

(2)調用過程Prcd_AppLock，對業務存儲過程加應用程序鎖，以保證該過程執行的唯一性。

(3)調用過程Prcd_ClearCDC，清理變更表中失效數據。

(4)調用過程Prcd_GetLSN,獲得變更捕獲表中的最大最小LSN。

(5)同步增量統計報表結果到報表數據存儲表。

獲得增量數據方法：

第一類，例如報表服務業務SQL:

select a.column1,sum(a.column2),sum(b.column3) from a join b on a.column1=b.column1 .

其中column1為業務主鍵，column2,column3為數值類型字段

只需要將表a,b替換，

select a.column1,sum(a.column2),sum(b.column3) from

(select column1,sum(case __$operation when 1 then -column2 when 2 then column2 when 3 then - column2 else column2 end) from a where __$start_lsn >0 and __$start_lsn< ? group by column1) a

join

(select column1,sum(case __$operation when 1 then -column3 when 2 then column3 when 3 then - column3 else column3 end) from a where __$start_lsn >0 and __$start_lsn< ? group by column1) b

on a.column1=b.column1 .

另外需要加條件加以控制增量統計范圍，__$start_lsn為LSN事務序列號。

第二類，例如報表服務業務SQL:

Select column1,sum(column2) from a group by column1

其中column1為字符類型字段，column2為數值類型字段，且column1不一定為主鍵。Column1字段可能會變更并影響報表結果。

Select column1,sum(column2) from

(select column1,sum(case __$operation when 1 then -column2 when 2 then column2 when 3 then - column2 else column2 end) from a where __$start_lsn >0 and __$start_lsn< ? ) a

group by column1

MERGE同步數據：

示例SQL腳本如下(需要將上述增量計算SQL替換【源結果集】位置，將報表數據存儲表名替換到【目標表】位置)：

merge 目標表 as t

using( 源結果集 ) as s

on(s.關聯鍵值 = t. 關聯鍵值)

when matched then

update set t.字段 = s. 字段+ t.字段

when not matched then

insert values(s.字段) ;

(6)根據業務復雜度可能重復上述同步增量統計數據過程。

(7)調用過程Prcd_RunLog，將執行信息寫入到日志表。

(8)調用過程Prcd_AppUnLock，對業務存儲過程解應用程序鎖。

(9)如對數據基數小且變更不頻繁的報表服務業務表作統計分析，因為未開啟變更數據捕捉，則使用步驟類似但有所區別，所不同的是不需采用獲得增量數據過程，而是直接執行merge同步統計分析數據過程。

(10)當報表存儲過程業務流程處理過程中出現異常，則調用存儲過程Prcd_ErrorLog存儲過程記錄異常到異常記錄表。

[1] 楊志國.SQL Server2005數據庫管理精講[M].北京:電子工業出版社, 2007.

[2] Kalen Delaney.Microsoft SQl Server 2005技術內幕:存儲引擎[M].北京：電子工業出版社，2010.

[3] 陳振.基于日志分析的SQL Server數據庫變更數據捕獲方法的研究與實現[D].廣州：暨南大學,2010.

[4] 孫燕.異構數據庫數據同步的關鍵技術研究[D].唐山：華北理工大學，2015.