Total SQL Statistics is .Net Ready!

Total SQL Statistics supports Microsoft’s .Net platform. You can create Visual Studio.Net projects and include a reference to the Total SQL Statistics Runtime DLL just as you would in Visual Basic 6. Additionally, Total SQL Statistics can generate Visual Basic.Net code that runs any scenario you have defined.

See Generate Code Form for more information.


Designed for Visual Basic

Designed for Visual Basic and Visual Studio.Net

Total SQL Statistics is designed specifically for Visual Basic and Visual Studio.Net developers using and SQL Server data.

A wide range of statistical functions is available to analyze data stored in SQL Server. The data can be accessed in tables, views, stored procedures, and user defined functions that return a table. The results are created in tables so you have maximum flexibility in working with these data sets.

Analysis scenarios are designed by running our standalone Scenario Designer program. Once you assign the parameters of the scenario with this program, you can run it from your VB program with a simple DLL call into a distributable run-time library. Only the structure of the scenario is saved, not the data, so analysis is always performed on the current data.


Scenario Designer

The Total SQL Statistics Scenario Designer is where you create analysis scenarios. Scenarios contain information on the data to analyze and the statistical options to perform. A Wizard-like interface guides you through the process with no programming required. This allows you to perform powerful analysis with point-and-click ease. Your selections are automatically saved as "scenarios " which can be run interactively or invoked programmatically.

Total SQL Statistics adds three small tables in your SQL Server database to store these scenarios.


Programmatic Interface

Total SQL Statistics includes a programmatic interface for programmers who want to incorporate statistical functions directly into their applications. A DLL is all that is needed to add Total SQL Statistics to your applications—simply reference our DLL with the scenario you designed in the Scenario Designer, and the results are generated. You can specify all login information and direct output tables to a different database. While the analysis is running, events are available to let you know its progress. The analysis runs in the background, so your users won’t even know how you generate the complex analysis!

For more information on how Total SQL Statistics works, click here.


Total Access and Total VB Statistics Owners

If you already use Total Access Statistics or Total VB Statistics, Total SQL Statistics should be very familiar to you. The user interface is similar, but there are some differences:

  • Total SQL Statistics has a Scenario Designer that is a separate EXE program.

  • Total SQL Statistics uses a DLL rather than an Access library. This DLL is distributed with your application.

  • The Scenario Designer has a right mouse menu to let you jump to a particular page in the Wizard.

  • The Scenario Designer displays the list of scenarios in a grid that can be sorted by clicking on the column heading.

  • The syntax for the programmatic interface is different.

  • A code generator is available from the Scenario Designer to create the code for you.

  • The way you connect to a database is different. In Access it presumes that you have rights to the current database since it is in your workspace. In SQL Server, a connection needs to be explicitly opened in code with the user name and password provided, or by specifying Windows Authentication.


Our Internet Web Site

To provide you with the most current information about our products, FMS can be reached on the World Wide Web at www.fmsinc.com. Our site is being updated constantly with news and features for developers. Please drop by and take a look.

Product Announcements and Press Releases

Read the latest information on new products, new versions, and future products. Press releases are available the same day they are sent to the press. Sign up in our Feedback section to have press releases automatically sent to you via email.

Product Descriptions and Demos

Detailed descriptions on all our products are available. Each product has its own page with information on features and capabilities. Demo versions for several of our products are also available.

Product Registration

Register your copy of Total SQL Statistics on-line. Be sure to select the email notification option so you can be contacted when updates are available or news is released. You must be registered to receive technical support.

Product Updates

FMS is committed to quality software. When we find problems in our products, we fix them and post the new builds on our web site. Check our Product Updates page in the Technical Support area for the latest build, or see Using the Update Wizard for information on how to check for the availability of updates.

Utilities and Free Software

Download free demos and utilities, including add-ins and tools for VBA, Office, and VB developers.

Technical Papers

FMS personnel often speak at conferences and write magazine articles, papers, and books. Copies and portions of this information are available to you on-line. Learn about our latest ideas and tricks for developing more effectively.

Newsgroups

Share your experiences, learn from others, and ask your questions in our virtual community. Point your web browser to:

www.fmsinc.com/support/newsgrp.htm

or visit our web site for additional instructions.

Links to Other Microsoft Access Sites

Jump to other locations, including newsgroups, user group home pages, and other sites with news, development techniques, and related services.


System Requirements

The requirements for Total SQL Statistics are:

  • Pentium processor or better

  • Microsoft Windows 98, Windows 98 Second Edition, Windows Millennium Edition (ME), Windows NT 4.0 (Service Pack 6 or higher), or Windows 2000

  • A minimum of 32 MB RAM

  • 10 MB of hard disk space

  • Microsoft Visual Basic 5.0 or 6.0

  • Client Tools for Microsoft SQL Server 7 or 2000

  • Microsoft Data Access Components (MDAC), version 2.5 or higher (available at www.microsoft.com/data)


Installing Total SQL Statistics

As a VB developer, your system already contains many of the run-time files required by Total SQL Statistics: the VB run-time virtual machine, the Windows common controls OCX, and the ADO data controls. In addition, you will need to have MDAC library version 2.5 or higher installed. If you have SQL Server Enterprise Manager or SQL Server itself installed on your machine, MDAC will be registered and ready to use. Otherwise, you will need to download it from www.microsoft.com/data.

The installation program installs the Total SQL Statistics Scenario Designer program (FMSSTAT.EXE) and the Total SQL Statistics Statistical Calculation Engine (FMSSTAT7.DLL). The FMSSTAT.EXE program is installed in the program’s installation directory, along with the help file and other supporting files. The calculation engine is an ActiveX DLL component, and is stored in your \WINDOWS\SYSTEM or WINNT\SYSTEM32 directory.

The installation program updates the system registry to register the Total SQL Statistics Statistical calculation engine on your system. This DLL must be registered in order for your program to use it.

Licensing Rules

Total SQL Statistics may be installed on a local hard disk or a shared network directory. Each developer must have a license. Additional licenses (user counts) and site licenses may be obtained by contacting FMS, Inc.

See also: Running the Setup Program


Running the Setup Program

A standard Windows setup program is used. Please follow these steps to install Total SQL Statistics:

Step 1 – Close all Programs

Before running the Total SQL Statistics setup, please close any other programs, including virus scanners, disk utilities, and programs in your Windows Startup folder.

Step 2 – Start the Setup Program

Insert the CD in the drive. If AutoRun is enabled, the setup program starts automatically. If setup does not start, then in Windows, press the Start button and select RUN. Type the letter of the drive containing the Total SQL Statistics CD ROM, followed by a colon and the word SETUP. For example, if D is the letter for the drive containing the CD ROM, type D:SETUP. After a moment, the Setup program starts.

Step 3 – Run the Setup Program

Follow the instructions in the setup program to install Total SQL Statistics. Press the [Help] button on the setup screen at any time to receive help. On some systems, the setup program tells you to restart Windows to complete the installation process. Close any open programs and re-start Windows to finish installation.

Network Installation

Total SQL Statistics can be installed on a network directory, but can only be used by the machine from which it was installed.

List of Files Installed

The INSTALL.LOG file in the Total SQL Statistics installation directory lists the files installed plus a complete log of the Total SQL Statistics setup, including overwritten files, and the registry entries and Start Menu items created.

Read the README.TXT File

After the setup program installs all the files on your system, the README.TXT file is presented. Be sure to read it as it contains updated information not found in this manual.


Using the Update Wizard

Total SQL Statistics includes a built-in mechanism to check the availability of updates via the Internet. If you have an active Internet connection, you can use the Total SQL Statistics Update Wizard to ensure that you have the latest version.

To run this program, press the Windows Start button and select Programs, FMS, Total SQL Statistics, and choose the Update Wizard. Follow the prompts to check for the latest update.


Un-installing Total SQL Statistics

To remove Total SQL Statistics:

  • Run Add/Remove Programs from your Windows Control Panel.

  • Select Total SQL Statistics from the list and press the [Add/Remove] button.

  • Choose the "Automatic" option to ensure that files used by other programs remain on your system.

  • Follow the Wizard interface to uninstall the program.

Total SQL Statistics also creates three tables to store the scenarios you create (see How Total SQL Statistics Works for more information). These tables exist in each database with which you have used Total SQL Statistics, and they are not removed when you uninstall the program. You may choose to delete them from your databases manually, but you may want to keep them to preserve your scenarios if you expect to use Total SQL Statistics again in the future. In general, these tables are very small and do not interfere with your application.

Delete the three Total SQL Statistics files that begin with "usysTStat" (usysTStatScenarios, usysTStatOptions, and usysTStatFields).


Important Concepts

There are several important concepts you should be familiar with before installing and using Total SQL Statistics:

  • The analysis is performed on the tables, views, stored procedures, and user defined functions in your SQL Server database. Total SQL Statistics supports stored procedures and user defined functions that return data—specifically stored procedures that return only one recordset, and table-type user-defined functions.

  • Three tables are used to store scenario settings. These tables are added to every database that you open with the Scenario Designer. The information remains with your database even if you rename the database, move it, or re-install Total SQL Statistics.

  • Your analysis selections (scenarios) are automatically saved for reuse. Only the settings are saved, not the data, so the latest data is always used to recalculate the results when you run a scenario.

  • Your data is never modified. Total SQL Statistics only reads and sorts your data. When it needs to process intermediate data, it is created in separate temporary tables that are deleted after the analysis is completed.

  • Multiple fields can be analyzed at one time.

  • Groups of records can be analyzed simultaneously, similar to how the "Group By" clause works in T-SQL.

  • Records can be weighted by assigning a weighting field to designate the number of times the record is counted.

  • Null values are automatically ignored. You can also specify specific values or ranges of values to ignore.

  • The results are placed in one or two permanent tables in the current database or another database you specify.

Using a Separate Database

Since Total SQL Statistics creates output tables in the current database, you may clutter your database with unnecessary tables. If you are performing a lot of ad hoc analysis, consider creating a new database. This keeps the results of Total SQL Statistics separate from your main database.

If you plan to add Total SQL Statistics features to your Visual Basic or Visual Studio.Net applications, you need to create scenarios in a development database and distribute all scenario data with your application. This can be done in one of two ways:

  • Generate scripts that create the tables, insert the data, and then run these scripts on the target machine.

  • Create the scenarios in your application’s source database and then copy the database to the target machine. This is appropriate for distribution of a new application.

See Programmatic Interface for more information on calling Total SQL Statistics from your program.


Starting Total SQL Statistics

To start Total SQL Statistics, launch the FMSSTAT.EXE Scenario Designer program from the shortcut on the Start Menu (under Start, Programs, FMS, Total SQL Statistics).

More:

Open Scenario Database

Sample Database

Main Form of the Scenario Designer


Open Scenario Database

Connect to the server containing the data to you want to analyze. You must have permissions to read and create tables in the database. Use this dialog to log into the server and specify the database to analyze:

Open Scenario Database Dialog

Type the server name directly into the Server Name combo box. Depending on which version of Windows you are running and whether or not you have the SQL Server client tools installed, you may see a list of SQL Server machines that are located on your network the first time you run Total SQL Statistics. Any additional servers you enter in this combo box will appear each subsequent time you run Total SQL Statistics.

SQL Server and Windows Authentication

If SQL Server is set up to handle authentication separately from your network login, make sure the Use Windows Authentication check box is unchecked and enter the login name and password. If the server is configured to handle security via the network, check the Windows Authentication checkbox and leave the name and password blank. You should see your network administrator or database administrator if you do not know which security scheme is in use on a particular installation of SQL Server. Press the Test Connection button to test the connection with the login information provided.

Check the Remember User Name and Password checkbox to have Total SQL Statistics save your login information. If you leave this option unchecked, you will need to specify your user name and password every time you connect to the server.

If all login information is correct, the Database combo box will display a list of databases on the server that you can choose. Select the database containing the data you need to analyze and click [OK].

For more information, see Starting Total SQL Statistics.


Sample Database

The sample database contains sample tables and scenarios. To install it, start the Total SQL Statistics scenario designer, select Tools, Install Sample Database, select the server on which to copy the FMSSample database, log in to it, and click OK.

For more information, see Starting Total SQL Statistics.


Main Form of the Scenario Designer

After you select a database, the Scenario Designer’s main form appears:

Scenario Designer Main Form

The main form of the Scenario Designer controls the program. From here, you can create, edit and run scenarios. The scenarios are organized by the following Analysis Types:

Analysis Type Tabs

When you click on the Analysis Type tabs, the corresponding page of options and scenarios shows. Each of the three analysis types has additional types, which are displayed in the option group buttons on the left side of the page. If you have existing scenarios, they appear in the list on the right.

When you click on a scenario to select it, the buttons below the list become enabled to let you edit, copy, delete, or run it. A right click popup menu is also available (see Scenario Popup Menu for details).

See Using the Scenario Designer for a detailed discussion of the Scenario Designer.

For more information, see Starting Total SQL Statistics.


Analysis Types

All of Total SQL Statistics’s features are organized in analysis type categories. In general, every analysis shows the number of observations and the number of missing (ignored) values.

By understanding these types and their sub-categories, you can easily perform the desired analysis.

More:

Parametric Analysis Options

Group Analysis Options

Non-Parametric Analysis Options


Parametric Analysis Options

Parametric analysis is performed on numeric fields that are assumed to be continuous and normally distributed. Fields can be analyzed individually or compared with each other.

Type | Description
Describe | Analyze a numeric field: standard deviation, standard error, variance, coefficient of variance, skewness, kurtosis, geometric mean, harmonic mean, RMS, mode, confidence intervals, t-Test vs. mean, percentiles, etc.
Frequency | For each field, analyze frequency distribution for each interval (range of values): count, sum, percent of total, cumulative count, percent, and sum.
Percentiles | Perform analysis similar to Describe, but place results in records rather than fields (each percentile is a separate record): Median, quartiles, quintiles, deciles, and percentiles.
Compare | Compare two fields: mean and standard deviation of difference, correlation, covariance, R-square, paired t-Test.
Matrix | Perform analysis similar to Compare, but rather than comparing several fields to one, compare all fields to each other creating a matrix.
Regression | Calculate simple, multiple, and polynomial regressions with ANOVA and residual table.
Crosstab | Create cross-tabulation with row and column summaries, and % of row, column, and total for each cell. Chi-Square analysis is also available with expected value and % of expected for each cell.

For a list of all analysis types, see Analysis Types.


Group Analysis Options

Group analysis is the comparison of continuous, normally distributed numeric data between groups of records. A comparison field in the table defines the groups. For instance, you may want to compare data by gender or by race. Unlike the Compare feature in Describe, which is for paired values, these groups are usually of different sizes (number of records).

Type | Description
Two Sample t-Test | Compare means between two groups of records. Calculations include pooled and separate t-values for the two groups.
ANOVA (analysis of variance) | Compare the means of multiple groups of records. Calculations include degrees of freedom, sum of squares within and between groups, F-value, and probability.
Two Way ANOVA | Compare multiple fields between groups of records. The results are the same as ANOVA, except that they have additional values for each additional field. Use two way ANOVA to measure relative impact of each variable on the mean.

For a list of all analysis types, see Analysis Types.


Non-Parametric Options

Less powerful than parametric analysis, non-parametric analysis is used when the underlying data is not continuous, such as data that is ordinal or not normally distributed. Non-parametric analysis makes no assumption on the distribution of the underlying data, since the results are based on the ranks of the data. Non-parametric analysis can be made for each numeric field individually, compared with each other, or between groups of records (samples).

Type | Description
Chi-Square | Evaluates distribution and expected value for each unique value in a field.
Sign Test | Calculates a one sample sign test versus median, mean, or user defined value.
K-S Fit | Performs Goodness of Fit tests to determine if a numeric field fits a uniform, normal, or Poisson distribution.
2 Sample | Executes Wald-Wolfowitz Runs Test, Mann-Whitney U Test, and Kolmogorov-Smirnov.
N Sample | Performs Kruskal-Wallis one way ANOVA.
Paired Fields | Performs field comparisons: paired sign test, Wilcoxon Signed Rank, and Spearman’s Rho correlation.
N Fields | Calculates Friedman’s two way ANOVA.

For a list of all analysis types, see Analysis Types.


Preparing Data

To take full advantage of Total SQL Statistics, it is best to follow these guidelines:

  • Use Normalized Data.

  • Analyze One Table at a Time.


Using Normalized Data

Without going into theoretical discussions on data normalization, the basic idea is to store your data in a format that supports using summary queries, which allow Totals with Group By and Sum fields. See the "good" and "bad" example below to get an idea of what we mean. Avoid storing data across several fields (in spreadsheet format), since new columns need to be added if new categories arise. A normalized structure allows you to accommodate new data by easily adding records, not fields.

Total SQL Statistics does not perform "sums" across fields, only within fields. If you need spreadsheet-type reports, use the Total SQL Statistics crosstab or SQL Server pivot table feature when needed, but keep the data in a normalized format for analysis.

Good Normalized Data

Bad Non-Normalized "Spreadsheet" Data

For more information on normalized data, see Converting to Normalized Format and for more tips on preparing for analysis, see Preparing Data.


Converting Data to Normalized Format

To convert data from a non-normalized format, first create a table with the proper structure. Then, use an INSERT statement to fill the new table. Refer to SQL Server Books Online for more information on Transact-SQL INSERT statements.

INSERT INTO Good ( Customer, Product, Sales )
 SELECT Bad.Customer, 'Desk' AS ColProduct, Bad.Desk
 FROM Bad
 WHERE (((Bad.Desk) Is Not Null));

This statement adds the non-null Desk records to the GOOD table with the word "Desk" in the Product field. Repeat this process for each product.

Depending on the number of null values in the BAD table, normalizing the data may make your new table significantly smaller than the original. More importantly, since the products are no longer field dependent once they are in normalized format, you can manage an unlimited number of products.

Additionally, normalized format makes T-SQL statements much faster and simpler.

Of course, this effort is less effective if the Customer field is very large, or if you repeat several fields from the original table (e.g. customer name and address) in each of the new records. This is why most people assign a unique value for each customer. In the non-normalized format, each record would repeat the same data for each customer.

In a normalized format, the idea is not only to "normalize" the [Sales] information, but to also separate customer-specific information. Therefore, you should create a normalized Sales table with a unique customer ID for each customer, AND create a separate customer table with each customer’s ID, name, address, phone number, etc. That way, if the customer information changes, you only need to change it in one place. Similarly, you may want to create a Product table and assign a unique ID for each product. The linking of normalized tables is the basis for using a relational database.

See also: Preparing Data


Analyze One Table at a Time

You may analyze several tables during a Total SQL Statistics session, but only one at a time. In some cases, related data may exist in several tables; for instance, a separate table may exist for each year of sales. While Total SQL Statistics can analyze each table separately, it can only analyze all the data at one time if it is in the same table. In this situation, you would want a table that is similar to the original tables, but with an additional field designating the year.

Similarly, if you have an Invoice and Customer system and want to analyze invoices based on customer information (e.g. Sales by State or Zip Code), you need to combine the Invoice and Customer information. You do not need to create a table combining the two tables. Just create a multi-table view. Total SQL Statistics can analyze tables or other data objects, including views, stored procedures, and user-defined functions.

If you are performing multiple analyses on the same data, it may be more efficient to store the view’s results in a table and have Total SQL Statistics analyze that table.

See also: Preparing Data


Scenarios

Total SQL Statistics saves each analysis as a scenario, which is saved in three tables within the database where the analysis is run. A scenario stores all information about the analysis, including the table and fields to analyze, the type of analysis to perform, any options within the analysis type, the output table(s), and the description. A scenario does not, however, store the data that you analyze — the latest data is always used when you run a scenario. Since results are not saved within your scenarios, you can easily repeat an analysis on updated tables, modify your analysis, or share analyses among users.

Programmatic Interface provides more information on how scenarios are saved and how to run them within your applications.


Calculations

Total SQL Statistics uses double precision (15 digits of accuracy) for all calculations. Results are placed in tables, which show all decimal places for each number. Probability values (0 to 100%) are presented as decimal values between 0 and 1 (i.e. 48% is 0.48).


Scenario Selection (Main Form)

Scenario Designer Main Form

When you open Total SQL Statistics, the Scenario Designer main form appears. The analysis tabs across the top determine the list of scenarios displayed. The analysis types are organized into Parametric, Group, and Non-Parametric options. When you select one of these three options, the corresponding sub-analysis types are displayed on the left side of the page. If you select one of these options (Describe, Frequency, Percentiles, etc.), only scenarios of that particular type are shown. Select All to show all scenarios of the selected analysis type.

Click on any column header to sort the scenarios.

Scenario Buttons

The following buttons are available from the main form:

Button Name | Description
New | Create a new scenario.
Edit | Edit the currently selected scenario.
Duplicate | Copy the selected scenario.
Delete | Delete the selected scenario.
Run | Run the selected scenario.

As a shortcut, use the button’s hot key by pressing [Alt] and the underlined hot key of the button.

Scenario Popup Menu

In addition to the buttons, you may right-click on a scenario in the list and choose from one of these context menu choices:

Context Menu

Menu Item | Description
Run | Run the scenario.
Edit Fields | Edit the scenario’s field selections (same as the [Edit] button).
Edit Options | Edit the scenario’s option information (same as [Edit] button, but skips the Field selection screen).
Edit Output | Edit the scenario’s output table names (same as the [Edit] button, but skips the Field and Options screens).
Duplicate | Copy the scenario.
Delete | Delete the scenario.
View Data Table | View the table analyzed by the scenario.
View Output Table 1 | Display the output table, if it exists. Note that this may not be for this scenario if other scenarios have the same output table name.
View Output Table 2 | Display the second output table, if it exists. Note that this may not be for this scenario if other scenarios have the same output table name.
Generate Code | Automatically generate the code required to call the selected scenario programmatically.
Help | Display the help file.
Using an Existing Scenario

To use an existing scenario, click to select it. For instance, highlighting a scenario and pressing the [Run] button executes it and generates the output table(s). To examine a scenario you can press [Edit] to see and edit all of its settings, or use the right mouse button’s menu to jump to a particular edit screen.


Creating a New Scenario

To create a new scenario, you can copy and edit an existing one, or press the [New] button to display this screen:

Select the Analysis Type for a New Scenario

Choose the type of scenario to create and press the [Next] button to go to the Select Source Object screen:

New Data Source Selection

The tabs across the top allow you to select from a list of tables, views, stored procedures, or user defined functions. Highlight the object to analyze and press [Next>]. Press [<Back] to return to the previous screen.

See also: Using Stored Procedures and User Defined Functions.


Using Stored Procedures and User Defined Functions

Whether you select [New] or [Edit], the Scenario Designer takes you to the field selection screen shown below. However, selecting a stored procedure or a user-defined function introduces a couple of intermediate steps.

In order to get a list of fields returned by a stored procedure or user defined function, the object must first be executed. Since we cannot determine exactly what these objects may do when executed, you should always examine them before execution to determine that they are safe and will not alter or delete any data on your system.

Warning: You must review the text of the stored procedure or user-defined function and verify that it will not cause data loss or alteration before continuing.

The following screen allows you to review the object’s T-SQL code and confirm that it is safe to execute:

Execution Warning and T-SQL of Data Source

Selecting [Yes] will execute the object if it requires no input parameters. If any parameters are required for execution, you are prompted to enter them with the following screen:

Parameters Dialog

Enter all required parameters in the Value column. If you want to use Null, simply enter the word "NULL" (without the quotes).

When you click [OK], the stored procedure or user-defined function is executed, and the list of fields is available (as shown in the Field Selection section below).


Field Selection

The Field Selection screen is divided into two parts. The left side allows you to enter a description for this scenario, change the data source to analyze, and view the list of available fields in the selected data source. The right side of the form displays the fields selected for analysis and how they are assigned. To assign a field, highlight it on the left, and press one of the [image] buttons on the right.

Scenario Designer’s Field Selection Form

By default, the list of fields is displayed in the order that they appear in the table. Use the Sort options to change the order in which the fields display.

More:

Field Types to Specify

Field Type Overview

Selecting Fields for Analysis

Reordering Fields

Removing Fields from Analysis

Finishing

Canceling


Field Types to Specify

The right side of the form varies depending on the analysis type selected, but there can be as many as five field types:

Group Fields

You can optionally select one or more Group fields to define unique sets of records for analysis. This is similar to "Group By" in T-SQL. When you choose a Group field in Total SQL Statistics, the output has a separate record for each unique Group By field’s value. For every combination of unique values in the group fields, a separate analysis (output record) is generated. If no group fields are specified, every record in the table is analyzed as one set.

Independent (X) Fields

X fields are the number fields (independent variables) to analyze. You must select at least one X field.

Dependent (Y) Field

When you select an analysis type involving field comparisons, you must assign a numeric value as the Y field. The Y field is the dependent variable that each X field is compared against.

Weighting (W) Field

The Weighting field (variable) is an optional numeric field that allows you to "Weight" each record. It is useful if your records contain summary information. For instance, if each record is a country with [Population] and [Average Age] fields, by assigning [Population] as the weight field and [Average Age] as the X Field, you can calculate the statistics for all the countries taking relative size into account. If the weight field is null or zero, the record is ignored.

Comparison Field

Use a comparison field when performing group analysis and non-parametric sample comparisons. The comparison field defines each sample set of records (e.g. a Sex field with Male and Female values). This field is explained in more detail in the Group Analysis.

For more information, see Field Selection.


Field Type Overview

All analysis types have Group, Independent (X), and Weighting field options. Analyses that require field comparisons (such as Regressions) have the Dependent (Y) field option. The Comparison field is used for group analysis.

Crosstabs have a completely different set of options: Row Fields, Column Field, Value Fields and Weighting Field.

For more information, see Field Selection.


Selecting Fields for Analysis

Button to assign a field

To assign a field to a particular type, highlight the field in the list, and press the right arrow button in the box where you want to assign the field. For instance, to assign the [State] field as a Group field, click to highlight "State" in the Fields list, and then click on the right arrow in the "Group Fields" box.

Hint: Double click on a field to automatically assign it. Numeric fields are assigned as X Fields, while other field types are assigned as Group fields.

For more information, see Field Selection.


Reordering Fields

Re-order field upward

Re-order field downward

For some fields such as Group and X fields, several fields may be assigned. By default, the fields are processed in the order you select them, but you can change this by using the up and down buttons. To move the selected field, simply highlight the field you want to move and click on one of the buttons.

For more information, see Field Selection.


Removing Fields from Analysis

Button to un-assign a field | To remove a selected field, highlight it and press the left arrow button, or double click on it.
Button to un-assign all fields in the category | You can remove all the fields of a particular type by pressing the large left arrow.

For more information, see Field Selection.


Finishing

When you have selected the fields to analyze, press the [Next] button. The next screen displays the analysis type options.

For more information, see Field Selection.


Canceling

If you decide not to proceed, press the [Back] button to cancel changes and return to the previous (main) form.

For more information, see Field Selection.


Ignoring Data

Total SQL Statistics automatically ignores null values during analysis. By default, all non-null values are used in the calculations, but there may be times when you want to ignore a specific value or range of values. Total SQL Statistics includes a powerful feature to do this from the [Specify Values to Ignore] button on the field selection form:

Ignore Values Form

Ignoring a Specific Value

In some situations, a dummy value is used rather than or in addition to nulls (for instance 999). This is useful when you want to designate that a value is unknown rather than not filled. In this instance, you want to keep the dummy value out of your calculations and treat it as a missing value. To specify a value to ignore, choose the option to "Ignore a Specific Value" and enter the value to ignore.

Ignoring a Range of Values

Alternatively, you may want to limit your analysis to a range of values. You can choose to ignore all values above a certain value, below a value, or both. Select the Ignore a Range of Values option and enter the values in the two fields. The values ignored are greater than or less than the values you enter. Values equal to the number you enter are not ignored, so if you want to ignore values greater than and equal to a value (e.g. 100), enter a value slightly smaller (99.999999).

Which Values are Ignored

When Ignore options are set, they are applied to the Independent (X) and Dependent (Y) fields. If a Comparison field is numeric it also respects the ignore options. The Weighting (W) field is affected if a Specific value is ignored, but ignoring a range of values does not affect weightings.

Values in Group fields are not affected. Group fields can be any field type and create separate records for each group in the output table. Calculations of each group are entirely separate, so there is no problem with Groups of invalid values. You can easily ignore the records you do not want in the output table.


Analysis Options

After you assign the fields to analyze, press the [Next>] button to display other analysis options.

The options available vary depending on the analysis type selected.

Describe Options

After choosing the options desired, press [Next] to continue. To go back to the field selection screen, press [<Back].

Check Boxes

Click on a check box to select the option, and click on it again to unselect it. Each check box is independent of other options.

Option Group Buttons

Options group buttons are linked to each other, and you can only select one option in the group.


Output Tables and Description

When you press [Next] on the analysis option screen, you will see the following screen:

Output Table Screen for Regressions with Residual Table Option Selected

Enter the names of the output table(s) to be generated. By default, each analysis type’s output table has a unique name. The table is created in your current database. You can change the name to any valid SQL Server table name.

Total SQL Statistics allows you the option to store the scenario’s output tables in temporary tables that are visible only to your login, and are automatically deleted when you disconnect from the server (or when you exit Total SQL Statistics). To do this, preface the table name with a single pound (#) sign.

You can optionally add a scenario description to help you remember details about the analysis. The description is saved with the scenario, and can be viewed from the main screen of the Scenario Designer. The description can also be entered in the Field Selection form.

When you finish, press [Save] to save the scenario and return to the main screen of the Scenario Designer, or press [Save and Run] to save and run this scenario. If the output table already exists, a message appears asking for permission to overwrite it.

If the scenario runs successfully, the output tables are created and displayed. Use these tables like any other SQL Server table: search, reformat fields, export, etc. You can also use report designers to create presentable reports. When you are finished examining the output, close the tables and return to the Scenario Designer for additional analysis.


Parametric Analysis Overview

The Parametric Analysis features are available when you select the [Parametric Analysis] tab from the main form:

Scenario Designer with Parametric Analysis Selected

Parametric Analysis is divided into seven types of analysis:

  • Describe (Field Descriptives)
    Analyze each independent (X) field separately. Functions include variance, skewness, mode, means, confidence intervals, and t Test vs. mean. Functions also include Frequency Distribution and Percentile calculations, which are available separately as well.

  • Frequency Distribution
    Bin data into ranges of values and determine the count, sum, and cumulative values for each independent field.

  • Percentiles
    Determine the percentile values for each independent field. Median, quartiles, quintiles, deciles, and individual percentiles can be calculated.

  • Compare (Field Comparisons)
    Compare multiple independent (X) fields against a comparison (Y) field to determine how closely they are related. Results include average difference, count of differences, correlation, and paired t-Test.

  • Matrix
    Perform analysis similar to Compare, but compare multiple fields against each other rather than specifying only one comparison (Y) field.

  • Regression
    Calculate simple, multiple, and polynomial regressions to find equation coefficients matching least squares fit.

  • Crosstab and Chi-Square
    Create cross-tabulations and Chi-Square analysis to determine how independent the column and row fields are.


Describe (Field Descriptives)

Describe lets you analyze all X fields you specify, creating the following statistical values:

  • Count, Missing, Mean.

  • Minimum, Maximum, Range.

  • Variance, Coefficient of Variance, Standard Deviation, Standard Error.

  • Sum, Sum Squared.

  • Geometric Mean, Harmonic Mean, Root Mean Square.

  • Skewness, Kurtosis.

  • Mode, Mode Count.

  • Confidence Intervals using t or Normal distribution.

  • t-Test versus Mean.

  • Percentiles (Median, Quartiles, Deciles).

  • Frequency distribution.

More:

Field Selection

Describe Options

Describe Output Table

Describe Output Fields


Field Selection

On the Field Selection form, you can select multiple Group and Weight fields in addition to choosing X fields. Separate calculations are made for each group (combination of unique Group fields values), and a separate record is created for each group and X field. See Field Selections for details about the Field Selection form.

Field Selection Form for Describe Analysis

After selecting fields, press [Next>], to display the Describe options.

See also: Describe (Field Descriptives)


Describe Options

Describe Options

When you select a check box, the corresponding fields are generated. When you finish, press [Next>] to specify the Output Tables and Description. To return to the field selection screen, press the [<Back] button and your changes are canceled.

See also: Describe (Field Descriptives)


Describe Output Table

Following is an example of an analysis of three X fields (Age, Weight, Cholesterol) grouped by State (not all output fields are shown):

More:

Describe Output Fields

Describe Output Fields: Minimum, Maximum, Range

Describe Output Fields: Variance Fields

Describe Output Fields: Sum, Sum Squared

Describe Output Fields: Means: Geometric, Harmonic, RMS

Describe Output Fields: Skewness, Kurtosis

Describe Output Fields: Mode, Mode Count

Describe Output Fields: Confidence Intervals

Describe Output Fields: t-Test vs. Mean

Describe Output Fields: Percentiles

Describe Output Fields: Frequency Distribution


Describe Output Fields

These fields are always created for the Describe analysis:

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
Count | The number of non-Null X field records (if Weight field specified, the sum of weightings).
Observations | The number of X field records (different from N). This field is only created if a Weight field is specified.
Missing | The number of blank (null) X field records.
Mean | The sample mean and an estimate of the population ("real") mean (µ).

The following sections list the fields that are created if its check box is selected:

Describe Output Fields: Minimum, Maximum, Range

Describe Output Fields: Variance Fields

Describe Output Fields: Sum, Sum Squared

Describe Output Fields: Means: Geometric, Harmonic, RMS

Describe Output Fields: Skewness, Kurtosis

Describe Output Fields: Mode, Mode Count

Describe Output Fields: Confidence Intervals

Describe Output Fields: t-Test vs. Mean

Describe Output Fields: Percentiles

Describe Output Fields: Frequency Distribution


Minimum, Maximum, Range

Minimum | The smallest value in X field.
Maximum | The largest value in X field.
Range | The range of values in field: = Maximum – Minimum

See also: Describe Output Table, Describe Output Fields.


Variance Fields

There is an option for calculating these values using an N-1 or N method. The variance divisor depends on the method selected. The N 1 method creates an "adjusted" (unbiased) sample variance. The N method is for a population variance and is equivalent to the VARP( ) function. The N 1 Method is preferred when the data represents a portion (sample) of the entire population. For large N (N > 30), both definitions result in similar numbers.

Variance | or Variance is a measure of the distribution of the data from its mean. It is a data point’s average squared difference from the sample mean. The formula depends on the method selected.
CoeffOf Variance | Coefficient of Variance This removes the effect of a larger variance due to larger means.
StdDeviation | The Standard Deviation is the square root of variance.
StdError | Standard Error of the Mean = Regardless of whether the population is normally distributed, the sample means [image] should be normally distributed. Standard error measures the standard deviation of [image] to the population mean. As sample size increases, [image] approaches population mean and standard error decreases.

See also: Describe Output Table, Describe Output Fields.


Sum, Sum Squared

Sum | Sum of X values
SumSquared | Sum of squared values
AdjSum Squared | Adjusted sum squared The mean squared value.

See also: Describe Output Table, Describe Output Fields.


Means: Geometric, Harmonic, RMS

Geometric Mean | Geometric Mean is used for a variable with a constant rate of change; that is, as the mean increases, so does its variance. A logarithmic transformation eliminates this effect. The geometric mean is undefined if any X value is zero or negative.
Harmonic Mean | Harmonic Mean The Harmonic Mean is the mean of a reciprocal transformation, and is often used to analyze ratios. It is undefined if any X value is zero or negative.
RMS | Root Mean Square = Root Mean Square, or quadratic mean, is often used in physical systems. It is the mean of the square transformation.

For positive values of X, H.M. G.M. RMS

See also: Describe Output Table, Describe Output Fields.


Skewness, Kurtosis

Skewness | Skewness measures the symmetry of the data. A normal distribution (bell curve) has zero skewness. If it has a longer tail to the right of the mean, it is skewed to the right and has positive skewness. Negative skewness indicates skew to the left.
SE_Skewness | Standard Error of Kurtosis depends on sample size, not data values.
Kurtosis | Kurtosis measures the peakedness of the data. A normally distributed sample has a Kurtosis equal to zero. A highly peaked distribution (leptokurtic) has positive kurtosis. A relatively flat (thick tailed) distribution (platykurtic) has negative kurtosis. A moderately peaked distribution is called mesokurtic.
SE_Kurtosis | Standard Error of Kurtosis (depends on sample size, not data values)

See also: Describe Output Table, Describe Output Fields.


Mode, Mode Count

Mode | The most common value. If a tie exists, there is no mode.
Mode Count | Mode Count is the occurrences of the Mode. If there is a tie, this value is shown, but Mode is blank.

See also: Describe Output Table, Describe Output Fields.


Confidence Intervals

Confidence intervals are used to estimate the population mean from sample data. The sample is assumed to contain sampling variability, so its mean is not necessarily the population mean. Confidence intervals estimate the value of the population mean with an expected degree of accuracy. For instance, one may describe the mean as having a 95% chance of being 52 ± 5, where 95% is the confidence interval, 52 is the sample mean, and 5 is the confidence interval band width. As the sample size increases, this band becomes smaller. Similarly, the band increases as the confidence interval (%) increases.

Intervals

There are two possible confidence intervals to select. You can either select the check box for the 95% interval or type in your own interval (between 1 and 99.999%). Type in whole numbers (e.g. type 50 for 50% not 0.5).

Two techniques may be used:

t-Test

The t-Test (sometimes called the Student’s t) is a distribution that changes with sample size. For small samples (N < 30), t Tests are considered the most robust test. For large samples (N > 30), t-Test yields results similar to the normal distribution test. The t-Test uses the sample standard deviation to determine the confidence interval. This always results in a wider interval than that of the Normal distribution option.

Normal

The normal distribution test provides a more accurate result, but requires knowledge of the standard deviation of the population. Enter this value in the Std. Dev. field. If you selected more than one X field, and each X field has a different standard deviation, you need to run a separate scenario for each X field.

These confidence interval fields are created:

Field Name | Description
n% T Band | The band width for t-Test, where n is the confidence interval. For Normal distribution the field is n% N Band.
n% Upper | The upper limit: = (mean + band)
n% Lower | The lower limit: = (mean - band)

See also: Describe Output Table, Describe Output Fields.


t-Test vs. Mean

This feature is sometimes called a one-group or one-variable t Test and is used to compare each X field to a hypothetical mean. The result indicates the probability that the sample has the hypothetical mean. Enter the mean to test in the field provided.

Both 1-tailed and 2-tailed probabilities are calculated. A 2-tailed test determines if the sample mean is significantly different from the hypothetical mean (greater than OR less than). A 1-tailed test is only concerned with the difference on one side (e.g. Is the sample mean significantly greater than zero?).

Field Name | Description
Population Mean | The population mean being tested (your input value).
t-Value | where N is the count of the group and s is the sample’s standard deviation using the N-1 method.
Prob (1-tail) | The statistical probability (0-1) that the sample mean is the same as the [Population Mean]. If the probability is too small (e.g. less than 1% or 5%), the hypothesis is rejected. Probability is calculated from the t-Value and degrees of freedom (N - 1).
Prob (2-tail) | The 2-tailed probability that the sample mean is the same as the [Population Mean]. This probability is used when there is no a priori reason to assume the sample mean will be greater than or less than the population mean.

See also: Describe Output Table, Describe Output Fields.


Frequency Distribution

Frequency distribution counts the number of records for each evenly spaced range of X field values. For instance, this feature can let you determine the number of records for each age group: 20 to 30, 30 to 40, etc. If you are interested in the frequency of individual values within a field (e.g. the response to a survey question where answers are 1 through 5), you should use the detail table of the non-parametric Chi-Square feature.

Frequency Distribution creates a field for each data interval and counts the number of occurrences in each interval. You can select two types of output: Count and Percent. Count is the number of occurrences, and Percent is the Count as a percentage of total occurrences in the group. These are the options:

Number of Intervals

Determines the number of frequency intervals (fields) to create.

Include in Each Interval

Determines to which group an interval "boundary" value belongs. Lower places the value in the lower limit of the interval (x <= N < z), while Upper makes the intervals x < N <= z. For example, with Lower, 20 is placed in the interval 20-30. Upper places the value in the interval 10-20.

Display Type

Count displays the number of occurrences in each interval. Cumulative displays the number of occurrences in each interval plus all the previous intervals. The Percent fields also reflect the choice selected.

Groupings

Specify the grouping intervals for calculating frequency. Default calculates an initial value and width interval to accommodate all the data points for the number of intervals specified. However, the intervals may not be the most desirable groupings since the interval width would rarely be a nice round number. If you select Specified, two fields appear, [Initial Value] and [Width], to let you specify the exact initial value (start of the first interval) and the interval width. By default, Total SQL Statistics creates two additional intervals for the count of items so that no records are skipped: less than the initial value and greater than the maximum interval.

The following frequency fields are created:

Field Name | Description
N <= x | The count of records (or weightings) with values less than or equal to x (first interval value). This field is created if Groupings = Specified.
y <= N < y + w | The count of records (or weightings) with values between y and y + w (width). There is one of these fields for each of the intervals.
z <= N | The count of records (or weightings) with values greater than z (last interval value). This field is created if Groupings = Specified.

For percentages, the field names use "%" rather than "N".

The Frequency Analysis Type

From the main Scenario Designer form, there is a Frequency feature separate from Describe. That Frequency feature has additional options and presents the results differently. Read the Frequency Distribution section for more information.

More:

Difference from Describe

Frequency Field Selection

Frequency Options

Frequency Output Table

Frequency Output Fields


Percentiles

To create Percentile calculations, select the Percentiles check box, and choose the type of Percentiles to calculate.

Percentiles are calculated by sorting the data from smallest to largest. The middle value is the median (50th percentile). Dividing into 4 groups gives quartiles (25th, 50th, and 75th percentiles), and 10 groups give deciles. This is the formula used to determine which record is selected:

Where N is the number of items, and Percentile is a number between 0 and 100. For instance, for a sample size of 13, the 25th, 50th, and 75th percentiles are 4, 7, 11. By definition, the 0th and 100th percentiles are the lowest and highest values.

If the percentile cutoffs do not coincide with a particular data point, a linear interpolation of the two closest points is used. If a weighting field is assigned, values are considered continuous and no interpolation is performed.

Median Option

Median

The median value.

Quartile Option

25 Percentile | The 1st Quartile.
50 Percentile | The Median.
75 Percentile | The 3rd Quartile.
Quartile Range | The 3rd Quartile minus the 1st Quartile.

Decile Option

10 Percentile
...
90 Percentile

Every decile: 10, 20, 30, etc.

Performance Hint
Calculating Mode and Percentiles requires sorting each X Field in each group separately. For large tables, select Mode or Percentiles only when needed.

Other Percentile Feature

There is a separate Percentiles feature outside this Describe section, which has some different calculations and output table format. See Percentiles for more details.

See also: Describe Output Table, Describe Output Fields.


Frequency Distribution

Frequency analysis determines the number of occurrences within each range of numeric values. If you want to determine the count of specific values rather than ranges, use the non-parametric analysis Chi-Square. The detail table of Chi-Square gives a result similar to the Frequency table.


Difference from Describe

The Frequency feature here differs from the frequency calculation in Describe in these ways:

  • The Frequency feature always calculates count, cumulative and percent of total and cumulative values. Describe requires you to choose count or cumulative.

  • Results are shown with each interval as a separate record with information for each interval shown in one record side-by-side. With Describe, results are in one record with each interval as a separate field, so examining an interval’s count and percent of total is more difficult.

  • The Frequency feature provides the option to calculate the sum and percent of sum.

See Parametric Analysis Overview for a list of all parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Frequency Field Selection

Frequency Options

Frequency Output Table


Frequency Field Selection

The Frequency field selection is identical to Describe field. Choose as many X fields as you want. Group and Weight fields are optional. Since the frequency intervals are identical for all the X fields, choose fields with similar values. For instance, using the same intervals for Age and Annual Income would be meaningless since the values are so different. Press [Next] to see the Frequency Options. See Field Selections for an overview of the Field Selections form.

See also: Frequency Distribution


Frequency Options

The options are identical to the frequency distribution options under Describe with the exception of the Calculate Sum of Values option. If you check this option, additional fields are created to show the sum and cumulative sum for each interval and their percent of total.

See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

See also: Frequency Distribution


Frequency Output Table

This example is an analysis of the Age field, grouped by State, with 5 intervals, lower option, specified initial value of 25, and interval width of 10. Since specified values were provided, two extra intervals before and after the range are added for a total of 7 intervals:

See also: Frequency Distribution

More:

Frequency Output Fields


Frequency Output Fields

The following fields are always created:

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The field name identifying the data in the record.
Interval | The interval number (1 to number of intervals).
Low<Range | The interval range’s low value. If Lower option is selected, the field is [Low<=Range].
Range<High | The interval range’s high value. If Upper option is selected, the field is [Range<=High].
Count | The number of records (or weightings) in an interval.
Count% | The percent of interval’s count to total count of group.
Cumulative | The sum of count for this and all previous intervals in group.
Cumulative% | The cumulative sum of percentages.

Sum of Values

If you check Calculate Sum of Values, the following additional fields are created:

Sum | The sum of X field values in each interval (adjusted for weightings if specified).
Sum% | The percent of interval’s sum to total sum for group.
CumulativeSum | The sum for this and all previous intervals in group.
Cumulative Sum% | The cumulative percentage of cumulative sum.

See also: Frequency Output Table


Percentiles

Percentile analysis determines the percentiles for each Group and X field you select. It uses the same formula as the Percentile calculation in the Describe section (explained Describe: Percentiles).

More:

Difference from Describe

Percentile Field Selection

Percentile Options

Percentile Output Table


Difference from Describe

The Percentile analysis type differs from the percentile calculation under Describe in these ways:

  • Results are shown with each percentile as a separate record. In Describe, all the results are in one record with each percentile as a separate field.

  • The minimum and maximum values are displayed (0th and 100th percentiles).

  • There is an option to calculate every percentile (0 through 100).

  • Quintiles can be calculated (every 20th percentile).

See Parametric Analysis Overview for a list of all parametric analysis types, and Analysis Types for a list of all analysis types available.

For more information on percentile analysis, see Percentiles.


Percentile Field Selection

The Percentile field selection is virtually identical to the field selection of Describe and Frequency. Choose as many X fields as you want. Group and Weight fields are optional. Unlike frequency intervals, percentiles are unique to each field, so it does not matter if the X fields are of similar values or not.

Press [Next] to go to the Percentile Options page.

See also: Percentiles


Percentile Options

You can generate the following percentile calculations:

  • Median (0, 50th, and 100th percentiles)

  • Quartiles (0, 25th, 50th, 75th, and 100th percentiles)

  • Quintiles (0, 20th, 40th, 60th, 80th, and 100th percentiles)

  • Deciles (0, 10th, 20th, 30th, through 100th percentiles)

  • Percentiles (0, 1st, 2nd, through 100th percentiles)

See also: Percentiles, Percentile Output Table


Percentile Output Table

This example shows decile calculations for the Age and Weight fields, grouped by State:

See also: Percentiles

More:

Percentile Output Fields


Percentile Output Fields

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The field name identifying the data in the record.
Percentile | The percentile number: a value between 0 and 100 depending on which percentile type was selected.
Value | The value corresponding to the percentile.

See also: Percentile Output Table


Compare (Field Comparisons)

Compare performs a record-by-record comparison between each X field and the specified Y field. If the Y field is null, the record is skipped. If the Y field exists and the X field is null, the record is skipped for that X field calculation, but not for other X fields that exist. The output table contains a record for each combination of group fields and X field.

See Parametric Analysis Overview for a list of all parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Compare Field Selection

Compare Options

Compare Output Table


Compare Field Selection

The Compare field selection screen differs from the previous screens with the addition of a Dependent Field (Y) option:

Compare Field Selection Options

One numeric field must be designated as the Y field. At least one X field must be assigned. The Group and Weight fields are optional. A separate calculation is made for each group of records (combination of unique Group fields values). A separate record is created for each group and X field. After you select your fields and press the [Next] button, the Compare options appear. See Field Selections for an overview of the Field Selections form.

See also: Compare (Field Comparisons)


Compare Options

You can create the following statistical values:

  • Count Difference: cases where X is less than, equal to, and greater than Y.

  • Mean Difference: difference and standard deviation of means.

  • Correlation: covariance, correlation, R-square.

  • Paired t-Test: degrees of freedom, t-value, and probability.

See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

See also: Compare (Field Comparisons), Compare Output Table


Compare Output Table

Following is an example of comparing Age and Weight against Cholesterol grouped by State (not all fields are shown):

See also: Compare (Field Comparisons)

More:

Compare Output Fields


Compare Output Fields

By default, the following fields are always created (formulas are adjusted for Weight field if specified):

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The field name identifying the data in the record.
Count of Y Field | The count of X field values (if Weight field specified, the sum of weightings) where X and Y are not blank. The Y field name is included for reference.
Observations | The number of X field records (different from N). Only created if Weight field specified.
Missing | The number of records with nulls in the X or Y fields.

Count Difference

Count < Y | Cases where X < Y.
Count = Y | Cases where X = Y.
Count > Y | Cases where X > Y.

Mean Difference

MeanX | The mean of the X field.
MeanY | The mean of the Y field.
MeanDiff | The mean difference between X and Y.
StdDevDiff | = The standard deviation of the difference between X and Y.

Correlation

Covariance | COV
Correlation | Pearson’s Correlation [image] , where [image] and [image] are the standard deviation of X and Y. Correlation measures the relationship between X and Y, and is a value between -1 and 1. 1 is an exact match, 1 a perfect negative correlation, and zero is no correlation.
R-Square | The square of correlation. R-Square is a value between 0 and 1.

Paired t-Test

The Paired t-Test determines if the X and Y fields have statistically similar means. Probabilities greater than 5% are usually accepted as statistically significant.

t-DF | Degrees of freedom = (N - 1).
Paired t-Value |
Prob (1-tail) | One-tailed probability (0-1) that X and Y have the same mean.
Prob (2-tail) | Two-tailed probability that X and Y have the same mean.

See also: Compare Output Table


Matrix

Matrix performs the same calculation as Compare but offers a quick way to look for correlations and other relationships between many fields. Compare compares several X fields to one Y field. Matrix compares all the X fields to each other.

See Parametric Analysis Overview for a list of all parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Matrix Field Selection

Matrix Options

Matrix Output Table


Matrix Field Selection

The Matrix field selection screen is identical to the Describe field selection screen. Choose the X fields to analyze. The matrix becomes larger as more X fields are selected — keep in mind that the number of calculations and amount of time needed increases with the square of the number of X fields [image]. See Field Selections for an overview of the Field Selections form.

See also: Matrix, Matrix Options


Matrix Options

Each selection creates a separate matrix comparing the fields. See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

See also: Matrix, Matrix Output Table


Matrix Output Table

The following example is a matrix of Age, Weight and Cholesterol grouped by State with Correlation, R-Square, t-Value and Prob (2-tail) options:

The [CalcType] field identifies the set of records for each matrix.

The correlation matrix is symmetrical (i.e. the correlation between Age and Weight is the same as Weight and Age). This is true for Count, Missing, Covariance, Correlation, R Square, Std. Dev. Difference, and Probability. Mean Difference and Paired t Value produce negatively symmetrical matrices (i.e. Mean X-Y of Age to Weight is the negative of Weight to Age). Definitions of these calculations are given in the Compare section.

See also: Matrix, Matrix Output Fields


Matrix Output Fields

The output table for Matrix always includes these fields, regardless of which options are selected:

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The field name identifying the data in the record.
CalcType | The type of calculation: Count Missing MeanDiff StdDevDiff Covariance Correlation R-Square t-Value Prob (1-tail) or Prob (2-tail)
X Field Names | The name of X Field. The value in this field is the CalcType comparison with the field in DataField.

See also: Matrix Output Table


Regression

Regression calculates the least squares (best fit) curve to determine an equation which relates the Y (dependent) variable to the X (independent) variable(s).

See Parametric Analysis Overview for a list of all parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Regression Field Selection

Regression Options

Regression Output Tables

Regression Failures

Regression Residual Table


Regression Field Selection

The field selection screen is identical to that for Compare . See Field Selections for an overview of the Field Selections screen. Press [Next] to go to the Regression Options form.

See also: Regression


Regression Options

Regression Type

Choose among the three regression types:

  • Simple
    where A and C are the constants that are solved. "A" is the slope of the line, "C" is the y-intercept. A linear (simple) regression is calculated between the Y field and each X field. Any record with a null X or Y field is ignored. One equation (record) is created for each set of group fields and X field.

  • Multiple
    where xi is the ith X field, and Ai and C are constants. A multiple regression of the linear combination of X fields to the Y field is calculated. All X fields are assumed to be independent variables. At least two X fields must be assigned; otherwise you should use simple regression. Any record with a blank X field or Y field is ignored. One equation (record) is created for each group of check fields.

  • Polynomial
    where N is the polynomial order, and Ai and C are constants. A polynomial regression between each X field and Y field is calculated. The polynomial order (up to 9) determines the maximum power (exponent) of X in the equation. Any record with a null X or Y field is ignored. One equation (record) is created for each set of group fields and X field.

Calculation Options

  • Calculate Y-Intercept
    This option controls whether the y-intercept (C constant) is calculated or set to zero (through the origin). If this option is checked, the y-intercept is calculated.

  • Calculate Regression ANOVA
    If this option is selected, each regression’s ANOVA values are calculated. The field names are described later.

  • Create Residual Table
    If this option is selected, a separate Residual table is created. The residual table contains one record for each data point used in the regression calculation along with residual information. The Residual is the difference between the actual Y and the regression’s estimated Y value.

See Analysis Options for an overview of the Options form.

When you finish, press [Next] to specify the Output Tables and Description.

See also: Regression, Regression Output Tables


Regression Output Tables

The following examples are different types of regressions for comparing X fields [Age] and [Weight] against Y Field [Cholesterol], grouped by [State] (not all fields shown):

Sample of Simple Regression

Example of Multiple Regression

Example of Polynomial Regression (Order 3)

See also: Regression, Regression Options

More:

Regression Output Fields


Regression Output Fields

Regardless of which options are selected, the output table for Regression always includes these fields:

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The field name identifying the data in the record.
Count of Y Field | The number of data points in group, where Y Field is the name of the Y field.
Missing | The missing number of records.
Multiple R | The square root of R-Square.
R-Square | R-Square measures the proportion of variation in Y that is explained by the regression. R-Square is between 0 and 1 (1 is a perfect fit).
Adj_R-Square | Adjusted R-Square
StdError_of_ Estimate | Standard error of the Y estimate (regression) is the standard deviation of the residuals.
Y-Intercept | The Y-Intercept value, if this option is selected.
Coef_of_X | The coefficient(s) of regression equation.

The regression coefficient fields vary depending on the type of regression selected:

  • Simple Regressions: one field for the X coefficient.

  • Multiple Regressions: one field for each X field:
    Coef_of_X1, Coef_of_X2, Coef_of_X3, etc.

  • Polynomial Regressions: one field for each polynomial order: Coef_of_X^1, Coef_of_X^2, Coef_of_X^3, etc.

See also: Regression, Regression Output Tables


Coefficient Analysis

In addition to the Y-Intercept and equation coefficients, four other fields are calculated for each coefficient to determine the accuracy and relative importance of each coefficient. The four fields are explained below with the field names in brackets:

Standard Error of Coefficients [SE_of_X]

Standard Error of Coefficients is a measure of each coefficient’s variability. Although the coefficient is calculated, the sample data is assumed to contain variability. The population coefficient is the sample coefficient plus or minus the standard error.

Beta Value of Coefficients [Beta_of_X]

Beta Value of Coefficients (also known as standardized regression coefficients) shows the relative importance of each coefficient in the regression equation. For multiple and polynomial regressions, it can be misleading to examine only the unstandardized coefficients for importance. A large coefficient may have little impact if its variable does not vary much. The Beta removes this effect. Beta is not calculated for the Y-Intercept.

where [image] is the coefficient of variable i
 is its standard deviation, and
is the standard deviation of Y

t-Value of Coefficients [t-Value_of_X]

The t-Value of the coefficient is used to determine (via t-Test) the probability that the coefficient value is significantly different from zero. t-Value is the ratio of the Coefficient to its Standard Error.

Probability of Coefficients [Prob_of_X]

Probability of coefficients determines the probability that the coefficient is zero based on its t Value.

See also: Regression, Regression Output Tables


Regression ANOVA

The regression ANOVA indicates how much of the data variance is due to the regression, and how much is not (residual). If the regression has a poor "fit" (large residual), the F Value is small and the probability that all regression coefficients are zero (the null hypothesis) approaches 1. These are the ANOVA fields:

Field Name | Description
Regression_DF | The regression degrees of freedom. The number of indepen dent variables.
Residual_DF | The residual degrees of freedom = N - DF1 - 1 where N is the number of data points.
Total_DF | The total degrees of freedom = N - 1
Regression_SS | The regression (estimated-average) sum of squares: =
Residual_SS | The residual (actual-estimated) sum of squares: =
Total_SS | The total sum of squares: = SS1 + SS2
Regression_MS | The regression mean squares: = SS1 / DF1
Residual_MS | The residual mean squares: = SS2 / DF2
F-Value | The ratio of mean squares: = MS1 / MS2
Prob | The probability (0-1) that all coefficients: = 0

See also: Regression, Regression Output Tables


Regression Failures

If regressions cannot be calculated, the output record only displays the group fields, count, and missing values, leaving all other fields blank. Although the regression is not possible for a particular group, other regressions are not affected and are calculated as usual. These are various reasons this may happen:

Insufficient Data

A minimum number of data points is required to calculate a regression. Regressions with Zero-Intercept require one fewer data point since the constant (Y-Intercept) is not calculated:

Minimum Number of Records
Regression Type | Y-Intercept | Zero-Intercept
Simple Multiple Polynomial | 3 X Fields + 2 Polynomial order + 2 | 2 X Fields + 1 Polynomial order + 1

X or Y has zero variance

If X or Y has no variance (all values are identical), a regression cannot be computed.

Multicollinearity

Regression calculations solve a matrix of equations. When the matrix is singular or near singular (multicollinear), the matrix inverse does not exist and a unique least squares solution cannot be found. This is the most common cause of a regression failure given a large and diverse data set. To avoid this problem for multiple regressions, exclude X fields that are highly correlated to Y. For polynomial regressions, reduce the polynomial order.

See also: Regression


Residual Table

If the Residual Table option is selected, a second table is created:

This table contains all data (X and Y) used in the regression plus the estimated Y value and residual. A record is created for every data point. Data that is not used in the regression (null values) or groups for which a regression could not be calculated are not included in the Residual table.

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The field name identifying the data in the record.
X Value | The value of the X field.
Y:Y Field | The actual Y value where Y Field is the Y field name.
Estimated Y | The estimated Y value using X and the regression equation.
Residual | The actual Y minus the estimated Y.

See also: Regression


Crosstab and Chi-Square

Cross-tabulations (also known as pivot tables) allow you to create matrices showing summaries of one field across two fields. Crosstabs compare the row field to the column field to display relationships between the values in the two fields quickly. Each unique value in the column field is a field in the crosstab. The determination of unique values is case insensitive (i.e. age is the same as Age).

Two output tables may be created: Cross-tabulation and Chi-Square. Chi-Square measures the independence of the two variables. If you are interested in Chi-Square for a single variable, refer to the Chi-Square feature under Non-Parametric Analysis.

See Parametric Analysis Overview for a list of all parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Crosstab Field Selection

Crosstab Options

Crosstab Output Table

Chi-Square Table


Crosstab Field Selection

The field selection screen for crosstabs is different from other types of analysis:

For a crosstab, three types of fields are required:

Row Fields

The row field(s) becomes the left column(s) of the crosstab. If you choose more than one row field, totals are calculated for the first R-1 row fields (where R is the number of row fields). Basically, a separate crosstab for each set of unique values in the first R-1 row fields.

Column Field

Specify only one column field. Each unique value in the column field becomes a field in the crosstab.

Value Fields

Specify the fields to be calculated (Sum, Count, etc.) and placed in each of the crosstab’s cells.

Weight Fields

As with other types of analysis, you can optionally specify a Weight field.

See Field Selections for an overview of the Field Selections form.

Press [Next] to see the Crosstab Options.

See also: Crosstab and Chi-Square


Crosstab Options

Cross-Tabulation Type

This specifies the type of calculation to perform. Refer to the Describe section for definitions of these functions.

If you select a type other than Count, the crosstab generated includes an extra row that shows the Count of each cell. For instance, for Standard Deviation, you would see both the standard deviation for each cell and the number of data points used to calculate the value.

Percentage Options

You may optionally calculate each cell’s value (based on the Cross-Tabulation Type) relative to its row, column, or total summaries. For percent of column and total, the cell is compared to the corresponding value for its subset of records (unique values in R-1 rows).

Chi-Square Options

Chi-Square options are available (visible) if the calculation type is Count or Sum. Chi-Square measures the independence of the distribution between the row and column fields.

There are several Chi-Square options. For the main crosstab table, you can add % of Expected values or the Expected value for each cell. Expected values are calculated for each subset. You can also create a separate Chi-Square table that calculates the Chi-Square for each crosstab (subset).

See Analysis Options for an overview of the Options form.

When you finish, press [Next] to specify the Output Tables and Description.

See also: Crosstab and Chi-Square, Crosstab Output Table, Chi-Square Table


Crosstab Output Table

This is a crosstab of the Sample table examining Sex vs. State, with the count of the Age field. This quickly shows how many people fall in each combination of Sex and State:

The [DataType] field shows each record’s calculation including the percent calculations and "GROUP Total" for the crosstab summary. There is also a [RowSummary] field.

See also: Crosstab and Chi-Square , Chi-Square Table

More:

Crosstab Fields


Crosstab Fields

Field Name | Description
DataField | The field name identifying the value field.
Row Fields | The row fields in the order you selected them.
DataType | The record identifier: Data: type Cell value where type is the calculation type (Count, Sum, Min, Max, etc.) % Row Cell’s percent of row summary % Col Cell’s percent of subset column % Total Cell’s percent of subset total % Expected Cell’s percent of expected value Expected Cell’s expected value using Chi-Square calculation GROUP ... Group (subset) summary: Total, %Row, %Col, % Total, etc.
RowSummary | A summary for the row. This is the summary of the input table data associated with the row and not the values in the crosstab. For instance, the row’s average is not the average of the crosstab’s cells.
Column Fields | A field is created for each unique value in the column field. This is case insensitive, i.e. "AAA" is the same as "aaa". If the column field contains a null value, a field named <Null> is created.

See also: Crosstab Output Table


Chi-Square Table

Chi-Square (also known as Pearson’s Chi-Square) is a test to determine if the variables in the crosstab row and column fields are independent. The results are placed in a separate table. One record is created for each subset (unique combination of the first R 1 fields, where R is the number of row fields). If there is only one row field, only one record is created per value field.

Chi-Square is only calculated if the Crosstab type is Count or Sum. If a Sum crosstab is selected, all values in the Value field must be positive. Chi-Square analysis of Sum crosstabs is intended for data that contains summed counts.

Sample Chi-Square Table

See also: Crosstab and Chi-Square, Crosstab Output Table

More:

Chi-Square Fields


Chi-Square Fields

Field Name | Description
DataField | The field name identifying the value field.
Row Fields | The first R-1 row fields in the order selected.
Count (N) | The number of records (or sum of weightings).
MinExpected | The minimum cell’s expected value.
MaxExpected | The maximum cell’s expected value.
Columns (C) | The number of columns with data (some subsets may have no values for a column).
Rows (R) | The number of rows with data.
DF | The degrees of freedom: = (C-1) x (R-1)
Chi-Square (2) | , sum over all the cells where O is the observed value and E the expected. A cell’s expected value is:
Prob | The probability that the rows and columns are independent. Based on 2 and DF
Phi-Coefficient | The Phi-Coefficient:
Coeff_of_ Contingency | The Coefficient of Contingency:
Cramer’s_V | , where k is the smaller of C and R. Same as Phi-Coefficient for 2 x 2 crosstabs.

See also: Chi-Square Table


Group Analysis Overview

The Group Analysis features are available by selecting the [Group Analysis] tab from the main form:

Scenario Designer with Group Analysis Selected

The three types of group analysis are listed on the left:

Two Sample t-Test

Compare two sets of records defined by a Comparison field containing only two values (e.g. Sex, Yes/No, etc.).

Analysis of Variance (ANOVA)

Analyze multiple sets of records defined by a Comparison field with multiple values (e.g. Race, State, etc.).

Two Way ANOVA

Analyze multiple groups of records where each record belongs to more than one group. Multiple Comparison fields with multiple values in each field define the groups. The impact of each Comparison field is shown.

See Analysis Types for a list of all analysis types available.


Two Sample t-Test

The Two-Sample t-Test compares two samples (sets of records) to determine if their means are identical. A Comparison field designates the samples (for example, Sex: Male vs. Female). This test is sometimes called an unpaired t-test because the data points come from different records (unlike the paired t-test in Compare and Matrix), and each sample generally has a different amount of data (records).

See Group Analysis Overview for a list of all group analysis types, and Analysis Types for a list of all analysis types available.

More:

Two Sample t-Test Field Selection

Two Sample t-Test Options

Two Sample t-Test Output Table

Two Sample t-Test Detail Table


Two Sample t-Test Field Selection

The field selection screen for the Two Sample t-Test has a Comparison field to designate the field defining the two samples to compare. A Comparison field must be assigned:

The example scenario above would perform a Two Sample t-Test to determine if the mean Age, Weight, and Cholesterol are the same between the sexes.

Note: The Two Sample t-Test should be used when there are only two values in the comparison field. It does not prevent you from using a comparison field with more than two values, and compares each set of records (as defined by a comparison field value) to every other set. If there are many samples, the number of pairings can be huge and will take time to calculate. If you have more than two comparison values, use ANOVA.

See Field Selections for an overview of the Field Selections form. Press [Next] to see the Two Sample t-Test Options.

See also: Two Sample t-Test


Two Sample t-Test Options

These options are identical for all Group Analysis including ANOVA and Two Way ANOVA.

See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

Ignore Blanks

Select this option to exclude records with blank (null) values in the Comparison field. For instance, a text field such as [Sex] can contain Male, Female, and blank values. If this option is not checked, the blank records are treated as another group and compared to Male and Female records. To prevent this, select this option. Records with blank X field values are automatically ignored.

Create Detail Table

Select this option to create a separate table that shows the basic information for each sample. Further details are provided below.

See also: Two Sample t-Test, Two Sample t-Test Output Table


Two Sample t-Test Output Table

The Two-Sample t-Test example above compares the means between Female and Male records (not all fields are shown):

More:

Two Sample t-Test Fields

Two Sample t-Test


Two Sample t-Test Fields

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The field name identifying the data in the record.
CompareField-1 | Sample #1’s comparison field value.
CompareField-2 | Sample #2’s comparison field value.
Count-1 | The count of non-blank records in Sample #1.
Count-2 | The count of non-blank records in Sample #2.
Mean-1 | The mean of values in Sample #1.
Mean-2 | The mean of values in Sample #2.
Mean 1-2 | The difference between Sample #1 and #2 mean.
StdDeviation-1 | The standard deviation of Sample #1.
StdDeviation-2 | The standard deviation of Sample #2.
StdError-1 | The standard error of Sample #1.
StdError-2 | The standard error of Sample #2.

Pooled t-Test

A pooled t-Test is generated with the fields below. Use these values if you assume the samples have the same variance.

DF-Pooled | The degrees of freedom: =
t-Value_Pooled |
Prob_(2-tail)_ Pooled | The probability (2-tailed) that the two samples have identical means.

Separate t-Test

A separate t-Test is also generated. Use these values if you assume the samples have different variances:

DF-Separate | Degrees of freedom =
t-Value_Separate |
Prob_(2-tail)_ Separate | The probability (2-tailed) that the two samples have identical means.

To decide which t-test values to use, you need to analyze the population variance between the two samples. The Detail table can help you by providing the variance for each sample. If the variances can be assumed to be similar, the pooled t-test can be used; otherwise, the separate t-test is appropriate.

See also: Two Sample t-Test, Two Sample t-Test Output Table


Two Sample t-Test Detail Table

If you select the option to create a detail table, this is the result:

This table gives detailed information on each sample and is similar to the data you can create under the Describe option with the Comparison field as a Group field.

Field Name | Description
Group Fields | The group fields selected (if any).
Compare Field | The Comparison field value which is summarized where Compare Field is the compare field name.
DataField | The X field name identifying the data in the record.
Count | The count of X field values (if Weight field specified, the sum of weightings).
Mean | The sample mean.
Minimum | The minimum value.
Maximum | The maximum value.
Range | The difference between maximum and minimum.
Sum | The sum of the X field.
Variance | The variance using N-1 method.
CoeffOf Variance | The coefficient of variance.
StdDeviation | The standard deviation.
StdError | The standard error.

For more information on the calculation of these fields, refer to the Describe section.

See also: Two Sample t-Test, Two Sample t-Test Output Table


Analysis of Variance (ANOVA)

Analysis of variance (ANOVA) analyzes variance between and within multiple groups of records. The assumption is that all the groups come from populations with the same mean. Based on group sizes and variances, ANOVA determines if that assumption is true. A Comparison field defines the groups. One record is created for each X field. The field selection is identical to that for a Two Sample t-Test.

See Group Analysis Overview for a list of all group analysis types, and Analysis Types for a list of all analysis types available.

More:

ANOVA Options

ANOVA Output Table

ANOVA Output Fields

ANOVA Detail Table


ANOVA Options

The ANOVA options are identical to the Two Sample t-Test options. Select whether you want to include all groups or only records with non-blank values in the Comparison field.

See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

See also: Analysis of Variance (ANOVA), ANOVA Output Table


ANOVA Output Table

The following table is an example of an ANOVA analyzing Age, Weight, and Cholesterol grouped by State:

Notice how each of the three ANOVA calculations is displayed in three records.

See also: Analysis of Variance (ANOVA)

More:

ANOVA Output Fields


ANOVA Output Fields

The ANOVA table contains the following fields:

Field Name

Description

Group Fields

The group fields selected (if any).

DataField

The X field name identifying the data in the record.

Index

The ID number for each record in the ANOVA.

Source_of_
Variation

The record identifier has these values:

  • Main: Field
    where Field is the name of the Comparison field. The Main variation is the variation "Between Groups."

  • Residual
    Variation "Within Groups."

  • Total
    Total variation of the X field.

Sum_of_
Squares

Sum of squares, depending on type of variation

  • Main: Field
    The sum of squares between groups:

  • Residual
    The sum of squares within groups:

  • Total
    The sum of Main and Residual squares:

DF

The degrees of freedom:

  • Main: Field

  • Residual

  • Total

Mean_Square

The mean squares:
[Sum_of_Squares] divided by [DF] fields.

F-Value

The F-value: the ratio between Main and Residual Mean_Squares.

Prob

The probability that all groups have the same mean. The probability is the F Value with DFM and DFR degrees of freedom.

See also: ANOVA Output Table


ANOVA Detail Table

The Detail table is identical to the Detail table for the Two Sample t-Test.

See also: Analysis of Variance (ANOVA)


Two Way ANOVA

Two way ANOVA, also known as two-factor ANOVA, is a generalization of the one way or one factor ANOVA, and allows analysis of multiple Comparison fields.

See Group Analysis Overview for a list of all group analysis types, and Analysis Types for a list of all analysis types available.

More:

Two Way ANOVA Field Selection

Two Way ANOVA Options

Two Way A NOVA Output Table

Two Way ANOVA Detail Table


Two Way ANOVA Field Selection

The field selection screen for Two Way ANOVA is similar to the other Group Analysis field selections, except two Compare Fields (factors) may be selected. The Compare Fields determine the groups to compare:

See Field Selections for an overview of the Field Selections form.

Press [Next] to see the Two Way ANOVA Options.

See also: Two Way ANOVA


Two Way ANOVA Options

The Two Way ANOVA options are identical to the other Group Analysis options. You may choose to include all groups or only those with non-blank values in all Compare fields.

See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description .

See also: Two Way ANOVA, Two Way ANOVA Output Table


Two Way ANOVA Output Table

Two Way ANOVA is calculated using the Experimental method. The output fields are identical to those for ANOVA. There is a record measuring the total Main Effects, each main effect (factor) separately, the Interaction between the factors, the total Explained effect (sum of Main and Interactions), Residual and Total. Note that the total Main Effects is not the sum of the factors.

See also: Two Way ANOVA

More:

Two Way ANOVA Output Fields


Two Way ANOVA Output Fields

The Two Way ANOVA table contains the same fields as the ANOVA table. The only difference is the extra record for each Compare Field. The [Prob] field shows the probability of variation due to each Compare field.

See also: Two Way ANOVA Output Table


Two Way ANOVA Detail Table

The Detail table is similar to the Detail table for the Two Sample t-Test, but it has two additional fields: Compare_Field and Compare_Value. These fields show the record’s Compare field name and value:

See also: Two Way ANOVA, Two Way ANOVA Output Table


Non-Parametric Analysis Overview

Parametric calculations are necessary for analyzing data that is non-continuous and/or non-normally distributed. Because these assumptions cannot be made, these tests are less powerful than parametric analysis. Non-parametric tests allow examination of data independent of the population distribution.

Rather than direct comparisons of the values in your fields, many non-parametric analyses rely on their relative rank. These features are available by selecting the [Non-Parametric Analysis] tab from the main form:

Scenario Designer with Non-Parametric Analysis Selected

A wide variety of non-parametric tests is available and depends on the analysis you choose. There are single field (one sample), group comparisons, and paired fields analysis.

The first three options are for one sample tests:

  • Chi-Square
    One sample Chi-Square: evaluates expected value for each unique value in a field.

  • Sign Test
    One sample sign test versus median, mean or user defined value.

  • K-S Fit
    Goodness of fit tests for comparison against Uniform, Normal and Poisson distributions.

The following options compare groups of records:

  • 2 Sample
    Two sample tests: Wald-Wolfowitz Runs Test, Mann-Whitney U Test, and Kolmogorov-Smirnov.

  • N Sample
    Kruskal-Wallis one way ANOVA.

The following options compare fields:

  • Paired Field
    Field comparisons: paired sign test, Wilcoxon Signed Rank, Spearman’s Correlation Coefficient.

  • N-Field
    Compare multiple fields with Friedman’s two way ANOVA.


Chi-Square

Chi-Square measures the number of occurrences of each value in a field and determines how randomly (evenly) the values are distributed. Unlike the Frequency Distribution feature in Parametric analysis, which counts occurrences for ranges of numeric values, Chi-Square counts each value of any field type (rather than the range).

See Non-Parametric Analysis Overview for a list of all non-parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Chi-Square Field Selection

Chi-Square Options

Chi-Square Output Table

Chi-Square Detail Table


Chi-Square Field Selection

The following field selection screen appears for Chi-Square:

Assign the fields to analyze to the Compare Fields. Each Compare Field is analyzed within each group (unique combination of group fields). You must specify at least one Compare field, and you can optionally select a Weight Field. See Field Selections for an overview of the Field Selections form.

Press [Next] to view the Chi-Square Options.

See also: Chi-Square


Chi-Square Options

After selecting the fields, the Chi-Square options are presented:

Same Comparison Values for Each Group

This option is only necessary if you specify group fields. Every combination of unique values in the group fields defines a group of records that is analyzed separately. A Compare field may have many values, but some groups may not have all of them. In this situation, you may analyze each group with only its Compare field values, or use the same Compare field values for each group. If you have the check box selected, the unique values in the Compare field are determined regardless of group. Then the values for each compare value in each group are counted and all results (including zeros) are presented.

Ignore Records with Blank Value in Comparison Field

Select this option to ignore blanks values (nulls) in the Compare field. This affects the Chi-Square analysis by eliminating Null as a degree of freedom.

Create Detail Table

The detail table shows the actual count and percent of total values for each comparison field value. This is explained in detail below, and is similar to the frequency distribution feature in the parametric analysis.

See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

See also: Chi-Square, Chi-Square Output Table


Chi-Square Output Table

Chi-Square analysis is performed for each group and X field:

See also: Chi-Square

More:

Chi-Square Output Fields


Chi-Square Output Fields

Chi-Square analysis determines the distribution of the data for each Compare field value. Following traditional statistical terminology, each Compare field value forms a "Group." This is different from the groups formed by the Group fields, and is similar to the groups defined in the Group Analysis feature. Therefore, other than the Group Fields, the groups below refer to groups formed by the Compare fields.

Chi-Square determines the number of groups and calculates the expected value or frequency for each group (total count divided by number of groups). The actual (observed) value is compared. The larger the difference, the larger Chi-Square becomes and the more likely the Compare field values are independent.

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The compare field name.
Count | The number of records (or weighting).
Groups | The number of groups (unique Compare field values).
Expected | The expected value for each group: E = N / Groups
Chi-Square | The Chi-Square value: This is a sum over all the groups where O is the observed value and E the expected.
DF | The degrees of freedom: = Groups – 1
Prob | The probability of Chi-Square based on DF. A large probability indicates distribution is close to expected.

See also: Chi-Square Output Table


Chi-Square Detail Table

The Chi-Square detail table is only created if you select the option, and provides information for each Compare field value used in the Chi-Square analysis. This table is similar to the Frequency feature of parametric analysis (not all fields are shown):

The detail table also shows the distribution percent for each compare field within each group of Group fields:

Field Name | Description
Group Fields | The group fields selected (if any).
Compare_Field | The compare field name.
Compare_Value | The compare field value.
Observed | The count of occurrences of the compare field value.
Expected | The expected value for this group (same as in Chi-Square table).
Difference | The difference between Observed and Expected values: = O - E
Chi-Square | The Chi-Square value:
Observed_Pcnt | The observed percent of total in group: = O / Total
Expected_Pcnt | The expected percent of total (same value for all Compare values in group).
Pcnt_Diff | The difference between Observed and Expected percentages.
Cumulative | The cumulative sum of observed values.
Cumulative_Pcnt | The cumulative sum of observed values as percent of group total.

If only one Compare field is selected, the [Compare_Field] and [Compare_Value] fields are replaced by just the Compare field using the Compare field name and containing the Compare field values.

See also: Chi-Square


Sign Test — One Sample

Given a sample distribution, the one sample Sign test statistically measures whether the median of a population is above or below a test value provided. Total SQL Statistics allows you to test the sample data’s mean or median, or your own custom value. This test should not be confused with the paired Sign test, which compares means between two fields.

See Non-Parametric Analysis Overview for a list of all non-parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Sign Test Field Selection

Sign Test Options

Sign Test Output Table


Sign Test Field Selection

Sign test field selection is similar to parametric field selections. Specify the numeric fields to analyze as the Independent (X) fields, and optionally specify Group and Weight fields. See Field Selections for an overview of the Field Selections form.

Press [Next] to see the Sign Test Options.

See also: Sign Test — One Sample


Sign Test Options

Mean and Median options test either each group’s mean and median, or the entire data set’s mean and median if no group fields are specified. If you select Custom, you can enter the value to test all the groups.

See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

See also: Sign Test — One Sample, Sign Test Output Table


Sign Test Output Table

A separate record for each selected test appears in the Sign test output table. In the example below, there are three records for each group and X field (Group by State, X field = Age). Each test is identified by the [TestType] field with the value tested in [TestValue] (not all fields shown):

More:

Sign Test Output Fields


Sign Test Output Fields

Based on the observed number of cases less than, equal to, and greater than the test value, the probability of the true (population) median being less than or greater than the test value is determined:

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
Count | The number of records in group.
Missing | The number of blank (missing) records.
TestType | "Mean", "Median" or "Custom" depending on the options selected.
TestValue | The numeric value associated with TestType. The analysis tests the probability of the population median above or below this value.
Count<Test | The count of records with values below the test value.
Count=Test | The count of records with equal to the test value.
Count>Test | The count of records with values above the test value.
Prob<=Test | The probability the median is less than or equal to the test value.
Prob>=Test | The probability the median is greater than or equal to the test value.

See also: Sign Test Output Table


K-S Fit — Kolmogorov-Smirnov Goodness of Fit Test

Kolmogorov-Smirnov Goodness of Fit tests determine how closely data fits a uniform, normal, or Poisson distribution. If you want to determine if two sets of records are distributed similarly, use the Kolmogorov-Smirnov option under the 2 Sample feature.

See Non-Parametric Analysis Overview for a list of all non-parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

K-S Fit Field Selection

K-S Fit Options

K-S Fit Output Table


K-S Fit Field Selection

The field selection is identical to the Sign Test Field Selection. Specify the numeric fields to test as X fields. See Field Selections for an overview of the Field Selections form.

Press [Next] to see the K-S Fit Options

See also: K-S Fit — Kolmogorov-Smirnov Goodness of Fit Test


K-S Fit Options

The Kolmogorov-Smirnov Goodness of Fit test sorts your data in ascending order. Based on your data, a "perfect" distribution of each test is created, and the difference between the data and test distributions is calculated (between 0 and 1). The maximum difference is used to determine how closely the data fits the distribution being tested. If there is no difference, the data fits the test type exactly. The larger the difference, the less likely the fit. Choose the type of distribution to test:

Uniform

Data is distributed linearly (straight line).

Normal

Data is normally distributed (bell curve).

Poisson

A Poisson distribution is given by:

Since the Poisson calculation involves a factorial calculation (x!), it takes a longer time to calculate for large values of x. Only select Poisson calculations for small X values (less than 1000) or small data sets.

See Analysis Options for an overview of the Options form.

When you finish, press [Next] to specify the Output Tables and Description.

See also: K-S Fit — Kolmogorov-Smirnov Goodness of Fit Test, K-S Fit Output Table


K-S Fit Output Table

The output table has a separate record for each distribution type selected. In the example below, there are three records for each group. Each test is identified by the [TestType] field. Not all fields are shown:

More:

K-S Fit Output Fields

K-S Fit — Kolmogorov-Smirnov Goodness of Fit Test


K-S Fit Output Fields

The first set of fields is independent of test type (most not shown in the output table example):

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
Count | The number of records in the group.
Mean | The mean value.
StdDeviation | The standard deviation.
Minimum | The minimum value.
Maximum | The maximum value.
Range | The range of values: Maximum - Minimum

These fields are based on the test type:

TestType | The test type selected: "Uniform", "Normal", or "Poisson"
Max_Absolute | The maximum absolute difference between the data and test distribution (between 0 to 1).
Max_Positive | The maximum positive difference between the tests.
Max_Negative | The maximum negative difference between the tests.
K-S_Z | The Kolmogorov-Smirnov Z value based on [Max_Absolute] value.
Prob | The probability that the data matches the distribution test type.

See also: K-S Fit Output Table


2 Sample

The two sample non-parametric test compares two sets of records that may be of different size. A Compare field containing two values designates the samples. Tests include:

  • Wald-Wolfowitz Runs test.

  • Mann-Whitney U test.

  • Kolmogorov-Smirnov Two Sample test.

See Non-Parametric Analysis Overview for a list of all non-parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Two Sample Field Selection

Two Sample Options

Two Sample: Wald-Wolfowitz Runs Output Table

Two Sample: Mann-Whitney U Test and Kolmogorov-Smirnov Output Table


Two Sample Field Selection

The Two Sample field selection screen is similar to that of Two Sample t Test:

The Comparison field can be any field type, and it defines the two samples. The Dependent field must be numeric, and it sorts the data.

Press [Next] to see the Two Sample Options .

See also: 2 Sample


Two Sample Options

Two options are available:

  • Wald-Wolfowitz Runs test.

  • Mann-Whitney U test and
    Kolmogorov-Smirnov Two Sample test.

See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

See also:

2 Sample

Two Sample: Wald-Wolfowitz Runs Output Table

Two Sample: Mann-Whitney U Test and Kolmogorov-Smirnov Output Table


Wald-Wolfowitz Runs Output Table

The Wald-Wolfowitz Runs test determines if data is randomly distributed between two samples (Comparison field values), using the run theory to determine randomness. A run is a series of cases from the same sample.

With the X field sorted in ascending order, a random distribution would expect a random number of runs; that is, groups distributed independent of the values (i.e. a few cases from one sample followed by a few cases from the other, and so on).

A low number of runs indicates no randomness (all the low values are of one sample and the high values for the other). Similarly, a very high number of runs (e.g. every other case is a different run) also indicates a lack of randomness since the distribution is so orderly. Both the minimum and maximum number of runs is determined, and the associated Z and probability values calculated. For a random distribution, the number of runs should neither be too high or too low.

See also:

Two Sample: Mann-Whitney U Test and Kolmogorov-Smirnov Output Table

2 Sample

More:

Two Sample: Wald-Wolfowitz Runs Output Fields


Wald-Wolfowitz Runs Output Fields

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
CompareField-1 | The compare field sample #1 value.
CompareField-2 | The compare field sample #2 value.
Count | The number of records in the sample:
Missing | The number of blank (missing) records.
Cases-1 | The number of records in sample #1 (defined by CompareField-1 field).
Cases-2 | The number of records in sample #2 (defined by CompareField-2 field).
Wald-Mean | The mean of sampling distribution:
Wald-StdDev | The standard deviation of sampling distribution:
Ties | The number of records tied to other records.
TiedGroups | The number of X field values with more than one record.
Wald_Min_Runs | The minimum number of runs.
Wald_Min_Z | The Z value associated with the minimum runs: where R is the minimum number of runs. If N < 50, the numerator of Z is adjusted: if R < [image] , 0.5 is added if R > [image] , 0.5 is subtracted
Wald_Min_Prob | The probability of [Wald_Min_Z].
Wald_Max_Runs | The maximum number of runs.
Wald_Max_Z | The Z value associated with the maximum runs.
Wald_Max_Prob | The probability of [Wald_Max_Z].

See also: Two Sample: Wald-Wolfowitz Runs Output Table


Mann-Whitney U Test and Kolmogorov-Smirnov Output Table

Both of these tests sort the X fields and rank the values with average rank assigned to ties. The ranks for each of the two groups are then summed and compared.

See also:

2 Sample

Two Sample: Wald-Wolfowitz Runs Output Fields

More:

Two Sample: Mann-Whitney U and K-S Output Fields


Mann-Whitney U and K-S Output Fields

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
CompareField-1 | The compare field sample #1 value.
CompareField-2 | The compare field sample #2 value.
Count | The number of records in the group.
Missing | The number of blank (missing) records.
Cases-1 | The number of records in sample #1 (defined by CompareField-1 field).
Cases-2 | The number of records in sample #2 (defined by CompareField-2 field).
SumRank-1 | The sum of ranks for sample #1.
SumRank-2 | The sum of ranks for sample #2.
TotalRank | The sum of all ranks:
MeanRank-1 | The mean of ranks for sample #1:
MeanRank-2 | The mean of ranks for sample #2:
Ties | The number of records tied to other records.
TiedGroups | The number of X field values with more than one record.
SumTies | The sum of ties: where t is the number of tied records in each tied group (identical X value). The summation is over all tied groups.

See also: Two Sample: Mann-Whitney U Test and Kolmogorov-Smirnov Output Table


Mann-Whitney U Tests Fields

The Mann-Whitney U Test, sometimes just called the U Test or Wilcoxon test, determines whether or not there is a statistical difference between two data samples. The closer the rank sums between the two samples, the more likely the samples are similar. This test is similar to the parametric Two Sample t Test.

MW_U | The Mann-Whitney U value based on smaller rank sum. For R1 < R2 this is U: otherwise:
MW_U_Prime | The Mann-Whitney U prime value is the same as U for the larger Runs:
MW_W | The rank sum of the sample with the larger count.
MW_Z |
MW_Prob | The probability that the two samples are distributed identically.
MW_Z_(ties) | The Z adjusted for ties:
MW_Prob_(ties) | The probability that the two samples are distributed identically accounting for ties.

See also: Two Sample: Mann-Whitney U Test and Kolmogorov-Smirnov Output Table


Kolmogorov-Smirnov Fields

From the sorted data, the Kolmogorov-Smirnov two sample test determines the difference between the relative rankings of the two samples. Similar to the K-S Fit tests, this test determines if the two samples are distributed similarly.

KS_MaxAbsoluteDiff | The maximum absolute difference (0 to 1) between the two distributions.
KS_MaxNegDiff | The maximum negative difference.
KS_MaxPosDiff | The maximum positive difference.
KS_Z |
KS_Prob | The probability that the two samples have similarly shaped distributions.

See also: Two Sample: Mann-Whitney U Test and Kolmogorov-Smirnov Output Table


N Sample — Kruskal-Wallis One Way Analysis of Variance

N Sample is a generalization of the Mann-Whitney U (2 sample) test for comparing multiple (N) sets of records. Each set may be a different size (number of records). This is similar to the ANOVA feature under Group Analysis. For non-parametric analysis, the equivalent function is the Kruskal-Wallis H test (or just H test).

Kruskal-Wallis sorts and ranks the data across all the samples. The sum of rankings for each group is calculated and compared to determine if all the samples come from the same population.

See Non-Parametric Analysis Overview for a list of all non-parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

N Sample — Kruskal-Wallis Field Selection

N Sample — Kruskal-Wallis Options

N Sample — Kruskal-Wallis Output Table

N Sample — Kruskal-Wallis Detail Table


Kruskal-Wallis Field Selection

The field selection screen for N Sample analysis is identical to the 2 Sample screen. Rather than a Compare field with only 2 values, select a Compare field with more than 2 values. See Field Selections for an overview of the Field Selections form.

Press [Next] to see the N Sample — Kruskal-Wallis Options.

See also: N Sample — Kruskal-Wallis One Way Analysis of Variance


Kruskal-Wallis Options

There is an option to create a detail table that provides rank sum information for each group. See Analysis Options for an overview of the Options form.

When you finished, press [Next] to specify the Output Tables and Description.

See also:

N Sample — Kruskal-Wallis One Way Analysis of Variance

N Sample — Kruskal-Wallis Output Table


Kruskal-Wallis Output Table

Like the Mann-Whitney U Test, Kruskal-Wallis sorts and ranks the X fields across all the samples. Average rank is assigned to tied cases. Ranks are summed for each sample (group).

See also:

N Sample — Kruskal-Wallis One Way Analysis of Variance

N Sample — Kruskal-Wallis Detail Table

More:

N Sample — Kruskal-Wallis Output Fields


Kruskal-Wallis Output Fields

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
Count | The number of records.
Missing | The number of blank (missing) records.
Groups | The number of unique comparison field values.
Ties | The number of records tied to other records.
TiedGroups | The number of X field values with more than one record.
SumTies | The sum of ties: , where t is the number of tied records in each tied group. The summation is over all tied groups.
H | The H test statistic:
Prob | The probability that the sample groups are similar. The calculation uses H in a Chi-Square distribution with k-1 degrees of freedom.
H_(ties) | The H adjusted for ties:
Prob_(ties) | The probability based on [H_ties].

See also: N Sample — Kruskal-Wallis Output Table


Kruskal-Wallis Detail Table

The detail table shows the size (count), sum of ranks, and mean rank for each sample. This is the data used for calculating the Kruskal-Wallis results.

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
Count | The number of records.
SumRanks | The sum of rankings for this group.
MeanRank | The mean rank for the records in this group:

See also: N Sample — Kruskal-Wallis One Way Analysis of Variance


Paired Fields

The non-parametric Paired fields option allows you to compare fields similar to the parametric Compare feature. Tests include:

  • Paired Sign test.

  • Wilcoxon Signed Rank test
    (or Wilcoxon Matched Pairs).

  • Spearman’s Rank Correlation Coefficient.

See Non-Parametric Analysis Overview for a list of all non-parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

Paired Fields Field Selection

Paired Fields Options

Paired Fields Output Table


Paired Fields Field Selection

You must specify a Dependent (Y) field to compare the X fields, and you can optionally specify Group and Weight fields. See Field Selections for an overview of the Field Selections form.

Press [Next] to see the Paired Fields Options.

See also: Paired Fields


Paired Fields Options

Select the analysis to generate. The options are described in detail in Paired Fields Output Table and Paired Fields Output Fields. See Analysis Options for an overview of the Options form.

When you finish, press [Next] to specify the Output Tables and Description.

See also: Paired Fields


Paired Fields Output Table

See also: Paired Fields

More:

Paired Fields Output Fields


Paired Fields Output Fields

The following fields are always generated (see the Compare section for more details):

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
Count_of_ Y-Field | The number of records where Y-Field is the name of the Y field.
Missing | The number of blank (missing) records.
Count<Y | The number of records with X < Y.
Count=Y | The number of records with X = Y.
Count>Y | The number of records with X > Y.
MeanX | The mean of X field.
MeanY | The mean of Y field.
MeanDiff | The difference of means: MeanX - MeanY
Correlation | Pearson’s correlation between X and Y.

Paired Sign Test

The Paired Sign Test determines if two fields have the same distribution even if they are not normally distributed. The analysis is based on the number of pairs where the difference between X and Y is positive or negative.

SignZ | Z value for sign test: where [image] is the total count, and [image] the count of the larger group
SignProb | The two-tailed probability that the X and Y fields have the same distribution.

See also: Paired Fields Output Table


Wilcoxon Signed Rank Test

The Wilcoxon Signed Rank Test is similar to the parametric paired t-test in the Compare section (see page 53). Unlike the Paired Sign Test, the Wilcoxon Signed Rank measures the difference — not just the sign — between the fields: . It sorts the absolute values of the differences and assigns ranks.

Wilcoxon_Neg_SumRank | The sum of ranks for differences < 0.
Wilcoxon_Pos_SumRank | The sum of ranks for differences > 0.
Wilcoxon_Neg_MeanRank | The mean rank of negative differences: [Wilcoxon_Neg_SumRank]/[Count<Y]
Wilcoxon_Pos_MeanRank | The mean rank of positive differences: [Wilcoxon_Pos_SumRank]/[Count>Y]
Wilcoxon_Z | The Wilcoxon Z value based on positive and negative SumRanks.
Wilcoxon_Prob | The two-tailed probability that the two fields have the same mean.

See also: Paired Fields


Spearman's Rank Correlation Coefficient

Similar to the Pearson's Correlation in the Compare analysis type, Spearman’s Rank Correlation Coefficient is used in non-parametric situations. Rather than the actual values, the rankings of values in X and Y are compared, and a value between -1 and 1 is generated. A value of -1 or 1 indicates a perfect (linear) correlation.

Spearman’s Rank Correlation is calculated by sorting and ranking the X and Y fields separately. The difference [image] in rank between each pair of fields is calculated and summed.

TiedGroups_X | The number of X field groups with ties.
TiedGroups_Y | The number of Y field groups with ties.
Spearman | Spearman’s correlation coefficient (sometimes called Spearman’s Rho)
Spearman_t | Spearman t value: The significance of Spearman value.
Spearman_Prob | The two-tailed probability associated with [Spearman_t] with N-2 degrees of freedom.
Spearman_(ties) | Spearman’s correlation coefficient adjusted for ties: where [image] and [image] is where t is the number of ties for an X or Y rank. The summation is over all tied groups.
Spearman_t_(ties) | Spearman t value adjusted for ties.
Spearman_Prob_(ties) | The probability for [Spearman_t_(ties)].

See also: Paired Fields Output Table


N Fields — Friedman's Two Way ANOVA

The N Fields test, Friedman’s Two Way ANOVA of Ranks, is a generalization of the Paired Fields tests. This test compares multiple numeric fields to determine whether data is normally distributed among the fields.

See Non-Parametric Analysis Overview for a list of all non-parametric analysis types, and Analysis Types for a list of all analysis types available.

More:

N Fields — Friedman's Field Selection

N Fields — Friedman's Two Way ANOVA Options

N Fields — Friedman's Two Way ANOVA Output Table

N Fields — Friedman's Detail Output Table


Friedman's Field Selection

Specify the numeric fields to analyze as Independent X fields:

See Field Selections for an overview of the Field Selections form.

Press [Next] to see the Friedman's Two Way ANOVA Options.

See also: N Fields — Friedman's Two Way ANOVA


Friedman's Two Way ANOVA Options

There is an option to create a detail table with the count, sum of ranks and mean rank for each group and X field.

See also:

N Fields — Friedman's Two Way ANOVA


Friedman's Two Way ANOVA Output Table

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
Count | The number of records in the group.
Missing | The number of missing records (if any X field is blank, it is considered a missing record).
DF | The degrees of freedom: k - 1
Fields | The number of X fields.
Chi-Square | The Chi-square calculation: where Si is sum of ranks for the ith X field.
Prob | The probability based on Chi-Square and DF that the X field populations are identical.

See also:

N Fields - Friedman's Two Way ANOVA


Friedman's Detail Output Table

The detail table provides rank summaries for each X field:

Field Name | Description
Group Fields | The group fields selected (if any).
DataField | The X field name identifying the data in the record.
Count | The number of records.
SumRanks | The sum of ranks for the X field.
MeanRank | The mean rank: = SumRanks / Count

See also: N Fields — Friedman's Two Way ANOVA


Calculator Options

From the toolbar on the main form, press the [image] button, and the probability calculator appears:

Probability Calculation for a given Z-Value

The option buttons on the left side allow you to choose the type of probability to evaluate: Z-Value, t-Value (student’s t test), Chi-Square, and F-Value. Depending on the option selected, you may need to enter zero, one, or two degrees of freedom in addition to the test value to evaluate. Enter the values requested and the probability value appears.

Probability Inverse Calculation

Given a probability and degrees of freedom, the probability calculator can determine the test value. Press the [Inverse] button to do this:

Z-Value Calculation based on a given Probability


Z-Value

The Z-Value is a number associated with the Standard Normal Distribution. Z is a measurement of a sample’s deviation from the mean of a normally distributed population with known standard deviation. For example, Z = 1.96 equals p = 5% (0.05). As Z increases, probability decreases. The probability displayed is the two tailed probability. To calculate the one tailed probability, simply divide the probability by two.

Probability Calculation for a given Z-Value

NOTE: Some references use 1 minus this probability, so when Z=1.96, the probability is 95%. Make sure you understand the definition you are seeking and subtract the calculator’s results from 1 if necessary.

See also:

Calculator Options

t-Value

Chi-Square

F-Value


t-Value

The t-Value is the Student’s t-distribution. t-Values are used to analyze sample data from a population with unknown variance. The t-distribution is a function of the degrees of freedom (DF = sample size minus 1). You must enter both the t-value and the DF. Probability decreases as the t-Value increases. The probability displayed is the two tailed probability. To calculate the one tailed probability, just divide the probability displayed by two.

Probability Calculation for a given t-Value

Inverse t-Value Calculation

Press the [Inverse] button to calculate the t-Value for a given Probability and Degrees of Freedom:

t-Value Calculation for a given Probability

See also:

Calculator Options

Z-Value

Chi-Square

F-Value


Chi-Square

Chi-Square is a measurement of independence between variables. Both the Chi-Square and DF need to be provided. Probability (independence) decreases as Chi-Square increases:

Probability Calculation for a given Chi-Square

Inverse Chi-Square Calculation

Press the [Inverse] button to calculate the Chi-Square for a given Probability and Degrees of Freedom:

Chi-Square Calculation for a given Probability and Degrees of Freedom

See also:

Calculator Options

Z-Value

t-Value

F-Value


F-Value

The F-Value (Fisher’s F Distribution) is the ratio (usually of variance) between two data sets. You need to provide the F-Value and two different degrees of freedom (Degrees of Freedom #1 and Degrees of Freedom #2). The order of the two Degrees of Freedom is important. Probability decreases as F increases.

Probability Calculation for a given F-Value

Inverse F-Value Calculation

Press the Inverse button to calculate the F-Value for a given Probability and two Degrees of Freedom:

F-Value Calculation for a given Probability and Degrees of Freedom

See also:

Calculator Options

Z-Value

t-Value

Chi-Square


Programmatic Overview

This section describes the programmatic interface of Total SQL Statistics. It assumes that you are familiar with using Visual Basic or Visual Studio.Net and invoking functions.

Prior to using the programmatic interface, you must create the scenarios with the Scenario Designer. Run your scenario in the Scenario Designer before adding it to your program to verify that it works.

Total SQL Statistics includes three functions to let you add its features inside your application:

  • RunScenario
    Run any saved scenario.

  • Probability
    Calculate probability as used in the Probability Calculator.

  • ProbInverse
    Calculate inverse probability as used in the Probability Calculator.

See also: Preparing Your Application, Licensing Rules


Preparing Your Application

The statistics engine for Total SQL Statistics ships in the form of an ActiveX DLL component. To use its functions, you need to create a reference to the DLL.

VB Project References Dialog

To use a function in a library database, you need to create an explicit reference from your database to the DLL. Choose the Project | References command from the VB IDE menu. Make sure FMS Total SQL Statistics is selected. If it does not appear in the list, use the [Browse] button to locate the FMSSTAT7.DLL library file (installed in the Windows\System or WINNT\System32 directory).

Note: The library file exists in this location because it is a shared component. It must be a shared component to allow more than one application to use it at a time. A DLL may only be registered at one location on a machine. This is similar to the way all VB OCX controls are installed in the \Windows\System directory.

An alternative to creating an early-bound reference to the Total SQL Statistics DLL is to use run-time binding (i.e. late binding) via the VB CreateObject command. See the VB help for information on late vs. early binding. All of the examples shown in this manual assume early bound references, but late binding also works with some performance penalty.

Note that in order to receive status events while the calculation is being performed, you must use early binding.

See also: Programmatic Overview


Licensing Rules

Licensing rules require each developer to own a Total SQL Statistics license (user count). Each license includes royalty-free distribution rights for the licensed developer. At no time may the interactive Scenario Designer be distributed, and you may not build an application that emulates the Scenario Designer. Complete licensing rules are provided at the beginning of the manual.

See also: Programmatic Overview, License Agreement


How Total SQL Statistics Works

Before using Total SQL Statistics programmatically, you should understand how it works, and where it keeps its scenario settings.

Total SQL Statistics is written entirely in Visual Basic It consists of a design-time component (the Scenario Designer) and a redistributable run-time component (the calculation engine). When you create and test an analysis scenario with the Scenario Designer, you are working with the same calculation engine that is distributed with your application.

The Scenario Designer is the interactive component (FMSSTAT.EXE) used to set up the scenarios. This component may not be distributed.

The calculation engine has no user interface. It is used purely to perform calculations and work with the tables created by the Scenario Designer. It exposes several public functions that you can call from your VB application to generate the analyses dynamically at run-time (RunScenario Function, Probability Function, and Inverse Probability Function).

More:

Tables

Forms

Analysis


Tables

Total SQL Statistics saves scenario settings in each database it analyzes. Scenarios are stored in three tables:

  • usysTStatScenarios
    Contains one record for each scenario holding the basic scenario information (analysis type, data table name, output tables, options, description, etc.).

  • usysTStatOptions
    Contains one record for each scenario holding the analysis options selected .

  • usysTStatFields
    Contains the list of selected fields for each scenario in a one-to-many relationship to the other two tables.

While you can edit these tables, we strongly recommend that you do not do this directly and only update them through the Scenario Designer.

See also: How Total SQL Statistics Works


Forms

The Total SQL Statistics Scenario Designer uses several forms to allow you to maintain analysis scenarios. The main form displays the list of saved scenarios. Other forms allow you to select tables and fields for analysis. Each scenario has an option form for setting the analysis type options. Finally, each scenario has an output form for specifying the output table(s) and scenario description input.

Use the Scenario Designer to set up the parameters of the analysis scenario. The Scenario Designer is not distributed with your application, and your users are not allowed to interact with the Scenario Designer.

See also: How Total SQL Statistics Works


Analysis

How Total SQL Statistics performs its analysis depends on the type of analysis selected. It generally uses a recordset to retrieve the selected fields, and sort the records by the group fields. The records are processed and calculations generated for each group of records (unique combination of group field values). Most calculations allow processing all the X fields in one pass, while some require processing each field separately. Temporary tables (created in a temporary database) may also be used. All calculations are performed with double (float) precision (15-digit accuracy).

See also: How Total SQL Statistics Works


Generate Code Form

The scenario designer contains a utility to help you write the code for calling the methods of the TotalSQLStatistics object in Visual Basic and Visual Studio.Net. It actually generates the code you need—just copy and paste!

To open the utility, click on the scenario desired, and choose Tools, Generate Code from the menu:

Generate Code

Using the Code Type dropdown, select whether to generate code for Visual Basic or VB.Net:

Code Type

Depending on the code type you select, the utility generates a sample procedure for Visual Basic or VB.Net. It has several options to let you customize the style of the code generated. The options vary slightly depending on the code type you choose. Differences are noted.

The options available are:

Code Style

Code Detail

Database Open Method


Code Style

Both Visual Basic and VB.Net offer two code style choices:

  • Properties
    Specify parameters as properties of the TotalSQLStatistics object variable specifically rather than using arguments to the RunScenario method.

  • Arguments
    Specify parameters as arguments to the TotalSQLStatistics RunScenario function rather than specifying properties.

See also: Generate Code Form


Code Detail

This setting determines how much code detail is created. Both options are available for Visual Basic code, but only the first is available when generating VB.Net code:

  • Take Defaults
    Specify only the minimum number of arguments or properties. Don’t override default settings.

  • Specify All Arguments/Properties
    Specify all arguments or properties even if they are the defaults. This is handy for seeing all of the arguments/properties in one place.

See also: Generate Code Form


Database Open Method

This setting determines how database arguments/properties are to be supplied. Both options are available for Visual Basic code, but only the first is available when generating VB.Net code:

  • Use Connection String
    The arguments/properties for ScenarioDatabase, SourceDatabase and OutputDatabase are specified as strings.

  • Use Object Variable
    The arguments/properties for ScenarioDatabase, SourceDatabase and OutputDatabase are specified as database object variables declared in code.

After using the settings to generate code that matches your coding style, press [Copy] to copy the generated procedure onto the Windows clipboard, and paste it into your project.

See also: Generate Code Form


RunScenario Function

RunScenario is the main procedure in the Total SQL Statistics DLL. This is the function/method invoked in the code created by the Generate Code form. RunScenario allows you to run a scenario previously created with the Scenario Designer programmatically. This is the full syntax of the RunScenario method:

Public Function RunScenario( _
ScenarioNo As Long, _
Optional ScenarioDb As Variant, _
Optional SourceDb As Variant, _
Optional OutputDb As Variant, _
Optional ScenarioTbl As String, _
Optional OptionsTbl As String, _
Optional FieldTbl As String, _
Optional SourceTableName As String, _
Optional OutputTableName As String, _
Optional OutputTableName2 As String, _
Optional ShowProgress As Boolean = False, _
Optional ShowErrors As Boolean = False) _
As Boolean

This method has many arguments, but fortunately most are optional. If values are not supplied, defaults are used. For example, if you specify the path to the "ScenarioDb" (i.e. the database containing the three hidden uSys scenario tables) but omit the arguments for SourceDb and OutputDb, the value for ScenarioDb is used for all three arguments. A detailed description of each argument follows.

Because the RunScenario function is a method of the TotalSQLStatistics object contained in the Total SQL Statistics DLL, you cannot simply call it directly in your code—you must instantiate an object variable from the DLL, then call RunScenario as a method of that object. The object name for the calculation engine is "TotalSQLStatistics". For example:

Public Function SampleRunScenarioRoutine() As Boolean
' Comments : Runs the Total SQL Statistics analysis
' Scenario Named : [Sample Run]
' Scenario Number: 1
' Parameters: None
' Returns : True if successful, else False
' Source : Generated by Total SQL Statistics 1.0

Dim stats As TotalSQLLStatistics
Dim fOK As Boolean

Set stats = New TotalSQLStatistics

With stats
.ScenarioDatabase = _
"Provider=SQLOLEDB;User ID=sa;Password=; _
Data Source=Server;Initial Catalog=Sample"
fOK = .RunScenario(1)
End With

SampleRunScenarioRoutine = fOK

End Function

Notice that all but the first two arguments to the function were effectively omitted.

When you execute this method, it returns a value of TRUE (-1) or FALSE (zero) depending whether or not the analysis was successfully completed (TRUE). If the result of the function is FALSE, examine the ErrorInfo property of the object variable to get a string containing the cause of the error.

More:

Parameters

Properties

Database Argument/Properties

Limitations

Progress Events


Parameters

Parameter | Description
ScenarioNo | The Scenario Number of the scenario to run. Each scenario has a unique ID. The scenario number can be seen in the Scenario Designer. You can also view the usysTStatScenarios table directly to find the scenario ID you want.
ScenarioDB | The database containing the three scenario information tables. This argument may either be a string indicating the full path to the database, or an object variable of the "database" type (which is useful if you have already opened the database in your own program).
SourceDB | The database containing the table or source object to analyze. This argument may either be a string indicating the full path to the database, or it may be an object variable of the "database" type. If this argument is omitted, it is assumed that the input source object is in the same database as the database indicated by the ScenarioDB argument.
OutputDB | The database where the analysis scenario output table will be created. This argument may either be a string indicating the full path to the database, or an object variable of the "database" type. If this argument is omitted, it is assumed that the output table is to be built in the same database as the database indicated by the ScenarioDB argument.
ScenarioTbl | The name of table listing the scenarios. If this Argument is omitted, the usysTStatScenarios table is used. However, you can create a copy of this table and use a different name, as long as it has the same structure as the default table.
OptionsTbl | The name of the table with the scenario options. If this argument is omitted, the usysTStatOptions table is used. However, you can create a copy of this table and use a different name, as long as it has the same structure as the default table.
FieldTbl | The name of the table with the field assignments. If this argument is omitted, the usysTStatFields table is used. However, you can create a copy of this table and use a different name, as long as it has the same structure as the default table.
SourceTable Name | The name of the table or source object to analyze. If this argument is omitted, the value specified at design time in the Scenario Designer is used.
Output Table Name | The name of the analysis output table. If this argument is omitted, the value specified at design time in the Scenario Designer is used.
OutputTable Name2 | The name of the second analysis output table (if there is one). If this argument is omitted, the value specified at design time in the Scenario Designer is used.
ShowProgress | TRUE if you want Total SQL Statistics to raise progress information events during its analysis. FALSE if you want to suppress the events.
ShowErrors | TRUE if you want the Total SQL Statistics RunScenario method to display its own error message in the case of errors. FALSE if you want to handle the error yourself.


Properties

If you prefer, instead of passing information to the RunScenario method in the form of arguments, you may use the corresponding properties of the TotalSQLStatistics object:

Public Function SampleRunScenarioRoutine() As Boolean
' Comments : Runs the Total SQL Statistics analysis
' Scenario Named : [Sample Run]
' Scenario Number: 1
' Parameters: None
' Returns : True if successful, else False
' Source : Generated by Total SQL Statistics 5

Dim stats As TotalSQLStatistics
Dim fOK As Boolean

Set stats = New TotalSQLStatistics

With stats
.ScenarioDatabase = "Provider=SQLOLEDB; _
User ID=sa;Password=;Data Source=Server; _
Initial Catalog=Sample"
.SourceDatabase = "Provider=SQLOLEDB; _
User ID=sa;Password=; Data Source=Server; _
Initial Catalog=Sample"
.OutputDatabase = "Provider=SQLOLEDB; _
User ID=sa;Password=; Data Source=Server; _
Initial Catalog=Sample"
.ScenarioTbl = "usysTStatScenarios"
.OptionsTbl = "usysTStatOptions"
.FieldTbl = "usysTStatFields"
.SourceTableName = "Sample"
.OutputTableName = "TSST_Describe"
.OutputTableName2 = ""
.ShowProgress = False
.ShowErrors = False
fOK = .RunScenario(1)
If fOK = False Then
MsgBox "Error: " & .ErrorInfo
End If
End With

SampleRunScenarioRoutine = fOK

End Function

Although this example specifies all properties for completeness, it is only necessary to specify the properties that differ from the default values.

See also: RunScenario Function


Database Argument/Properties

Three of the arguments (and properties) are used to specify the database containing the scenarios, the input table, and the output table(s). You can specify these arguments in two ways:

  • The Connection String the database.

  • A database object variable already opened in your program.

The examples in Properties use the first technique. The ScenarioDatabase, SourceDatabase and OutputDatabase arguments can all take strings, or an expression that evaluates to a string and specifies the connection information for the database. If, however, your application already has a valid connection open, you may use the alternate method described below.

Working with Connection Objects

If you already have a connection opened in your application, or if you need to open it with specific security settings, pass a connection object variable to the property or argument instead of a string:

Public Function SampleRunScenarioRoutine() As Boolean
' Comments : Runs the Total SQL Statistics analysis
' Scenario Named : [Test Secured]
' Scenario Number: 1
' Parameters: None
' Returns : True if successful, else False
' Source : Generated by Total SQL Statistics 5

Dim cnn As ADODB.Connection
Dim stats As TotalSQLStatistics
Dim fOK As Boolean

Set cnn = New ADODB.Connection
cnn.Provider = "SQLOLEDB"
cnn.CusorLocation = adUseClient
cnn.Properties("Data Source") = "Server"
cnn.Properties("Initial Catalog") = "Sample"
cnn.Properties("User ID") = "sa"
cnn.Properties("Password") = ""

Set stats = New TotalSQLStatistics

With stats
.ScenarioDatabase = cnn
fOK = .RunScenario(1)
End With

SampleRunScenarioRoutine = fOK

End Function

In this example, the statistics engine does not independently open up the Sample database. It assumes that your program already successfully opened it, and uses the object variable directly.

You must be using a connection object variable declared using a reference to ADO 2.5 or 2.6 for this technique to work.

See also: RunScenario Function


Limitations

When you run analysis scenarios from your program, there is very little error checking on the parameters supplied to the RunScenario method. The Total SQL Statistics function assumes the scenario settings are correct and the data table is available. If not, the analysis will fail.

If the output table or tables exist, they are overwritten without warning.

All security rights (ability to read the data table and create output tables) are assumed to be set.


Progress Events

It is possible to have the Total SQL Statistics RunScenario method raise events back into your program as it performs the analysis. The events that you can trap and respond to are:

  • RunScenarioBegin

  • StatStatus

  • RunScenarioComplete

The StatStatus and RunScenarioComplete events have additional arguments to provide information about the event.

To receive the events that Total SQL Statistics raises into your application, you must declare the TotalSQLStatistics object variable in the Declarations section of a form or another class, using the "WithEvents" keyword:

Option Explicit

Private WithEvents mStats As TotalSQLStatistics

You can then instantiate the variable and call its methods as you would with a local object variable:

Private Sub cmdRun_Click()
Set mStats = New TotalSQLStatistics

mStats.ScenarioDatabase = cnn
mStats.ShowProgress = True
mStats.RunScenario 1
End Sub

When the variable is declared using the "WithEvents" keyword, three additional events are created in your module. The procedure generated for the event consists of the name of the object variable concatenated with a "_" character and the name of the event.

For example, using an object variable named "mStats", the following event procedures are available in your code (they are not created automatically; you must select the "mStats" object in the left-hand drop-down list in the module window, and select the event from the drop-down list on the right):

Here is an example of how you might respond to these events in your code:

Private Sub mStats_RunScenarioBegin()
lblStatus.Caption = "Begin Analysis Scenario..."
lblStatus.Refresh
End Sub

Private Sub mStats_StatStatus(StatusMessage As String)
lblStatus.Caption = StatusMessage
lblStatus.Refresh
End Sub

Private Sub mStats_RunScenarioComplete( _
fStatus As Boolean, _
strError As String)

If fStatus = True Then
lblStatus.Caption = "Analysis successful!"
Else
lblStatus.Caption = "Analysis error:" & strError
End If
End Sub

The StatStatus event contains an argument "StatusMessage" which is a textual message indicating the progress of the analysis. The RunScenarioComplete event contains an argument "fStatus," which contains the same value as the return value from the RunScenario method. It also contains an argument "strError," which contains the same error string contained in the object’s ErrorInfo property.

If you set the ShowProgress property/argument to False, the StatStatus event is not raised. This may result in a very slight performance improvement.


Probability Function

In addition to running scenarios, Total SQL Statistics also lets you incorporate the probability calculator into your applications. The public method of the TotalSQLStatistics object is:

Public Function Probability( _
ByVal strTestType As String, _
ByVal dblTestValue As Double, _
ByVal dblDegreesOfFreedom1 As Double, _
ByVal dblDegreesOfFreedom2 As Double) _
As Double

Once again, in order to use this method you must create an object variable of the TotalSQLStatistics type and call it as a method. This example calculates the probability of a t-value of 0.5 with 30 degrees of freedom:

Dim prob As TotalSQLStatistics
Dim dblProb As Double
Set prob = New TotalSQLStatistics

dblProb = prob.Probability("T", 0.5, 30, 0)

Sample use of the Probability Method

See also: Parameters


Parameters

Parameter | Description
strTestType | "Z", "T", "C" or "F" depending if you want to perform probabilities for Z-Value, t-Value, Chi-Square, or F-Value.
dblTestValue | The test value to evaluate.
dblDegreesOfFreedom1 | The first degrees of freedom. Pass a zero for Z-Value, required for other Test Types
dblDegreesOfFreedom2 | The second degrees of freedom. Only needed for F-Value. Otherwise, pass a zero.

See also: Probability Function


Inverse Probability Function

You may also call the Probability Calculator's inverse probability method. Total SQL Statistics lets you incorporate this feature directly into your applications:

Public Function ProbInverse( _
ByVal strTestType As String, _
ByVal dblProbability As Double, _
ByVal dblDegreesOfFreedom1 As Double, _
ByVal dblDegreesOfFreedom2 As Double) _
As Double

As with the Probability method, to use this method you must create an object variable of the TotalSQLStatistics type and call ProbInverse as a method. This example finds the t-value with a probability of 0.05 (5%) for a distribution with 30 degrees of freedom:

Dim prob As TotalSQLStatistics
Dim dblValue As Double
Set prob = New TotalSQLStatistics

dblValue = prob.ProbInverse("T", 0.05, 30, 0)

Sample use of the ProbInverse Method


Parameters

Parameter | Description
strTestType | "Z", "T", "C" or "F" depending if you want to find Z-Value, t-Value, Chi-Square, or F-Value.
dblProbability | The probability value to evaluate.
dblDegreesOfFreedom1 | The first degrees of freedom. Pass a zero for Z-Value, required for other Test Types.
dblDegreesOfFreedom2 | The second degrees of freedom. Only needed for F-Value. Otherwise, pass a zero.


Distributing the Statistics DLL

The only part of Total SQL Statistics that you are allowed to distribute with your VB application is the Statistics Engine (DLL), which interprets and runs the analysis scenarios you created at design time. In essence the one and only one Total SQL Statistics file you need to distribute is FMSSTAT7.DLL.

See also: Licensing Rules, Installation and Registration, Support Files


Support Files

This .DLL needs several other files in order to work. Since you are a Visual Basic 6 developer, you have all these files. The run-time files required in order to use Total SQL Statistics with your application are:

  • The VB run-time library MSVBVM60.DLL

  • MDAC 2.5 or higher

  • Microsoft SQL Server redistributable components

Most likely, since your application works with SQL Server, your setup program already includes these components. If you need more information on distributing MDAC with your application, see the VB documentation.

See also: Distributing the Statistics DLL


Installation and Registration

The FMSSTAT7.DLL engine is an ActiveX DLL component. As such, it must be registered on your user’s machine for it to work. Your setup program should do this. For example, the setup programs created with the VB Setup Wizard automatically register server components.

If you wish to register the DLL manually, you must use a tool such as REGSVR32.EXE that comes with VB. An example of a command line required to register the DLL would be:

REGSVR32.EXE c:\windows\system32\fmsstat7.dll

To un-register the server using this utility, you would use the command:

REGSVR32.EXE –u c:\windows\system32\fmsstat5.dll

Microsoft recommends that shared ActiveX DLL components be installed in the user’s Windows\System directory (Windows\system32 for Windows NT and Windows 2000). Although the Statistics Engine works from any location if it is registered correctly, problems could arise if the DLL is installed and registered in more than one location on your machine. Windows only allows one copy of a DLL to be registered on the system at any one time. Therefore, if you install the server into a private application directory, and another user installs a different copy into another directory, and registers it from there, the latest-registered version is used.

See also: Distributing the Statistics DLL


Scenario Doesn't Run Successfully

If you have trouble using the statistics function, troubleshoot the problem by checking the following:

Run the Scenario from the Scenario Designer

Always examine and run the scenario from the Scenario Designer. Edit the scenario to see if all options are set properly, then run it. If the scenario runs successfully from the Scenario Designer, it should run in your code.

If it does not, verify that the data source being analyzed is valid and contains data.

VB Code

Make sure your VB code is correct. You should use the sample code generated by the Scenario Designer—simply copy and paste the code into your application. If you don’t modify the code, it should always work. See the Scenario Designer for more information.

Database Security

You need to make sure you specify the appropriate login settings. You must have rights to read the table or run the view, stored procedure, or user-defined function that is being analyzed.

If you are sending the results into another database, that database must exist, and you must have rights to create tables in it.

See also: Contact Us


Contact Us

The Scenario Designer uses the same analysis engine as the programmatic interface. If the scenario runs in the Scenario Designer, the VB code is correct and there are no security issues, we’re stumped too, but we want to help.

Due to the complexity of such situations, FMS can only provide limited support over the telephone. If you need assistance, please email a copy of your code that calls our method to us at support@fmsinc.com. Please do not email your entire application, just the code—we may be able to determine a problem with the way you are calling our code from that. If we cannot determine the problem, we may request a copy of your database containing the scenarios and data being analyzed. Please do not send your database until we request it.

Please note that we can only help you with how to use Total SQL Statistics. We cannot provide technical support for general statistical analysis questions or VB techniques. Please refer VB questions to Microsoft.

Note: FMS does not provide technical support to users of your applications containing Total SQL Statistics. We can only provide support for the registered developer.

See Also: Contacting Technical Support


Troubleshooting

There are many resources available to resolve issues you may encounter. Please check the following:

Readme File

Check the README file for the latest product information. The README file can be opened from the Start group of Total SQL Statistics.

User Manual

We’ve spent a great deal of care and time to make sure the Total SQL Statistics manual is very detailed. Check the Table of Contents and Index for your question then read the appropriate pages.

On-line Help

The On-line Help system is also very detailed. Consult the Contents or Index of the On-line Help files to find the information you need.

See Also: Web Site Support, Contacting Technical Support


Web Site Support

The FMS web site contains extensive resources to help you use our products better. Resources include product updates, frequently asked questions (also known as FAQs), newsgroups, information on new versions, betas, and other resources.

Home Page

The FMS home page is located at:

www.fmsinc.com

News and important announcements are posted here.

Support Site

The main support page is located at:

www.fmsinc.com/support/

From this page, you can quickly locate the other support resources:

Product Updates

FMS takes product quality very seriously. When bugs are reported and we can fix them, we make the updates available on our web site. If you are encountering problems with our product, make sure you are using the latest version.

Product updates can also be checked using the update wizard. From the Windows Start menu, select the Update Wizard under your product group.

Frequently Asked Questions (FAQs)

Common questions and additional information beyond what is in the manual are often available from our FAQs.

Newsgroups

FMS also has general and product specific newsgroups where you can connect with FMS Technical Support and other users. Share your experiences, learn from others, and ask your questions in our virtual community. Point your web browser to:

www.fmsinc.com/support/newsgrp.htm

or visit our web site for additional information.

Microsoft Patches

Our support site also includes links to Microsoft patches that are related to our products. Make sure you’re using the latest versions by checking here or visiting the Microsoft site.

See also: Troubleshooting, Contacting Technical Support


Contacting Technical Support

If the previous suggestions fail to resolve your problem, contact our technical support department. We are very interested in making sure you are satisfied with our product.

Registering Your Software

Please register your copy of Total SQL Statistics. You must be registered to receive technical support. Registration also entitles you to free product updates, notifications, information about upcoming products, and beta invitations. You can even receive free email notification of our latest news.

You may register your software by mailing in your registration card or directly from our web site:

www.fmsinc.com

Support for Licensed Developers Only

FMS provides technical support for licensed developers using or distributing Total SQL Statistics. FMS does not, however, provide technical support or customer service for users of your applications that use our controls — this is your responsibility.

Contact Us

The best way to contact us is via email at:

  • Email: support@fmsinc.com

With the exception of installation problems, telephone support is not currently available.

Please provide detailed information on the problem. This should include the name of the product, version, your operating system, and the specific problem. If the product generated an error file, please submit that as well.

With email, technical support issues can be more accurately resolved and tracked in our internal technical support system. It also gives us more time to understand the entire problem and allows our technical support staff to contact the developers with the entire story when necessary. Please bear in mind that a unique issue may involve meetings between the technical support staff and product developers, so your patience is appreciated.

Microsoft Technical Support

FMS only provides technical support for its products. If you have questions regarding Microsoft Windows, Microsoft Office, or Microsoft Visual Basic, please contact Microsoft technical support, or the FMS newsgroups.

See Also: Troubleshooting, Web Site Support


References

Overview

Some people spend their whole life dedicated to learning the complex world of statistical analysis. Due to the complexity of statistics, this manual can only cover how Total SQL Statistics works, and not the wide variety of situations and conditions for using particular tests. If you are not familiar with some statistical functions, we strongly urge you to explore other sources, including formal training if you are serious about the field.

Of course, there are thousands of books on statistics available at technical bookstores and college libraries. The following are a few books we know that may help you better understand Total SQL Statistics and statistics in general. Some of these books were used during the development and testing of Total SQL Statistics. The Basic books provide fundamental overviews of statistical definitions and concepts. The Advanced books provide information on the actual calculation of statistical functions.

Basic

Beyer, W.H. Ph.D., ed., CRC Standard Mathematical Tables, 26th edition, (1981), CRC Press, Inc., Boca Raton, FL.

Downing, D. Ph.D; Clark, J. Ph.D, Statistics the Easy Way, 2nd edition, (1989) Barron’s Educational Series, Inc., Hauppauge, NY.

Shefler, William C., Statistics: Concepts and Applications, (1988), Benjamin/Cummings Publishing Company, Inc., Menlo Park, CA.

Spiegel, M.R., Theory and Problems of Statistics, 2nd edition, (1988) Schaum’s Outline Series, McGraw-Hill Publishing Company, NY.

Advanced

Dowdy, S., Wearden, S., Statistics for Research, (1983) John Wiley & Sons, Inc., NY.

Niee, N.H., Hull, C.H., Jenkins, J.G., Steinbrenner, K., Bent, D.H., Statistical Package for the Social Sciences (SPSS), 2nd edition, (1975), McGraw-Hill Book Company, NY.

Searle, S.R., Linear Models, (1971) John Wiley & Sons, Inc., NY.

Snedecor, G.W., Cochran, W.G., Statistical Methods, 8th edition, (1989) Iowa State University Press, Ames, Iowa.

Wetherill, G.B. Elementary Statistical Methods, 3rd edition, (1982) Chapman & Hall, Ltd., NY.

Winer, B.J., Statistical Principles in Experimental Design, 2nd edition, (1971) McGraw-Hill Book Company, NY.


License Agreement

PLEASE READ THE FMS SOFTWARE LICENSE AGREEMENT. YOU MUST AGREE TO BE BOUND BY THE TERMS OF THIS AGREEMENT BEFORE YOU CAN INSTALL OR USE THE SOFTWARE.

IF YOU DO NOT ACCEPT THE TERMS OF THE LICENSE AGREEMENT FOR THIS OR ANY FMS SOFTWARE PRODUCT, YOU MAY NOT INSTALL OR USE THE SOFTWARE. YOU SHOULD PROMPTLY RETURN ANY FMS SOFTWARE PRODUCT FOR WHICH YOU ARE UNWILLING OR UNABLE TO AGREE TO THE TERMS OF THE FMS SOFTWARE LICENSE AGREEMENT FOR A REFUND OF THE PURCHASE PRICE.

Ownership of the Software

The enclosed software program ("SOFTWARE") and the accompanying written materials are owned by FMS, Inc. or its suppliers and are protected by United States copyright laws, by laws of other nations, and by international treaties. You must treat the SOFTWARE like any other copyrighted material except that you may make one copy of the SOFTWARE solely for backup or archival purpose, and you may transfer the SOFTWARE to a permanent storage device.

Grant of License

The SOFTWARE is available in a single license or 5 license version. Contact FMS, Inc. to purchase additional licenses.

Licenses are granted on a PER DEVELOPER basis. For each license, one designated developer can use the SOFTWARE on one computer at a time.

Use and Redistribution Rights

This SOFTWARE license is for one user only and includes a Runtime License granting limited redistribution rights.

The Runtime Library Database is available to let you distribute applications that run the scenarios you created. FMS grants you the right to distribute the SOFTWARE’s dynamic link library (DLL) on a non-exclusive, royalty free basis, provided that:

  1. Each developer using the program owns a license of the SOFTWARE.

  2. Your application adds substantial value to the SOFTWARE and is not a standalone statistical analysis program.

  3. Your application does not attempt to replicate the interactive user interface of the SOFTWARE.

  4. Your application is not a freeware or shareware product.

  5. You only distribute the DLL file and no other SOFTWARE files.

  6. If you claim a copyright, you must add a clause stating "Portions of this program are Copyright Total SQL Statistics from FMS, Inc." and do not claim ownership of the SOFTWARE.

  7. You agree to indemnify, hold harmless, and defend FMS and its suppliers or contractors from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution or other activities relating to your applications.

Restrictions

Under no circumstances may you attempt to reverse engineer this product. You may not rent or lease the SOFTWARE, but you may transfer the SOFTWARE and the accompanying written materials on a permanent basis provided you retain no copies, the recipient agrees to the terms in this SOFTWARE License, and you have not distributed applications using the SOFTWARE. Ownership transfers must be reported to FMS, Inc. in writing.

Transfer of License

If your SOFTWARE is marked "NOT FOR RESALE," you may not sell or resell the SOFTWARE, nor may you transfer the FMS SOFTWARE license.

If your SOFTWARE is not marked "NOT FOR RESALE," you may transfer your license of the SOFTWARE to another user or entity provided that:

  1. You have not distributed applications including the SOFTWARE.

  2. The recipient agrees to all terms of the FMS SOFTWARE License Agreement.

  3. You provide all original materials including SOFTWARE disks or compact disks, and any other part of the SOFTWARE’s physical distribution to the recipient.

  4. You remove all installations of the SOFTWARE.

  5. You notify FMS, in writing, of the ownership transfer.

Limited Warranty

If you discover physical defects in the media on which this SOFTWARE is distributed, or in the related manual, FMS, Inc. will replace the media or manual at no charge to you, provided you return the item(s) within 60 days after purchase.

ALL IMPLIED WARRANTIES ON THE MEDIA AND MANUAL, INCLUDING IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE LIMITED TO SIXTY (60) DAYS FROM THE DATE OF PURCHASE OF THIS PRODUCT.

Although FMS, Inc. has tested this program and reviewed the documentation, FMS, Inc. makes no warranty or representation, either expressed or implied, with respect to this SOFTWARE, its quality, performance, merchantability, or fitness for a particular purpose. As a result, this SOFTWARE is licensed "AS-IS", and you are assuming the entire risk as to its quality and performance. IN NO EVENT WILL FMS, INC. BE LIABLE FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES RESULTING FROM THE USE, OR INABILITY TO USE THIS SOFTWARE OR ITS DOCUMENTATION.

THE WARRANTY AND REMEDIES SET FORTH IN THIS LIMITED WARRANTY ARE EXCLUSIVE AND IN LIEU OF ALL OTHERS, ORAL OR WRITTEN, EXPRESSED OR IMPLIED.

Some states do not allow the exclusion or limitation of implied warrantees or liability for incidental or consequential damages, so the above limitations or exclusions may not apply to you. This warranty gives you specific legal rights. You may also have other rights that vary from state to state.

U.S. Government Restricted Rights

The SOFTWARE and documentation are provided with RESTRICTED RIGHTS. Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c) (1) (ii) of the Rights in Technical Data and Computer Software clause at DFARS 252.227-7013 or subparagraphs (c) (1) and (2) of the Commercial Computer Software - Restricted Rights at 48 CFR 52.227-19, as applicable.

Manufacturer is FMS Inc., Vienna, Virginia. Printed in the USA.

Total SQL Statistics is copyright Ó 1995-2001 by Financial Modeling Specialists, Inc.
All rights reserved.
Microsoft, SQL Server, Visual Basic, Windows, Windows NT, and Windows 2000 are registered trademarks of Microsoft Corporation.
All references to "VB" refer to Visual Basic,
All other trademarks are trademarks of their respective owners.