Design And Construction Of A Data Warehouse System For The Commercial Bank Operation Management

Facing the intense competition, a commercial bank needs to try to understand andmaster the operation management situation, at the same time it should take actions toadapt the management decision which is appropriate for risk tolerance and managementlevel. In case for the future usage it needs a data warehouse system support which give auniversal data management concerning the bank monitor, operation management and allkind of decision. At the same time the business innovation pressure makes it hasbecome a mandatory choice for banking.This thesis mainly studies how to design and construct data warehouse system fora commercial bank operation management which reference the IBM ReferenceArchitecture as well as the mainstream data warehouse architecture. and adapted thetheory of two masters Inomon and Kimball. Basically it is driven by business, from thearchitecture, Modeling, ETL etc. which are the core technologies to develop the datawarehouse. The architecture design also borrowed idea of the Architecture DescriptionStandard V2.0.ODS(Operational Data Store) System is a source-copy design which is a directcopy of the business data from the source system which will be loaded into ODS layeraccording to the data analysis result.There are three layers for the data extract, transform,and load tactics of EDW, including10JOB,20JOB,30JOB.10JOB is charge of the deltafiles creation from DataMirror.20JOB will finish the conversion of the surrogate keysand data types.30JOB will handle with the data and generate the flat files according tothe business mapping rules, which will be used to load. Then load data into SSA（SORStaging Area）.For higher performance the function will be implemented by DB2loadcommand.Ultimately the data will be stored into the SOR（System Of Record）.After data hasbeen loaded into the SSA and the apply action will be kicked off which will implementby the AP SQL. In the same way the fault tolerance mechanism will be applied. For thehistory table loading it can support daily batching running. Based on the solution of the data warehouse, we put forward an end-to-endsolution for the construction of commercial bank data warehouse, which covered theunderlying data extraction, integration, transformation to the data warehousemanagement, Data Mart, load management, security controlling, and front-end displayas well as the web application deployment. A job scheduler platform was developed forthe daily batching running. The scheduled tasks included the source data extracting,ETL processing, and data distribution of Data Mart and the operation managementsystem for the decision support Decision Support.