SuperQuery Online Help
|
|||||||||||||||||||||||||||||||||||||||||||
| What is 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 help 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 Discovery version can read Access, xBASE, Excel, Paradox and Text files directly. and can also use ODBC to open most of the popular databases that have ODBC drivers. SuperQuery Office version can only reads MS Access and Excel files. The Open Table Wizard will help you in opening your data. After you open the data you may analyze the entire table ( see limitations in the open Wizard ), a random sample, or a sample that satisfies given criteria. |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Introduction | |||||||||||||||||||||||||||||||||||||||||||
|
SuperQuery is a query and database analysis tool. It helps you explore and analyze your data and discover any existing patterns.
SuperQuery is composed of three integrated facilities: ·Fact Finding System. ·Query Visual Tool. ·Tab Organizer.
The fact finding system searches your table and reports all interesting facts and exceptions. The query tool allows you to visually create different views on your data. Each view may have its own criteria and columns. Each view automatically displays statistical data and graphs about the current column. The Tab Organizer allows you to store all your views into different tabs. This helps you in using and presenting your analysis.
SuperQuery requires no knowledge of database languages or SQL. Fact Finding and Classification SuperQuery finds all “interesting” similarities among your data. For example, it may find that all your low income customers buy product A while most of your High Income customers are interested in product B. On the other hand, SuperQuery also detects exceptions. For example, in a mutual fund data table, SuperQuery may detect the exception that only two funds have Risk = Low and income = High.
SuperQuery uses heuristic functions to evaluate the interest level of facts and exceptions so that it can control the number and quality of facts reported. The user can raise or lower the interest threshold thus decreasing or increasing the number of facts and exceptions reported. Lowering the interest threshold reduces (and eventually cancel) the effects of heuristic functions.
In order to find facts in numerical columns ( like price, volume, temperature, etc.), we need to classify those columns into categories or ranges. SuperQuery can automatically create a new “virtual” column that classifies a numeric column into three ranges; High, Medium, and Low. You can change the number of categories and specify the ranges.
Moreover, SuperQuery allows general classification on other types of columns like Date and Text. You can create a virtual column that contains criteria on one or more columns. For example, you can classify your active customers as follows: Large Customer if shipyear = 95 and sales > 100 Medium Customerifshipyear = 95 and sales isbetween( 10, 100 ) Small Customer if shipyear = 95 and sales < 10
Once you create the “Active Customers” column, SuperQuery is able to find facts about your Large, Medium, and Small customers. The more classified your data is, the more opportunities for finding interesting facts.
Virtual columns also include a “Formula” type that allows the user to create calculated columns. For example create a “Total Cost” column multiplying Quantity by Price columns. Another type is the “Keyword” column that detects keywords from description columns. |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Chapter1 Getting Started in SuperQuery | |||||||||||||||||||||||||||||||||||||||||||
|
This tutorial will show you how to analyze and mine your data using SuperQuery. In the following chapters you will learn how to:
·Access your product sales data that is stored in the Sales table. ·Browse, set filters and create visual queries. ·Discover the hidden patterns in your data that are potentially useful. ·Summarize your sales data by product. The tutorial consists of 5 chapters, each requires about 10 minutes.
Launch SuperQuery If you are in the "Getting Started" dialog then, go to step 2. If you are in the SuperQuery main screen then, select the Table | Open Wizard menu item and go to "Read the Data" section.
1.Launch SuperQuery Click the Start button, click Programs and click SuperQuery. In the list of SuperQuery items that appears, click SuperQuery. The SuperQuery Getting Started dialog appears
2. Click on the "Use Wizard to read your data" button. Press OK.
Read the Data To access your data, you first need to read the database. SuperQuery will start the Open Wizard to help you read your table.
Chapter 1: Getting Started in SuperQuery Chapter 2: Organizing your Data Chapter 3: Querying the Database Chapter 4: Discovering Facts and Rules Chapter 5: Summarizing your Data
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Chapter2 Organizing your Data | |||||||||||||||||||||||||||||||||||||||||||
|
Since you will create more views in the coming sections, you need to organize these views in order to easily access them.
SuperQuery main screen consists of one row of eight tabs. You can edit the tab title and change the tab color. You can explore these features from the tab property menu. To display the property menu, click the right mouse button on the tab you want to edit.
From the property menu of the first tab, select the "Title" menu item and write down " Sales Data" in the "Edit Title" edit box, then press OK. Repeat for the second tab, and write down the title "Product Summary".
Now we need to copy the main data view and put it in a separate tab before performing more analysis. Press the Duplicate button. SuperQuery creates a new copy of the same view. To move this view to the third tab, press the button. The button state will change to indicating that the view is now floating. Click on the third tab, in order to activate it. The current active tab becomes the third tab and the view still floats on this tab. Press the button again. The view is now fixed in this tab and the button state changes to . Write down the title "Raw Data" on the third tab in the same way you did for the two previous tabs.
SuperQuery's data view consists of four panes; the main pane is the Data Table that controls the scrolling of other panes. The other three panes are: Total Page, Reps Graph and the Data Page. Each pane has a re-sizable frame, to allow you to change 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 to in order to re-size vertical splitters and to re-size horizontal splitters separating the panes. As you move the cursor towards the splitter ends, the cursor changes to one of the following shapes in order to maximize or minimize the pane in the arrow's direction.
To change the pane type simply, select the "Change Type" menu item from the pane property menu. To display the property menu, click on the right mouse button when placed on the pane you want to edit. Select the new pane type from the list of the available types. Each pane has its own unique property menu that is tailored to its own functionality.
It is recommended that you spend some time to familiarize yourself with the different pane types and their properties in order to explore the strengths and capabilities of the system.
Chapter 1: Getting Started in SuperQuery Chapter 2: Organizing your Data Chapter 3: Querying the Database |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
|
Now that you have the view of the table "Sales", you can start browsing, exploring and learning more about your data. SuperQuery Table View contains several panes separated by Splitters. As you are scrolling in the Data Table pane, all other panes in the same view are updated at all times. You can completely reconfigure any view by including some or all pane types.
Simple Queries: In the Sales view:
Click on the "Sales Data" tab. Move the cursor through the columns, and look at the Reps Graph to see the most common values in each column.
Go to a numerical column "PROFITDLR" or "QTYSOLD" and look at the Total Page pane to see the total, average, minimum and maximum values.
Click on any cell in the "CITY" column. The Reps Graph pane will show the most common values in this column. Double click on the "New York" bar in the graph. SuperQuery will immediately position the cursor in the Data Table at the first row that has the value "CITY = New York". Now push the filter button, and you will see only those rows satisfying that value "CITY = New York". Scroll through the other columns and watch all graphs and statistics calculated for New York. Furthermore, move to the "Profit" column and place the cursor on any cell having the value "High" then press the button again. This will simply perform the Query "City = New York AND Profit = High". This means that you are only viewing sales in New York City, which has high profits.
As you may have noticed, both "Profit" and "Quantity" columns have the cyan color, to reflect that they are calculated by the system. The Analysis Wizard created these two columns when you were reading the data table.
In order to edit the "Profit" column; first make sure that no filters are set on the view. To release all filters, press on the button, so that the green light is turned off. Double click on the column "Profit", the "Enter Ranges" dialog is displayed. Click on "5 Range" button and press OK. The column will be recalculated to map the original numerical values into five ranges (Very High, High, Medium, Low and Very Low) instead of three ranges (High, Medium, and Low).
More Comprehensive Queries: In the "Sales" table, suppose that you want to study the New York City sales only during the winter. To do this, you need to create a new column of type "Filter" to perform the criterion: CITY = New York and MONTH is one of (December, January and February).
Click on the first tab "Sales Data" in order to work with the Sales data view. Make sure that no filters are set on the view. To release all filters press on the button, so that the green light is turned off.
Chapter 1: Getting Started in SuperQuery Chapter 2: Organizing your Data Chapter 3: Querying the Database |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Chapter4 Discover Facts and Rules. | |||||||||||||||||||||||||||||||||||||||||||
|
Now you are ready to run the most powerful feature in SuperQuery and discover the patterns hidden in the data.
Click on the first tab "Sales Data" in order to work again with the Sales data view. Make sure that no filters are set on the view. To release all filters, press on the filter button, so that the green light is turned off.
When the engine finishes scanning the data , it asks you if you want to view the discovered facts in a table view. Press "Yes" to view the facts in the SuperQuery Facts View.
The new view will be floating on top of the data view. You can move this view to one of the empty tabs and edit its title to read "Profit Facts" (as explained in Chapter 2).
Now you can scroll through the "Fact Table View" rows and read the facts. Notice that as you are scrolling in the rows, the Facts pane (the pane below the Data Table pane) shows the description of the current fact in natural language. For example, in the first row, you should have a fact similar to following:
This Fact means that "Most Hat Sales have Medium Profits." The fact has a certainty factor of 80% and there are 25 rows supporting this fact.
You can also look at the Facts pane and read the same fact in a more natural language "Most Sales where PRODUCT = Hat --> have Profit = Medium. Percentage 80% Supported by 25 rows." You can also read the fact in the Rules format. To do this, click on the Right mouse button when placed on the Facts pane and check the Rules Format. The text will be changed to "If PRODUCT = Hat --> then Profit = Medium. Confidence 80% Supported by 25 rows."
Press the Drill-Down button to view those data rows supporting this fact. SuperQuery will create a new data view and set filter on those data rows. You can close the view or move it to another tab. It is sometimes easier to read the discovered facts in a report form. SuperQuery allows you to export the discovered facts into text files in both Facts and Rules format. From the Table menu, select the Export | Facts menu items. When the "Select Table" dialog is displayed, select the "Profit Facts" table. Press OK, the Save As dialog is displayed. Write down "ProfFact" in the "File name" edit box and press OK. SuperQuery saves the new file as a text file "ProfFact.txt". Open this file using any text editors and view the text. Try to do the same steps to export the Facts Tables in the Rules format and compare the two.
In situations where you have a lot of facts, it is useful to be able to set filters on specific types of facts and also see a graph of the distribution of facts among columns and values. To do this, you need the "Reps Graph Pane" which is already in the view but minimized. Use the splitter bar at the right side to resize it, then scroll through the view columns to see the different distributions.
Chapter 1: Getting Started in SuperQuery Chapter 2: Organizing your Data Chapter 3: Querying the Database Chapter 4: Discovering Facts and Rules |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Chapter 5 Summarize your data | |||||||||||||||||||||||||||||||||||||||||||
|
Suppose that you want to analyze your sales by product. For example, for each product, you want to know: The most common city and month in sales, The total quantity sold, and The total profit.
Now click on the first tab "Sales Data" in order to work again with the Sales data view. Make sure that no filters are set on the view. To release all filters, press on the filter button so that the green light is turned off.
On the sales view, press the Summary button; the Add / Edit Summary table dialog is displayed. In the Select / Add Summary Table edit box, write down "Product Summary" to name the new Summary table. Click on the "Group By" drop-down box to select the PRODUCT column. Press the "Select Cols" button in order to select the columns City, Month, QTYSOLD and PROFITDLR and their associated summary operators. The "Select columns and their summary calculations" dialog is displayed. Check the boxes next to the columns: City, Month, QTYSOLD and PROFITDLR. Click on the drop-down box next to the "CITY", and select the operator "Most common". Also for the "MONTH", select the operator "Most common". For both "QTYSOLD" and "PROFITDLR", select the operator "Total". Press OK to return back to the Add / Edit Summary Table dialog and press OK.
SuperQuery will summarize the data on each of the products: Coat, Glove, Hat, Jacket and Shirt and will show them in a new Summary view.
The new view will be floating on the top of the data view. Move this view to third tab "Product Summary". To do this, click on the "Product Summary" tab. The view will move to this tab. Press on the button to fix the view in this tab.
The first row in the summary view reveals the following :
In order to view those rows that have "PRODUCT = Coat", place the cursor on the first row and click on the Drill-Down button. SuperQuery will create a new data view and set a filter on those data rows. You can close the view or move it to another tab.
The Data Graph is showing the values of the column you have the cursor on. In this case, the graph is showing the values of the column "Total QTYSOLD". It is clear that Shirts are the highest quantity sold since they have the longest bar in the graph. You can set the Graph Pane to show the highest or lowest values using its property menu.
Browse through the summary view rows and columns and look at the Data Graph and experiment with changing the property of each pane.
Chapter 1: Getting Started in SuperQuery Chapter 2: Organizing your Data Chapter 3: Querying the Database Chapter 4: Discovering Facts and Rules Chapter 5: Summarizing your Data |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
|
SuperQuery defines three major concepts are for data visualization and organization:
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||||||||||||
|
Definitions
Query and Statistical Analysis
|
|||||||||||||||||||||||||||||||||||||||||||
| Query and Statistical Analysis | |||||||||||||||||||||||||||||||||||||||||||
|
SuperQuery allows you to create queries visually in a “Query-By-Example” style. Furthermore, there are a number of short cuts that let you interact faster with your data. For example:
Examples of SuperQuery Panes are:
The user can completely configure any view by including some or all pane types. Panes process rows selected in the filter, for example, if you need to find total sales from NY then:
Similarly, if you need to find how you shipped to NY, move to the shipment column and look at the Reps Graph. The Graph will show the percentage of your different shipment methods to NY. |
|||||||||||||||||||||||||||||||||||||||||||
| Tab Organization | |||||||||||||||||||||||||||||||||||||||||||
|
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. 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 . |
|||||||||||||||||||||||||||||||||||||||||||
| Criteria | |||||||||||||||||||||||||||||||||||||||||||
|
SuperQuery allows you to build criteria in a very easy manner ; SQ criterion consists of any number of Expressions ANDed or ORed together. Each expression consists of : Column identifier Operator Value (s)
See the criterion example See the criteria example |
|||||||||||||||||||||||||||||||||||||||||||
| Calculation statement | |||||||||||||||||||||||||||||||||||||||||||
|
The Calculation statement consists of one or more Operands and one Operator.
The Operand types are:
The Operator types are:
You may choose among these operator types depending on the operand's type. |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Criteria Operators: | |||||||||||||||||||||||||||||||||||||||||||
|
SuperQuery supports a wide selection of comparison operators, following is a list of these operators:
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Date Operators | |||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
Logical Operators |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Mathematical Operators | |||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Numerical Operators | |||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Text Operators | |||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Calculation Statement Operators | |||||||||||||||||||||||||||||||||||||||||||
|
SuperQuery supports the five following Calculation Statement operator's types:
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Column identifier | |||||||||||||||||||||||||||||||||||||||||||
|
The column identifier is any valid column name that exists in the data table. |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Primary key | |||||||||||||||||||||||||||||||||||||||||||
|
A primary key is the table key. It is a data column with unique values. An example of the primary key is Employer Name column in the Employee table. |
|||||||||||||||||||||||||||||||||||||||||||
| Foreign Key | |||||||||||||||||||||||||||||||||||||||||||
|
The foreign key in a table is a column that represents the primary key of another table . Each table may contain non-or many foreign keys. |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||||||||||||
| SQL Types | |||||||||||||||||||||||||||||||||||||||||||
|
SuperQuery Supports the following SQL types list which contains the major ODBC SQL types:
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||||||||||||
| Statistical calculations | |||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||||||||||||
| Value (s) | |||||||||||||||||||||||||||||||||||||||||||
|
Values may be literal for example, 100 and “New Jersey” and may not be in the table, or may selected from the existing values. |
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
| Tab or Folder | |||||||||||||||||||||||||||||||||||||||||||
|
The main window in SuperQuery contains a set of tabs or folders. 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. Tab customization includes:
Changing the tab background color Moving views from one tab to the other Adding tabs to a specific tab level or even Adding tab level to within another tab
|
|
||||||||||||||||||||||||||||||||||||||||||
| Editing the tab title | |||||||||||||||||||||||||||||||||||||||||||
|
Select the tab, then, choose the Tab | Title from the main menu, or click on the right mouse button on the tab and choose the title menu item. The Edit Title dialog will be displayed to allow you to enter or edit the tab name. |
|||||||||||||||||||||||||||||||||||||||||||
| Changing the tab background color | |||||||||||||||||||||||||||||||||||||||||||
|
Select the tab, then choose the Tab | Color from the main menu or, click the right mouse button on the tab and choose the Color menu item. The Color dialog will be displayed to allow you to change the tab background color. |
|||||||||||||||||||||||||||||||||||||||||||
| Adding tabs to a specific tab level | |||||||||||||||||||||||||||||||||||||||||||
|
Select the tab level that you want to add a tab to, by clicking on any of its tabs. Choose the Tab | New | Tab from the main menu or, click the right mouse button on the tab and choose the New | Tab menu item. |
|||||||||||||||||||||||||||||||||||||||||||
| Adding a tab level | |||||||||||||||||||||||||||||||||||||||||||
|
Select an empty tab that you want to insert a tab level in. Choose the Tab | New | Tab Level from the main menu or, click the right mouse button on the tab and choose the New | Tab Level menu item. The Insert Tab(s) dialog will be displayed.
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||||||||||||
| Table view (TV) | |||||||||||||||||||||||||||||||||||||||||||
|
SuperQuery Table View is a new innovative way of viewing the data. Each Table View consists of a set of | |||||||||||||||||||||||||||||||||||||||||||