TECHNIQUES TO EXTRACT AND FLATTEN HIERARCHIES

• US 20090319546A1
• Filed: 06/18/2008
• Published: 12/24/2009
• Est. Priority Date: 06/18/2008
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Shaik, Sadiq
• Granted Patent: US 9,659,073 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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

Abstract

Techniques for extracting hierarchical data stored in multiple records, flattening the hierarchical data, and storing the flattened data in a data warehouse. The data source may be an online transaction processing (OLTP) system that is designed to perform transaction processing and that stores hierarchy data in the form of multiple parent-child relationship records. The hierarchy data extracted from the data source is flattened and stored in a flattened form in a target system such as a data warehouse. A database function such as the SYS_CONNECT_BY_PATH may be used as part of the flattening process.

Claims

1. A method of facilitating storage of information for a hierarchy comprising a plurality of components, the method comprising:

generating a first set of strings based upon hierarchy data for the hierarchy stored in a plurality of records, each record in the plurality of records identifying a parent-child relationship between two components in the hierarchy, the first set of strings representing hierarchical paths in the hierarchy; and

creating one or more records for one or more strings from the first set of strings.

2. The method of claim 1 further comprising determining a second set of strings from the first set of strings by applying a filter to the first set of strings, and wherein the creating comprises creating a record for each record in the second set of strings.

3. The method of claim 2 wherein the second set of strings comprises one or more strings from the first set of strings representing hierarchical paths between a root component in the hierarchy and a leaf component in the hierarchy.

4. The method of claim 1 wherein, for each component in the hierarchy, the first set of strings comprises a string specifying a path from a root component of the hierarchy to the component.

5. The method of claim 1 wherein the creating comprises:

for each string in the one or more strings from the first set of strings,

parsing the string to extract one or more hierarchy components from the string, and

storing the one or more hierarchy components extracted from the string in a record created for the string.

6. The method of claim 5 wherein the storing comprises storing each hierarchy component extracted from the string in a separate field of the record created for the string.

7. The method of claim 1 further comprising storing the records created for the one or more strings from the first set of strings in a data warehouse.

8. The method of claim 7 wherein the generating and creating are performed at the data warehouse.

9. The method of claim 1 wherein the plurality of records is stored on a data source system and the generating and creating are performed on the data source system, the method further comprising storing the one or more records created for the one or more strings from the first set of strings on a target system.

10. The method of claim 1 wherein generating the first set of strings comprises using a SYS_CONNECT_BY_PATH function to generate the first set of strings.

11. The method of claim 1 wherein the plurality of records are stored in an online transaction processing system, the method further comprising storing the one or more records created for the one or more strings from the first set of strings in a data warehouse.

12. A computer-readable medium storing a plurality of instructions for controlling a processor to facilitate storage of information for a hierarchy comprising a plurality of components, the plurality of instructions comprising:

instructions that cause the processor to generate a first set of strings based upon hierarchy data for the hierarchy stored in a plurality of records, each record in the plurality of records identifying a parent-child relationship between two components in the hierarchy, the first set of strings representing hierarchical paths in the hierarchy; and

instructions that cause the processor to create one or more records for one or more strings from the first set of strings.

13. The computer-readable medium of claim 12 wherein:

the plurality of instructions comprises instructions that cause the processor to determine a second set of strings from the first set of strings by applying a filter to the first set of strings; and

the instructions that cause the processor to create the one or more records comprise instructions that cause the processor to create a record for each record in the second set of strings.

14. The computer-readable medium of claim 12 wherein the instructions that cause the processor to create the one or more records comprises:

for each string in the one or more strings from the first set of strings,

instructions that cause the processor to parse the string to extract one or more hierarchy components from the string, and

instructions that cause the processor to store the one or more hierarchy components extracted from the string in a record created for the string.

15. The computer-readable medium of claim 14 wherein the instructions that cause the processor to store the one or more hierarchy components comprises instructions that cause the processor to store each hierarchy component extracted from the string in a separate field of the record created for the string.

16. The computer-readable medium of claim 12 wherein the plurality of instructions comprises instructions that cause the processor to store the records created for the one or more strings from the first set of strings in a data warehouse.

17. The computer-readable medium of claim 12 wherein:

the plurality of records is stored on a data source system; and

the plurality of instructions comprises instructions that cause the processor to store the one or more records created for the one or more strings from the first set of strings on a target system.

18. The computer-readable medium of claim 12 wherein the instructions that cause the processor to generate the first set of strings comprises instructions that cause the processor to execute a SYS_CONNECT_BY_PATH function to generate the first set of strings.

19. The computer-readable medium of claim 12 wherein:

the plurality of records are stored in an online transaction processing system; and

the plurality of instructions comprises instructions that cause the processor to store the one or more records created for the one or more strings from the first set of strings in a data warehouse.

20. A system for facilitating storage of information for a hierarchy comprising a plurality of components, the system comprising:

a processor; and

a memory coupled to the processor;

wherein the processor is configured to

generate a first set of strings based upon hierarchy data for the hierarchy stored in a plurality of records, each record in the plurality of records identifying a parent-child relationship between two components in the hierarchy, the first set of strings representing hierarchical paths in the hierarchy, and

store one or more records in the memory for one or more strings from the first set of strings.

21. The system of claim 20 wherein the processor is configured to:

determine a second set of strings from the first set of strings by applying a filter to the first set of strings; and

store a record in the memory for each record in the second set of strings.

22. The system of claim 20 wherein the processor is configured to:

for each string in the one or more strings from the first set of strings,

parse the string to extract one or more hierarchy components from the string, and

store the one or more hierarchy components extracted from the string in a record created for the string.

23. The system of claim 22 wherein the processor is configured to store each hierarchy component extracted from the string in a separate field of the record created for the string.

24. The system of claim 20 wherein the processor is configured to store the records created for the one or more strings from the first set of strings in a data warehouse.

25. The system of claim 20 wherein:

the plurality of records is stored on a data source system; and

the processor is configured to store the one or more records created for the one or more strings from the first set of strings on a target system.

26. The system of claim 20 wherein the processor is configured to generate the first set of strings using a SYS_CONNECT_BY_PATH function.

27. The system of claim 20 wherein:

the plurality of records are stored in an online transaction processing system; and

the processor is configured to store the one or more records created for the one or more strings from the first set of strings in the memory of the data warehouse.