Rule-based extraction, transformation, and loading of data between disparate data sources

• US 10,402,420 B2
• Filed: 06/05/2015
• Issued: 09/03/2019
• Est. Priority Date: 10/04/2012
• Status: Active Grant

• Current Assignee: Adobe Inc.
• Original Assignee: Adobe Inc.
• Inventors: Brown, Nicholas J., Cardon, David L., Carter, Jason A.
• Primary Examiner(s): Morrison, Jay A
• Application Number:
  US14/732,232
• Publication Number:
  US 20150278318A1
• Time in Patent Office: 1,551 Days
• CPC Class Codes: G06F 16/2291 User-Defined Types; Storage management thereof
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/2477 Temporal data queries
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

Methods and apparatus are presented for extracting, transforming, and loading data from one database to another database. For example, an extraction, transformation, and loading (ETL) component may access an operational log of a given database in order to detect an update to the database. Upon detecting the update, the ETL component may extract a subset of data from the operational log, where the extraction of the subset of data is based on one or more rules. Ones the subset of data has been extracted, the ETL component may transform the extracted subset of data from the operational log into a format for another, target database, where the data format for the other, target database is different from a data format for the given, source database. The ETL component may then load the subset of data transformed into the data format for the other, target database into the target database.

Claims

1. A method, comprising:

accessing, by a computing system, an operational log to detect an update to a transactional database, wherein the transactional database updates the operational log when transactional data is received, the transactional data generated when Internet marketing is performed and comprising an attribute for a keyword, an ad, a group that includes certain keywords or ads, or a marketing campaign;

performing, by the computing system and in response to detecting the update, operations comprising;

extracting a subset of data from the operational log without accessing the transactional database, wherein the extracting includes identifying attribute-based data corresponding to the attribute, and

transforming the extracted subset of data into transformed data by aggregating the subset of data having a same attribute value for the attribute and occurring within a specific time range;

accessing, by the computing system, a data object having an attribute field and a metrics field corresponding to the specific time range, the data object being indexed by the attribute field; and

updating, by the computing system, the data object by adding the transformed data to the attribute field and adding metrics data from an analytics database to the metrics field, the metrics data corresponding to the specific time range.

2. The method of claim 1, further comprising:

accessing another operational log of the analytics database to detect an update to the analytics database;

in response to detecting the update to the analytics database, extracting an additional subset of data from the other operational log without accessing the analytics database;

transforming the additional extracted subset of data by aggregating the subset of data into the data object, wherein the data object aggregates analytic data having a same time range wherein transforming the extracted subset of data and the additional extracted subset of data comprises formatting the transformed data and the additional transformed subset of data for loading into an aggregation database that includes the data object; and

loading, into the aggregation database, the additional transformed subset of data.

3. The method of claim 2, wherein the analytics database is an online analytical processing (OLAP) database, and wherein the transactional database is not an OLAP database.

4. The method of claim 1, wherein the extracting is based on one or more rules that are configurable.

5. The method of claim 4, wherein the one or more rules are designed to identify attribute-based data in accord with attribute data used for indexing into the data object.

6. The method of claim 1, wherein the operational log indicates an update to the transactional database, the update indicating a delta from a previous update of the operational log.

7. The method of claim 1, wherein the transactional database stores attribute-based data and updates the operational log as transactional data is received.

8. The method of claim 1, wherein adding the transformed data to the attribute field of the data object enables the transformed data to be accessed without accessing the transactional database.

9. The method of claim 1, further comprising:

receiving, from a user, a query related to data included in the transactional database;

identifying, using the query, an existing data object; and

determining, using the existing data object, results for the query.

10. The method of claim 1, further comprising:

receiving a query having one or more parameters that match source data stored in the transactional database and source data stored in the analytics database;

determining that the transformed data added to the attribute field of the data object and the metrics data added to the metrics field of the data object match the query parameters; and

servicing the query by retrieving the transformed data and the metrics data from the data object rather than the transactional database and the analytics database.

11. The method of claim 10, wherein the one or more parameters include an attribute parameter and a metrics parameter, wherein the attribute parameter matches attribute data included in the source data from the transactional database and the metrics parameter matches metrics data included in the source data from the analytics database.

12. A non-transitory computer-readable storage medium storing program instructions, wherein the program instructions are computer executable to implement:

aggregating data from at least two different data sources, wherein said aggregating comprises;

accessing an operational log to detect an update to a transactional database, wherein the transactional database updates the operational log when transactional data is received, the transactional data generated when Internet marketing is performed and comprising an attribute for a keyword, an ad, a group that includes certain keywords or ads, or a marketing campaign;

performing, in response to detecting the update, operations comprising;

(i) extracting a subset of data from the operational log without accessing the transactional database, wherein the extracting includes identifying attribute-based data corresponding to the attribute, and

(ii) transforming the extracted subset of data into transformed data by aggregating the subset of data having a same attribute value for the attribute and occurring within a specific time range;

accessing a data object having an attribute field and

a metrics field corresponding to the specific time range, the data object being indexed by the attribute field; and

updating, the data object by adding the transformed data to the attribute field and adding metrics data from an analytics database to the metrics field, the metrics data corresponding to the specific time range.

13. The non-transitory computer-readable storage medium of claim 12, wherein the extracting is based on one or more rules that are configurable.

14. The non-transitory computer-readable storage medium of claim 13, wherein the one or more rules are designed to identify attribute-based data in accord with attribute data used for indexing into the data object.

15. The non-transitory computer-readable storage medium of claim 13, wherein the program instructions include additional program instructions that are computer executable to implement:

receiving, from a user, a query related to data included in the transactional database;

identifying, using the query, an existing data object; and

determining, using the existing data object, results for the query.

16. A system, comprising:

at least one processing device; and

a memory device communicatively coupled to the at least one processing device and comprising program instructions,

wherein the at least one processing devices is configured for executing the program instructions

and thereby aggregating data from at least two different data sources, wherein the aggregating comprises;

accessing an operational log to detect an update to a transactional database, wherein the transactional database updates the operational log when transactional data is received, the transactional data generated when Internet marketing is performed and comprising an attribute for a keyword, an ad, a group that includes certain keywords or ads, or a marketing campaign,

performing, in response to detecting the update, operations comprising;

(i) extracting a subset of data from the operational log without accessing the transactional database, wherein the extracting includes identifying attribute-based data corresponding to the attribute, and

(ii) transforming the extracted subset of data into transformed data by aggregating the subset of data having a same attribute value for the attribute and occurring within a specific time range,

accessing a data object having an attribute field and

a metrics field corresponding to the specific time range, the data object being indexed by the attribute field, and

updating, the data object by adding the transformed data to the attribute field and adding metrics data from an analytics database to the metrics field, the metrics data corresponding to the specific time range.

17. The system of claim 16, wherein the extracting is based on one or more rules that are configurable.

18. The system of claim 17, wherein the one or more rules are designed to identify attribute-based data in accord with attribute data used for indexing into the data object.

19. The system of claim 17, wherein the aggregating further comprises:

obtaining the one or more rules, the one or more rules identifying one or more types of logging updates that are of interest to a user, the one or more rules corresponding to a structure of the data object.

20. The system of claim 16, wherein the memory device stores additional program instructions, wherein execution of the additional program instructions cause the at least one processing device to perform:

receiving, from a user, a query related to data included in the transactional database;

identifying, using the query, an existing data object; and

determining, using the existing data object, results for the query.