Browse Prior Art Database

On-the-fly visualization of value distributions for SQL queries

IP.com Disclosure Number: IPCOM000216059D
Publication Date: 2012-Mar-21
Document File: 7 page(s) / 71K

Publishing Venue

The IP.com Prior Art Database

Abstract

This document presents a way to visualize the complete query result as well as partial results in real-time while the user types the query, without the need to send SQL queries to the database explicitly. Real-time behavior is achieved by pre-fetching as much data as needed.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 25% of the total text.

Page 01 of 7

On-the-fly visualization of value distributions for SQL queries

Overview

Users writing SQL queries often face the situation that they have a good idea which criteria a query result should satisfy, but it is not easy to write a query that meets these criteria. Such criteria could be value distributions or the size of the final result set. The user's challenge is to find a query that meets not just one but all the user's criteria. Usually many iterations (rewrite and execute query, review the results) are needed until the query is created finally and returns the desired data.

This article presents a method to visualize the complete query result as well as partial results in real-time as the user types the query, without the need to send SQL queries to the database explicitly.

The system constantly analyzes the query while the user is manipulating it, detects relevant expressions and sub-expressions in the query, marks them in the query, automatically obtains the corresponding data to evaluate these expressions, and visualizes the results. The whole process is performed while the user assembles the query, the user does not need to trigger any of these steps explicitly.

Application Area

Data Warehousing, where one of the user's tasks is writing SQL queries to filter their data. Generally, all areas where SQL queries have to be written.

Details

When the user assembles a query, the following tasks have to be done:
Specify which are the database objects that should be joined, and how should the


1.

join condition look like. This information is typically provided in the FROM clause. Specify which rows should be contained in the final result. This information is typically provided in the WHERE clause.

Specify which columns should be contained in the final result, and should new

(calculated) columns be created. This information is typically provided in the SELECT list.

The core application domain of the method is to support the steps 2 and 3 above.

We describe how the method works in a typical environment for SQL query creation, a special database tool. Other environments, like a plain SQL editor, are possible. We assume that the database objects to join and the join condition are available (e.g. specified by the user before) when our method is used. So we start with an initial query that contains the selected database objects as well as the join condition. The flowchart (Figure 1) contains the initial steps to collect database objects and optionally a join condition from the user as well as the core method steps.

Initially, the user selects the data sets (like tables or views) that should appear in the query (in the FROM clause). If more than one data set is selected, we need to create a join, and to this end the user also has to enter a join condition. Using this

1


2.


3.


Page 02 of 7

information, the system creates and presents the initial query. For example, in the simplest case, after selecting the table CUSTOMERS, it looks like this: "S...