Data Segmentation for Ad Hoc Querying in Warehouses

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing data warehouse systems are limited in their ability to handle ad hoc queries due to pre-defined data structures, which restrict flexibility and efficiency, and storing unstructured raw data is costly and inefficient.

Innovation Solution

The system divides received application data into an aggregate data store and one or more raw data stores, allowing a reporting tool to dynamically locate the correct data store, extract data, and generate reports, thereby enabling ad hoc querying.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If data is stored in a pre-defined structured manner in a data warehouse, then query efficiency is improved, but ad hoc querying capability deteriorates

Engineering Contradiction:

Improvequery efficiencyVSAvoidad hoc querying capability

Core Design Contradiction:

ProductivityVSAdaptability or versatility

Solution Approach 1:

The patent segments data storage into two distinct components: a data warehouse with pre-defined structured data for efficient querying, and a separate data lake storing unstructured raw data for ad hoc analysis. This segmentation allows each storage system to optimize for its specific purpose without compromising the other.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent adds a new dimension to the data storage architecture by introducing a data lake layer alongside the traditional data warehouse. This dimensional expansion enables the system to handle both structured and unstructured data simultaneously, providing versatility without sacrificing query efficiency.

Inventive Principle:

Principle #17Another dimension (Dimensionality change)

2Adaptability or versatility

If unstructured raw data is stored to enable ad hoc querying, then querying flexibility is improved, but storage cost and efficiency deteriorate

Engineering Contradiction:

Improvequerying flexibilityVSAvoidstorage space consumption

Core Design Contradiction:

Adaptability or versatilityVSQuantity of substance

Solution Approach 1:

The patent applies local quality by storing unstructured raw data only in the data lake portion of the architecture, while structured processed data resides in the data warehouse. This localized storage approach enables ad hoc querying flexibility where needed without unnecessarily consuming storage space for all data types.

Inventive Principle:

Principle #3Local quality

3Reliability

If all raw data is stored to satisfy any possible query, then query completeness is improved, but storage cost increases

Engineering Contradiction:

Improvequery completenessVSAvoidstorage space

Core Design Contradiction:

ReliabilityVSQuantity of substance

Solution Approach 1:

The patent segments the storage system into a data warehouse for structured data and a data lake for unstructured raw data, allowing the system to maintain query completeness for both structured and unstructured queries while optimizing storage space by not duplicating data across both systems.

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS7587388B2Separating uploads into aggregate and raw data storage

Publication Date: 2009.09.08 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

A system for sampling raw data from a reporting application and segmenting portions of the sampled data into at least one of a set of raw data stores. The system enables ad hoc querying to be done against a data warehouse using the set of raw data stores and in conjunction with an aggregate store. A reporting aspect of the system is responsible for locating the appropriate store when responding to a query. The system also segments data based on anticipated usage of the raw data and appropriately places them into a raw data store having an access speed that corresponds to the anticipated usage level of the data.