Data analysis application data timeliness improving method based on offline warehouse counting
By constructing a data dependency record table and updating batch processing tasks on demand, the data processing order of the offline data warehouse is optimized, solving the data timeliness problem of the offline data warehouse in special scenarios and improving the timeliness of data analysis applications.
Patent Information
- Application Number
- CN202510806407.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-06-17
- Publication Date
- 2025-11-18
AI Technical Summary
Existing offline data warehouses are unable to meet occasional data update needs at the hourly or even minute level. Due to their architectural features and operational efficiency bottlenecks, they cannot meet the data timeliness requirements of certain special scenarios in enterprise business.
By analyzing the data and processing operation dependencies of terminal data analysis applications on each layer of the data warehouse, a data dependency record table is constructed. Batch processing tasks are updated as needed, and data updates are executed layer by layer from bottom to top, optimizing the data processing order to improve data timeliness.
With less resource and time consumption, it breaks through the T+1 data timeliness limitation of traditional offline data warehouses, and achieves hourly or even minute-level timeliness improvement of data analysis application results, which greatly makes up for the shortcomings of traditional offline data warehouses.
Smart Images
Figure CN120973789A_ABST
Abstract
Description
Technical Field
[0001] This invention belongs to the field of data analysis application technology, specifically relating to a method for improving the timeliness of data in data analysis applications based on offline data warehouses. Background Technology
[0002] In enterprise big data application scenarios, offline data warehouses are widely used due to their stable data, high accuracy, and large capacity. Offline data warehouses typically use scheduled batch processing jobs to perform unified global processing on data across different layers of the data warehouse. The scheduling frequency is on a daily basis, providing T+1 (one day prior) data timeliness.
[0003] In the daily operations of enterprises, there are occasional special scenarios where a specific data analysis application needs to reflect real-time data at the hourly or even minute level. Although this is not a regular requirement, the sheer number of data analysis applications built on offline data warehouses means that the occasional special needs of several specific applications effectively constitute a relatively frequent type of "over-standard" data update requirement. Due to the architectural characteristics and operational efficiency bottlenecks of offline data warehouses, this type of requirement is difficult to meet.
[0004] Therefore, overcoming the shortcomings of existing technologies is a problem that urgently needs to be solved in the field of data analysis application technology. Summary of the Invention
[0005] The purpose of this invention is to address the shortcomings of existing technologies and provide a method for improving the timeliness of data analysis applications based on offline data warehouses.
[0006] To achieve the above objectives, the technical solution adopted by the present invention is as follows: A method for improving the timeliness of data analysis applications based on offline data warehouses includes the following steps: Step (1): Analyze the dependencies of the terminal data analysis application on the data and processing operations of each layer of the data warehouse, and construct a data dependency record table for the data analysis application; Step (2): The terminal data analysis application initiates an update data request and transmits the application identifier of the terminal data analysis application. Step (3): According to the application identifier passed by the terminal data analysis application, query the data dependency record table of the data analysis application to determine the datasets and processing operation scopes related to the terminal application in each layer of the data warehouse; Step (4): Based on the datasets and processing operation scopes related to terminal applications in each layer of the data warehouse, arrange the data batch processing tasks to be updated as needed according to the runtime sequence of each layer of the data warehouse. Step (5) Execute batch processing tasks to update the range data related to terminal data analysis applications in the data warehouse layer by layer from bottom to top, and finally improve the timeliness of data displayed by terminal applications.
[0007] Furthermore, preferably, in step (1), the data analysis application data dependency record table includes application ID, application name, dependency object level, dependency object type, dependency object identifier, and last execution time.
[0008] Furthermore, preferably, the specific steps for parsing the dependencies of the terminal data analysis application on the data and processing operations of each layer of the data warehouse are as follows: parsing the data dependencies of the terminal data analysis application, associating the dependencies of the terminal application on the data warehouse ADS layer data tables and processing programs, the dependencies of the ADS layer on the DWD layer data tables and processing programs, and the dependencies of the DWD layer on the ODS layer data tables and processing programs with the terminal data analysis application identifier at the granularity of a single data table or executable processing program, forming queryable data records, and storing them in the data analysis application data dependency record table.
[0009] Furthermore, preferably, the specific method of step (3) is as follows: receive a data update request sent by the terminal data analysis application, use the application identification information in the request as a keyword, query the data dependency relationship record table of the data analysis application, obtain all data warehouse dependency object records related to the application, and thereby determine the datasets and processing operation scope related to the terminal application in each layer of the data warehouse.
[0010] Furthermore, preferably, the specific method of step (4) is as follows: Based on the terminal data analysis application data dependency records obtained in step (3), identify and orchestrate batch processing tasks: First, identify the last execution time field of the ADS layer record. If the time interval with the current time is less than a certain preset threshold, there is no need to start the update task again. Otherwise, start arranging batch processing tasks and arrange them in the order of ODS layer > DWD layer > ADS layer. In the ODS layer, the data table objects involved in the dependency relationship are compiled into the ETL task; The data processing program objects involved in the DWD layer are incorporated into the program to perform tasks. In the ADS layer, the data processing program objects involved are compiled into the program to execute tasks.
[0011] Furthermore, preferably, the threshold is 10 minutes.
[0012] Furthermore, preferably, the specific method of step (5) is as follows: First, perform ETL data extraction processing on the data tables that the terminal applications depend on in the data warehouse ODS layer to obtain the latest data from the business system; then, execute the data processing program that the terminal applications depend on in the DWD layer to process and generate updated data for the DWD layer based on the updated data in the ODS layer; finally, execute the data processing program that the terminal applications depend on in the ADS layer to process and generate updated data for the ADS layer based on the updated data in the DWD layer. At this time, the terminal data analysis application can obtain the latest updated data when accessing the data in the data warehouse ADS layer, thereby achieving a data timeliness improvement that exceeds the T+1 limit.
[0013] This invention breaks through the T+1 data timeliness limitation of offline data warehouses and provides a supplementary mechanism that allows data analysis applications built on offline data warehouses to improve data timeliness in special scenarios (without the need for operation and maintenance intervention) and obtain data update results at the hourly or even minute level.
[0014] In step (1) of this invention, the data dependency record table for data analysis applications is a general relational database table, mainly including application ID (terminal data analysis application identifier), application name, dependency object level (corresponding to data warehouse layers, such as ODS, DWD, ADS), dependency object type (such as data table, data processing program, etc.), dependency object identifier, and last execution time. Parsing the data dependencies of the terminal data analysis application involves reverse engineering the data analysis application construction process, associating the terminal application's dependencies on ADS layer data tables and processing programs, the ADS layer's dependencies on DWD layer data tables and processing programs, and the DWD layer's dependencies on ODS layer data tables and processing programs with the terminal data analysis application identifier at the granularity of a single data table or executable processing program, forming queryable data records, which are stored in the data dependency record table for data analysis applications. After parsing a terminal data analysis application, several dependency records may be obtained, each reflecting information about the dependent object in a data warehouse.
[0015] In a typical offline data warehouse architecture, the DW layer can be subdivided into DWD, DWM, DWS, etc. In specific application scenarios, the layers can be simplified according to the actual situation. This invention adopts a simplified model, with DWD representing the entire DW layer for ease of description. The method principle described in this invention is also applicable in the subdivided architecture, which is hereby stated.
[0016] In step (2) of this invention, the data update request is initiated by the user from the interactive interface of the terminal data analysis application. For example, the initiation method can be by clicking a "Update Data" button on the interface. The "application identifier" representing the terminal data analysis application itself is included in the request for transmission. This identifier is used as a basis in subsequent steps to define the scope of the data and processing procedures involved, so as to perform on-demand, local, and non-redundant data update actions.
[0017] In step (3) of this invention, a data update request is received from the terminal data analysis application. Using the "application identifier" information in the request as a keyword, the "data analysis application data dependency record table" is queried to obtain all data warehouse dependency object records related to the application. These records comprehensively reflect the scope of datasets and processing operations related to the terminal application in each layer of the data warehouse.
[0018] In step (4) of this invention, the data dependency records of the terminal data analysis application obtained in step (3) are used to identify and arrange batch processing tasks. First, the "last execution time" field of the ADS layer record is identified. If the time interval with the current time is less than a certain threshold (such as 10 minutes, set according to actual needs), it means that the data has just been updated within an acceptable time interval and there is no need to start the update task again. Otherwise, the batch processing tasks are arranged in the order of ODS layer > DWD layer > ADS layer. In the ODS layer, the data table objects involved in the dependency relationship are included in the ETL task, in the DWD layer, the data processing program objects involved are included in the program execution task, and in the ADS layer, the data processing program objects involved are included in the program execution task.
[0019] The 10-minute threshold is merely an example and not a crucial setting required in this invention. The purpose of the threshold is to control unnecessary and repetitive data update requests while meeting the requirement of data timeliness. Depending on the specific application scenario, the threshold can be flexibly set, and can also be 15 minutes, 30 minutes, etc.
[0020] In step (5) of this invention, the specific method for executing the arranged batch processing task is as follows: First, ETL is performed on the data tables that the terminal applications depend on in the data warehouse ODS layer to extract data and obtain the latest data from the business system; then, the data processing program that the terminal applications depend on in the DWD layer is executed to process and generate updated data for the DWD layer based on the updated data in the ODS layer; finally, the data processing program that the terminal applications depend on in the ADS layer is executed to process and generate updated data for the ADS layer based on the updated data in the DWD layer; at this time, the terminal data analysis application can obtain the latest updated data when accessing the data in the data warehouse ADS layer, thereby achieving a data timeliness improvement that exceeds the T+1 limit.
[0021] Steps (3) to (5) of this invention can be implemented in the on-demand data update scheduling and processing module.
[0022] The working principle of this invention is as follows: Based on the conventional architecture and operation mechanism of offline data warehouses, a supplementary mechanism is constructed. From the vertical perspective of a single terminal data analysis application, the dependency relationship between the application and the datasets and data processing operations of each layer of the data warehouse is analyzed to obtain the local data update range of the data warehouse. Data update actions related only to the terminal application are executed in an on-demand, local, and non-redundant manner. With less system resource consumption and time consumption, the timeliness of terminal data analysis application results can break through the T+1 limit and be improved to the hourly or even minute level.
[0023] Compared with the prior art, the beneficial effects of this invention are as follows: This invention effectively overcomes the T+1 data timeliness limitation of traditional offline data warehouse data analysis applications (i.e., the latest data reflected is from the previous day). In practical application scenarios, it can typically improve the timeliness of data analysis application results to within one hour, with less resource and time consumption, depending on specific application needs. For data analysis applications involving smaller data volumes, it can even reflect the latest data within 5 minutes, greatly compensating for the shortcomings of traditional offline data warehouse applications and significantly enhancing the application value of offline data warehouse-based data analysis applications in business. Attached Figure Description
[0024] Figure 1 This is a flowchart illustrating the method for improving the timeliness of data analysis applications based on offline data warehouses according to the present invention. Figure 2 This is a schematic diagram of the application structure of the data analysis application timeliness improvement method based on offline data warehouse of the present invention; wherein, M1: represents the "on-demand update data scheduling and processing module", which is the main execution part of the method of the present invention.
[0025] M2: Represents "Data Dependency Record Table for Data Analysis Applications", which is used to store dependency records of datasets and data processing program objects at each layer of the data warehouse parsed by terminal data analysis applications.
[0026] P1: Indicates that the terminal data analysis application initiates an update data request and passes the application identifier to the "on-demand update data scheduling and processing module".
[0027] P2: This indicates that the "On-Demand Update Data Scheduling Processing Module" queries the dependency records in the "Data Analysis Application Data Dependency Record Table" by application identifier.
[0028] P3: This indicates that the "on-demand update data scheduling and processing module" obtains dependency record information, which is used to arrange the order of batch processing tasks for execution.
[0029] P4: This indicates that when the "On-Demand Data Update Scheduling and Processing Module" executes batch processing tasks, it first performs specific ETL actions related to the data warehouse ODS layer and terminal applications to achieve local data updates in the ODS layer.
[0030] P5: This indicates that when the "On-Demand Data Update Scheduling and Processing Module" executes batch processing tasks, it then executes specific data processing programs related to the application in the data warehouse DWD layer to achieve local data updates in the DWD layer.
[0031] P6: This indicates that when the "On-Demand Data Update Scheduling and Processing Module" executes batch processing tasks, it finally executes a specific range of data processing programs related to the application in the data warehouse ADS layer to achieve local data updates in the ADS layer. Detailed Implementation
[0032] The present invention will now be described in further detail with reference to the embodiments.
[0033] Those skilled in the art will understand that the following embodiments are for illustrative purposes only and should not be construed as limiting the scope of the invention. Where specific techniques or conditions are not specified in the embodiments, they are performed in accordance with the techniques or conditions described in the literature in the field or according to the product instructions. Materials or equipment whose manufacturers are not specified are all conventional products that can be obtained by purchase. Example 1
[0034] like Figure 1 As shown, a method for improving the timeliness of data analysis applications based on offline data warehouses includes the following steps: Step (1): Analyze the dependencies of the terminal data analysis application on the data and processing operations of each layer of the data warehouse, and construct a data dependency record table for the data analysis application; Step (2): The terminal data analysis application initiates an update data request and transmits the application identifier of the terminal data analysis application. Step (3): According to the application identifier passed by the terminal data analysis application, query the data dependency record table of the data analysis application to determine the datasets and processing operation scopes related to the terminal application in each layer of the data warehouse; Step (4): Based on the datasets and processing operation scopes related to terminal applications in each layer of the data warehouse, arrange the data batch processing tasks to be updated as needed according to the runtime sequence of each layer of the data warehouse. Step (5) Execute batch processing tasks to update the range data related to terminal data analysis applications in the data warehouse layer by layer from bottom to top, and finally improve the timeliness of data displayed by terminal applications. Example 2
[0035] like Figure 1As shown, a method for improving the timeliness of data analysis applications based on offline data warehouses includes the following steps: Step (1): Analyze the dependencies of the terminal data analysis application on the data and processing operations of each layer of the data warehouse, and construct a data dependency record table for the data analysis application; Step (2): The terminal data analysis application initiates an update data request and transmits the application identifier of the terminal data analysis application. Step (3): According to the application identifier passed by the terminal data analysis application, query the data dependency record table of the data analysis application to determine the datasets and processing operation scopes related to the terminal application in each layer of the data warehouse; Step (4): Based on the datasets and processing operation scopes related to terminal applications in each layer of the data warehouse, arrange the data batch processing tasks to be updated as needed according to the runtime sequence of each layer of the data warehouse. Step (5) Execute batch processing tasks to update the range data related to terminal data analysis applications in the data warehouse layer by layer from bottom to top, and finally improve the timeliness of data displayed by terminal applications.
[0036] In step (1), the data analysis application data dependency record table includes application ID, application name, dependency object level, dependency object type, dependency object identifier, and last execution time.
[0037] Specifically, parsing the dependencies of terminal data analysis applications on data and processing operations at each layer of the data warehouse involves: parsing the data dependencies of terminal data analysis applications, associating the dependencies of the terminal application on data tables and processing programs in the ADS layer of the data warehouse, the dependencies of the ADS layer on data tables and processing programs in the DWD layer, and the dependencies of the DWD layer on data tables and processing programs in the ODS layer with the terminal data analysis application identifier at the granularity of a single data table or executable processing program, forming queryable data records, and storing them in the data analysis application data dependency record table.
[0038] The specific method of step (3) is as follows: receive the data update request sent by the terminal data analysis application, use the application identification information in the request as the key, query the data dependency record table of the data analysis application, obtain all data warehouse dependency object records related to the application, and thus determine the dataset and processing operation scope related to the terminal application in each layer of the data warehouse.
[0039] The specific method for step (4) is as follows: Based on the terminal data analysis application data dependency records obtained in step (3), identify and orchestrate batch processing tasks: First, identify the last execution time field of the ADS layer record. If the time interval with the current time is less than a certain preset threshold, there is no need to start the update task again. Otherwise, start arranging batch processing tasks and arrange them in the order of ODS layer > DWD layer > ADS layer. In the ODS layer, the data table objects involved in the dependency relationship are compiled into the ETL task; The data processing program objects involved in the DWD layer are incorporated into the program to perform tasks. In the ADS layer, the data processing program objects involved are compiled into the program to execute tasks.
[0040] The threshold is 10 minutes.
[0041] The specific method for step (5) is as follows: First, perform ETL data extraction processing on the data tables that the terminal applications depend on in the data warehouse ODS layer to obtain the latest data from the business system; then, execute the data processing program that the terminal applications depend on in the DWD layer to process and generate updated data for the DWD layer based on the updated data in the ODS layer; finally, execute the data processing program that the terminal applications depend on in the ADS layer to process and generate updated data for the ADS layer based on the updated data in the DWD layer. At this time, the terminal data analysis application can obtain the latest updated data when accessing the data in the data warehouse ADS layer, thereby achieving a data timeliness improvement that exceeds the T+1 limit. Example 3
[0042] In this embodiment, steps (3) to (5) can be implemented in the on-demand data update scheduling and processing module. Specifically, a method for improving the timeliness of data analysis applications based on offline data warehouses includes the following steps: like Figure 2 As shown: Step (1), parse the dependencies of the terminal data analysis application on the data and processing operations of each layer of the data warehouse, and construct M2: [Data Analysis Application Data Dependency Record Table]; Step (2), the terminal data analysis application initiates the P1: [Initiate an update data request and pass the application identifier] action, and passes the application identifier to M1: [Update data on demand scheduling and processing module]; Step (3), M1: [On-demand update data scheduling and processing module] executes P2: [Query dependency relationship by application identifier] to query M2: [Data analysis application data dependency relationship record table] to obtain the datasets and processing operation scope related to terminal applications in each layer of the data warehouse; Step (4), M1: [On-demand data update scheduling and processing module] Based on the datasets and processing operation scope related to terminal applications in each layer of the data warehouse obtained from the P3 action feedback, arrange the "on-demand data update" batch processing tasks according to the runtime sequence of each layer of the data warehouse.
[0043] Step (5), M1: [On-Demand Data Update Scheduling Processing Module] According to the arranged batch processing tasks, execute action P4 in sequence to update the data related to the ODS layer and the terminal application, execute action P5 to update the data related to the DWD layer and the terminal application, and execute action P6 to update the data related to the ADS layer and the terminal application. After each action is executed, the execution time information is written back, and the corresponding dependency records in M2 are updated. Finally, the terminal application can obtain the latest data when accessing the ADS layer data.
[0044] Application Example 1 In this example, steps (3) to (5) above can be implemented in the on-demand data update scheduling and processing module. Specifically, a method for improving the timeliness of data analysis applications based on offline data warehouses, such as... Figure 2 As shown, it includes the following steps: Step (1): Analyze the dependencies of a terminal data analysis application on the data and processing operations of each layer of the data warehouse, form records, and store them in M2 [Data Analysis Application Data Dependency Record Table]. Some entries of the Data Analysis Application Data Dependency Record Table are shown in Table 1 below: Table 1
[0045] Step (2): The terminal data analysis application "Sales Analysis - 24-hour delivery rate" initiates a data update request (P1) and passes the application ID "XSFX-0059" to M1 [On-demand data update scheduling and processing module].
[0046] Step (3), M1 [On-demand data update scheduling processing module] executes action P2, queries M2 [data analysis application data dependency record table] according to the application ID in the received request, and retrieves the record information shown in Table 1 in step (1); Step (4), M1 [On-Demand Data Update Scheduling Processing Module] obtains all dependency information related to each layer of the data warehouse and the terminal data analysis application through the P3 action. By comparing the difference between the last execution time and the current time of the ADS layer stored procedure object "P_ADS_SVR_FHL001", it determines that a data update needs to be performed. Therefore, the batch processing tasks are arranged to be executed sequentially as follows: Task 1: Update the T_SALE_001 table in the ODS layer via ETL operations; Task 2: Update the T_SALE_002 table in the ODS layer via ETL operations; Task 3: Update the T_SALE_003 table in the ODS layer via ETL operations; Task 4: Execute the DWD layer stored procedure P_DWD_SVR_XSFH01 to update the DWD layer tables T_DWD_SVR_XS001 and T_DWD_SVR_XS002; Task 5: Execute the ADS layer stored procedure P_ADS_SVR_FHL001 to update the ADS layer table T_ADS_SVR_FHL001; Step (5), M1 [On-demand data update scheduling and processing module] executes the batch processing tasks arranged in step (4). In sequence, first, "Task 1", "Task 2" and "Task 3" are executed through the P4 action; then, "Task 4" is executed through the P5 action; and finally, "Task 5" is executed through the P6 action.
[0047] After the batch processing task is completed, the terminal data analysis application can access the "P_ADS_SVR_FHL001" table in the ADS layer to obtain the latest updated data for display.
[0048] The foregoing has shown and described the basic principles, main features, and advantages of the present invention. Those skilled in the art should understand that the present invention is not limited to the above embodiments. The embodiments and descriptions in the specification are merely illustrative of the principles of the invention. Various changes and modifications can be made to the invention without departing from its spirit and scope, and all such changes and modifications fall within the scope of the present invention as claimed. The scope of protection of this invention is defined by the appended claims and their equivalents.
Claims
1. An offline data warehouse-based data analysis application data timeliness improvement method, characterized in that, Comprising the following steps: Step (1), analyze the dependence relationship of terminal data analysis application to each layer data and processing operation of data warehouse, and construct data analysis application data dependence relationship record table; Step (2), the terminal data analysis application initiates an update data request, and transmits the application identifier of the terminal data analysis application; Step (3), according to the application identifier transmitted by the terminal data analysis application, query the data analysis application data dependence relationship record table to determine the data set and processing operation range related to the terminal application in each layer of the data warehouse; Step (4), according to the data set and processing operation range related to the terminal application in each layer of the data warehouse obtained, schedule the on-demand update data batch processing task according to the run sequence of each layer of the data warehouse; Step (5), execute the batch processing task, update the range data related to the terminal data analysis application in the data warehouse from bottom to top, and finally realize the timeliness improvement of the terminal application display data.
2. The method of claim 1, wherein the method is used for improving the timeliness of data analysis based on an offline data warehouse. In step (1), the data analysis application data dependence relationship record table includes application ID, application name, dependence object level, dependence object type, dependence object identifier, and last execution time.
3. The method of claim 1, wherein the method further comprises: The dependence relationship of terminal data analysis application to each layer data and processing operation of data warehouse is specifically: the analysis of terminal data analysis application data dependence relationship, the dependence of terminal application to data table and processing program of ADS layer of data warehouse, the dependence of ADS layer to data table and processing program of DWD layer, the dependence of DWD layer to data table and processing program of ODS layer are associated with terminal data analysis application identifier at the granularity of single data table or executable processing program, form a queryable data record, and stored in data analysis application data dependence relationship record table.
4. The data analysis application data timeliness improvement method based on an offline data warehouse according to claim 1 or 3, characterized in that, The specific method of step (3) is: receiving the data update request sent by the terminal data analysis application, taking the application identifier information in the request as the key, querying the data analysis application data dependence relationship record table, obtaining all data warehouse dependence object records related to the application, and determining the data set and processing operation range related to the terminal application in each layer of the data warehouse.
5. The method of claim 1, wherein the method further comprises: The specific method of step (4) is: According to the terminal data analysis application data dependence relationship record obtained in step (3), identify and schedule batch processing task: First, identify the last execution time field of ADS layer record, if the interval with current time is less than a certain preset threshold, no need to start update task again, otherwise, start to schedule batch processing task, schedule according to the running order of ODS layer>DWD layer>ADS layer; The data table objects involved in the dependence relationship in ODS layer are compiled into ETL task; The data processing program objects involved in DWD layer are compiled into program execution task; The data processing program objects involved in ADS layer are compiled into program execution task.
6. The data analysis application data timeliness improvement method based on an offline data warehouse according to claim 5, characterized in that, The threshold is 10 minutes.
7. The method of claim 1, wherein the method further comprises: The specific method of step (5) is: first, performing ETL extraction data processing on the data table relied by the terminal application in the ODS layer of the data warehouse, and obtaining the latest data from the business system; then, executing the data processing program relied by the terminal application in the DWD layer, and generating the updated data of the DWD layer according to the data processing of the updated data of the ODS layer; finally, executing the data processing program relied by the terminal application in the ADS layer, and generating the updated data of the ADS layer according to the data processing of the updated data of the DWD layer. At this time, when the terminal data analysis application accesses the data of the ADS layer of the data warehouse, the latest updated data can be obtained, so as to realize the improvement of the timeliness of the data beyond the T+1 limit.