A data processing method and system based on data warehouse model
By adopting data processing methods based on data warehouse models in the financial industry, the problem that traditional methods are difficult to deal with large-scale complex data is solved, efficient data processing and analysis is achieved, and data security and decision support are enhanced.
Patent Information
- Application Number
- CN202410629802.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-05-21
- Publication Date
- 2024-11-19
- Estimated Expiration
- 2044-05-21
AI Technical Summary
Traditional data processing methods are difficult to meet the financial industry's need to efficiently and accurately process large-scale complex data, especially when data types are diversified and transaction volumes are increasing.
Using data processing methods based on data warehouse model, we use real-time and historical data from financial transaction sources, preprocess and standardize, design a star-shaped data warehouse, and use statistical analysis and machine learning technology to perform data analysis and risk level division. And generate a visual report.
It improves data processing efficiency and accuracy, supports complex data analysis, discovers implicit relationships and trends between data, enhances data security and privacy protection, and supports financial market analysis and decision-making.
Smart Images

Figure CN118535657B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the financial field, and in particular to a data processing method and system based on a data warehouse model. Background Art
[0002] In today's financial industry, data collection, management, and analysis have become crucial. With the increase in transaction volume and the diversification of data types, traditional data processing methods can no longer meet the needs of efficiently and accurately processing large-scale complex data. Therefore, data processing methods based on data warehouses have emerged to improve data processing efficiency, support complex data analysis, and provide a scientific basis for decision-making.
[0003] Data warehouse technology provides an effective data integration solution, enabling enterprises to centrally manage and analyze data from different sources. By establishing a centralized data storage environment, enterprises can better conduct data mining, predictive analysis, and data visualization. In addition, the star model, as a common data model in data warehouses, significantly improves query efficiency by organizing data in fact tables and multiple dimension tables, thereby speeding up the decision-making process.
[0004] In view of this, the present invention proposes a data processing method and system based on a data warehouse model. Summary of the invention
[0005] The purpose of this section is to summarize some aspects of embodiments of the present invention and briefly introduce some preferred embodiments. Some simplifications or omissions may be made in this section and the specification abstract and the invention title of this application to avoid blurring the purpose of this section, the specification abstract and the invention title, and such simplifications or omissions cannot be used to limit the scope of the present invention.
[0006] In view of the above-mentioned problems, the present invention is proposed.
[0007] To solve the above technical problems, the present invention provides the following technical solutions: a data processing method based on a data warehouse model, characterized in that it includes: acquiring transaction data from a financial transaction source and preprocessing the transaction data; designing a data warehouse, associating the transaction data with a star table, and aggregating the transaction data; mining and analyzing the transaction data, and analyzing the data using statistical analysis and machine learning techniques; constructing a visual report, and preparing a report based on the transaction data analysis results to provide support for financial decision-making.
[0008] Acquire real-time and historical financial data from a variety of financial transaction sources, using APIs and web crawler technology to obtain real-time data, such as stock market trading systems, bank transaction records, and financial aggregation media;
[0009] Preprocess the acquired data to remove outliers and qualified but non-compliant data items, standardize the data, and unify the data format; add labels to the acquired data, create industry classification labels and risk labels for the data, and merge them with the master data to store them as complete transaction data.
[0010] Design a data warehouse to store transaction data;
[0011] The data contents in the transaction data are associated in a star table and stored in the data warehouse; in the star table, the relationship between the data items is represented in the form of a fact table and a dimension table; the fact table includes the transaction amount, transaction time and transaction type; the dimension table includes a customer dimension table, a time dimension table and a geographic dimension table; the customer dimension table is used to record the customer's information and behavior type; the time dimension table is used to record the time information of the transaction and is stored in the form of a time series; the geographic dimension table contains transaction location information for regional analysis;
[0012] The data is aggregated in the form of a star table, and the association between data items is used as the topological relationship between the data.
[0013] Select a database as the carrier of the data warehouse model, including Oracle, SQL Server and MySOL; convert the transaction data in the data warehouse into tables, columns, data types and constraints in the database according to the topological logic of the star model; create indexes for fact tables and dimension tables in the database to optimize query performance; partition the tables in the database according to transaction amount, transaction time and transaction type according to the topological logic in the star table;
[0014] Establish and configure an authentication mechanism for the database to ensure that authorized users access the data warehouse, define different types of users and permissions to control access to the data warehouse by different users and user groups, encrypt the data stored in the data warehouse, including static data encryption and transmission data encryption; use TLS / SSL protocol to protect data transmission.
[0015] Conduct correlation analysis between different transaction data, calculate the correlation between different types of transaction data, and use partial correlation coefficients to analyze the correlation between different types of transaction data.
[0016] Perform correlation analysis on different transaction data X and transaction data Y to construct the trend of transaction data X and Y:
[0017] X=β 0A +β 1A T+β 2A D+∈x;
[0018] Y=β 0B +β1B T+β 2B D+∈y;
[0019] Among them, β 0A , β 0B , β 1A , β 1B , β 2A and β 2B are regression coefficients, ∈x and ∈y are regression residuals, representing the changes in transaction amounts A and B after removing the effects of transaction type and transaction time; ∈x and ∈y are obtained by subtracting the portion predicted by transaction type and time from the actual transaction amount;
[0020] Use the values of ∈x and ∈y to represent the correlation between transaction data X and Y:
[0021]
[0022] in, and is the mean value of the residuals; γ(XY,M) represents the correlation between transaction data X and transaction data Y. If γ(XY,M) is significantly different from zero, there is a significant linear correlation between transaction data X and transaction data.
[0023] Using machine learning technology, after the machine learning model learns the risk levels of different types of transaction data, newly created transactions are classified into risk levels;
[0024] According to the transaction behavior characteristics, the transaction data is manually classified into risk levels, the risk levels are used as labels for the transaction data, and the transaction data and the corresponding risk labels are used as data sets to train the machine learning model to learn transaction risk classification; the data sets include training sets, validation sets, and test sets;
[0025] The machine learning model learns different types of transaction data and corresponding risk levels, and uses the probabilities of different risk levels in the training set data as output. Training stops when the number of training times for the machine learning model is reached.
[0026] The trained machine learning model is deployed to the data warehouse. When new transaction data is stored in the data warehouse, the risk level of the transaction data is analyzed by the machine learning model; the machine learning model is a decision tree model.
[0027] Generate visual reports on data stored in the data warehouse, use data analysis tools, create dashboards and charts to display intuitive analysis results;
[0028] Share analysis results through online platforms and internal systems to ensure user access to result information.
[0029] A system for a data processing method based on a data warehouse model, comprising: a data acquisition and processing module, a data warehouse module, a data analysis module and a visualization module;
[0030] The data acquisition and processing module is used to acquire transaction data from a financial transaction source and pre-process the transaction data;
[0031] The data warehouse module is used to design a data warehouse, associate transaction data with a star table, and perform data aggregation on the transaction data;
[0032] The data analysis module is used to mine and analyze transaction data, and analyze the data using statistical analysis and machine learning techniques;
[0033] The visualization module constructs visualization reports and produces reports based on the transaction data analysis results to provide support for financial decision-making.
[0034] The beneficial effects of the present invention are as follows: improving data processing efficiency, by acquiring data from multiple financial transaction sources in real time and adopting preprocessing techniques such as removing outliers, standardizing and adding labels, the present invention can effectively improve data accuracy and processing speed.
[0035] It supports complex data analysis and uses a star model to make data storage and query more efficient. At the same time, through statistical analysis and machine learning technology, it can conduct in-depth data mining to discover implicit relationships and trends between data, providing strong technical support for financial market analysis.
[0036] Enhance data security and privacy protection, implement encryption measures and configure authentication mechanisms in the data warehouse to ensure that only authorized users can access sensitive data, thereby protecting enterprise data security and customer privacy.
[0037] Support decision-making by analyzing the risk level of transaction data through machine learning models and generating visual reports. Decision makers can intuitively understand market dynamics and potential risks and make data-based decisions faster. BRIEF DESCRIPTION OF THE DRAWINGS
[0038] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for describing the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative labor. Among them:
[0039] Figure 1 This is a flow chart of a data processing method based on a data warehouse model of the present invention;
[0040] Figure 2 This is a structural diagram of a data processing system based on a data warehouse model of the present invention. DETAILED DESCRIPTION
[0041] In order to make the above-mentioned purposes, features and advantages of the present invention more understandable, the specific implementation methods of the present invention are described in detail below in conjunction with the drawings of the specification. Obviously, the described embodiments are part of the embodiments of the present invention, but not all of them. Based on the embodiments of the present invention, all other embodiments obtained by ordinary persons in the art without creative work should fall within the scope of protection of the present invention.
[0042] In the following description, many specific details are set forth to facilitate a full understanding of the present invention, but the present invention may also be implemented in other ways different from those described herein, and those skilled in the art may make similar generalizations without violating the connotation of the present invention. Therefore, the present invention is not limited to the specific embodiments disclosed below.
[0043] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The term "in one embodiment" that appears in different places in this specification does not necessarily refer to the same embodiment, nor does it refer to a separate or selective embodiment that is mutually exclusive with other embodiments.
[0044] The present invention is described in detail with reference to schematic diagrams. When describing the embodiments of the present invention, for the sake of convenience, the cross-sectional diagrams showing the device structure will not be partially enlarged according to the general scale, and the schematic diagrams are only examples, which should not limit the scope of protection of the present invention. In addition, in actual production, the three-dimensional dimensions of length, width and depth should be included.
[0045] At the same time, in the description of the present invention, it should be noted that the directions or positional relationships indicated by the terms "upper, lower, inner and outer" are based on the directions or positional relationships shown in the drawings, and are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific direction, be constructed and operated in a specific direction, and therefore cannot be understood as limiting the present invention. In addition, the terms "first, second or third" are only used for descriptive purposes and cannot be understood as indicating or implying relative importance.
[0046] In the present invention, unless otherwise clearly specified and limited, the terms "install, connect, connect" should be understood in a broad sense, for example: it can be a fixed connection, a detachable connection or an integral connection; it can also be a mechanical connection, an electrical connection or a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.
[0047] Example 1
[0048] Reference Figure 1 , which is the first embodiment of the present invention, provides a data processing method based on a data warehouse model.
[0049] Acquire real-time and historical financial data from multiple financial transaction sources, using APIs and web crawler technology to obtain real-time data. The financial transaction sources include stock market trading systems, bank transaction records, and financial aggregation media.
[0050] Preprocess the acquired data to remove outliers and qualified but non-compliant data items, standardize the data, and unify the data format; add labels to the acquired data, create industry classification labels and risk labels for the data, and merge them with the master data to store them as complete transaction data.
[0051] Design a data warehouse to store transaction data.
[0052] The data content in the transaction data is associated in a star table and stored in a data warehouse; in the star table, the relationship between data items is represented in the form of a fact table and a dimension table; the fact table includes the transaction amount, transaction time and transaction type; the dimension table includes a customer dimension table, a time dimension table and a geographic dimension table; the customer dimension table is used to record customer information and behavior types; the time dimension table is used to record transaction time information and is stored in the form of a time series; the geographic dimension table contains transaction location information for regional analysis.
[0053] The data is aggregated in the form of a star table, and the association between data items is used as the topological relationship between the data.
[0054] A database is selected as the carrier of the data warehouse model, and the database includes Oracle, SQLSever and MySOL; the transaction data in the data warehouse is converted into tables, columns, data types and their constraints in the database according to the topological logic of the star model; in the database, indexes are created for fact tables and dimension tables to optimize query performance; according to the topological logic in the star table, the tables in the database are partitioned according to transaction amount, transaction time and transaction type.
[0055] Establish and configure an authentication mechanism for the database to ensure that authorized users access the data warehouse, define different types of users and permissions to control access to the data warehouse by different users and user groups, encrypt the data stored in the data warehouse, including static data encryption and transmission data encryption; use TLS / SSL protocol to protect data transmission.
[0056] Conduct correlation analysis between different transaction data, calculate the correlation between different types of transaction data, and use partial correlation coefficients to analyze the correlation between different types of transaction data.
[0057] Perform correlation analysis on different transaction data X and transaction data Y to construct the trend of transaction data X and Y:
[0058] X=β 0A +β 1A T+β 2A D+∈x;
[0059] Y=β 0B +β 1B T+β 2B D+∈y;
[0060] Among them, β 0A , β 0B , β 1A , β 1B , β 2A and β 2B are the regression coefficients, ∈x and ∈y are the regression residuals, representing the changes in transaction amounts A and B after removing the effects of transaction type and transaction time; ∈x and ∈y are obtained by subtracting the part predicted by transaction type and time from the actual transaction amount.
[0061] Use the values of ∈x and ∈y to represent the correlation between transaction data X and Y:
[0062]
[0063] in, and is the mean value of the residuals; γ(XY,M) represents the correlation between transaction data X and transaction data Y. If γ(XY,M) is significantly different from zero, there is a significant linear correlation between transaction data X and transaction data.
[0064] Using machine learning technology, after the machine learning model learns the risk levels of different types of transaction data, newly created transactions are classified into risk levels.
[0065] According to the transaction behavior characteristics, the transaction data is manually divided into risk levels, the risk levels are used as labels for the transaction data, and the transaction data and the corresponding risk labels are used as data sets to train a machine learning model to learn transaction risk classification; the data set includes a training set, a validation set, and a test set.
[0066] The machine learning model learns different types of transaction data and corresponding risk levels, and uses the probabilities of different risk levels in the training set data as output. Training stops when the number of training times for the machine learning model is reached.
[0067] The trained machine learning model is deployed to the data warehouse. When new transaction data is stored in the data warehouse, the risk level of the transaction data is analyzed by the machine learning model; the machine learning model is a decision tree model.
[0068] Generate visual reports on the data stored in the data warehouse, use data analysis tools, create dashboards and charts to display intuitive analysis results.
[0069] Share analysis results through online platforms and internal systems to ensure user access to result information.
[0070] Example 2
[0071] The second embodiment of the present invention provides a data processing system based on the data warehouse model.
[0072] The system includes: a data acquisition and processing module, a data warehouse module, a data analysis module and a visualization module.
[0073] The data acquisition and processing module is used to acquire transaction data from a financial transaction source and pre-process the transaction data.
[0074] The data warehouse module is used to design a data warehouse, associate transaction data with a star table, and perform data aggregation on the transaction data.
[0075] The data analysis module is used to mine and analyze transaction data, and analyze the data using statistical analysis and machine learning techniques.
[0076] The visualization module constructs visualization reports and produces reports based on the transaction data analysis results to provide support for financial decision-making.
[0077] Example 3
[0078] The third embodiment of the present invention is different from the previous embodiment in that:
[0079] If the functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc., which can store program code.
[0080] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute instructions), or in conjunction with such instruction execution systems, devices or apparatuses. For the purposes of this specification, "computer-readable medium" can be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution system, device or apparatus, or in conjunction with such instruction execution systems, devices or apparatuses.
[0081] More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk case (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be a paper or other suitable medium on which the program is printed, since the program may be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, deciphering or, if necessary, processing in another suitable manner, and then stored in a computer memory.
[0082] It should be understood that the various parts of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.
[0083] Additionally, in order to provide a concise description of exemplary embodiments, all features of an actual embodiment (ie, those features that are not relevant to the best mode presently contemplated for carrying out the invention or those that are not relevant to implementing the invention) may not be described.
[0084] It will be appreciated that in the development of any actual implementation, as in any engineering or design project, numerous implementation-specific decisions may be made. Such a development effort may be complex and time-consuming, but will be a routine task of design, fabrication, and production for those of ordinary skill having the benefit of this disclosure without undue experimentation.
[0085] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention rather than to limit it. Although the present invention has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solutions of the present invention may be modified or replaced by equivalents without departing from the spirit and scope of the technical solutions of the present invention, which should all be included in the scope of the claims of the present invention.
Claims
1. A data processing method based on a data warehouse model, characterized in that: include: Acquire transaction data from financial transaction sources and pre-process the transaction data; Design data warehouse, associate transaction data with star tables, and perform data aggregation on transaction data; Mining and analyzing transaction data, using statistical analysis and machine learning techniques to analyze data; Build visual reports and produce reports based on transaction data analysis results to provide support for financial decision-making; Acquire real-time and historical financial data from a variety of financial transaction sources, using APIs and web crawler technology to obtain real-time data, such as stock market trading systems, bank transaction records, and financial aggregation media; Pre-process the acquired data, remove outliers and qualified but non-compliant data items, standardize the data, and unify the data format; add labels to the acquired data, create industry classification labels and risk labels for the data, and merge them with the master data to store them as complete transaction data; Design a data warehouse to store transaction data; The data contents in the transaction data are associated in a star table and stored in a data warehouse; in the star table, the relationship between the data items is represented in the form of a fact table and a dimension table; the fact table includes the transaction amount, transaction time and transaction type; The dimension tables include a customer dimension table, a time dimension table and a geographic dimension table; The customer dimension table is used to record customer information and behavior types; The time dimension table is used to record the time information of the transaction and store it in the form of a time series; The geographic dimension table contains transaction location information for regional analysis; Aggregate data in the form of a star table, using the associations between data items as the topological relationship between data; Select a database as the carrier of the data warehouse model, including Oracle, SQL Server and MySOL; convert the transaction data in the data warehouse into tables, columns, data types and constraints in the database according to the topological logic of the star model; create indexes for fact tables and dimension tables in the database to optimize query performance; partition the tables in the database according to transaction amount, transaction time and transaction type according to the topological logic in the star table; Establish and configure an authentication mechanism for the database to ensure that authorized users can access the data warehouse, define different types of users and permissions to control the access of different users and user groups to the data warehouse, and encrypt the data stored in the data warehouse, including static data encryption and transmission data encryption; Use TLS / SSL protocol to protect data transmission; Conduct correlation analysis between different transaction data, calculate the correlation between different types of transaction data, and use partial correlation coefficients to analyze the correlation between different types of transaction data; Perform correlation analysis on different transaction data X and transaction data Y to construct the trend of transaction data X and Y: X=β 0A +b 1A T+β 2A D+ x; Y=β 0B +b 1B T+β 2B D+ y; Among them, β 0A , β 0B , β 1A , β 1B , β 2A and β 2B is the regression coefficient, x and y is the regression residual, which represents the change in transaction amounts A and B after removing the effects of transaction type and transaction time; x and y is obtained by subtracting the portion predicted by transaction type and time from the actual transaction amount; use x and The value of y represents the correlation between transaction data X and Y: in, and is the mean value of the residuals; γ(XY,M) represents the correlation between transaction data X and transaction data Y. If γ(XY,M) is significantly different from zero, there is a significant linear correlation between transaction data X and transaction data; Using machine learning technology, after the machine learning model learns the risk levels of different types of transaction data, newly created transactions are classified into risk levels; According to the transaction behavior characteristics, the transaction data is manually classified into risk levels, the risk levels are used as labels for the transaction data, and the transaction data and the corresponding risk labels are used as data sets to train the machine learning model to learn transaction risk classification; the data sets include training sets, validation sets, and test sets; The machine learning model learns different types of transaction data and corresponding risk levels, and outputs the probabilities of different risk levels in the training set data. When the number of training times of the machine learning model is reached, the training stops; Deploy the trained machine learning model to the data warehouse, and when new transaction data is stored in the data warehouse, analyze the risk level of the transaction data through the machine learning model; the machine learning model is a decision tree model; Generate visual reports on data stored in the data warehouse, use data analysis tools, create dashboards and charts to display intuitive analysis results; Share analysis results through online platforms and internal systems to ensure user access to result information.
2. A system using a data processing method based on a data warehouse model as claimed in claim 1, characterized in that: include: Data acquisition and processing module, data warehouse module, data analysis module, and visualization module; The data acquisition and processing module is used to acquire transaction data from a financial transaction source and pre-process the transaction data; The data warehouse module is used to design a data warehouse, associate transaction data with a star table, and perform data aggregation on the transaction data; The data analysis module is used to mine and analyze transaction data, and analyze the data using statistical analysis and machine learning techniques; The visualization module constructs visualization reports and produces reports based on the transaction data analysis results to provide support for financial decision-making.
Citation Information
Patent Citations
Systems, methods, and computer readable media for security in profile utilizing systems
CN102971738A
Platform for promoting intelligent development of industrial internet of things system
CN114424167A
