Policy-Based Trigger Execution in Data Warehouses

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing data warehouse update and analysis methods are inefficient, as they often result in costly and inconsistent execution of trigger queries due to frequent data updates, whether periodic, on-demand, or real-time, leading to resource wastage and timeliness issues.

Innovation Solution

Implementing a policy-based method that converts trigger conditions into differential queries, allowing for timely and efficient execution by determining when and where to send queries to data sources, using a mediator to aggregate results from both local and remote data sources, thus optimizing query frequency and resource usage.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of time

If data updates are sent to the DW periodically with a short period/interval, then the trigger query execution frequency increases and timeliness improves, but computing resource cost increases

Engineering Contradiction:

Improvetrigger evaluation timelinessVSAvoidcomputing resource cost

Core Design Contradiction:

Loss of timeVSUse of energy by moving object

Solution Approach 1:

The system performs preliminary actions by maintaining materialized views and pre-computed aggregates at the data warehouse, allowing trigger conditions to be evaluated against these pre-prepared structures rather than querying raw data sources directly. This enables faster trigger evaluation without requiring frequent updates from source systems.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent introduces a mediator layer (data warehouse with materialized views) between the remote data sources and the trigger evaluation mechanism. This intermediary stores pre-aggregated data that can be quickly queried for trigger condition evaluation, reducing the need for frequent direct queries to source systems while maintaining evaluation timeliness.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Reliability

If data updates are sent to the DW in real time, then trigger query execution frequency increases and trigger detection accuracy improves, but computing resource cost and system load increase significantly

Engineering Contradiction:

Improvetrigger condition detection accuracyVSAvoidsystem resource consumption

Core Design Contradiction:

ReliabilityVSLoss of energy

Solution Approach 1:

The system applies partial action by selectively updating only the portions of materialized views that are affected by source data changes, rather than performing complete re-synchronization. Trigger conditions are evaluated against these partial updates, providing sufficient accuracy without the overhead of full real-time updates.

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The patent implements periodic action through scheduled materialized view refreshes and trigger evaluations at optimized intervals, rather than continuous real-time processing. This periodic approach maintains adequate trigger detection accuracy while significantly reducing system resource consumption compared to continuous real-time updates.

Inventive Principle:

Principle #19Periodic action

3Use of energy by moving object

If the update period has a long interval, then computing resource cost decreases, but trigger evaluation timeliness and query result freshness deteriorate

Engineering Contradiction:

Improvecomputing resource costVSAvoidquery result freshness

Core Design Contradiction:

Use of energy by moving objectVSLoss of time

Solution Approach 1:

The system uses a nested structure where materialized views are nested within the data warehouse, which itself nests around the remote data sources. This nested architecture allows trigger evaluation to operate at the data warehouse level using pre-aggregated data, reducing the need for frequent external queries while maintaining result freshness through the intermediate aggregation layer.

Inventive Principle:

Principle #7Nested doll (Nesting)

Solution Approach 2:

Data is preliminarily aggregated and stored in materialized views before trigger evaluation is needed. This preliminary action of pre-computing aggregates allows the system to use longer update intervals while still providing timely trigger evaluation, as the aggregated data is already prepared and available for quick comparison against trigger conditions.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS8135666B2Systems and methods for policy based execution of time critical data warehouse triggers

Publication Date: 2012.03.13 SERVICENOW INC

AI summary

Embodiments of the invention broadly contemplate systems, methods, apparatuses and program products that employ a policy based method of timely execution of triggers over a data warehouse. Embodiments of the invention utilize methods to convert a trigger condition into differential queries over data sources, to decide when to send queries to data sources, and determine to which data sources to send queries.