Decision-making system and method based on Dores database

Abstract

The invention discloses a decision-making system and method based on a Dores database, and the system comprises a data access layer which is used for accessing multi-source heterogeneous financial data in real time, a Dores storage calculation layer, and achieves the data storage, column-type bucket separation and materialized view pre-calculation based on the Dores database; the visual application layer is used for providing bidirectional interactive financial data visualization and decision support; wherein the data access layer acquires data in real time through a streaming computing engine and transmits the data to the Dores storage computing layer, and the visual application layer performs real-time data interaction with the Dores storage computing layer through a two-way communication protocol. According to the method, the synchronous delay of financial data is shortened from 6 hours to 1 second, second-level real-time integration is realized, the aggregation query response time of billion-level data is reduced to be within 1 second based on a Dores column type storage and materialized view technology, the user interaction response delay is lower than 500 milliseconds through WebSocket two-way communication, and real-time adjustment of parameters and dynamic refreshing of a billboard are supported.

Description

Technical Field

[0001] This invention belongs to the field of financial system technology, specifically a decision-making system and method based on the Doris database. Background Technology

[0002] In the field of financial data processing and analysis, existing technologies have been dedicated to improving decision-making efficiency through data integration and visualization, but there are still several key technical deficiencies that restrict their performance in terms of real-time performance, processing performance, and interactive agility.

[0003] First, the real-time performance of data integration is severely lacking. For example, published patent document CN110634089A (A Financial Information Integration System, 2019) proposes an ETL (Extract, Transform, Load) method based on a data warehouse for financial data integration. However, this method relies on offline batch processing, typically requiring daily scheduled task execution, resulting in business data delays generally exceeding 6 hours. For financial scenarios requiring minute-level or even second-level responses, such as cash flow monitoring and sudden transaction risk warnings, this solution cannot meet the timeliness requirements. The fundamental reason is that the architecture design does not incorporate streaming computing technology, making it unable to handle continuously generated data streams.

[0004] Secondly, the processing and querying of massive amounts of data is inefficient. For example, the published patent document CN112257225A (Real-time Analysis Method for Enterprise Financial Big Data, 2021) uses the Hadoop ecosystem for financial data analysis. Although it can handle large volumes of data, its MapReduce-based computing framework often has a response time exceeding 30 seconds when faced with complex multi-dimensional relational queries (such as cost trend analysis across years and multiple departments), and the CPU and other resources are used at extremely high rates (often exceeding 80%) during the calculation process. This is mainly due to the low I / O efficiency of traditional row-oriented databases or early big data architectures when dealing with high-concurrency online analytical processing (OLAP) scenarios, which prevents them from fully utilizing the compression and vectorized computing advantages of column-oriented storage.

[0005] Furthermore, there is a disconnect between the visualization presentation and the underlying analytical model. For example, US10846239B1 (Dynamic Financial Dashboard System, 2020) provides rich financial data visualization dashboards. However, in its system architecture, the visualization layer and the backend calculation model typically use a one-way, asynchronous data communication method (such as REST API). This results in users being unable to trigger real-time recalculation of the underlying model and receive immediate feedback after performing operations on the front-end interface (such as adjusting budget assumptions or exchange rate parameters), with response delays often exceeding 2 seconds. This lag in interaction makes exploratory data analysis less smooth, significantly reduces the flexibility of decision support, and fails to fully realize the value of visualization.

[0006] In summary, existing technologies have significant shortcomings in three aspects: real-time data processing, efficiency in handling massive queries, and agility in human-computer interaction. The core bottlenecks are: the use of offline or micro-batch processing architectures makes it difficult to adapt to the real-time processing needs of streaming financial data; the selection of storage and computing engines is not deeply optimized for OLAP scenarios, failing to efficiently support high concurrency and complex ad-hoc queries; and excessive decoupling of the front-end and back-end architectures, lacking an efficient two-way real-time communication mechanism, leads to a break in the analysis loop. Summary of the Invention

[0007] The technical solution adopted in this invention is as follows: A decision-making system based on the Doris database, comprising:

[0008] The data access layer is used for real-time access to multi-source heterogeneous financial data;

[0009] The Doris storage and computation layer is based on the Doris database and enables data storage, columnar bucketing, and pre-computation of materialized views.

[0010] The visualization application layer is used to provide two-way interactive financial data visualization and decision support;

[0011] The data access layer collects data in real time through a streaming computing engine and transmits it to the Doris storage and computing layer, while the visualization application layer interacts with the Doris storage and computing layer in real time through a bidirectional communication protocol.

[0012] A decision-making system based on the Doris database according to claim 1, characterized in that the data access layer includes:

[0013] The multi-source data source module is used to access ERP, SAP, Excel, and PDF contract data;

[0014] The Apache Flink real-time stream processing engine is used for real-time data parsing and cleaning.

[0015] The Kafka message queue module is used to buffer real-time data streams.

[0016] Furthermore, the Doris storage computing layer includes:

[0017] Doris FE nodes are used for metadata management;

[0018] Doris BE nodes are used for columnar storage and bucketing.

[0019] The materialized view module is used to pre-compute high-frequency query models.

[0020] Furthermore, the visualization application layer includes:

[0021] FineReport Designer module, used to configure visual templates;

[0022] WebSocket bidirectional communication module for real-time communication;

[0023] A dynamic parameter interaction interface is used to receive user parameter input;

[0024] The visualization dashboard module is used to display Sankey diagrams, tree diagrams, and real-time dashboards.

[0025] Furthermore, the Apache Flink real-time stream processing engine is configured with a dynamic rules engine to execute Groovy scripts to perform abnormal data cleaning.

[0026] Furthermore, the materialized view module supports an incremental update mechanism, refreshing the view only for newly written data.

[0027] Furthermore, after receiving user input parameters, the WebSocket bidirectional communication module sends parameterized SQL queries to the Doris storage and computing layer and pushes the query results to the visualization dashboard module in real time.

[0028] A decision-making method based on a Doris database, applied to any of the above-described Doris database-based decision-making systems, includes the following steps:

[0029] Step 1: Collect multi-source financial data in real time through the data access layer, and perform streaming cleaning and analysis;

[0030] Step 2: Write the cleaned data into the Doris storage and computing layer, store it in columnar buckets according to financial themes, and create materialized views for pre-computation.

[0031] Step 3: Receive user interaction parameters through the visualization application layer and send them to the Doris storage and computing layer for real-time querying via a two-way communication protocol;

[0032] Step 4: Push the query results to the visualization dashboard module in real time and dynamically update the financial analysis charts.

[0033] Furthermore, in the step of real-time acquisition of multi-source financial data, the Apache Flink CDCConnector is used to capture source data change logs and write them to the bucket table through the Doris Stream Load interface.

[0034] Furthermore, in the visualization application layer, users can adjust financial parameters through a dynamic parameter interaction interface, triggering a recalculation of the materialized view and completing the dashboard update within 500 milliseconds.

[0035] In summary, due to the adoption of the above technical solution, the beneficial effects of the present invention are:

[0036] (1) In this invention, the real-time data processing capability is significantly improved. By replacing the traditional offline ETL with a streaming data pipeline based on Apache Flink, the synchronization delay of multi-source financial data is reduced from 6 hours in the existing technology to less than 1 second, which is an improvement of 21,600 times. This can meet the needs of high-timeliness business scenarios such as real-time cash flow monitoring and early warning of sudden cost fluctuations.

[0037] (2) In this invention, the performance of massive data query and analysis is greatly optimized. With the help of the columnar storage, bucketing strategy and materialized view pre-computation technology of Doris database, the response time of complex aggregation query of 1 billion data is reduced from 32.5 seconds in Hadoop solution to 0.8 seconds, the resource occupancy rate is significantly reduced, and high-efficiency and low-consumption online analysis and processing are realized.

[0038] (3) In this invention, bidirectional real-time interaction between the visualization layer and the underlying computing model is realized. The traditional asynchronous REST API is replaced by the WebSocket bidirectional communication protocol, so that after the user adjusts the analysis parameters (such as exchange rate and allocation ratio) on the front end, the system can trigger model recalculation and dynamically refresh the visualization dashboard within 500 milliseconds, which greatly enhances the flexibility of analysis and the agility of decision-making.

[0039] (4) In this invention, the economic cost of system construction and operation and maintenance is reduced. Through lightweight architecture and efficient compressed storage, hardware investment and energy consumption are reduced. At the same time, the automated data processing process shortens the monthly financial report generation time from 3 days to 1 hour, which significantly saves labor costs and has direct economic benefits.

[0040] (5) In this invention, there is good scalability and industry applicability. The streaming computing, columnar storage and configurable visualization templates adopted by the system are all modular designs, which can be quickly adapted to financial data analysis scenarios in other industries such as manufacturing and retail. It has strong reusability and can shorten the development cycle of similar systems by more than 50%.

[0041] (6) In response to the national call for green energy conservation, the high compression characteristics of columnar storage increase the data compression rate by 80%, effectively reducing the number of storage devices and data center energy consumption, reducing carbon emissions, and generating positive social and environmental benefits.

[0042] (7) In this invention, a complete closed loop is formed from real-time data access and efficient analysis to agile decision-making, breaking down the barriers between data integration, calculation and display in traditional financial systems, and providing reliable technical support for enterprises to cope with market changes and conduct real-time financial simulations and decisions. Attached Figure Description

[0043] Figure 1 This is a schematic diagram of the process of the present invention;

[0044] Figure 2 This is a schematic diagram of the data access layer of the present invention;

[0045] Figure 3 This is a schematic diagram of the Doris storage computing layer of the present invention;

[0046] Figure 4 This is a schematic diagram of the application layer of the present invention.

[0047] In the diagram: 1. Data access layer; 2. Doris storage and computing layer; 3. Visualization application layer. Detailed Implementation

[0048] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0049] Example 1

[0050] Reference Figure 1 - Figure 4 :

[0051] This embodiment details a specific implementation of a decision-making system and method based on the Doris database.

[0052] System Architecture and Components

[0053] This system mainly consists of three layers: data access layer 1, Doris storage and computing layer 2, and visualization application layer 3. Each layer communicates and collaborates via a network.

[0054] Data Access Layer 1 is responsible for real-time access to financial data from various business systems within the enterprise. The multi-source data source module configures connectors to access the database of the Enterprise Resource Planning (ERP) system, the SAP system interface, regularly uploaded Excel reports, and scanned and archived PDF contract documents. These data sources have diverse structures, representing multi-source heterogeneous data. The Apache Flink real-time stream processing engine, as the core processing component, defines the streaming pipeline from the above multiple sources to the Kafka message queue by writing Flink SQL jobs. The engine integrates a dynamic rule engine, allowing operations personnel to upload or configure Groovy scripts. These scripts are used for real-time parsing, format conversion, and logical judgment of the incoming data, such as filtering out data records with negative amounts or abnormal account codes, performing real-time data cleaning. The Kafka message queue module receives the standardized data stream processed by Flink, acting as a buffer and decoupling mechanism to ensure stable reception by downstream systems under high data throughput.

[0055] Doris Storage and Computation Layer 2, built on the Apache Doris database, is responsible for efficient storage and computation. Doris FE nodes deploy front-end nodes, managing database metadata, user permissions, and receiving and planning SQL queries from external sources. Doris BE nodes deploy multiple back-end nodes using a columnar storage format. Bucketed tables are created based on financial analysis themes (e.g., "Accounts Receivable," "Costs," etc.). For example, "Accounts Receivable" data can be bucketed by "Company Code" and "Payment Period" to optimize scanning efficiency and parallel computation. The materialized view module creates corresponding materialized views on the base tables for high-frequency and time-consuming analytical queries (e.g., monthly cost year-on-year analysis). These views pre-calculate aggregation results. When new data is incrementally written to the base tables via Stream Load or similar methods, the system automatically refreshes the relevant materialized views through an incremental update mechanism, greatly improving query speed.

[0056] The visualization application layer 3 provides users with an interactive analysis interface. The FineReport Designer module allows business personnel to configure and develop visualization templates using a pre-built drag-and-drop component library. Templates support various formats such as Sankey diagrams for cash flow (fields: source, target, value), cost structure tree diagrams (fields: node, parent, cost), and real-time dashboards (fields: current_income, current_cost, profit_rate). The WebSocket bidirectional communication module develops the corresponding communication server-side code. This module establishes and maintains a full-duplex communication link with the front-end browser. The dynamic parameter interaction interface embeds interactive components such as manual input boxes and sliders into the dashboard, allowing users to adjust query parameters in real time, such as "statistical month" and "cost center." The visualization dashboard module is the final page module for rendering and displaying charts. After users adjust parameters through the dynamic parameter interaction interface, the parameter values ​​are sent to the application backend in real time via the WebSocket bidirectional communication module. The backend assembles parameterized SQL query statements based on the parameters and sends them to the Doris storage and computing layer 2 for querying. The query results are then pushed back to the front end in real time via the same WebSocket channel, driving components such as Sankey diagrams, tree diagrams, or dashboards to complete dynamic updates within 500 milliseconds.

[0057] Reference Figure 1 - Figure 4 :

[0058] A decision-making method based on the Doris database includes the following steps:

[0059] Step 1: Real-time Data Access and Cleaning: Multi-source financial data is collected in real-time through Data Access Layer 1. Specifically, the Apache Flink CDC Connector is used to capture change logs from source databases (such as ERP systems) while simultaneously accessing file data streams. The Flink engine calls a pre-configured Groovy script to clean and parse the data in real time, and then publishes the cleaned data to Kafka.

[0060] Step 2: Data Storage and Pre-computation: Using Doris's Stream Load interface, data from Kafka is written in real-time to the corresponding bucketed tables in Doris's storage and computation layer 2. Data is stored in buckets according to financial themes (e.g., accounts receivable). The system automatically or manually creates materialized views based on predefined aggregation models and performs incremental pre-computation on the newly written data.

[0061] Step 3: Interactive Query Trigger: The user adjusts financial analysis parameters (such as selecting different branches) through the dynamic parameter interaction interface of the visual application layer 3. After this operation is captured, the parameters are sent to the backend service via the WebSocket bidirectional communication protocol.

[0062] Step 4: Real-time Results Display: The backend service combines the received parameters with the pre-defined query template to generate an SQL statement and initiate a query to Doris Storage Computation Layer 2. Thanks to the pre-aggregation of materialized views, the query is completed in a very short time. The query results are pushed back to the front-end visualization dashboard module in real time via a WebSocket channel, triggering dynamic updates to the charts and forming an interactive analysis loop.

[0063] Performance

[0064] Through the above implementation methods, the system achieved significant performance improvements. For example, the response time for aggregate queries on 1 billion data points was reduced from 32.5 seconds using the traditional Hadoop solution to 0.8 seconds; the latency from data generation to analyzability was shortened from 6 hours using offline ETL to 0.9 seconds using Flink stream processing; and the response latency for charts after user operations on the front end was reduced from 2.1 seconds using the REST API to 0.4 seconds using the WebSocket solution. This demonstrates the effectiveness and superiority of this system in terms of real-time performance, interactivity, and big data processing capabilities.

[0065] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A decision-making system based on a Doris database, characterized in that, include: The data access layer (1) is used for real-time access to multi-source heterogeneous financial data; Doris storage and computing layer (2) is based on the Doris database to realize data storage, columnar bucketing and materialized view pre-computation; The visualization application layer (3) is used to provide two-way interactive financial data visualization and decision support; The data access layer (1) collects data in real time through a streaming computing engine and transmits it to the Doris storage computing layer (2). The visualization application layer (3) interacts with the Doris storage computing layer (2) in real time through a bidirectional communication protocol.

2. The decision-making system based on the Doris database according to claim 1, characterized in that, The data access layer (1) includes: The multi-source data source module is used to access ERP, SAP, Excel, and PDF contract data; The Apache Flink real-time stream processing engine is used for real-time data parsing and cleaning. The Kafka message queue module is used to buffer real-time data streams.

3. A decision-making system based on a Doris database according to claim 1, characterized in that, The Doris storage computing layer (2) includes: Doris FE nodes are used for metadata management; Doris BE nodes are used for columnar storage and bucketing. The materialized view module is used to pre-compute high-frequency query models.

4. A decision-making system based on a Doris database according to claim 1, characterized in that, The visualization application layer (3) includes: FineReport Designer module, used to configure visual templates; WebSocket bidirectional communication module for real-time communication; A dynamic parameter interaction interface is used to receive user parameter input; The visualization dashboard module is used to display Sankey diagrams, tree diagrams, and real-time dashboards.

5. A decision-making system based on a Doris database according to claim 2, characterized in that, The Apache Flink real-time stream processing engine is configured with a dynamic rule engine, which is used to execute Groovy scripts to clean up abnormal data.

6. A decision-making system based on a Doris database according to claim 3, characterized in that, The materialized view module supports an incremental update mechanism, refreshing the view only for newly written data.

7. A decision-making system based on a Doris database according to claim 4, characterized in that, After receiving user input parameters, the WebSocket bidirectional communication module sends parameterized SQL queries to the Doris storage and computing layer (2) and pushes the query results to the visualization dashboard module in real time.

8. A decision-making method based on the Doris database, characterized in that, The system applied to a Doris database-based decision-making system according to any one of claims 1-7 includes the following steps: Step 1: Collect multi-source financial data in real time through the data access layer (1), and perform streaming cleaning and analysis; Step 2: Write the cleaned data into the Doris storage and computing layer (2), store it in columnar buckets according to the financial theme, and create a materialized view for pre-computation; Step 3: Receive user interaction parameters through the visualization application layer (3) and send them to the Doris storage and computing layer (2) for real-time query via the bidirectional communication protocol; Step 4: Push the query results to the visualization dashboard module in real time and dynamically update the financial analysis charts.

9. A decision-making method based on a Doris database according to claim 8, characterized in that, In the step of real-time acquisition of multi-source financial data, the Apache File CDC Connector is used to capture source data change logs and write them to the bucket table through the Doris Stream Load interface.

10. A decision-making method based on a Doris database according to claim 8, characterized in that, In the visualization application layer (3), users adjust financial parameters through the dynamic parameter interaction interface, triggering the materialized view to be recalculated and completing the dashboard update within 500 milliseconds.

Images