Extensible Data Warehouse Attributes Via Dynamic ETL

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

The challenge of adding custom fields in cloud-based data warehouses without IT involvement, as modifying the data warehouse model, ETL framework, and reporting workflows is expensive and impractical.

Innovation Solution

A method that dynamically maps metadata from the source system to the target data warehouse, including labels, languages, and lists of supported values for custom fields, using ETL tools to generate dynamic code for seamless integration of new attributes.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If custom fields are added to cloud-based data warehouses through traditional IT modification processes, then the data warehouse can support custom reporting metrics, but the cost and complexity of modifying the data warehouse model, ETL framework, and reporting workflows increases significantly

Engineering Contradiction:

Improveability to add custom fieldsVSAvoidcomplexity of modifying data warehouse model, ETL framework, and reporting workflows

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The system enables end-users to add custom fields and metadata directly through the cloud interface without requiring IT department involvement. Users can define their own attributes, data types, and reporting metrics, and the system automatically integrates these into the data warehouse model, ETL processes, and reporting workflows through self-service mechanisms.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The data warehouse model is designed to be dynamic and extensible, allowing attributes to be added, modified, or removed without redeploying the entire system. The ETL framework and reporting workflows automatically adapt to changes in the data model through dynamic code generation and configuration, eliminating the need for static, hard-coded structures.

Inventive Principle:

Principle #15Dynamics

2Adaptability or versatility

If custom fields are added to cloud-based data warehouses through traditional redeployment processes, then new attributes can be integrated into the system, but the time and cost of redeploying the data model and workflows increases

Engineering Contradiction:

Improveability to add new attributesVSAvoidtime required to redeploy data model and workflows

Core Design Contradiction:

Adaptability or versatilityVSLoss of time

Solution Approach 1:

The system pre-configures the data warehouse model with extensible attribute structures and metadata frameworks that anticipate future customization needs. ETL processes and reporting workflows are designed with built-in flexibility and adaptation mechanisms that automatically activate when new attributes are added, eliminating the need for time-consuming redeployment cycles.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

When users add new attributes, the system automatically performs the integration tasks that would traditionally require manual redeployment, including updating the data model schema, configuring ETL mappings, and adjusting reporting workflows. This automated self-service process reduces deployment time from days or weeks to minutes or seconds.

Inventive Principle:

Principle #25Self-service

3Quantity of substance

If traditional ETL data mapping is used for custom fields, then data can be transferred from source to target, but metadata such as labels, languages, and supported values cannot be dynamically mapped

Engineering Contradiction:

Improvedata transfer capabilityVSAvoidability to dynamically map metadata

Core Design Contradiction:

Quantity of substanceVSAdaptability or versatility

Solution Approach 1:

The ETL framework is enhanced to handle multiple types of data simultaneously - both the actual data values and their associated metadata (labels, languages, supported values, data types). The same ETL infrastructure that transfers data from source to target systems also automatically maps and transforms metadata, providing a universal solution that handles both data and descriptive information through a single integrated process.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

A metadata layer is introduced as an intermediary between the source data and target data warehouse. This metadata layer captures descriptive information such as labels, languages, and supported values, and the ETL process automatically maps these metadata attributes alongside the data values. This intermediary layer enables dynamic metadata mapping without requiring separate manual configuration processes.

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS12443616B2Extensible attributes for data warehouses

Publication Date: 2025.10.14 ORACLE INT CORP

AI summary

Systems and methods for implementing extensible attributes in ETL are disclosed. In some examples, attributes configured at a source file may be extracted from the source file. The extracted attributes can be mapped to a target column of a data warehouse table, and then a dynamic ETL script may be generated. The dynamic script may be executed to move data associated with the attributes to an appropriate new column of the data warehouse.