Table of Contents


1 INTRODUCTION
            1.1
WELCOME TO SUPERQUERY
            1.2
FEATURE SUMMARY
            1.3 SYSTEM REQUIREMENTS
            1.4 INSTALLATION
            1.5 SERVICE AND SUPPORT

2 GETTING STARTED
            2.1 STARTING SUPERQUERY
            2.2 READING YOUR DATA
            2.3 BROWSING YOUR DATA
            2.4 UPDATING YOUR DATA
            2.5 EXPORTING YOUR DATA
 

3 ORGANIZING YOUR DATA
            3.1 TABS
            3.2 VIEWS
            3.3 PANES
                3.3.1 DATA TABLE PANE
                3.3.2 DATA PAGE PANE
                3.3.3 REPS TABLE PANE
                3.3.4 REPS GRAPH PANE
                3.3.5 DATA GRAPH PANE
                3.3.6 TOTAL PAGE PANE
                3.3.7 TOTAL TABLE PANE
                3.3.8 NOTES PANE
                3.3.9 DATA CELL PANE
                3.3.10 FACTS PANE
            3.4 QUICK FILTERS
            3.5 INDEXING YOUR DATA

 

4 WORKING WITH MULTIPLE TABLES
            4.1 LINKING TABLES
            4.2 BROWSING MULTIPLE DATA VIEWS
 

5 ANALYZING YOUR DATA
            5.1 STATISTICAL ANALYSIS
            5.2 MAPPING NUMERICAL DATA INTO RANGES
            5.3 MATHEMATICAL AND LOGICAL CALCULATIONS
            5.4 COMPREHENSIVE QUERIES USING THE FILTER COLUMN
            5.5 CLASSIFYING DATA USING THE CLASSIFICATION COLUMN
            5.6 ANALYZING TEXTUAL DATA USING THE KEYWORD COLUMN
 

6 SUMMARIZING YOUR DATA
            6.1 WHAT IS THE SUMMARY TABLE?
            6.2 SUMMARY OPERATORS
            6.3 CREATING A SUMMARY TABLE
 

7 DATA MINING -- DISCOVERING FACTS AND EXCEPTIONS
            7.1 PREPARING THE DATA FOR MINING
            7.2 FACT DISCOVERY ENGINE FOR THE DISCOVERY EDITION
                7.2.1 RUNNING THE FACT DISCOVERY ENGINE
                7.2.2 TUNING THE FACT ENGINE
                    7.2.2.1 FINE-TUNE THE FACT-FINDING PERFORMANCE
                    7.2.2.2 IGNORE DEPENDENT COLUMNS
                    7.2.2.3 FILTER IMPLIED FACTS

1 Introduction

 

1.1 Welcome to SuperQuery

 

SuperQuery is a data analysis and mining tool that runs under the Windows. Using rule induction technology, SuperQuery searches data tables and reports all interesting patterns and exceptions.  The Fact Discovery Engine is easily tuned to meet various analysis needs. 

 

SuperQuery also assists in preparing data for analysis by providing a number of facilities for partitioning, classifying and processing data columns.  In addition, SuperQuery helps to explore and analyze data by automatically displaying graphs and calculating statistics. It contains a number of Wizards that help read, update, and analyze data effortlessly.  SuperQuery can access and query a number of databases, spreadsheets, text files directly, and through ODBC drivers . 

 

1.  Asking Questions -- Visual Query

SuperQuery helps you read the entire database or any part of it. You can specify criteria, and/or scope. For example: you can just read those records that contain data for Year = 96. You can also read a random sample of your data. Once the data is read in SuperQuery, you can easily create quick filters, indexes, and choose and order columns into views.

 

A view in SuperQuery is a window of your data. A view displays a group of columns that you select. It shows all rows that satisfy any filter you set, in the order you specify.

 

Furthermore, a view consists of a number of panes. Each pane displays the data in a different format. Table Pane displays data in a table, Page Pane displays one row in a page form, Reps Pane that displays the frequency (i.e. how common each value).

 

For example: If you are looking at a column that has TRUE and FALSE values, the Reps pane will show you how many TRUE’s and FALSE’s are in that column. There are also Graphics Panes that display Values or Reps of the current column in a bar graph. SuperQuery has also Total Panes that display any set of calculations like total, maximum, average, standard deviation, etc.

 

You do not need to do anything, SuperQuery will do it automatically. Each view is pre-configured with a set of panes suitable to its contents. You just look at the view and resize the panes to fit your needs. If you want, you can change the type of any pane and change its configuration. SuperQuery is very flexible and easy to customize.

 

As you scroll through or set filters on your table, all associated views recalculate and redisplay statistics and graphs, automatically. For example: If you set a filter on one category of products, all statistics and graphs are recalculated for this category. If you scroll to the "order amount column", you will see Min, Max, Total, and Average of orders for this product category. In addition graphs are updated to show the top orders and most common order amounts.

 

2.  Analyzing Data

SuperQuery also helps you create Calculated Columns. These are Virtual Columns that are calculated from other columns. You can’t make a syntax error, SuperQuery calculations are entered using simple dialogs. Furthermore, SuperQuery provides additional types of virtual columns. These are analysis-oriented short cuts to an otherwise complicated Calculated column. There are Range, Filter, Classification and Keyword columns.

 

The Range column is an easy way to divide a numerical column into ranges. For example you can divide sales into 3 levels; High, Medium, and Low. This is useful in setting filters on these ranges and looking at Graphs, and/or Total panes. This is also very helpful in discovering patterns and facts on numerical columns. You can create this example with just 3 mouse clicks.

 

The Filter column is a virtual column that contains TRUE or FALSE values depending on its criteria. You can create complex criteria using one or more Filter columns. This makes your criteria more modular and easier to understand.

 

The Classification column is a virtual column that simplifies categorizing your data. This helps you study each individual class and identify patterns among those classes. Suppose you want to study your new orders starting from 1996. You can classify your orders into 4 categories; Large, Medium, Small and Old. You can create a Classification column that has 4 values and 4 criteria or conditions as follows:

Value

Criteria

Large

Order_Date >= 01/01/96 and Order_Value > 5000

Medium

Order_Date >= 01/01/96 and Order_Value > 1000

Small

Order_Date >= 01/01/96

Old

Any other order

Classification Column Criteria

This gives you the ability to see graphs and statistics for each class of your orders and run the fact discovery engine.

 

The Keyword column is a shortcut for extracting keywords from a "description" column. For example, you can create a Keyword column on "Services Description" to identify your entire client rows with the word "hardware" in their service description. You can then study those clients and identify any patterns they share.

 

All Data, Graphs and Total panes are automatically prepared for any new virtual column.

 

Another Important analysis facility is the Summary Table. Suppose you would like to see the number of orders of Large, Medium, Small and Old orders. Suppose also that you would like to see Total, Average, and other statistics for each order class. You can create a summary table that groups your orders by the Order_Class column. The summary table may include: Count of orders, Total Amount, Average Amount, etc. This summary table will look like this:

 

Order Class

No of Orders

Total Amount

Average Amount 

Large

1000

100,000

100

Medium

4000

200,000

50

Small

8000

200,000

25

Old

9000

450,000

50

Summary Table Example

 

You can group your data on more than one column. For example: on Year, then State, then Product Type. On the other hand, for each cell in the summary table, you can see all the details.

 

Double click on any cell and a view that shows the details is created. This kind of analysis is also known as Multi-Dimensional Analysis and Drill Down.

 

Summary tables are normal tables that you can add virtual columns to, summarize, and search for facts. Also their views can contain all data, graphics and total panes.

 

 

Viewing related tables.

SuperQuery helps you analyze any number of tables quickly and easily. After defining primary and foreign keys in your tables, you can tell SuperQuery to synchronize your data views. Suppose you have two views: Customers, and Orders. If you scroll in Orders, the Customers view will automatically scroll to show you the customer details. On the other hand, if you scroll in the Customer view, SuperQuery will automatically set a filter on the Orders view to show you all the orders of the current customer. So as you look at each customer record, you also see all their orders. Furthermore, if you have total and graph panes open, you will also see all the statistics and graphs on those orders AUTOMATICALLY. The more screen real estate you give SuperQuery, the more information you have at your fingertips. It is that simple!

 

3.  Discovering Facts

The ultimate form of intelligence is Inference. The simplest form of inference is to:

 

·         observe patterns and similarities among objects or concepts then,

·         create general theories and exceptions.

 

By observing that many individual birds can fly, you can create a theory or a Fact that: All birds can fly. This allows you to create a class of living beings called Birds and associate the property Fly to the class rather than to each individual bird. We are probably programmed to do this, just to save memory in our brain! However, once you have a class, you can search for and expect more similarities among its members. Notice that most theories have exceptions -- there are some birds that can not fly.

 

What are SuperQuery Facts and Exceptions?

SuperQuery scans your table and searches for patterns. For example, it could find that all you Large orders have the same month -- December. SuperQuery considers this a "Fact" and reports it. A Fact may have exceptions, so SuperQuery will still report that fact even if only 80% of your Large orders happens in December. SuperQuery differentiates between "All" and "Most" type facts. Furthermore, SuperQuery reports the exact percentage (e.g. 80%). Facts can be seen as rules that govern your data, or patterns that exist among your objects.

 

 

Most of the large orders happen in December.

Fact

Most Orders where Amount = Large have Month = December. 80% of orders.

Rule

If Order Amount = Large then Month = December. 80% Confidence.

 

SuperQuery also reports any exceptions to those patterns. For example, if only 5% of your orders are paid in cash, then SuperQuery reports this as an exception. Furthermore, exceptions may be errors in your database. This means that you can use SuperQuery to improve the quality of data.

 

How to hunt down those FACTS using SuperQuery?

There are more opportunities for finding patterns in a table if it contains columns that have repeated values. For example columns like Year, Month, State, Season, Class, Color, etc. If your data is mostly numeric and has no repeated values, then you have to classify it into Levels, Ranges, or Categories. To simplify this process, SuperQuery has the "Range Virtual Column" that you can create with minimum effort. Here are the steps to help hunt down those interesting facts:

 

·         Create Range columns on your numerical columns.

·         Create Classification columns to categorize your data entities into interesting classes ( e.g. Clients, Orders, Products, etc. ).

·         Create Filter columns

·         Create Summaries on useful categories.

·         Fire the Fact engine on the data tables as well as the summary tables.

 

SuperQuery saves you a lot of time and gives you a head start by doing its systematic search for facts. You start your search for patterns from a Factbase rather than from a database. After all, that is what computers are good for.

 

4.  Organizing Views

While conducting your analysis, you create many interesting views on your data and you need to be able to organize these views and find them efficiently. You also need to present your findings in an easy to use medium.

 

SuperQuery allows you to create tabs and folders with Titles and different Color Codes. Furthermore, you can create tabs within tabs.  For example, you can create a tab for each year and in each year you can have tabs for each product type.

 

 

1.2 Feature Summary

 

 

Visual Query

  • Visual Query Creation -- no programming or SQL required.

  •  Quick Filters.

  • Easy Indexing of your data tables.

  • Automatically create graphs that shows common values, exceptions, high, and low values

  • Automatically create Total, Min, Max, Average, etc. for values in the current column.

  • Automatically synchronize related views.

  • Open Access, xBASE, Excel, Paradox, Text files directly .

  • Open most popular databases using ODBC drivers .

 

 

 

Data Analysis

 Virtual Columns:

  • Range. Automatically Divide a numerical column into a number of ranges (High, Medium, Low).

  • Filter. Visually create comprehensive criteria.

  • Keyword. Easy short cut to extract keywords from description-type columns

  • Classification. Easy short cut to map values in one or more column(s) .

  • Formula. Visually create calculations.

·Create Summaries using more than 20 functions.

  • Summary tables are normal tables that can be re-analyzed and re-summarized.

  •   Drill Down. Just double click on any summary cell to create a detail view.

Fact Discovery

  •  on button click to start the Fact Discovery Engine.

  • Find Facts (rules or patterns) and Exceptions or Errors.

  • Many options to control the search process.

  • Report facts as a Table so that you can re-analyze the Factbase itself 

  • Report facts in an easy to read " If...then" rules or "All / Most" statements.

  • Drill Down. Just double click on any Fact cell to create a detail view . 

Organization

 

  •  Organize your views into tabs.

  • Create tabs within tabs .

 

1.3 System Requirements

SuperQuery version1.20 32-Bit

·         Windows 95, or Windows NT.

·         486 Processor or Better.

·         8 MB of memory or more.

·         8 MB of available Hard Disk space.

 

1.4  Installation

Check your hard disk space to ensure you have enough space to install SuperQuery depending on the version you are installing.

 

  1. Insert the first disk labeled SuperQuery Setup Disk 1 of -- into your Floppy disk drive.

  2. From the program Manager File menu, choose Run (If you' re running Windows 95, from the Start menu, choose Run.)

  3. For command Line, type a:\Setup, (in which a is your Floppy disk drive)

  4. Click OK to start Setup, then follow the instructions on screen.

 

After installation is complete, restart Windows. SuperQuery creates a group in the Program Manager and a SuperQuery directory on your hard disk.

 

SuperQuery ships with a 7-days standard trial period. In order to register or to upgrade your SuperQuery copy, please follow the following steps:

 

Make sure you have obtained both the User ID and Registration key from Azmy Thinkware Inc.

  1. Run SuperQuery

  2. In the Getting Started Dialog, press the Register (or upgrade) button.  This will display the Registration Dialog.

  3. In Version, select the version you want to upgrade to.

  4. In Term, select term of your usage to SuperQuery; purchase or trial.

  5. In Days, put "0" if you choose purchase or the number of the days for the trial.

  6. In User ID, and the Registration Key type the User ID and Registration Key you obtained from Azmy Thinkware Inc.

  7. Press OK.

 

 

1.5 Service and Support

For questions that you cannot answer using this manual or online help, contact technical support through the AZMY Thinkware Inc. World Wide Web at www.azmy.com. Or you can call AZMY Thinkware technical support directly at (201) 947 1881.

 

Registered users of AZMY Thinkware SuperQuery receive 60 days of complimentary technical support, beginning with their first call to AZMY Thinkware Technical Support.

 

2 Getting started

2.1 Starting SuperQuery

When you run SuperQuery the Getting Started dialog  (figure 1) will be displayed. You can either select any SuperQuery document from the drop down list, or click the Use Wizard to read your data button to run the Open Wizard (figure 2) to  open your data. 

 

 

Figure 1 Getting Started dialog

 

You can also start SuperQuery from the File menu; select one of the three menu items:

 

New

To create a new document.

 

Open SuperQuery Document

To select an exiting SuperQuery document (*.sqd).

Open  Database

To run the Open Wizard and to  open your data .

 

 

2.2 Reading your data

SuperQuery can read most common types of databases directly using  “Data Access Objects. SuperQuery also supports ODBC  if you have both ODBC and your database drivers installed.

 

From the Table menu select the Open Wizard menu item. The Open Wizard (figure 2) will run and will assist  you to open your data.

Figure 2 Open wizard - WELCOME Page

 

Figure 3 Open wizard - MORE Page

 

After opening the data table, the Wizard (figure 3) will give you the options to:

- Read another table ,          

- Define links among open tables, or to

- Call the analysis Wizard to analyze your data.

 

2.3 Browsing your data

After reading your data , SuperQuery will automatically create a Table View (figure 4) to show the data. The Table View   contains several panes separated by Splitters. All panes are kept synchronized at all times. You can completely configure any view by including some or all pane types.

 

Figure 4 SuperQuery Table View showing two the panes: Data Table Pane and Reps Graph Pane

 

As you scroll through the data columns , look at the Reps Graph to see the most common values in each column. Change the graph type to Data Graph and see the highest values in each column. Look at the Totals pane to see the total, min, max and average values. You can select from more than 20 statistical functions.

 

Double click on a bar in the Graph Pane or a value in the Total Pane and SuperQuery immediately position the cursor   at that row in the Data Pane. Now push the filter button, and you immediately see only those rows satisfying that value. Scroll through your columns and watch all graphs  and statistics calculated for that filter.

 

Now push the summary table button and create a summary on any category. Or push the Facts button and launch the fact discovery engine.

 

Map a numerical column into High, Medium, and Low by just a couple of clicks. Use any of the five Virtual Column Shortcuts to quickly perform calculations , classifications , and other analysis tasks.

 

Scroll in many tables simultaneously. For example, link your Customer and Order tables and scroll in the Customer table. SuperQuery automatically sets a filter to all orders by that customer and shows you the total, min, max, and average orders by that customer. Scroll in the orders table and SuperQuery automatically highlights the customer who made these orders.

 

SuperQuery  , automatically   does what used to require many complex SQL and programming procedures. It is that simple! You have to try it yourself, to believe how simple data   analysis  could be.

 

 

2.4 Updating your data

SuperQuery   allows you to update your data   tables if the original data is changed. SuperQuery will read the new data from its original location (you can specify another source) and update the entire analysis and virtual columns you have performed and added to your original data  .

 

From the Table menu   select the Update Wizard   menu item. The Update Wizard   (figure 5) will run and will assist XE "assist"  you to update your data  .

 

 

Figure 5 Update Wizard - WELCOME page

 

 

2.5 Exporting your data

SuperQuery   allows you to export both the data   tables and the generated facts into text files.

 

From the Table menu   select Export menu item then select any of the following menu items:

 

Tab  delimited

Write data   table into a text file with columns   separated by tabs  and rows   separated by new line.

Facts 

Write fact table   rows   in "Fact - Exception" format

Rules

Write fact table   rows   in "If ... Then" format.

 

Select Export | Tab delimited   to export data   tables into a tab delimited   file. The system will display the Select Table Dialog   to select a table   that you want to export, then will display the Save As dialog to save the new file.

 

Select Export | Facts   to export the facts generated into a "Fact - Exception" format text file.

 

Select Export | Rules to export the facts generated into an "If ... Then" format text file.

 

 

3 Organizing your data

 

In order to create a comprehensive analysis   on your data  , you need to create several views  . Some views showing rows   sharing common criteria   or compose a specific fact.

 

In order to be able to organize these views, SuperQuery   provides a Hierarchical Tab   Organizer  .  Each tab may contain one or more view. Each view consists of one or more pane. Initially, you have eight empty Tabs  .  You can start organizing your data views in these tabs  . Each view   has a “pin” button. You simply click the button to “fix  the view in the current   tab. Similarly you click the pin again to make the view “floating  ”, then change to another tab and “fix” it there.  If you need more tabs, you can add a new group of tabs inside any of the exiting ones. This allows you to create as many tabs as you want.

 

 

3.1 Tabs

The main   window in SuperQuery   contains a set of tabs   or folders (figure 6)  .  Each tab may have data   Table Views or other tabs (but not both). This means you can have a hierarchy   of tabs. A new SuperQuery document contains one tab-level that has eight tabs. You may add any number of tabs at this level or create more levels of tabs within any tab.

 

Figure 6 SuperQuery hierarchical   levels of tabs. A second tab level was created under tab3

 

In order to customize the appearance of a tab, click on the Right Mouse Button (RMB) when placed on the tab you want to customize. The property menu will pop up, select the property you want to change. You can also customize the tab by activating the tab first then selecting the Tab menu, and the item you want to change. 

Tab   customization includes:

 

·         Editing   the tab title

To edit the tab title,     select Title menu item the Edit Title dialog will be displayed. Type the new title to apply on the active tab.

 

·         Changing the tab background color

To change the tabs   color select     Color menu item. The standard color dialog will be displayed. Select the new color to apply on the active tab.

 

·         Moving views from one tab to the other

Each view   has a “pin” button. Simply click the button to make the view “floating  ”, then change to another tab and “fix  it there.

 

·         Adding more tabs   

    Select New   menu item. Either select the Tab menu item to add a new tab at the end of the active tab row or, select the Tab Level to display Insert Tab (s) dialog to add a new row of tabs   inside the active tab row.

 

 

3.2 Views

SuperQuery   Table View   is a new innovative way of viewing the data (figure 7)  . Each Table View consists of a set of ‘Panes’.  Each pane   displays data from its own point of view  .

 

Figure 7 SuperQuery standard Table View with the four panes Data Table Pane,   Total Page Pane  , Reps Graph Pane   and the Data Page Pane  .

 

The SuperQuery   standard Table View   consists of four panes  ; the main   pane   is the Data Table Pane   that controls the scrolling of the other three panes  ; Total Page Pane  ,  Reps Graph Pane   and the Data Page Pane  .

 

Each pane has a re-sizable frame, to allow you to change the pane size according to your preferences and the type of the analysis you are performing. As you move the mouse on the pane frames, the cursor changes toin order to resize the vertical splitters and   to resize the horizontal splitters separating the panes. As you move the cursor to the splitter end, the cursor change to one of the following four shapes  in order to maximize or minimize the pane in the arrow's direction.

 

Table view   contains a row of 12 sets of buttons:

 

1.       Fix button  

Press the Fix button   to fix   or float a view   in the current   tab. To move a view from one tab to another, first float the view, switch to the other tab, and then fix the view.

 

2.       View setup button

Press the View setup button   to display the Select and reorder columns   dialog in order to select / deselect and change the order of the columns in the view  .

 

3.       Duplicate button

Press the Duplicate button   to duplicate the current   view  .  You can use the Duplicate button to create a new copy of the view.  You can then set a different filter  , maximize a graph   pane  , or in general, perform   further analysis   on the new view.

 

4.       Print button

Press the Print button  , to see a print-preview the current   data   table   view  .  Press the print button from the preview screen to start the printing to your printer.

 

5.       Find button

Press the Find button   to display the Find dialog that allows you to search   for a specific word or phrase in the current   column.

 

6.       Filter buttons  

Filter buttons   are composed of three buttons.  When you press the left button, SuperQuery   sets a filter   on the value in the current   cell   in the Data Table Pane  .  Only rows   that have the filter value are shown in the view  .  If you move to another cell and press the button again, the filter further restricted by the new value.  Each time you press this button, you are ANDing the current cell value to the filter.

 

The button at the right side, is an "undo" filter   operation.  It will remove the last filter you set and hence expand the view  . 

 

The middle button acts mainly as an indicator  to show you if there is a filter   set. You can also use it as a shortcut to clear all filters. It is important to note that all panes   will change their contents according to the current   filter  .  For example  , “Total” panes will display totals for all rows   in the filter.

 

7.       Index buttons

Index buttons   are composed of two buttons. The left button displays the Add  / Edit Table Index dialog that allows you to create, edit, or select a Table Index.  The right button acts as an indicator   and a shortcut to set or set the current   index.

 

8.       Add   Virtual Columns

Press the Add   virtual   column button to display the Add / Edit Virtual Columns   dialog, which helps you, creates, edit, or delete a virtual column. SuperQuery   supports five types of the virtual columns  :

 

·         Filter column 

·         Range column

·         Keyword   column

·         Classification column

·         Formula column

 

9.        Drill - Down button  (only in Summary and Facts   views  )

This button creates a detailed view   of the current   row in the view. This button exists for Summary and Facts   tables only. When you press it in a Summary view, SuperQuery   creates a "Drill-Down" view the original data   of the current row.  Similarly, in the facts table   view, the Drill-Down button displays all data rows   that support the current fact.

 

10.    Summary button

Press the Summary button   to display the Add   / Edit Summary Table Dialog   that will help   you summarizes a data   table  .

 

11.     Facts button