WO2020139074A1

WO2020139074A1 - System and method for monitoring data errors in extract, transform and load (etl) flow

Info

Publication number: WO2020139074A1
Application number: PCT/MY2019/050129
Authority: WO
Inventors: Tong Khin Thong; Badrul Affandy AHMAD LATFI; Chee Kiam LEE
Original assignee: Mimos Bhd
Current assignee: Mimos Bhd
Priority date: 2018-12-26
Filing date: 2019-12-26
Publication date: 2020-07-02
Anticipated expiration: 2021-06-26
Also published as: MY198828A

Abstract

The present invention discloses a system and method for monitoring data errors in an extract, transform and load, ETL, flow initiated by a user. The system is a plugin module used as a sub-flow to the ETL flow. The system comprises a resolver table generator (100) for generating a resolver table in a database (101) comprising data that is subject to a data cleansing sub-flow for producing cleansed data. The system comprises an error correction module (200) comprising a first configuration Extensible Markup Language, XML, generator for providing an error correction form that is generated based on a data error type. The error correction module (200) completes the error correction form using the cleansed data and displays the completed error correction form to the user for data correction.

Description

SYSTEM AND METHOD FOR MONITORING DATA ERRORS IN EXTRACT, TRANSFORM AND LOAD (ETL) FLOW

FIELD OF THE INVENTION

The present invention relates generally to arrangement for an extract, transform and load (ETL) flow. More particularly, the present invention relates to a system for monitoring data errors in the ETL flow and to a method thereof.

BACKGROUND OF THE INVENTION

ETL is an abbreviation of extract, transform and load which are three separate but crucial functions combined into a single programming tool that helps in preparing data and in the management of databases. Extract, transform and load each denote a process in the movement of data from its source to a data, often referred to as a data warehouse or a database. ETL is a recurring activity of the data storage and needs to be agile, automated and well-documented.

In ETL, the extract function reads data from a source database and extracts the desired subset of data. The transform function filters, cleanses, and prepares the extracted data using lookup tables or rules or by creating combinations with other data and converts it to the desired state. The load function involving loading is the last stage of ETL flow. It writes the resulting data, i.e. the extracted and transformed data, to a target data repository. The entire ETL flow can be executed with the help of ETL tools that contain graphical interfaces which speed up the process of mapping tables and columns between the source and the target data repository.

One of the problems with the existing ETL flow is that it requires a separate database management tool in order to fix errors and inconsistencies in the data processed through ETL. In addition, the fixation must be manually made by a data operator who is responsible for the ETL process. Further, the error correction demands data to be stored and status tracked whether the data has been updated, deleted or pending for verification. Besides, many ETL jobs fail to organize and monitor data cleansing output for the user’s display. The data cleansing output can be generated in more than one format. For instance, in the duplicate detection, the output would be matched and non-matched lists. Different error correction can have different visual forms to display and correct the data.

For example, by way of background, United States Patent Publication No. 2017/0068595 A1 (hereinafter“the‘595 publication”) discloses a cloud-based ETL system provides error detection, error correction and reporting of data integration flows hosted by cloud services. According to the‘595 publication, categories of errors are identified using one or more checks at different points of a data integration flow and one or more actions selected based at least in part on the error category. The‘595 publication further teaches that a determination can be made whether the error category is fault tolerant and one or more actions can be selected based at least in part on the error fault tolerance to correct the error, restart a flow, or generate a notification assisting a user to correct the error.

Considering the problems stated above which have never been address sufficiently by prior attempts, there still exists a need for a system and a method for dynamically monitoring data errors in ETL flow that can be integrated with ETL tools thereby overcoming the problems and shortcomings of the prior art.

SUMMARY OF THE INVENTION

The following presents a simplified summary of the invention in order to provide a basic understanding of some aspects of the invention. This summary is not an extensive overview of the invention. Its sole purpose is to present some concepts of the invention in a simplified form as a prelude to the more detailed description that is presented later.

Accordingly, the present invention provides a system for monitoring data errors in an extract, transform and load (ETL) flow initiated by a user.

The system of the present invention communicating with a web server may be characterized as a plugin module used as a sub-flow of the ETL flow. The system preferably comprises a resolver table generator residing at the web server configured for generating a resolver table in a database comprising data designated for the ETL flow, wherein the data is subject to a data cleansing sub flow for producing cleansed data, wherein the resolver table includes a parent table, a column table, a row table and an address correction table; and an error correction module in communication the resolver table generator comprising a first configuration Extensible Markup Language (XML) generator configured for providing an error correction form, wherein the error correction form is generated based on a data error type selected by the user for use in an error correction job of the ETL flow at the web server, wherein the error correction module completes the error correction form using the cleansed data provided in the resolver table selected based on metadata associated with the data thereof, wherein the error correction module displays the completed error correction form to the user for data correction, wherein the data correction generates corrected data that replaces the data in the resolver table.

Preferably, the data error type includes a duplication error, a record linkage error, an address abbreviation and chunking error, a missing value error and a data domain violation error.

Preferably, the system further comprises an error detection module having a second configuration XML generator connected to a job repository. Preferably, the system further comprises an error correction form connector residing at the web server configured for facilitating displaying of the completed error correction form to the user thereof.

Preferably, the error correction module extracts the metadata of the data from the database thereof.

Preferably, the metadata includes data error type, table field and detection field. In accordance with another aspect, the present invention provides a method of monitoring data errors in an ETL flow initiated by a user.

The method may be characterized as a sub-flow of the ETL flow and the steps of providing and executing an error correction job to the ETL flow, comprising providing an error correction form based on a data error type selected by the user, and providing a resolver table in a database comprising data designated for the ETL flow, wherein the resolver table includes a parent table, a column table, a row table and an address correction table; subjecting the data to a data cleansing sub-flow for producing cleansed data; extracting metadata associated with the data; consolidating the cleansed data resulting from the data cleansing sub-flow thereof to the resolver table; completing the error correction form using the cleansed data of the resolver table; displaying the completed error correction form to the user for data correction; and receiving, based on the data correction, corrected data from the user to replace the data in the resolver table thereof.

It is an objective of the present invention to provide means for monitoring (i.e. in the form of system and method) data errors that can be integrated with the ETL process and can be used as a plugin module and sub-flow of the ETL flow. The means for monitoring of the present invention advantageously generates the error correction form dynamically in an automatic manner with the help of metadata associated with the data and the resolver table.

The foregoing and other objects, features, aspects and advantages of the present invention will become better understood from a careful reading of a detailed description provided herein below with appropriate reference to the accompanying drawings.

BRIEF DESCRIPTION OF THE DRAWINGS

A more complete appreciation of the invention and many of the attendant advantages thereof will be readily as the same becomes better understood by reference to the following detailed description when considered in connection with the accompanying drawings, wherein:

Figure 1 shows a system for monitoring data errors in an extract, transform and load (ETL) flow according to one embodiment of the present invention;

Figure 2 illustrates a resolver table including a parent table (tablea_xd), a column table (tablea_xcol), a row table (tablea_xrow) and an address correction table (tablea_xaddr) according to one embodiment of the present invention; Figure 3a is a flow diagram according to a prior art approach for use in error detection in the conventional ETL job flowchart;

Figure 3b is a flow diagram depicting a method for monitoring data errors in ETL flow according to one embodiment of the present invention;

Figure 4 is a flow diagram of the step of providing an error correction job to ETL flow of the method in Figure 3b according to one embodiment of the present invention;

Figure 5 is a flow diagram of the step of executing error correction module sub flow of the method in Figure 3b according to one embodiment of the present invention; Figure 6 is a flow diagram of the step of displaying the completed error correction form to user for data correction and the step of receiving the corrected data to replace data in resolver table of the method in Figure 3b according to one embodiment of the present invention; and Figures 7, 8 and 9 illustrate a sample summary of error correction form, a sample of error correction form showing a record linkage error and a sample of error correction form showing a data domain violation error, respectively, according to one embodiment of the present invention. It is noted that the drawings may not be to scale. The drawings are intended to depict only typical aspects of the invention, and therefore should not be considered as limiting the scope of the invention. In the drawings, like numberings represent like elements between the drawings.

DETAILED DESCRIPTION OF THE INVENTION

Essentially, the present invention discloses a system and a method for monitoring data errors in an extract, transform and load (ETL) flow as initiated by a user that can be used as a plugin module and sub-flow of the ETL flow with a user interface to help the user to access large volume of target-table data through group based records in web server, and to make correction of data errors dynamically in an automatic manner. The present invention advantageously allows the user to navigate, read, update, revert and delete error data, and ultimately, to transfer the corrected data to a target repository or target database.

The ETL flow used herein refers to a flow which is a message processing block that has its own processing strategy and exception handling strategy. It can be used in, for example, integration tasks, data processing, connecting applications and event processing. Sub-flow, on the other hand, processes messages synchronously but inherits processing strategy and exception handling strategy from a calling flow such as the ETL flow. It can be used to split common logic and be reused by other flows.

According to one preferred embodiment, the system of the present invention which communicates with a web server (e.g. an Internet location) comprises a resolver table generator 100, an error correction module 200, an error detection module 300 and an error correction form connector 400, as schematically shown in Figure 1 of the accompanying drawings. The system of the present invention further comprises a configuration retriever and parser, and a dynamic database connection.

The resolver table generator 100 and the error correction form connector 400 preferably reside at the web server alongside the configuration retriever and parser and the dynamic database connection. The error correction module 200 and the error detection module 300 preferably reside at ETL Client. The ETL client preferably has an interface that allows the user to access ETL scheduler and check status of jobs configured. A database 101 comprising data designated for ETL flow is deployed for use by the resolver table generator 100 and the error correction form connector 400. A job repository 102 is also utilized in the system of the present invention. The job repository 102 is preferably connected to the error correction module 200, the error detection module 300 and the configuration retriever and parser thereof. The job repository 102 of the present invention may contain, among others, particulars of parent-child dependency of the database 101 with hierarchical relationship of the same. The resolver table generator 100 is configured for generating a resolver table in the database 101 selected thereof. The data in the database 101 is subject to a data cleansing sub-flow for producing cleansed data. The data cleansing sub-flow is preferably part of the ETL flow. The resolver table preferably includes a parent table (tablea_xd), a column table (tablea_xcol), a row table (tablea_xrow) and an address correction table (tablea_xaddr). An example of the resolver table is shown in Figure 2.

The error correction module 200 comprises a first configuration Extensible Markup Language (XML) generator configured for providing an error correction form. The error correction form is preferably generated based on a data error type that is selected by the user. The data error type includes, but is not limited to, a duplication error, a record linkage error, an address abbreviation and chunking error, a missing value error and a data domain violation error. The error correction form is adopted for use in an error correction job of the ETL flow at the web server. With regards to the relation between the error correction form and the resolver table, the system of the present invention configures the error correction module 200 to complete and fill up the error correction form using the cleansed data provided in the resolver table. It is preferred that the cleansed data is selected from the resolver table based on metadata associated with the data thereof. The metadata includes, but is not limited to, data error type, table field and detection field. The metadata is preferably extracted out from the database 101 thereof by the error correction module 200.

The error correction module 200 is further configured to display the completed error correction form to the user for data correction. The displaying of the completed error correction form to the user is preferably facilitated by the error correction form connector 400 thereof. During the data correction, corrected data is generated and used to replace the data originally allocated in the resolver table.

The error detection module 300 has a second configuration XML generator 300a that is connected to the job repository 102 thereof.

According to another preferred embodiment, the method of the present invention is executed as a sub-flow of the ETL flow. A typical flow diagram, according to a prior art approach, for use in error detection in the conventional ETL job flowchart is shown in Figure 3a. Unlike the conventional, the method of the present invention comprises a number of inventive steps that is injected as sub-flows to the ETL flow (see Figure 3b). Accordingly, the method begins with the step of designing or providing an ETL flow in the ETL Client on a computer.

Subsequently, an error correction job (which is an ETL job) is designed and provided in step 500. The error correction job is preferably designed using the error correction module 200 thereof by the user. As shown in Figure 4, the step 500 comprises the step 500a of providing an error correction form based on a data error type selected by the user and the step of 500b of providing a resolver table in a database comprising data designated for the ETL flow. At the end of step 500, the error correction form and the resolver table are appropriately generated for use in the ETL flow. The configuration related to the error correction job, i.e. the error correction job configuration, is stored and saved in the job repository 102. Once step 500 is completed, the error correction job is executed and run on the web server.

Following the flow diagram of Figure 3b, the data is subject to a data cleansing sub-flow for producing cleansed data in step 501.

An error correction module sub-flow which is supervised by the error correction module 200 is explained by Figure 5. The execution of error correction module sub-flow comprises the step 502 of extracting metadata associated with the data thereof. The metadata extracted thereof may include data error type, table field and detection field. Subsequently, based from the extracted metadata, the data error type is checked whether or not it is supported for use or application in the ETL flow. If the data error type is not supported, then the error correction module 200 is disabled and all works are ceased to end. If the data error type is supported, then the cleansed data resulting from the data cleansing sub-flow in step 501 is consolidated to the resolver table as provided in step 500. Once consolidated, the cleansed data and the resolver table filled with the cleansed data are prepared for transition or transmission to the target repository or target database. If the transition of the cleansed data and the resolver table filled with the cleansed data is not successful, then the error correction module 200 is disabled and all works are ceased to end. If the transition of the cleansed data and the resolver table filled with the cleansed data is successful, then the error correction sub-flow is concluded by the error correction module 200 thereof. Once the error correction module sub-flow is completed, the error correction module 200 initiates the step 504 of completing the error correction form using the cleansed data of the resolver table, and the step 505 of displaying the completed error correction form to the user for data correction. Prior to that, the extraction of metadata associated with the data and the retrieval of cleansed data form the resolver table are executed. From the data correction, corrected data is produced and received from the user in step 506. The corrected data is used to replace the data originally in the resolver table thereof. The user stores the corrected data and subsequently, the corrected data is saved in the resolver table thereof. The user is triggered to confirm the corrected data in the resolver table. Upon confirmation, the corrected data is updated to a main data table. Figure 6 provides detailed flow diagram pertaining to the steps 504, 505 and 506.

Figure 7 illustrates a sample summary of error correction form. In the sample summary, the error correction form is prepared based on data error type which is listed in the first column. The number of data correction is showcased in the‘corrected’ column. For detailed information on data correction, the user can go to the respective buttons on the‘correction’ column.

Figure 8 illustrates a sample of error correction form showing a record linkage error. The user can arrive at the sample by way of hitting or clicking the relevant button from the summary of error correction form as sampled in Figure 7.

Figure 9 illustrates a sample of error correction form showing a data domain violation error. The user can arrive at the sample by way of hitting or clicking the relevant button from the summary of error correction form as sampled in Figure 7.

The terms“a” and“an,” as used herein, are defined as one or more than one. The term“plurality,” as used herein, is defined as two or more than two. The term“another,” as used herein, is defined as at least a second or more. The terms “including” and/or“having,” as used herein, are defined as comprising (i.e., open language). While this invention has been particularly shown and described with reference to the exemplary embodiments thereof, it will be understood by those skilled in the art that various changes in form and details may be made therein without departing from the scope of the invention as defined by the appended claims.

Claims

1. A system for monitoring data errors in an extract, transform and load, ETL, flow initiated by a user, characterized in that, the system communicating with a web server is a plugin module used as a sub-flow of the ETL flow, comprising:

a resolver table generator (100) residing at the web server configured for generating a resolver table in a database (101 ) comprising data designated for the ETL flow,

wherein the data is subject to a data cleansing sub-flow for producing cleansed data,

wherein the resolver table includes a parent table, a column table, a row table and an address correction table; and

an error correction module (200) in communication with the resolver table generator (100) comprising a first configuration Extensible Markup Language, XML, generator configured for providing an error correction form,

wherein the error correction form is generated based on a data error type selected by the user for use in an error correction job of the ETL flow at the web server,

wherein the error correction module (200) completes the error correction form using the cleansed data provided in the resolver table selected based on metadata associated with the data thereof, wherein the error correction module (200) displays the completed error correction form to the user for data correction,

wherein the data correction generates corrected data that replaces the data in the resolver table.

2. The system according to Claim 1 , wherein the data error type includes a duplication error, a record linkage error, an address abbreviation and chunking error, a missing value error and a data domain violation error.

3. The system according to Claim 1 further comprises an error detection module (300) having a second configuration XML generator (300a) connected to a job repository (102).

4. The system according to Claim 1 further comprises an error correction form connector (400) residing at the web server configured for facilitating displaying of the completed error correction form to the user thereof.

5. The system according to Claim 1 , wherein the error correction module (200) extracts the metadata of the data from the database (101 ) thereof.

6. The system according to Claim 1 , wherein the metadata includes data error type, table field and detection field.

7. A method of monitoring data errors in an extract, transform and load, ETL, flow initiated by a user, characterized in that, the method executed as a sub-flow of the ETL flow comprising the steps of:

providing and executing an error correction job to the ETL flow (500), comprising:

providing an error correction form based on a data error type selected by the user (500a); and

providing a resolver table in a database comprising data designated for the ETL flow, wherein the resolver table includes a parent table, a column table, a row table and an address correction table (500b);

subjecting the data to a data cleansing sub-flow for producing cleansed data (501 );

extracting metadata associated with the data (502);

consolidating the cleansed data resulting from the data cleansing sub-flow thereof to the resolver table (503);

completing the error correction form using the cleansed data of the resolver table (504);

displaying the completed error correction form to the user for data correction (505); and

receiving, based on the data correction, corrected data from the user to replace the data in the resolver table thereof (506).