Tabular Data Normalization for Mixed Column and Pivot Formats
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing ad-hoc analytic tools face inefficiencies when handling mixed format tabular data, as they often incorrectly interpret data, leading to large and unusable models due to assumptions about data layout, such as columnar or pivot table formats.
Innovation Solution
A method is introduced to identify and group columns with similar metric data, determining the type of metric data and using semantic analysis to generate tables in a consistent columnar format, allowing for dynamic database queries by separating grouped and ungrouped columns, and communicating the formatted data to an OLAP model library.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If tabular data in mixed format (columnar and pivot table) is imported into an ad-hoc tool with predetermined format assumptions, then the tool can process the data using standard modeling approaches, but the data may be incorrectly interpreted leading to large and unusable models
Solution Approach 1:
The system dynamically adapts its data interpretation approach based on detected format characteristics. Instead of using a fixed columnar format assumption, the tool automatically identifies whether data represents columnar or pivot table format through analysis of data patterns and relationships, then applies the appropriate modeling strategy. This dynamic adaptation resolves the contradiction by maintaining processing efficiency while improving interpretation accuracy through format-agnostic handling.
Solution Approach 2:
The system changes the interpretation parameters based on the detected data format. When pivot table format is detected, the modeling parameters are adjusted to handle hierarchical relationships differently than standard columnar data. This parameter transformation allows the same ad-hoc tool to correctly process both formats without requiring separate processing pipelines, thus maintaining productivity while improving reliability.
2Ease of operation
If the tool creates unique hierarchies for each column in mixed format data, then it follows the columnar format assumption, but this creates a very large model with limited usability
Solution Approach 1:
The system segments the data processing approach by identifying distinct format regions within the tabular data. When pivot table format is detected, it separates the hierarchical structure identification from standard column processing. This segmentation allows the tool to create appropriate hierarchies only where needed rather than forcing unique hierarchies on all columns, reducing model complexity while maintaining operational consistency through format-aware processing.
Solution Approach 2:
Instead of forcing all data into the columnar format paradigm (which creates excessive hierarchies), the system inverts the approach by detecting pivot table structures and applying inverted logic that recognizes parent-child relationships inherent in pivot data. This inversion prevents the creation of redundant unique hierarchies for each column, reducing model size while maintaining ease of operation through appropriate hierarchical grouping.
3Reliability
If manual data modeling is performed to correctly interpret mixed format data, then data interpretation accuracy improves, but time consumption and cost increase
Solution Approach 1:
The system implements self-service through automatic format detection and adaptive modeling. The ad-hoc tool autonomously analyzes the tabular data structure, identifies whether columnar or pivot table format is present, and applies the appropriate interpretation logic without requiring manual intervention. This self-service capability achieves manual-level interpretation accuracy while eliminating the time and cost overhead of manual modeling through automated format recognition and adaptive processing.
Solution Approach 2:
The system uses feedback mechanisms where the detected data format information feeds back into the modeling process to adjust interpretation strategies. The automatic detection of pivot table versus columnar format provides feedback that triggers appropriate handling routines, ensuring accurate interpretation without manual input. This feedback-driven automation achieves reliability comparable to manual modeling while drastically reducing time consumption through elimination of manual analysis steps.
Data Source
AI summary
A system for modeling tabular data containing column and pivot table formats. Tabular data containing categorical and/or metric data is received and the metric data is determined. A group of identified columns are grouped comprising one or more adjacent columns containing similar metric data. Adjacent columns with unique metric types are not grouped. A number of columns (n) and rows (m) are identified. A table is generated comprising two sub-tables. A first sub-table is populated by metric data of ungrouped columns, repeated n times, containing ungrouped column category labels. A second sub-table with two columns, populated with grouped column category labels, repeated m times, and metric data from the grouped columns respectively. Category labels of the second table are determined via semantic analysis. The generated table, containing (n×m)+1 rows, is communicated to a model library.