Techniques to extract and flatten hierarchies

• US 9,659,073 B2
• Filed: 06/18/2008
• Issued: 05/23/2017
• 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
• Primary Examiner(s): Stevens, Robert
• Application Number:
  US12/141,409
• Publication Number:
  US 20090319546A1
• Time in Patent Office: 3,261 Days
• Field of Search: 707602, 707607, 707809, 707829, 707E17039
• 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 comprising:

loading, from an online transaction processing (OLTP) system, a plurality of records, each of the records of the plurality of records identifies a parent-child relationship between two components in a hierarchy;

after loading the plurality of records from the OLTP system, storing the plurality of records in a data warehouse that is separate from the OLTP system;

after storing the plurality of records in the data warehouse, generating, at the data warehouse, a first set of strings based upon hierarchy data for the hierarchy stored in the plurality of records at the data warehouse by applying a recursive function to the plurality of records, the first set of strings representing hierarchical paths in the hierarchy, each string in the first set of strings comprising one or more components in the hierarchy and wherein the one or more components in the string are arranged in an order indicative of a hierarchical path represented by the string;

filtering the first set of strings to create a second set of strings, at the data warehouse, so that strings of the first set of strings that trace a path from a root of the hierarchy to a leaf of the hierarchy are selected to create the second set of strings, wherein the second set of strings have fewer than all strings from the first set of strings in order to perform hierarchical flattening of the plurality of records;

creating, in the data warehouse, a particular record for each string in the second set of strings; and

querying the data warehouse for the particular record for a string in the second set of strings.

2. 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.

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

for each particular string in the second set of strings,

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

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

4. The method of claim 3 wherein the storing comprises storing each hierarchy component extracted from the particular string in a separate field of the record created for the particular string;

wherein every record created for a string in the second set of strings has a same quantity of fields as every other record created for other strings in the second set of strings; and

wherein at least two strings in the second set of strings represent different quantities of hierarchical components.

5. 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 particular record on a target system different from the data source system.

6. 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.

7. The method of claim 1, wherein generating comprises invoking a database function which, for every particular component in the hierarchy:

follows parent-child relationships in the hierarchy data to determine a particular path between a root component of the hierarchy and the particular component, including all intermediate components between the root component and the particular component in the particular path, and

generates a particular string that specifies all components in the particular path.

8. The method of claim 1, wherein each record in the plurality of records specifies both a parent component and a child component, and wherein said generating comprises:

for each particular record in the plurality of records, determining whether a field in the particular record indicates that the child component specified in the particular record is a leaf component that has no children; and

in response to determining that the child component specified in the particular record is a leaf component that has no children, selecting the child component as a starting point to determine a path from the child component to a root component of the hierarchy.

9. The method of claim 1, wherein the first set of strings includes at least one string that specifies a path that excludes all leaf components of the hierarchy;

and wherein filtering the first set of strings comprises filtering the first set of strings by adding, to the second set of strings, only strings from the first set of strings that specify a path from a root component of the hierarchy to a leaf component of the hierarchy.

10. The method according to claim 1, wherein the data warehouse comprises a data store configured for query and analysis.

11. The method according to claim 1, further comprising analyzing the particular record received in response to the query.

12. A non-transitory computer-readable medium storing a plurality of instructions executable by one or more processors, the plurality of instructions comprising:

instructions that cause the processor to load, from an online transaction processing (OLTP) system, a plurality of records, each of the records of the plurality of records identifies a parent-child relationship between two components in a hierarchy;

instructions which, after the loading of the plurality of records from the OLTP system, cause the processor to store the plurality of records in a data warehouse that is separate from the OLTP system;

instructions which, after the storing of the plurality of the data records in the data warehouse, cause the processor to generate, at the data warehouse, a first set of strings based upon hierarchy data for the hierarchy stored in the plurality of records at the data warehouse by applying a recursive function to the plurality of records, the first set of strings representing hierarchical paths in the hierarchy, each string in the first set of strings comprising one or more components in the hierarchy and wherein the one or more components in the string are arranged in an order indicative of a hierarchical path represented by the string;

instructions that cause the processor to filter the first set of strings to create a second set of strings, at the data warehouse, so that strings of the first set of strings that trace a path from a root of the hierarchy to a leaf of the hierarchy are selected to create the second set of strings, wherein the second set of strings have fewer than all strings from the first set of strings in order to perform hierarchical flattening of the plurality of records;

instructions that cause the processor to create, in the data warehouse, a particular record for each string in the second set of strings; and

querying the data warehouse for the particular record for a string in the second set of strings.

13. 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.

14. The computer-readable medium of claim 12, wherein the instructions that cause the processor to generate comprise instructions that cause the processor to invoke a database function which, for every particular component in the hierarchy:

follows parent-child relationships in the hierarchy data to determine a particular path between a root component of the hierarchy and the particular component, including all intermediate components between the root component and the particular component in the particular path, and

generates a particular string that specifies all components in the particular path.

16. The computer-readable medium of claim 12, wherein each record in the plurality of records specifies both a parent component and a child component, and wherein said generating comprises:

for each particular record in the plurality of records, determining whether a field in the particular record indicates that the child component specified in the particular record is a leaf component that has no children; and

in response to determining that the child component specified in the particular record is a leaf component that has no children, selecting the child component as a starting point to determine a path from the child component to a root component of the hierarchy.

17. The computer-readable medium of claim 12, wherein the first set of strings includes at least one string that specifies a path that excludes all leaf components of the hierarchy;

and wherein the instructions that cause the processor to filter the first set of strings comprise instructions that cause the processor to filter the first set of strings by adding, to the second set of strings, only strings from the first set of strings that specify a path from a root component of the hierarchy to a leaf component of the hierarchy.

15. A system comprising:

a processor; and

a memory coupled to the processor;

wherein the processor is configured to load, from an online transaction processing (OLTP) system, a plurality of records, each of the records of the plurality of records identifies a parent-child relationship between two components in a hierarchy;

wherein the processor is configured, after the loading of the plurality of records from the OLTP system, to store the plurality of records in a data warehouse that is separate from the OLTP system;

wherein the processor is configured, after the storing of the plurality of the data records in the data warehouse, to generate, at the data warehouse, a first set of strings based upon hierarchy data for the hierarchy stored in the plurality of records at the data warehouse by applying a recursive function to the plurality of records, the first set of strings representing hierarchical paths in the hierarchy, each string in the first set of strings comprising one or more components in the hierarchy and wherein the one or more components in the string are arranged in an order indicative of a hierarchical path represented by the string;

wherein the processor is configured to filter the first set of strings to create a second set of strings, at the data warehouse, so that strings of the first set of strings that trace a path from a root of the hierarchy to a leaf of the hierarchy are selected to create the second set of strings, wherein the second set of strings have fewer than all strings from the first set of strings in order to perform hierarchical flattening of the plurality of records;

wherein the processor is configured to create, in the data warehouse, a particular record for each string in the second set of strings; and

querying the data warehouse for the particular record for a string in the second set of strings.

18. The system of claim 15, wherein the processor is configured to invoke a database function which, for every particular component in the hierarchy:

follows parent-child relationships in the hierarchy data to determine a particular path between a root component of the hierarchy and the particular component, including all intermediate components between the root component and the particular component in the particular path, and

generates a particular string that specifies all components in the particular path.

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

20. The system of claim 15, wherein each record in the plurality of records specifies both a parent component and a child component, and wherein:

for each particular record in the plurality of records, said processor is configured to determine whether a field in the particular record indicates that the child component specified in the particular record is a leaf component that has no children; and

said processor is configured, in response to determining that the child component specified in the particular record is a leaf component that has no children, to select the child component as a starting point to determine a path from the child component to a root component of the hierarchy.

21. The system of claim 15, wherein the first set of strings includes at least one string that specifies a path that excludes all leaf components of the hierarchy;

and wherein processor is configured to filter the first set of strings by adding, to the second set of strings, only strings from the first set of strings that specify a path from a root component of the hierarchy to a leaf component of the hierarchy.