Supporting combination of flow based ETL and entity relationship based ETL

• US 10,216,814 B2
• Filed: 10/02/2013
• Issued: 02/26/2019
• Est. Priority Date: 05/17/2013
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Allan, David, Lau, Kwok-hung (Thomas), Gong, Yu (Jeff)
• Primary Examiner(s): Brooks, David T.
• Application Number:
  US14/044,429
• Publication Number:
  US 20140344211A1
• Time in Patent Office: 1,973 Days
• Field of Search: None
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: G06F 8/433 Dependency analysis; Data or control flow analysis

Abstract

A data integration system is disclosed that incorporates one or more techniques for eases the design and maintenance of a mapping. As components are added to an existing design, the data integration system removes the need to specify all input and output attributes. In one aspect, components types are implement that allow entity relationships to be added and/or edited in a flow view of a logical design. Therefore, attributes of components representing datasets can be added and propagated to downstream components with minimal effort on the part of a map designer.

Claims

1. A method for generating a data integration scenario between a data source and a data target using a combination of a data flow extract-transform-load (ETL) and an entity relationship extract-transform-load (ETL), the method comprising:

receiving, at one or more computer systems, a set of extract-transform-load (ETL) entity relationships defined via a component of a logical design of the data integration scenario, the set of ETL entity relationships having a relationship defined between one or more attributes of a first entity in a dataset and one or more attributes of a second entity in the dataset;

automatically determining, with one or more processors associated with the one or more computer systems, an extract-transform-load (ETL) data flow model using the set of ETL entity relationships, wherein the ETL data flow model is configured to provide semantic definitions for the logical design, wherein the semantic definitions define entity relationships in the dataset that identify how columns of the data target are populated from attributes of the data source and from one of the one or more attributes of the first entity in the dataset and the one or more attributes of the second entity in the dataset; and

generating, with the one or more processors associated with the one or more computer systems, information indicative of the ETL data flow model in a view of the logical design, wherein a set of attributes are exposed to downstream components of the logical design that includes the one or more attributes of the first entity and the one or more attributes of the second entity.

2. The method of claim 1 further comprising deriving one or more attributes of each entity in the dataset using information declaring relationships between attributes of data sources.

3. The method of claim 1 further comprising receiving information specifying one or more components of the logical design that includes information indicative of an operation that changes a shape of the information flowing through the logical design.

4. The method of claim 1 further comprising receiving information specifying one or more components of the logical design that includes information indicative of an operation that controls a flow of information flowing through the logical design and does not change a shape of the information flowing through the logical design.

5. The method of claim 1 further comprising receiving information specifying one or more components of the logical design that includes information indicative of a target component having one or more attributes of data to be stored in a target datastore.

6. The method of claim 1 further comprising exporting the set of attributes to a downstream component in response to adding the downstream component to the logical design via a user interface.

7. The method of claim 1 further comprising:

receiving, at the one or more computer systems, a change in the logical design by introducing the set of ETL entity relationships; and

determining, with the one or more processors associated with the one or more computer systems, an updated data flow using the introduced set of ETL entity relationships.

8. The method according to claim 1, wherein the data integration scenario comprises components added to the logical design.

9. The method according to claim 1, further comprising generating a physical design based on the logical design of the data integration scenario.

10. The method according to claim 1, wherein a logical view of the logical design specifies entity relationships in order to populate attributes of the data target from attributes of the data source.

11. The method according to claim 1, wherein the ETL data flow model comprises a three-way relationship which absorbs an associative entity so that the first entity is configured to participate in two binary relationships at a same time.

12. A non-transitory computer-readable medium storing computer-executable code for generating a data integration scenario between a data source and a data target using a combination of a data flow extract-transform-load (ETL) and an entity relationship extract-transform-load (ETL), the non-transitory computer-readable medium comprising:

code for receiving a set of extract-transform-load (ETL) entity relationships defined via a component of a logical design of the data integration scenario, the set of ETL entity relationships having a relationship defined between one or more attributes of a first entity in a dataset and one or more attributes of a second entity in the dataset;

code for automatically determining an extract-transform-load (ETL) data flow model using the set of ETL entity relationships, wherein the ETL data flow model is configured to provide semantic definitions for the logical design, wherein the semantic definitions define entity relationships in the dataset that identify how columns of the data target are populated from attributes of the data source and from one of the one or more attributes of the first entity in the dataset and the one or more attributes of the second entity in the dataset; and

code for generating information indicative of the ETL data flow model in a view of the logical design, wherein a set of attributes are exposed to downstream components of the logical design that includes the one or more attributes of the first entity and the one or more attributes of the second entity.

13. The non-transitory computer-readable medium of claim 12 further comprising code for deriving one or more attributes of each entity in the dataset using information declaring relationships between attributes of data sources.

14. The non-transitory computer-readable medium of claim 12 further comprising code for receiving information specifying one or more components of the logical design that includes information indicative of an operation that changes a shape of the information flowing through the logical design.

15. The non-transitory computer-readable medium of claim 12 further comprising code for receiving information specifying one or more components of the logical design that includes information indicative of an operation that controls the flow of information flowing through the logical design but does not change a shape of the information flowing through the logical design.

16. The non-transitory computer-readable medium of claim 12 further comprising code for receiving information specifying one or more components of the logical design that includes information indicative of a target component having one or more attributes of data to be stored in a target datastore.

17. The non-transitory computer-readable medium of claim 12 further comprising code for exporting the set of attributes to a downstream component in response to adding the downstream component to the logical design via a user interface.

18. The non-transitory computer-readable medium of claim 12 further comprising:

code for receiving a change in the logical design by introducing the set of ETL entity relationships; and

code for determining an updated data flow using the introduced set of ETL entity relationships.

19. A system for generating a data integration scenario between a data source and a data target using a combination of a data flow extract-transform-load (ETL) and an entity relationship extract-transform-load (ETL), the system comprising:

a processor; and

a memory storing instructions which when executed by the processor configure the processor to;

receive a set of extract-transform-load (ETL) entity relationships defined via a component of a logical design of the data integration scenario, the set of ETL entity relationships having a relationship defined between one or more attributes of a first entity in a dataset and one or more attributes of a second entity in the dataset;

automatically determine an extract-transform-load (ETL) data flow model using the set of ETL entity relationships, wherein the ETL data flow model is configured to provide semantic definitions for the logical design, wherein the semantic definitions define entity relationships in the dataset that identify how columns of the data target are populated from attributes of the data source and from one of the one or more attributes of the first entity in the dataset and the one or more attributes of the second entity in dataset; and

generate information indicative of the ETL data flow model in a view of the logical design, wherein a set of attributes are exposed to downstream components of the logical design that includes the one or more attributes of the first entity and the one or more attributes of the second entity.

20. The system of claim 19 wherein the processor is further configured to derive one or more attributes of each entity in the dataset using information declaring relationships between attributes of data sources.

21. The system of claim 19 wherein the processor is further configured to receive information specifying one or more components of the logical design that includes information indicative of an operation that changes shape of the information flowing through the logical design.

22. The system of claim 19 wherein the processor is further configured to receive information specifying one or more components of the logical design that includes information indicative of an operation that controls the flow of information flowing through the logical design but does not change shape of the information flowing through the logical design.

23. The system of claim 19 wherein the processor is further configured to receive information specifying one or more components of the logical design that includes information indicative of a target component having one or more attributes of data to be stored in a target datastore.

24. The system of claim 19 wherein the processor is further configured to export the set of attributes to a downstream component in response to adding the downstream component to the logical design via a user interface.

25. The system of claim 19 wherein the processor is further configured to:

receive a change in the logical design by introducing the set of ETL entity relationships; and

determine an updated data flow using the introduced set of ETL entity relationships.