Flattening Hierarchical Data via Database Functions

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

ETL processes face bottlenecks and resource-intensive processing when flattening hierarchy data from parent-child relationships in OLTP systems for storage in data warehouses, especially due to the inefficiencies of traditional row-by-row processing methods.

Innovation Solution

The use of database functions like SYS_CONNECT_BY_PATH to generate strings representing hierarchical paths, followed by filtering and parsing to create records that store flattened hierarchy data, allowing for efficient extraction and storage in a data warehouse, potentially eliminating the need for an ETL tier.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Ease of manufacture

If traditional row-by-row processing is used to flatten hierarchy data, then ease of implementation is improved, but processing time and resource requirements increase significantly

Engineering Contradiction:

Improveease of implementationVSAvoidprocessing time

Core Design Contradiction:

Ease of manufactureVSProductivity

Solution Approach 1:

The patent replaces the mechanical row-by-row processing approach with a database function-based system. Specifically, it uses the SYS_CONNECT_BY_PATH function to generate hierarchical path strings directly from parent-child relationship records, eliminating the need for iterative ETL tier processing and significantly reducing processing time

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Solution Approach 2:

The database system itself performs the hierarchy flattening operation through the SYS_CONNECT_BY_PATH function, which automatically traverses the parent-child relationships and generates the required path strings without external ETL intervention, making the system self-sufficient for this transformation task

Inventive Principle:

Principle #25Self-service

2Ease of manufacture

If traditional row-by-row processing is used to flatten hierarchy data, then ease of implementation is improved, but resource requirements increase substantially

Engineering Contradiction:

Improveease of implementationVSAvoidprocessing resources

Core Design Contradiction:

Ease of manufactureVSUse of energy by moving object

Solution Approach 1:

The patent substitutes the resource-intensive ETL tier processing mechanism with a native database function (SYS_CONNECT_BY_PATH) that leverages the database's existing traversal capabilities, thereby reducing the computational resources required for hierarchy flattening operations

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

3Ease of operation

If row-by-row processing approach is used, then sequential record access is enabled, but the approach cannot be supported in ELT architecture where transformation is performed in data warehouse

Engineering Contradiction:

Improvesequential record accessVSAvoidarchitecture compatibility

Core Design Contradiction:

Ease of operationVSAdaptability or versatility

Solution Approach 1:

The database system performs the hierarchy flattening transformation natively through the SYS_CONNECT_BY_PATH function, allowing the transformation to occur within the data warehouse environment itself (ELT architecture) rather than requiring external ETL tier processing, thus enabling architectural flexibility

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The SYS_CONNECT_BY_PATH function serves multiple purposes: it traverses parent-child relationships, generates hierarchical path strings, and prepares data for both ETL and ELT architectures, making the solution universally applicable across different architectural paradigms

Inventive Principle:

Principle #6Universality (Multi-functionality)

Data Source

PatentUS9659073B2Techniques to extract and flatten hierarchies

Publication Date: 2017.05.23 ORACLE INT CORP

AI summary

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.