Review: Total SQL Statistics

Visual Studio Magazine, John Pearson 
November 1, 2002

Many of us shy away from using statistics because we don't know what they mean or how to calculate them. Total SQL Statistics makes the calculating part easier by performing calculations on data in SQL Server 7 or SQL Server 2000 databases.

With 15-digit accuracy, it calculates mean, variance, standard deviation, standard error, and general distribution data. The calculation types increase in complexity from regression through chi-square to one- and two-way analysis of variance. These statistics should suffice for 98 percent or more of your needs.

Under the hood, Total SQL Statistics creates three configuration tables in each database it analyzes. The tables contain statistical-analysis designs (called scenarios), the options you select, and the fields for the scenarios. The program's ActiveX component then does the calculations against the database and retrieves them in a recordset.

Total SQL Statistics includes 18 sample scenarios. You can use the Scenario Designer to create others on any SQL Server database from tables or stored procedures. The designer is most useful when you already know the answers to a series of "fill in the blanks" questions about your project, such as type of analysis, database to use, and statistics to generate. The designer uses your responses to create a scenario any user can run any time.

For VB5/6 and VB.NET programmers, a Total SQL Statistics utility creates code for each scenario. (You can't generate the scenarios at run time.) By adding the product's ActiveX component to your project, you make the VB code ready to use. The VB.NET code takes a bit more cajoling.

Installation requirements are slight: any Windows OS newer than NT4 (service pack 6), 128 MB of RAM, and 10 MB of disk space. Another requirement can be tricky—Microsoft Data Access Components (MDAC) 2.5. You might need to download and install a more recent MDAC release to get yours to work (or be prepared to copy several ActiveX components).

Total SQL Statistics has some room for improvement. It could benefit from a method to export the results to file formats such as CSV and XLS, and I'd like to see several creative Visual Basic demos with full source code. A native .NET component for VB.NET would be nice, as well as explanations and demos of where use of the various statistics is appropriate.

Total SQL Statistics can help your VB applications deliver data quickly from your database to your consumers without having to reenter or transform it. For example, you can use Total SQL Statistics to help automate recurring report generation, implement statistics-based ordering, or generate warnings when data exceeds statistical thresholds. Anyone who analyzes SQL Server data should give Total SQL Statistics strong consideration.

About the Author

John Pearson does Windows and Internet programming work for the Church of Jesus Christ of Latter-day Saints. He has contributed to several magazines, including Web Techniques and Java Pro.

Back to Main Reviews Page