Data lineage system

• US 9,075,860 B2
• Filed: 03/14/2013
• Issued: 07/07/2015
• Est. Priority Date: 10/18/2012
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Kozina, Ludmila, Rees, John K., Narayan, Abhishek
• Primary Examiner(s): SOMERS, MARC S
• Application Number:
  US13/803,883
• Publication Number:
  US 20140114907A1
• Time in Patent Office: 845 Days
• Field of Search: 707/600, 707/602, 707/603, 707/609, 707/803
• US Class Current: 1/1
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477
• 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/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A data lineage system is provided that traces a data lineage of a data warehouse. The data lineage system maps a target data element to one or more source data elements. The data lineage system further stores one or more source surrogate keys within one or more auxiliary columns of a target data record. The data lineage system further stores, for each source data element, a data lineage mapping system record within a data lineage mapping system table that represents the mapping of the target data element and the corresponding source data element. The data lineage system further maps a source data element to one or more target data elements. The system further stores, for each target data element, a shadow system record within a shadow system table that represents the mapping of the source data element and the corresponding target data element.

Claims

1. A non-transitory computer-readable medium having instructions stored thereon that, when executed by a processor, cause the processor to trace a data lineage of a data warehouse comprising one or more data tables, wherein each data table comprises one or more data records, the tracing comprising:

mapping a target data element to one or more source data elements, wherein the target data element comprises a column of a target table definition, and wherein each source data element comprises a column of a source table definition;

extending the target table definition to comprise one or more auxiliary columns to store one or more source surrogate keys;

storing, for each source data element, a data lineage mapping system record within a data lineage mapping system table that represents the mapping of the target data element and a corresponding source data element together with a column identity of an auxiliary column in a target table that stores a source surrogate key;

storing one or more source surrogate key values within one or more auxiliary columns of a target data record at the time the target data record is created or modified;

storing, for each target data record, one or more shadow system records within a shadow system table that represents a mapping of a source data record and a corresponding target data record source surrogate key value;

wherein the data lineage comprises the one or more data lineage mapping system records, the one or more shadow system records, and the one or more source surrogate keys;

storing a filter column identity within a first auxiliary column of the target data record, wherein the filter column identity identifies a filter column of the source data record; and

storing a filter value within a second auxiliary column of the target data record, wherein the filter value is a filter value of the source data record.

2. The non-transitory computer-readable medium of claim 1,

wherein the target data element is derived from the one or more source data elements using a data transformation;

wherein each source surrogate key comprises an identity of the source data record that comprises the corresponding source data element;

wherein the data lineage mapping system record comprises the corresponding source data element, the target data element, and the one or more auxiliary columns; and

wherein at least one shadow system record comprises a source surrogate key value that identifies the source data record, a target surrogate key value that identifies the corresponding target data record, and one or more auxiliary column values.

3. The non-transitory computer-readable medium of claim 2, wherein the data transformation is one of:

a direct mapping transformation, a union transformation, a join transformation, a horizontal to vertical unpivot transformation, a vertical to horizontal pivot transformation, or a case transformation.

4. The non-transitory computer-readable medium of claim 2, wherein the data lineage mapping system record further comprises a type of the data transformation.

5. The non-transitory computer-readable medium of claim 1, the tracing further comprising:

selecting a data element from the one or more data elements that are mapped using the data lineage; and

displaying one or more data elements that are mapped to the selected data element within a user interface using the data lineage.

6. The non-transitory computer-readable medium of claim 5, wherein the displaying of the data elements that are mapped to the selected data element further comprises, displaying, for each data element, a position of the data element within the data lineage.

7. The non-transitory computer-readable medium of claim 1, wherein each surrogate key of the one or more surrogate keys comprises an identity of a source data table that comprises the source data record.

8. The non-transitory computer-readable medium of claim 1, the tracing further comprising:

selecting, for each target data element, one source data element of the one or more source data elements as a preferred source data element; and

creating a preferred source path of the data lineage based on the selected one or more preferred source data elements.

9. The non-transitory computer-readable medium of claim 1, wherein the data warehouse comprises one or more ordered data models, wherein each ordered data model comprises one or more data tables, and wherein one or more data tables of a subsequent data model are formed from at least one table of a previous data model using a data transformation.

10. The non-transitory computer-readable medium of claim 1, the tracing further comprising storing a pivot column identity within an auxiliary column of the target data record, wherein the pivot column identity identifies a pivot column of the source data record.

11. The non-transitory computer-readable medium of claim 1, the tracing further comprising automatically adding the one or more auxiliary columns to the target data record based on the mapping of the target data element to the one or more source data elements.

12. A computer-implemented method for tracing a data lineage of a data warehouse, the computer-implemented method comprising:

mapping a target data element to one or more source data elements, wherein the target data element comprises a column of a target table definition, and wherein each source data element comprises a column of a source table definition;

extending the target table definition to comprise one or more auxiliary columns to store one or more source surrogate keys;

storing, for each source data element, a data lineage mapping system record within a data lineage mapping system table that represents the mapping of the target data element and a corresponding source data element together with a column identity of an auxiliary column in a target table that stores a source surrogate key;

storing one or more source surrogate key values within one or more auxiliary columns of a target data record at the time the target data record is created or modified;

storing, for each target data record, one or more shadow system records within a shadow system table that represents a mapping of a source data record and a corresponding target data record source surrogate key value;

wherein the data lineage comprises the one or more data lineage mapping system records, the one or more shadow system records, and the one or more source surrogate keys;

storing a filter column identity within a first auxiliary column of the target data record, wherein the filter column identity identifies a filter column of the source data record; and

storing a filter value within a second auxiliary column of the target data record, wherein the filter value is a filter value of the source data record.

13. The computer-implemented method of claim 12,

wherein the target data element is derived from the one or more source data elements using a data transformation;

wherein each source surrogate key comprises an identity of the source data record that comprises the corresponding source data element;

wherein the data lineage mapping system record comprises the corresponding source data element, the target data element, and the one or more auxiliary columns; and

wherein at least one shadow system record comprises a source surrogate key value that identifies the source data record, a target surrogate key value that identifies the corresponding target data record, and one or more auxiliary column values.

14. The computer-implemented method of claim 12, further comprising:

selecting a data element from the one or more data elements that are mapped using the data lineage; and

displaying one or more data elements that are mapped to the selected data element within a user interface using the data lineage.

15. The computer-implemented method of claim 12, wherein each surrogate key of the one or more surrogate keys comprises an identity of a source data table that comprises the source data record.

16. The computer-implemented method of claim 12, further comprising:

selecting, for each target data element, one source data element of the one or more source data elements as a preferred source data element; and

creating a preferred source path of the data lineage based on the selected one or more preferred source data elements.

17. The computer-implemented method of claim 12, wherein the data warehouse comprises one or more ordered data models, wherein each ordered data model comprises one or more data tables, and wherein one or more data tables of a subsequent data model are formed from at least one table of a previous data model using a data transformation.

18. A system for tracing a data lineage of a data warehouse, the system comprising:

a processor;

a memory configured to store one or more instructions;

a data lineage mapping module configured to map a target data element to one or more source data elements, wherein the target data element comprises a column of a target table definition, and wherein each source data element comprises a column of a source table definition;

an extension module configured to extend the target table definition to comprise one or more auxiliary columns to store one or more source surrogate keys; and

a data lineage storage module configured to store, for each source data element, a data lineage mapping system record within a data lineage mapping system table that represents the mapping of the target data element and a corresponding source data element together with a column identity of an auxiliary column in a target table that stores a source surrogate key;

wherein the data lineage storage module is further configured to store one or more source surrogate key values within one or more auxiliary columns of a target data record at the time the target data record is created or modified;

wherein the data lineage storage module is further configured to store, for each target data record, one or more shadow system records within a shadow system table that represents a mapping of a source data record and a corresponding target data record source surrogate key value;

wherein the data lineage comprises the one or more data lineage mapping system records, the one or more shadow system records, and the one or more source surrogate keys;

wherein the data lineage storage module is further configured to store a filter column identity within a first auxiliary column of the target data record, wherein the filter column identity identifies a filter column of the source data record; and

wherein the data lineage storage module is further configured to store a filter value within a second auxiliary column of the target data record, wherein the filter value is a filter value of the source data record.

19. The system of claim 18,

wherein the target data element is derived from the one or more source data elements using a data transformation;

wherein each source surrogate key comprises an identity of the source data record that comprises the corresponding source data element;

wherein the data lineage mapping system record comprises the corresponding source data element, the target data element, and the one or more auxiliary columns; and

wherein at least one shadow system record comprises a source surrogate key value that identifies the source data record, a target surrogate key value that identifies the corresponding target data record, and one or more auxiliary column values.

20. The system of claim 18, further comprising:

a data element selection module configured to select a data element from the one or more data elements that are mapped using the data lineage; and

a data element display module configured to display one or more data elements that are mapped to the selected data element within a user interface using the data lineage.

21. The system of claim 18, wherein each surrogate key of the one or more surrogate keys comprises an identity of a source data table that comprises the source data record.

22. The system of claim 18, the tracing further comprising:

a data element selection module configured to select, for each target data element, one source data element of the one or more source data elements as a preferred source data element; and

a preferred source path module configured to create a preferred source path of the data lineage based on the selected one or more preferred source data elements.

23. The system of claim 18, wherein the data warehouse comprises one or more ordered data models, wherein each ordered data model comprises one or more data tables, and wherein one or more data tables of a subsequent data model are formed from at least one table of a previous data model using a data transformation.

24. A non-transitory computer-readable medium having instructions stored thereon that, when executed by a processor, cause the processor to trace a data lineage of a data warehouse comprising one or more data tables, wherein each data table comprises one or more data records, the tracing comprising:

mapping a target data element to one or more source data elements, wherein the target data element comprises a column of a target data table definition, and wherein each source data element comprises a column of a source table definition;

storing, for each source data element, a data lineage mapping system record within a data lineage mapping system table that represents the mapping of the target data element and a corresponding source data element;

storing, for each target data record, one or more shadow system records within a shadow system table that represents a mapping of a source data record and a corresponding target data record;

wherein the data lineage comprises the one or more data lineage mapping system records, and the one or more shadow system records;

storing a filter column identity within a first auxiliary column of the target data record, wherein the filter column identity identifies a filter column of the source data record; and

storing a filter value within a second auxiliary column of the target data record, wherein the filter value is a filter value of the source data record.

25. A computer-implemented method for tracing a data lineage of a data warehouse, the computer-implemented method comprising:

mapping a target data element to one or more source data elements, wherein the target data element comprises a column of a target data table definition, and wherein each source data element comprises a column of a source table definition;

storing, for each source data element, a data lineage mapping system record within a data lineage mapping system table that represents the mapping of the target data element and a corresponding source data element;

storing, for each target data record, one or more shadow system records within a shadow system table that represents a mapping of a source data record and a corresponding target data record;

wherein the data lineage comprises the one or more data lineage mapping system records, and the one or more shadow system records;

storing a filter column identity within a first auxiliary column of the target data record, wherein the filter column identity identifies a filter column of the source data record; and

storing a filter value within a second auxiliary column of the target data record, wherein the filter value is a filter value of the source data record.

26. A system for tracing a data lineage of a data warehouse, the system comprising:

a processor;

a memory configured to store one or more instructions;

a data lineage mapping module configured to mapping a target data element to one or more source data elements, wherein the target data element comprises a column of a target data table definition, and wherein each source data element comprises a column of a source table definition; and

a data lineage storage module configured to store, for each target data record, one or more shadow system records within a shadow system table that represents a mapping of the source data record and a corresponding target data record;

wherein the data lineage storage module is further configured to store, for each target data record, one or more shadow system records within a shadow system table that represents a mapping of a source data record and a corresponding target data record;

wherein the data lineage comprises the one or more data lineage mapping system records, and the one or more shadow system records;

wherein the data lineage storage module is further configured to store a filter column identity within a first auxiliary column of the target data record, wherein the filter column identity identifies a filter column of the source data record; and

wherein the data lineage storage module is further configured to store a filter value within a second auxiliary column of the target data record, wherein the filter value is a filter value of the source data record.