Autonomous DSS ETL and Semantic Layer Updates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing decision support systems (DSSs) face challenges in efficiently updating data sources, leading to costly, time-consuming, and risky enhancements, with rigid systems that often fail to gracefully handle changes in source schema, resulting in short production life and high maintenance needs.
Innovation Solution
Implementing an AI-driven system that automatically updates ETL jobs and semantic layers of a DSS by detecting changes in data sources, such as new additions or schema modifications, without canceling pending jobs, using confidence metrics and AI models like rule-based and case-based algorithms to determine necessary updates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If manual updating methods are used for DSS data sources, then system reliability is maintained through human review, but productivity is reduced due to time-consuming and costly enhancement processes
Solution Approach 1:
The DSS system automatically detects schema changes in data sources and updates its own ETL jobs and semantic layers without human intervention. The system monitors data source schemas, compares them against existing configurations, and autonomously generates and applies update scripts, enabling the system to serve itself rather than requiring manual updates by administrators.
Solution Approach 2:
The system performs preliminary detection of schema changes before executing updates. It continuously monitors data source schemas and prepares update configurations in advance, comparing detected changes against update rules before applying modifications. This preliminary action allows the system to be ready for updates without waiting for manual intervention while maintaining control over the update process.
2Adaptability or versatility
If rigid update procedures are implemented in DSS, then system stability is maintained, but adaptability to schema changes is reduced
Solution Approach 1:
The system dynamically adapts its update procedures based on the type of schema change detected. Different update rules are applied depending on whether the change involves new columns, data type modifications, or structural alterations. The system adjusts its behavior in real-time based on the specific change scenario, making the update process flexible rather than rigid while maintaining stability through controlled application of changes.
Solution Approach 2:
The system changes its update parameters and strategies based on the detected schema modifications. When specific types of changes are detected (e.g., nullable column additions, data type changes), the system applies corresponding parameter adjustments and update rules. This allows the system to adapt to various schema change scenarios while maintaining stable operation through standardized update mechanisms.
3Loss of time
If frequent manual updates are performed on DSS, then data freshness is improved, but maintenance costs increase
Solution Approach 1:
The system performs schema change detection and update operations continuously without interrupting normal DSS operations. The monitoring and update processes run in the background, allowing the system to maintain data freshness while minimizing downtime. Updates are applied during normal operation rather than requiring system shutdowns, ensuring continuous useful action.
Solution Approach 2:
The system introduces an intermediary update management layer that handles schema change detection, validation, and application. This intermediary layer automates the maintenance process, reducing the complexity of manual updates by providing a standardized interface between schema changes and system updates. The intermediary manages the complexity internally while presenting a simplified operation to users.
4Extent of automation
If automated update systems are implemented in DSS, then productivity is improved through reduced manual intervention, but system complexity increases
Solution Approach 1:
The automated update system is segmented into distinct functional modules: schema change detection, change analysis, update rule selection, update script generation, and update application. Each module handles a specific aspect of the update process, making the overall complex system manageable through modular design. This segmentation allows high-level automation while organizing complexity into discrete, manageable components.
Data Source
AI summary
A computer-implemented method is disclosed which detects a change in a set of data sources of a DSS, such as addition of a new data source, removal of an existing data source, or a schema change of an existing data source in the set. A first set of DSS updates for pending ETL jobs of an ETL engine of the DSS are automatically determined based on the change in the set of data sources. The method automatically updates, without canceling, the pending ETL jobs of the ETL engine based on the first set of DSS updates. A second set of DSS updates for a semantic layer of a BI engine of the DSS are automatically determined based on the change in the set of data sources and based on the first set of DSS updates. The semantic layer is automatically updated based on the second set of DSS updates.