Techniques for relationship discovery between datasets

• US 10,650,000 B2
• Filed: 09/14/2017
• Issued: 05/12/2020
• Est. Priority Date: 09/15/2016
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Oberbreckling, Robert James, Rivas, Luis E., Malak, Michael, Murray, Glenn Allen
• Primary Examiner(s): Alam, Hosain T
• Assistant Examiner(s): Allen, Nicholas E
• Application Number:
  US15/705,160
• Publication Number:
  US 20180075104A1
• Time in Patent Office: 971 Days
• Field of Search: 707749
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

The present disclosure related to techniques for analyzing data from multiple different data sources to determine a relationship between the data (also referred to herein a “data relationship discovery”). The relationships between any two compared datasets may be used to determine one or more recommendations for merging (e.g., joining), or “blending,” the data sets together. Relationship discovery may include determining a relationship between a subset of data, such as a relationship between a pair of columns, or column pair, each column in a different dataset of the datasets that are compared. Given two datasets to process for relationship discovery, relationship discovery may identify and recommends a ranked subset of column pairs between two compared datasets. The ranked column pairs identified as a relationship may be useful for blending the datasets with respect to those column pairs.

Claims

1. A method comprising, at a computer system:

generating first profile metadata for each column of a first plurality of columns in a first dataset stored a first data source;

generating second profile metadata for each column of a second plurality of columns in a second dataset stored a second data source;

identifying a plurality of column pairs between the first dataset and the second dataset, wherein each column pair in the plurality of column pairs includes a different one of the first plurality of columns and a different one of the second plurality of columns;

determining one or more column pairs from the plurality of identified column pairs to exclude;

excluding at least one column pair from the one or more determined column pairs;

for each of the one or more column pairs remaining after the excluding step;

based on a type of join specified via a graphical interface, computing a plurality of scores for the column pair, each of the plurality of scores computed based on a different one of a plurality of scoring functions, the score indicating a measure for joining columns in the column pair;

computing a plurality of weighted scores, each of the plurality of weighted scores computed for a different one of the plurality of scores based on applying one of a plurality of weights to the different one of the plurality of scores; and

determining a pair score for the column pair, the pair score being a summation of the plurality of weighted scores;

based on the pair score for each of the one or more column pairs, selecting a first column pair from the one or more column pairs;

generating a third dataset based on merging, according to the type of join, the first dataset at a first column within the first column pair with the second dataset at a second column in the first column pair; and

generating the graphical interface to display the generated third dataset.

2. The method of claim 1, wherein the type of join is a left join, a right join, or an outer join.

3. The method of claim 1, wherein the one or more column pairs is a set of column pairs, and wherein the method further comprises:

determining a highest pair score from the pair score of each of the set of column pairs, wherein the first column pair is selected based on having the highest pair score.

4. The method of claim 1, wherein excluding the at least one column pair includes determining, based on the first profile metadata and the second profile metadata for columns in each of the at least one column pair, that the columns in each of the at least one column pair do not match each other based on a semantic category.

5. The method of claim 1, wherein excluding the at least one column pair includes determining, based on the first profile metadata and the second profile metadata for columns in each of the at least one column pair, that the columns in each of the at least one column pair do not have character sequence overlap.

6. The method of claim 1, wherein excluding the at least one column pair includes determining, based on the first profile metadata and the second profile metadata for columns in each of the at least one column pair, that the columns in each of the at least one column pair do not have population overlap based on text length range of each of the columns.

7. The method of claim 1, wherein excluding the at least one column pair includes determining, based on the first profile metadata and the second profile metadata for columns in each of the at least one column pair, that the columns in each of the at least one column pair do not have numerical overlap.

8. The method of claim 1, wherein the plurality of scoring functions include a first scoring function that is based on comparing example data for the columns in the column pair of the at least the one or more column pairs.

9. The method of claim 1, wherein the plurality of scoring functions includes a first scoring function based on column type.

10. The method of claim 1, wherein the plurality of scoring functions includes a first scoring function based on numerical data from the first profile metadata and the second profile metadata for the columns in the column pair of the at least the one or more column pairs.

11. A system comprising:

one or more processors; and

a memory accessible to the one or more processors, the memory comprising instructions that, when executed by the one or more processors, cause the one or more processors to;

generate first profile metadata for each column of a first plurality of columns in a first dataset stored a first data source;

generate second profile metadata for each column of a second plurality of columns in a second dataset stored a second data source;

identify a plurality of column pairs between the first dataset and the second dataset, wherein each column pair in the plurality of column pairs includes a different one of the first plurality of columns and a different one of the second plurality of columns;

determine one or more column pairs from the plurality of identified column pairs to exclude;

exclude at least one column pair from the one or more determined column pairs;

for each of the one or more column pairs remaining after the excluding step;

based on a type of join specified via a graphical interface, compute a plurality of scores for the column pair, each of the plurality of scores computed based on a different one of a plurality of scoring functions, the score indicating a measure for joining columns in the column pair;

compute a plurality of weighted scores, each of the plurality of weighted scores computed for a different one of the plurality of scores based on applying one of a plurality of weights to the different one of the plurality of scores; and

determine a pair score for the column pair, the pair score being a summation of the plurality of weighted scores;

based on the pair score for each of the one or more column pairs, select a first column pair from the one or more column pairs;

generate a third dataset based on merging, according to the type of join, the first dataset at a first column within the first column pair with the second dataset at a second column in the first column pair; and

generate the graphical interface to display the generated third dataset.

12. The system of claim 11, wherein the type of join is a left join, a right join, or an outer join.

13. The system of claim 11, wherein the one or more column pairs is a set of column pairs, and wherein the instructions, when executed by the one or more processors, further cause the one or more processors to:

determine a highest pair score from the pair score of each of the set of column pairs, wherein the first column pair is selected based on having the highest pair score.

14. The system of claim 11, wherein the plurality of scoring functions include a first scoring function that is based on comparing example data for the columns in the column pair of the at least the one or more column pairs.

15. The system of claim 11, wherein the plurality of scoring functions includes a first scoring function based on column type.

16. The system of claim 11, wherein the plurality of scoring functions includes a first scoring function based on numerical data from the first profile metadata and the second profile metadata for the columns in the column pair of the at least the one or more column pairs.

17. A non-transitory computer readable medium storing one or more instructions that are executable by one or more processors to cause the one or more processors to:

generate first profile metadata for each column of a first plurality of columns in a first dataset stored a first data source;

generate second profile metadata for each column of a second plurality of columns in a second dataset stored a second data source;

identify a plurality of column pairs between the first dataset and the second dataset, wherein each column pair in the plurality of column pairs includes a different one of the first plurality of columns and a different one of the second plurality of columns;

determine one or more column pairs from the plurality of identified column pairs;

exclude at least one column pair from the one or more determined column pairs;

for each of the one or more column pairs remaining after the excluding step;

based on a type of join specified via a graphical interface, compute a plurality of scores for the column pair, each of the plurality of scores computed based on a different one of a plurality of scoring functions, the score indicating a measure for joining columns in the column pair;

compute a plurality of weighted scores, each of the plurality of weighted scores computed for a different one of the plurality of scores based on applying one of a plurality of weights to the different one of the plurality of scores; and

determine a pair score for the column pair, the pair score being a summation of the plurality of weighted scores;

based on the pair score for each of the one or more column pairs, select a first column pair from the one or more column pairs;

generate a third dataset based on merging, according to the type of join, the first dataset at a first column within the first column pair with the second dataset at a second column in the first column pair; and

generate the graphical interface to display the generated third dataset.

18. The non-transitory computer readable medium of claim 17, wherein the one or more column pairs is a set of column pairs, and wherein the one or more instructions are executable by the one or more processors to further cause the one or more processors to:

determine a highest pair score from the pair score of each of the set of column pairs, wherein the first column pair is selected based on having the highest pair score.

19. The non-transitory computer readable medium of claim 17, wherein the type of join is a left join, a right join, or an outer join.

20. The non-transitory computer readable medium of claim 17, wherein excluding the at least one column pair includes determining, based on the first profile metadata and the second profile metadata for columns in each of the at least one column pair, that the columns in each of the at least one column pair do not match.