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
Newsletters
