Semi-structured Data Warehouse Schema Generation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current methods for creating and loading data warehouses from semi-structured documents, such as XML files, are inefficient due to the lack of tools that integrate schema creation and data population, especially when the document structure is unknown, leading to performance drawbacks and requiring excessive user effort for reformatting.
Innovation Solution
A computer-implemented method that analyzes semi-structured files to generate a generic schema, creates database tables, and loads data into corresponding columns, defining dimensions for the data warehouse, which can handle files with differing structures, formats, and semantics, using a root model to merge sub-document models and populate tables with data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If current methods are used to create and load data warehouses from semi-structured documents, then the process can handle various file structures, but it requires excessive user effort for reformatting and manual schema creation
Solution Approach 1:
The system automatically analyzes semi-structured documents and generates schemas without requiring user intervention for reformatting. The schema creation process is self-service, where the system extracts structure information directly from the documents and populates the data warehouse automatically, eliminating the need for manual schema definition and data reformatting efforts
Solution Approach 2:
The system performs preliminary analysis of document structures before loading data into the warehouse. By pre-analyzing the semi-structured documents to understand their format and content organization, the system prepares the necessary schema and transformation rules in advance, enabling automatic and efficient data population without requiring users to manually prepare the data beforehand
2Productivity
If native data type approach with XQuery is used, then users can query XML data, but performance deteriorates when loading large files into the data warehouse
Solution Approach 1:
The system extracts data from semi-structured documents and transforms it into a standardized relational format suitable for data warehouse storage. By separating the data extraction and transformation processes from the native XML query approach, the system enables efficient loading of large files into the data warehouse while maintaining the ability to query the transformed data effectively
Solution Approach 2:
The system changes the data representation parameters by converting semi-structured document formats into standardized relational database schemas. This parameter transformation enables the data to be stored and queried more efficiently in the data warehouse environment, improving loading performance for large files while maintaining data integrity and query capabilities
3Device complexity
If manual reformatting is performed to handle different file structures, then data consistency is maintained, but the process complexity increases
Solution Approach 1:
The system provides a universal schema creation and data population process that can handle multiple semi-structured document formats and structures. By implementing a multi-functional analysis engine that automatically adapts to different file structures, the system maintains data consistency across various formats without requiring separate manual reformatting processes for each structure type, thereby reducing overall process complexity
Data Source
AI summary
A data warehouse is created using an input file that can contain sub-documents of different formats. A root document model including path names to all nodes among the sub-documents is generated, and a table is generated with columns being derived from the path names of the root model. The sub-documents are shredded to populate the table. Then, the dimensions of the data warehouse are defined by selecting respective columns. A routine such as a DDL may then be generated to populate the data warehouse with data.