Apparatus and method for replicating data
An apparatus, computer-implemented method, and computer program are provided. In one embodiment, data is transferred from a database to a staging table in another database. The data is extracted from the staging table in order to transform the data, and the extracted data is transmitted to a database of another computing system.
1. A computer-implemented method, comprising:
transferring, by a computing system, data from a database to a staging table in another database;
extracting, by the computing system, the data from the staging table to transform the data and moving the extracted data to another staging table after transformation, wherein the extracting of the data comprises determining whether dimensions exist in the staging table in order to receive a dimension key or a new dimension key, returning the dimension key when the dimensions exist, and inserting the new dimension key into a dimension table when the dimensions do not exist and returning the new dimension key; and
transmitting, by the computing system, the extracted data to a database of another computing system;
wherein duplicate records are prevented from being created by:
performing, by the computing system, a check for duplicated records; and
deleting, by the computing system, duplicated records from the staging table.
2. The computer-implemented method of claim 1 , wherein the determining of whether the dimensions exist comprises:
creating, by the computing system, a duplication key to prevent duplicate records from being created.
3. The computer-implemented method of claim 1 , further comprising:
updating, by the computing system, a worktable record to indicate that the data has been successfully transferred.
4. The computer-implemented method of claim 1 , further comprising:
determining, by the computing system, a status of a job prior to processing the data; and
determining, by the computing system, whether the job is a local job.
5. The computer-implemented method of claim 4 , further comprising:
processing, by the computing system, the job when the job is a local job.
6. A computer program embodied on a non-transitory computer-readable medium, the computer program configured to cause at least one processor to:
transfer data from a database to a staging table in another database;
extract the data from the staging table to transform the data and move the extracted data to another staging table after transformation, wherein the extraction of the data comprises determining whether dimensions exist in the staging table in order to receive a dimension key or a new dimension key, returning the dimension key when the dimensions exist, and inserting the new dimension key into a dimension table when the dimensions do not exist and returning the new dimension key; and
transmit the extracted data to a database of another computing system;
wherein duplicate records are prevented from being created by:
performing, by the computing system, a check for duplicated records; and
deleting, by the computing system, duplicated records from the staging table.
7. The computer program of claim 6 , wherein the computer program is further configured to cause the at least one processor to:
create a duplication key to prevent duplicate records from being created.
8. The computer program of claim 6 , wherein the computer program is further configured to cause the at least one processor to:
update a worktable record to indicate that the data has been successfully transferred.
9. The computer program of claim 6 , wherein the computer program is further configured to cause the at least one processor to:
determine a status of a job prior to processing the data; and
determine whether the job is a local job.
10. The computer program of claim 9 , wherein the computer program is further configured to cause the at least one processor to:
process the job when the job is a local job.
11. An apparatus, comprising:
at least one processor; and
memory comprising instructions, wherein
the instructions, together with the at least one processor, are configured to cause the apparatus to:
transfer data from a database to a staging table in another database,
extract the data from the staging table to transform the data and move the extracted data to another staging table after transformation, wherein the extraction of the data comprises determining whether dimensions exist in the staging table in order to receive a dimension key or a new dimension key, returning the dimension key when the dimensions exist, and inserting the new dimension key into a dimension table when the dimensions do not exist and returning the new dimension key, and
transmit the extracted data to a database of another computing system;
wherein duplicate records are prevented from being created by:
performing, by the computing system, a check for duplicated records; and
deleting, by the computing system, duplicated records from the staging table.
12. The apparatus of claim 11 , wherein instructions, together with the at least one processor, are further configured to cause the apparatus to:
create a duplication key to prevent duplicate records from being created.
13. The apparatus of claim 11 , wherein instructions, together with the at least one processor, are further configured to cause the apparatus to:
update a worktable record to indicate that the data has been successfully transferred.
14. The apparatus of claim 11 , wherein instructions, together with the at least one processor, are further configured to cause the apparatus to:
determine a status of a job prior to processing the data;
determine whether the job is a local job; and
process the job when the job is a local job.