The Design And Implementation Of A Payment Statistical Information Management System Based On ETL

As the most important financial institution in economic activities,commercial banks have the functions of regulating economy,credit creation,credit intermediary,payment intermediary and financial service.Payment data generated from the payment system is the original record of economic and financial activities,and also can accurately,real-timely,objectively reflect the macroeconomic performance.At most foreign banks,payment related data may be stored in the core system developed by the parent bank,the local RMB payment system and other peripheral systems,or only exist in a form of original documents.Although foreign banks in China is still in its infancy,and data collection,summary and analysis can be realized through manual work by use of Access,Excel and other tools,yet this is revealed as lack of efficiency and high error rate issues with the increasing business in China.Nevertheless,the system of each bank is different,as well as the data format,means of storage,there is no ready-made products can complete this work.This paper designs and implements a data statistics system for payment business based on ETL technology.The system enables to extract the original data which contain payment information from various scattered business systems and to compile the original data according to the definition of statistical indicators that issued by the PBOC,so as to finally generate a report that complies with the requirements of the People's Bank of China.From the technic perspective,the system adopted B/S architecture based on the ASP.NET platform and SQL Server database.The system is divided into five layers: user interface layer,control layer,business logic layer,data persistence layer and system service layer,which reduces the coupling degree of the system and improves its scalability.At the same time ETL technology,PKI and other technologies have been applied in the process of the system development.At the operation level,the system is divided into several core modules such as data extraction,data processing,data verification,data warning,report generation and user ID management according to the job role division and the requirements of business process of M Bank.The modular design enables the organic collaboration crossing the units and meanwhile independency of individual department,which effectively upgrades the overall degree of automation of the business and reduces the workload by improving work efficiency.In order to solve the problems of dispersing raw data storage,different extraction conditions,and various statistical indexes,that existed in the process of system design and development,ETL technology has been applied to standardize various extraction conditions and implement the same criteria to define various extraction conditions,which improves the system scalability and flexibility to respond to the changes in line with the requirements from the authorities and business development of the bank.Meanwhile,the system uses a Windows integrated authentication mode,allowing users to login the computer with personal domain account without re-enter the password to access the system,which increase the convenience of system access,but also ensure the security of the system.The design and implementation of this system has been successfully implemented within M Bank for one year.The systematic process improves the efficiency of data extraction and accuracy,and reduces the staff workload.The report which previously needs at least 2 weeks by manual can be generated by the system and completed auditing and reporting within 1-2 days.The system realized its intent of design and made good effect in the efficiency improvement.