Denormalized Table Introspection Algorithm for Cloud Data Modeling

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Users without technical knowledge in database administration face challenges in creating data models for cloud computing and Business Intelligence applications, as they lack the expertise to design and optimize database structures effectively.

Innovation Solution

A denormalized table introspection algorithm that analyzes existing database structures, identifies measure and dimension candidates, and forms candidate fact tables, allowing for pre-population of data models by processing only a limited number of rows to conserve bandwidth and resources in cloud environments.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Ease of operation

If users manually generate data models based on their own limited knowledge, then users can create data models without skilled database administrators, but the quality and accuracy of data models deteriorates

Engineering Contradiction:

Improveease of creating data modelsVSAvoiddata model quality

Core Design Contradiction:

Ease of operationVSManufacturing precision

Solution Approach 1:

The system enables users to automatically generate data models through self-service mechanisms. The introspection algorithm autonomously analyzes database structures, identifies measures and dimensions, and creates fact tables without requiring user expertise in database administration, thus resolving the contradiction between ease of operation and data model quality.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The introspection algorithm acts as an intermediary between the database structure and the user. It automatically analyzes table definitions, identifies keys and relationships, and generates data models, serving as a mediator that translates complex database structures into user-friendly data models without requiring user technical knowledge.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Manufacturing precision

If all rows of a source table are analyzed to accurately identify data model structures, then data model accuracy is improved, but bandwidth consumption and processing time deteriorates

Engineering Contradiction:

Improvedata model accuracyVSAvoidbandwidth consumption

Core Design Contradiction:

Manufacturing precisionVSLoss of energy

Solution Approach 1:

The system applies partial action by analyzing only a limited number of rows from the source table rather than all rows. The introspection algorithm processes a representative sample to identify data model structures, measures, and dimensions, achieving sufficient accuracy while significantly reducing bandwidth consumption and processing resources in cloud environments.

Inventive Principle:

Principle #16Partial or excessive action

3Productivity

If complex database normalization structures are used, then data storage efficiency is improved, but ease of creating and understanding data models deteriorates

Engineering Contradiction:

Improvedata storage efficiencyVSAvoiddata model complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The system inverts the traditional approach by automatically analyzing complex normalized database structures and transforming them into simplified data models. The introspection algorithm traverses the complex normalization hierarchy, identifies relationships, and generates flattened fact tables with dimensions, converting complexity into simplicity automatically.

Inventive Principle:

Principle #13The other way round (Inversion)

Data Source

PatentUS10437793B2BI cloud services data modeling denormalized table introspection algorithm

Publication Date: 2019.10.08 ORACLE INT CORP

AI summary

A computer implemented algorithm performs introspection of an uploaded denormalized table and identifies candidate fact and dimension tables. The cardinality values of columns in a candidate dimension table are analyzed to identify simple/complex primary key candidates. Unused columns are further analyzed for assignment to candidate fact and/or dimension tables.