数据仓库建设探索与实践

广发银行东莞分行 胡小英 李 翔

1.前言

我行自业务主机上收总行后，为了满足分行的本地的报表查询和业务开展，通过将总行下发的数据存放在分行的Oracle数据库中，进行2次开发。但由于Oracle数据库的复杂性和以前建设仓促等原因，数据利用率、安全性不高，而且不能很好地满足业务需要，于是我们决定重新搭建分行数据仓库。

通过一段时间的摸索，我们最终决定采用两台SqlServer2008数据库，一台只存储最新一年数据的生产库，另一台是存储历史数据的历史库。两台服务器上的数据在生产库建立分区视图统一对外暴露出来，外部几乎不知道有历史库的存在，却能够查询所有时间点的数据。以后如果历史服务器负担重，我们可以再多添加几台服务器，把数据稀释到几台服务器上，这样可以通过增加服务器实现资源的扩展。

2.数据仓库建设过程

2.1 搭建生产库和历史库服务器。

3台机器均采用Windows Server 2008R2操作系统，内存8-16GB，CPU 2.93GHz*2，数据库为Microsoft SQL Server2008R2SP2(64位)，两者是基于微软同一平台开发的产品，配合起来更能发挥效果。EDS数据库因承担着大量的数据更新操作，使用简单恢复模式，避免产生大量的日志拖慢更新数据及占用磁盘空间。

2.2 制定数据归档策略，实现数据归档功能，实现分区视图重建功能。通过实现这两个功能来简化维护操作，其实即使没有，也可以手工维护。

2.2.1 通过权衡和数据测试，确定历史数据以分区表的形式存储在历史数据库中，按照季度进行分区，流水数据分区字段为qsrq，时点数据分区字段为jsrq，数据库会自行为数据组织存放位置，对于用户而言是透明的，由于数据归档时涉及的表多，数据多，通过自动归档的存储过程(可制定归档日期)，只要加入到每天的调度中，就可以在每天晚上自动归档数据(例如每天晚上自动归档三个月前的数据)。经过测试一旦单表的归档记录数超过一千万(可能和系统内存有关)，通过存储过程来归档要花费超过半小时的时间(存储过程不能多线程，2千8百万数据用了1个小时)，考虑到100多张表的归档排队，时间会大大延长，一般情况下像“历史资料表”，“科目日记表”，“会计分录表”等表的记录数增长很快，所以每年或每季度的间隔方式做归档不太好。目前78张表一个季度的数据大概25G，考虑到新增的48张CBS表，以及其他为加快查询而新建的索引等，即使按照每季度归档数据的方式，生产机的数据容量预计可以控制在100G以内。

2.2.2 另外在实践中每次归档的时候必须根据qsrq或jsrq重建约束，目前还不能实现自动化，所以放弃了一部分性能，不做分区视图，直接使用普通视图，经过测试查询时间所受的影响有限(分区表的优势抵消了大部分影响)。

2.3 选择一个合理的初始时间点，从EDS备份的文件中依次上传数据到最新日期并持续上送数据，保证数据最新。

我们选择2009-12-04作为起始时点，将EDS下发的数据上传到EDS生产数据库。上传情况良好，78个文件的上传时间保持在10分钟左右完成，

2.4 数据迁移，将初始时间点之前的流水表的历史数据迁移到历史库，至此数据已全部迁入新的数据库体系。

2.5 制定数据访问规则，以用户为单位做只读授权，不论谁，要访问业务数据，必须申请用户并指定要访问的数据，数据库管理员增加用户(或修改用户)进行授权访问，不再允许任何其他形式对EDS数据(包括接收的dat文件)的访问(在整改后)，也不允许开放诸如sa等特权用户。

创建的用户包括数据库管理员用户；管理用户ids(映射到EDS,HIS,IDS数据库的public,db_owner角色；用于对EDS,HIS,IDS三个库的数据管理或数据维护，例如增删存储过程，增删表，视图，同义词，访问授权等)；数据上传操作用户uploader(映射到EDS数据库的public,db_owner角色；用于EDS数据上传更新操作，EDS上传程序专用)；数据读取角色dbreader(用于外部程序使用，映射到IDS,EDS,HIS数据库的public角色；对dbreader用户的授权原则：统一在IDS数据库下进行授权访问，即只对dbreader开放IDS数据库的对象访问权限，比如表/视图/同义词的SELECT权限，或者存储过程和函数的执行权限，按照最小原则开放)。

2.6 对一些常见的费时查询进行测试。

案例一：查询综合业务系统中客户名包含“三”(like‘%三%’)的所有客户的活期存款账号，户名以及这些账号在2010年2月18日时点余额。

分析：以上查询使用到三个视图，“客户资料表”为生产机当前时点的客户资料数据(在视图中内置了条件29991231 between qsrq and jsrq)，“客户状况表”为生产机当前时点的活期账号资料档(在视图中内置了条件29991231 between qsrq and jsrq)，“客户余额表”为活期账号余额档的联合视图(通过20100218 between qsrq and jsrq这个条件获得20100218时点切片数据)，可以看到查询时间为10秒，物理读取0次，这个结果还是在没有对客户资料档的CUSNA1做索引的情况下得出的。我们目前在Oracle首先就无法进行时点切片数据查询，即无法查询任意时点的余额。

案例二：查询一户通系统客户名含有“李”的所有客户的一户通卡号，户名，账号，子账号以及20100209当日的余额。

分析：通过三个联合视图，查找20100 209的时点切片数据进行联合查询，获得超过10万条记录，耗时17秒，没有对PIFNAM字段建立索引。

案例三：模糊查询一户通系统证件号441900%760507023%的客户的卡号，户名，账号，子账号以及20100118当日的余额。

分析：同上例，从三个联合视图进行查询，得到77条记录，耗时7秒，未对PIFCER字段建立索引。

3.总结

(1)以上案例使用的是目前较为常见的查询，而且都是模糊查询，理论上来说应该是非常耗时的，但实际的测试表现确实很优秀。

(2)数据库的瓶颈在于IO读写。1)内存越大，越能避免IO读写，得到的性能越高；2)Raid阵列使一组物理磁盘的读写条带化，提高了IO读写效率；3)分区表使数据库能够快速屏蔽掉不需要的数据，并在多个分区进行并行查询加快速度。以上案例的表现已超出我的预期。

当中运用的一些方法参考了现有的考核系统，提高了效率，只要EDS有数，就可以很方便的通过添加函数和存储过程来进行加工，生成需要的数据或报表，使得对数据的分析和查询更为方便快捷。下一步我们将对现有应用系统进行改造，逐步将原来建立在Oracle数据库中的应用迁移到这个新的平台上去。