Bank Data Warehouse System Design And Realization

With the development of financial in china, bank has become to the phase, whose main features are customer-centered, management effectiveness-oriented, and information techniques innovation. Data warehouse is an effective method for information management, analysis and application. The integration of techniques of data warehouse and other software techniques is helpful for effective risk management, performance evaluation, profitability analysis and customer relationship management.This paper analyzes the application status of data warehouse in banks at home and abroad. Combining the reality of Shenzhen Development Bank, the paper proposes a total design solution for data warehouse system, and discusses the method and realization of the solution.The goal of developing an data warehouse in Shenzhen Development Bank is to build an information warehouse, which covers all the core business information, management information, and related external information in Shenzhen Development Bank, and to provide kinds of service for mangers and employees including information query, reporter automatically generating, multi-dimensions data analysis and data mining.The whole data warehouse system uses head office and branch integrate build pattern. We set up head office-level data warehouse in the head office, and branch-level data warehouse in the branches. Whose advantage is suit for the existing organization of banks. The architecture of data warehouse system are composed of three part: data warehouse, data aggregation, and data layout.1) Data warehouse is the core of the system. We use data warehouse bus architecture, whose advantage is that the development team can establish each data marts asynchronous according to the bus specification, and it can reduce the developing period and cost. This architecture has consistence and extensibility. Data modeling is a key step in data warehouse implementing, it can be classified into logical modeling and physical modeling.For dimensional model has more advantages than ER model, so we use dimensional model as logical modeling method for date warehouse. Thispaper gives the detailed design for dimensional model of bank data warehouse. First, we give the bus model matrix of bank data warehouse, and then define the consistence dimension and data marts in bank data warehouse. Second, we give the detailed dimensions modeling for data marts of deposit operations analysis. At last, we discussed such key issues as the Slowly Changing Dimensions, Surrogate Keys, snowflake Schema etc.Aiming at designing of data warehouse physical model, this paper discusses the partition strategy and index strategy based on ORACLE database. In data warehouse the fact tables are commonly super tables. We use the partition function provided by ORACLE 8i to solute the management of super tables. Data warehouse is query-centered, so a reliable index strategy is very useful. The paper discusses the strategies for indexes, above dimensions table and fact table, and then the index schedule of warehouse is provide.2) The part of data aggregation includes data extraction, transform and load (ETL). This paper proposes the solution of file transferring and data consolidation. This solution can reduce the jamming to business system as most possible, which is suit for the existing IT environment in banks. Change data capture is another issue that must be considered in data extraction. The paper introduces several change data capture methods, and discusses their advantages and disadvantages. We use data transform, date clean etc. to maintenance data consistence. In part of data load, the paper gives the handling process for slowly change dimension.3) The part of data warehouse layout has three layers: layer for report automatically generating and flexibly query, layer for topic statistic and analysis, and layer for decision supporting and data mining. According to the analysis status for commercial banks in china, we take the layer for report automatically generating and flexible query as the lowest requirement.For implementing the solution, we choose the IBM RS/6000 as the application platform, ORACLE 8i for data warehouse. We use the Oracle Warehouse Builder to complete the data modeling, data extraction, transformation, load, aggregation and management of metadata. The commercial information analysis tool Analyze is useras the data layout tools.Based on this solution, Shenzhen Development Bank has developed the comprehensive reporter system based on data warehouse. The main functionality of the system is reporter flexibly customization, generating and modifying. Data in reporter comes from the data marts based on bus model, which assure the reporter consistence among different departments. The system has got good application effect.