Author |Chen Hao (Wuhu Branch of Bank of Communications, columnist of Jiugua Financial Circle).
* |Jiugua financial circle.
With the development of enterprise informatization, banks have established numerous information systems to help enterprises process and manage internal and external business, thereby generating a large number of data resources.
A large number of data resources in the financial industry have the following difficulties due to the characteristics of the industry:
1.There is a lot of unstructured information in financial business. The financial industry has a long history and rich experience in data analysis, which is why it has accumulated a large amount of business data in the form of paper, such as contract texts and other information.
2.Financial data is widely dispersed. Taking commercial banks as an example, a large amount of data will be scattered at different levels such as the head office, branches, and secondary branches, and is not completely concentrated at the head office level, let alone data sharing between different institutions.
3.Data standards are inconsistent. Taking personal credit reporting business as an example, there are major inconsistencies in data account standards, data coding standards, data interface standards, data classification standards, and data security standards in different database systems.
4.The phenomenon of information silos is serious. Financial institutions have many heterogeneous database systems, and with the development of business, plug-in systems are difficult to integrate into the core system, resulting in the problem of information silos.
How banks deal with a large number of data resources is a hot and difficult point in research.
After years of informatization construction, domestic financial institutions have also accumulated a large amount of data through the traditional solution of "building data warehouses first, and then developing reports with manpower", but the limited analysis results produced by them are extremely limited. This model is dominated by technologists, who are likely to not know "what data is useful" and business people who are not clear about "where the useful data is". In data operation, the cooperation between the two departments will inevitably lead to problems such as slow response, poor flexibility, and difficult sharing.
The application of business intelligence (BI) allows business users to complete automatic data extraction, autonomous data analysis, self-service data mining, self-service data visualization, and even complete data exploration through natural language. In addition to designing a more optimized UI layout logic in the front end, it is equally important to build a data governance cooperation platform in the back end to turn the messy data in the financial industry into concise and concise analysis results. All of these processes require technical and business people to break down silos, be intimate, and work together.
Business intelligence (BI) is the use of data warehouse technology to store a variety of source system data into a data warehouse through data preprocessing (data extraction, transformation, loading, ETL), and through various query and analysis tools, online analysis and processing (OLAP) tools or data mining tools, to achieve data collection, management, analysis and presentation, and convert business and management data into information that can be used for enterprise decision-making. The system mainly includes four modules: data preprocessing, data warehousing, data presentation and data mining.
a) The concept of a data warehouse.
Data Warehouse, according to William HAccording to Inmon's definition in Building the Data Warehouse, a data warehouse is a topic-oriented, integrated, time-changing, and non-lossable collection of data that supports the decision-making process of the management department. A data warehouse has the following excellent features:
1) The data in the data warehouse is topic-oriented. The data in a data warehouse is organized for subject areas, which are often the focus of a user's decision-making with a data warehouse. Typical topics in the bank's data warehouse include accounts receivable, wealth management products, banking**, electronic bills, loans, deposits, customers, etc.
2) The data in the data warehouse is integrated. The data in the data warehouse can come from multiple scattered operational data, and the establishment of a data warehouse is the process of extracting, purifying, transforming, loading, and encoding the data.
3) The data in the data warehouse changes over time. In general, the time period for data in an enterprise data warehouse is typically 3-5 years, which is much longer than the time period for data in an operational system.
4) The data in the data warehouse is not updatable. Because the data in a data warehouse is a series of complex snapshots generated at one point in time, the operations involved in providing data for decision-making are primarily queries of the data.
2) Construction of bank data warehouse.
In order to realize the integration of financial data scattered in various places and stored in various forms, establish a scientific and reasonable index system and information collection mechanism, integrate information resources, and build a data center with a complete system, comprehensive content, high quality, authoritative and reliable data, and convenient extraction and query analysis, data warehouse is the best choice.
The bank data warehouse system needs to collect all the data sources related to the market operation of the enterprise's internal production management system, including the core business system, credit system, financial management system, characteristic business system, ECIF system (enterprise customer information system), risk management system, ECRM (enterprise customer relationship system) and other aspects of information, standardize and integrate them, and then standardize and integrate them according to customer information, deposits, loans, financial management, risk management, Featured business and other topics, the data will be stored in the form of a data mart, and multi-dimensional reports and mining tools are provided to provide analysts with a data warehouse system and analysis platform to solve the data dispersion and inconsistency faced by analysts before.
1. Lack of continuity in analytical work.
The architecture of a bank data warehouse consists of a data integration layer, a business processing layer, a decision support layer, and a data presentation layer, as shown in Figure 1.
Figure 1: Diagram of the bank's data warehouse architecture.
1) Data integration layer.
Data integration layerThe data integration layer extracts the data of the existing business system, supplements the collection of historical data, imports external data, and collects, cleanses, transforms (ETL) and classifies and stores these data. The extraction, transformation, and loading of data operations is what we usually call the ETL process, which is very complex and accounts for about 70% of the total time it takes to build a data warehouse
Data extraction: The task of data extraction is to extract data from the data source in preparation for data transformation and data loading. The data sources of banks usually include db2, gaussdb, kylin, mysql, fi, prestosql, and other data sources.
Data transformation: The task of data transformation is to transform the data extracted from the data source in a unified manner to form regular data that meets the requirements of the data warehouse and prepare for data loading.
Data loading: The task of data loading is to load the extracted and transformed data into the data warehouse.
After ETL operation, a large number of data resources can be classified and stored according to different topics such as customer information, deposits, loans, wealth management, risk management, and characteristic businesses, and provide effective data support for the follow-up.
2) Business processing layer.
The business processing layer, including financial data, metadata and business processing application system, realizes functions such as interactive data access, basic report generation, data sharing, data reporting and data maintenance, and processes the data stored in the classification, so as to obtain data of different themes, and provides support and data services for decision-making analysis.
3) Decision support layer.
The decision support layer is to logically classify and model statistical data, form analytical data, and use corresponding economic models, analysis and data mining tools for decision-making analysis and early warning analysis.
4) Data presentation layer.
Since different users have different needs, observation angles and observation methods for the data warehouse system, the data warehouse system should be able to provide a variety of data presentation methods to meet the needs of different users. In addition to showing the daily fixed data application support, the bank's data warehouse system can also be personalized through different roles, combined with the operation of different users and the classification of data, and displayed in various forms such as reports, graphics, and office tools, so that the data display is more in line with user habits.
1) Construction of BI platform for commercial banks.
Due to the needs of data governance and data application, the bank has comprehensively promoted the construction of the "Group Data Management and Application Platform". The main body of the platform includes BI self-service analysis, which can provide one-stop data services for all departments of the bank, including self-service data retrieval, self-service analysis, in-depth modeling, and data value mining.
The BI self-service analysis of commercial banks can meet the diversified and personalized operation management, performance appraisal, statistical analysis, and data mining scenarios of different business departments and users of different ethnic groups in the head office and branches, improve the efficiency of data use of the whole bank, cultivate professional talents for data analysis of business lines, establish a data co-construction and sharing mechanism, form a corporate culture of "speaking with data", and further improve the level of data governance of the whole bank. The built-in integrated BI tools mainly include business theme customization, portfolio analysis, perspective analysis and other functions, providing users with a data application experience of free exploration, self-service analysis, and self-sharing.
Figure 2: Architecture diagram of the BI self-service platform of commercial banks.
The architecture diagram of the BI self-service platform of a commercial bank is mainly composed of five functions: data connection, data preparation, analysis function, data display, and data viewing, as shown in Figure 2.
1) Data connection.
Data connection is a data resource that the BI platform can quickly connect to the data warehouse, data source table, and data set of the established business topics provided by the system, and these subject data will provide data support for subsequent data processing and analysis.
2) Data preparation.
Data preparation includes SQL datasets, which are for technicians, and self-service datasets for all users. SQL dataset is for the situation that the business topics provided by some systems cannot meet the analysis requirements, and technicians can create SQL datasets by themselves for subsequent data kanban production. Self-service datasets are datasets based on personalized needs, which can be visually associated with multiple tables and saved as self-service datasets based on business needs. These self-service datasets can be used for data analysis by dashboards, inventory tables, and pivot tables.
3) Analytical function.
After the data is prepared, the analysis function uses data mining technology to apply the corresponding economic model to operate the data, which can meet the daily needs of data processing with the help of multi-dimensional analysis, data drilling, data linkage, etc., and provide data support for subsequent decision-making analysis and early warning analysis.
4) Data presentation.
At present, commercial banks can provide display methods including list table, pivot table, line chart, bar chart, pie chart, funnel chart, radar chart, mixed chart, rich text, filter, ** component, scatter chart, instrument chart, rectangular tree chart, Sankey chart, index card, ranking card, label component, URL link and other components.
5) Data viewing.
The data kanban storage area made in the data operation room can be viewed through the data large screen, electronic **, combination analysis, and perspective analysis. Users can create their own unified data dashboards, and share data through OA, core systems and mobile terminals, and can also share and view the data they need through other users, so as to provide a basis for decision-making in the first time.
After the construction of the BI platform, the BI self-service platform of commercial banks provides a solution for rapid development and rapid iteration of reports, both technical personnel and business department analysts can create various display reports based on the established business themes, through combination analysis, perspective analysis and other functions, select fields from wide tables as needed, and set the display style of the table header and footer. A new report can be quickly customized as long as the data source meets your needs.
2) The application of the BI self-service platform of commercial banks.
In the traditional report development mode, the development of a new report needs to go through multiple links such as requirement writing, scheme design, system development, technical testing, business testing, and production online, which is cumbersome and time-consuming. Business personnel can complete routine data borrowing operations by themselves through the BI self-service platform, replacing the traditional mode of manual data borrowing or technical personnel background data retrieval, reducing the number of routine and repetitive data borrowing orders, and further improving the data usage efficiency of business departments. There are more than 2,000 applications of the BI platform of commercial banks after it was launched, and the following two examples are related applications of the BI self-service platform of commercial banks.
1.Personal Business Loan Customer Settlement Volume System.
In order to view the data statistics related to the settlement volume of personal business loan customers at any time, and at the same time provide data basis for the interest rate pricing of personal business loan customers, relying on a large number of data resources of the data warehouse BDHA of the head office, the settlement data of personal business loan customers is customized and developed, and the report is automatically generated through the system, which liberates labor and saves labor costs.
Function description: Statistical processing of the effective settlement volume and drop-off data of individual business loan customers and their corresponding enterprises is convenient for branches to refer to pricing, and flexible statistics can be carried out according to the time range, and the statistical results can be exported to Excel.
The statistical processing rules are as follows:
Set the settlement data of individual business loans as the settlement data of the corresponding enterprise + the settlement data of the individual, and the specific model rules are set as follows:
Assuming that the daily inflow of all personal accounts of the customer is A and the daily outflow is B, the daily net inflow of the individual is C=A-B (if the net inflow is negative, i.e., C<0, then C=0 will be uniformly taken).
If the daily inflow of all corporate accounts is d and the daily outflow is e, the daily net inflow of the company is f=d-e (if the net inflow is negative, i.e., f<0, then f=0).
then the customer's daily net inflow is x=(a 2+d)-(b 2+e) (if x<0, x=0 will be taken uniformly).
The net inflow of customers in any period of time is x=x1+x2+....xn, net personal inflow = c1 + c2 + .CN, the company's net inflow was F1+F2+.fn
At the same time, in order to improve the contribution of customer delivery, considering that the system statistics will also record the outflow of customers to corporate customers, the customer delivery will be accounted for separately, assuming that the monthly delivery to corporate customers is Y, then the amount of agency issuance in any period of time y=y1+y2+....+yn。In order to improve the importance of customers to dropshipping, in the final calculation, dropshipping will be included in the customer inflow by four times.
Therefore, after the correction, the total settlement inflow of customers is set to z=x+4y.
Finally, in order to balance the differences caused by different customer loans, the settlement loan ratio is used as the final assessment index.
Settlement loan ratio in any time period = customer settlement inflow Average daily loans.
Figure 3: Functional flow chart of the settlement volume system for individual business loan customers.
Decision-makers and account managers can view the settlement volume of personal business loan customers at any time at any time, and the decision-making department and the relevant personnel roles can analyze the interest rate pricing of personal business loan customers in real time in combination with the market, which promotes the development of business.
2.Off-site regulatory reporting system.
Data quality management is the basic work of bank operation and management, and it is also an effective support for the healthy development and risk management and control of banks. In recent years, with the gradual improvement of the requirements of the regulatory authorities for data quality and the gradual increase of report data, in order to reduce the burden on the grassroots level, reduce the workload of the grassroots jurisdiction, reduce the error of manual data statistics, improve work efficiency, and comprehensively improve the level of data management, BI tools are used to make and use them permanently at one time, reduce duplication of work, improve efficiency, and provide the required data quickly and accurately according to the requirements.
The system has set up four modules: "branch data import", "provincial bank data import", "display report" and "data comparison".
The first is to create a data collection platform to collect the scattered data of the jurisdictional bank, which can realize the effective centralization of data, reduce the pressure and error of manual statistics, break the situation of data islands between branches, and provide strong data support for subsequent decision-making management and data services.
The second is to achieve data empowerment, unified data management is an innovative exploration of using big data in the direction of data analysis, and data analysis methods and models can also be used in other business segments, laying a solid foundation for the coordinated development of the whole bank's business.
The report total score checking system has created a data collection platform to collect the scattered data of the jurisdictional banks, which can realize the effective centralization of data, reduce the pressure and error of manual statistics, break the situation of data islands between branches, and provide strong data support for subsequent decision-making management and data services.
The off-site supervision report total score verification system can promote the formation of a data sharing mechanism for the whole jurisdiction and realize the empowerment of science and technology. Effectively reduce the pressure on the grassroots, reduce the data reporting work of the business unit, take data from the system, simplify the work process, improve work efficiency, and reduce the burden on the grassrootsAt the same time, it can improve the quality of data statistics, continuously improve the quality of the statistical team, promote the construction of statistical systems and the improvement of long-term mechanisms for data quality control, consolidate the foundation of data quality, and improve statistical analysis capabilities.
The construction of BI in commercial banks not only solves the problems of difficult sharing and data inconsistency of a large amount of data, but also improves data qualityIt also provides a solution for rapid development and rapid iteration of reports, reducing repetitive work and improving work efficiency. The BI platform of commercial banks provides strong data support for decision-making management and data services, improves the work efficiency of the whole bank, and also improves the data management level of transportation to banks.