INTELLIGENT SYSTEMS

 

Volume XIII, Number 4                                                                                                                 July, 2006

 

A newsletter for clients and friends of Chenault Systems

Copyright © 2006 Chenault Systems, Inc.  All rights reserved.

 


Adapt Update

As we continue to serve the trade show industry, ADAPT has been made to interface with many registration vendors, such as: ExpoExchange, CompuSystems (CSI), AccuReg (ARI), AR Systems (ShowCare), and InfoSalons.

The link below contains a demonstration of ADAPT developed by Chenault Systems and Hanley-Wood during 2001-2002.  This product won "Trade Show Innovation of the Year" in 2003.  The system takes data from all kinds of formats and aggregates it into ONE central database.  The system can also be used over an Intranet.

The link to the 6-minute demo is www.adaptdb.com/adapt_demo2.html.

 

Spreadsheets and Databases

By Tom Chenault

This article has been brought back and revised from our August 1996 newsletter at the request of some of our followers and is directed to persons who need to clarify the differences between a spreadsheet product, such as Excel® or Lotus®, and a desktop database product such as Access®, FoxPro®, SQL Serve®r, Oracle®, or R:Base®.

Spreadsheet tools were around long before PC’s first appeared in the office.  Back in the 1970’s and early 1980’s, there were mainframe and mini-computer products, such as BBL, Profit II, IFPS, Model, Empire, and Express, etc. that provided financial modeling type spreadsheets.  “Decision support system (DSS)” was the big pitchbuzzword of that era.  The spreadsheet (or financial modeling) products were used for data analysis while databases, such as Focus, System 2000, IMS, DB2, and Total, were used for data processing and retrieval.  In those days, far fewer people were exposed to computers.  In most cases, only computer professionals had to understand terminology such as “database” and “spreadsheet” and most applications were placed in the proper category.

When the PC was introduced in the early 1980’s, products such as VisiCalc, Lotus (on the PC) and Excel (first on the Mac then PC) became household words and, to a great extent, facilitated the sales of desktop computers.  Nearly everyone learned how to use these remarkable easy-to-use products and saved an enormous amount of time producing financial reports.  More and more business professionals became self-sufficient and were less dependent on in-house information systems departments.  Information could be down loaded from mainframes to Lotus and Excel.  When database products became available on PC’s, non-computer professionals became even more independent.

However, nearly every month we see spreadsheet products, such as Excel or Lotus, being stretched beyond their limits when the application would be better placed in a database system.  This is hardly a surprise when one considers it was computer professionals who determined what data best fit a spreadsheet and what data best fit a database system.  Some of these people were left behind with the mainframes, leaving users to fend for themselves on the PC.

Spreadsheets have grown with in sophistication over the last twenty years, but users need to be careful in how they are used.  Excel and Lotus store calculation formulas in the worksheet with the data.  The worksheet is only a two dimensional matrix.  Database tools offer multiple tables (oftentimes files) to store larger amounts of data, providing for all intents and purposes an infinite number of rows (records) and columns (fields), only limited by the amount of computer resources or the database manager.  Database managers, such as Access, store formulas in functions, update screens and reports, but not in the tables where the data is stored.  The key is the functionality is separated from the data in a database manager.  The primary job of the spreadsheet is the analysis of small amounts of data.  The primary job of a database system is the accumulation and processing of large amounts of data.

Database systems have the ability to report on all or portions of the data through related queries, screens, and reports.  Typically, spreadsheets are used for financial projections and while databases store historical information.  For example, we use Excel for our vital cash flow projections.  We try to project client payments, along with cash outflows, to forecast ending cash balances from month-to-month.  We use a database system to accumulate transactions, such as time and billing information.  Database systems are should always be used for custom-built financial systems.

For example, an accounts payable system could have one table to accumulate vendor invoices with another “related” vendor table.  The vendor table will contains fields, such as vendor number, vendor name, and address, etc. and would be considered used for a master lookup or validation file.  The invoice table, considered the main transaction file, is related to the vendor table via the vendor number.  For example, the below vendor named “Caprock Janitorial” is related or linked to the below invoice table via the vendor number of “102."  The character string “Caprock Janitorial” is only stored in the database in one place instead of multiple places.

 

Vendor

 

Vendor #

Vendor Name

101

Llano Printing

102

Caprock Janitorial

103

Anderson, CPA

104

Lubbock Real Estate

 

Invoice

 

 

 

Vendor #

Inv. Date

Invoice #

Amount

101

06/24/96

cvd-3341

802.78

102

06/26/96

1278c9tc

111.53

102

06/27/96

1487

404.13

104

06/28/96

WX156Y

300.02

 

For validation purposes, no invoice can be entered from a screen into the invoice table unless the vendor is already in the vendor table.  This is one of the main concepts behind relational databases.  Also, redundant vendor names will not be repeated over and over againare not in the invoice tables.  This insures optimum system speed and storage, not to mention data accuracy and validationintegrity.

The primary job of the spreadsheet is the analysis of small amounts of data.  The primary job of a database system is the accumulation and processing of large amounts of data.

Tables are “indexed” to speed up data lookups.  For example, the index for the vendor table would be the vendor number.  The index for the invoice table could beis a combination of vendor number, invoice date, and invoice number to insure the uniqueness of each invoice transaction.

  Database products are backed supportedup with extensive programming languages to allow for complex business situations.  For example, with Access, you can use Visual Basic or Structured Query Language (SQL)SQL procedures can be used with SQL Server to handle process any complicated reporting or validation procedures.  Typically, Access is the user-friendly front end for forms, queries and reports and while SQL Server is the “back office” for industrial strength data maintenance in the form of tables.  FoxPro, along with other database systems, have the Structured Query Language (SQL) SQL for those queries or reports to select records based on certain screening and sorting criteria.  Spreadsheets use “macros” (spreadsheets have functions too!) to handle support procedures that are repetitious processes, such as the consolidation or the adding together of multiple worksheets after one data item has been changed (what if analysis).

In summary, many applications start out as a spreadsheet, prototyping the resultant database application.  Any lost effort is normally offset by the proof of concept the spreadsheet provides.No effort is really lost.  The following are attributes to look for in deciding whether to use a database product or a spreadsheet product:

 

Spreadsheet

·         Analysis of data

·         Small amount of data

·         Simple validation

·         Matrix oriented

·         Future projections

·         Limited security

·         Finite number of rows and columns

 

Database

·         Processing, reporting and storage of data

·         Large amount of data

·         Complex and strong need to validate data

·         Table (relational) oriented

·         Historical transactions

·         Full security

·         Infinite number of rows (records)

 

 Access® is a registered trademark of Microsoft Corporation

Excel® is a registered trademark of Microsoft Corporation

FoxPro® is a registered trademark of Microsoft Corporation

Lotus® is a registered trademark of Lotus Development Corp. a subsidiary of IBM Corporation.

R:Base® is a registered trademark of R:Base Technologies

SQL Server® is a registered trademark of Microsoft Corporation

 

Quotes Worth Noting

“The overly bureaucratic Sarbanes-Oxley (SOX) processes could lead to the end of global domination by the U.S. capital markets.  Ironically, the two gentlemen that created SOX did it with the intention of ‘preserving’ U.S. capital market leadership.  Their fear was that people viewed our markets as too risky, and so they created SOX to ensure that investors would ‘trust’ our markets.  It turns out that SOX is doing the opposite – it is ensuring the demise of the leadership of U.S. capital markets.  New up and coming companies outside the U.S. are now shunning the U.S. markets in mass.  Let us not forget that the NASDAQ has and as always had ‘weaker’ listing requirements that the NYSE.  And eventually, the then new and up and coming companies like Microsoft, Cisco, and Intel eventually came to dominate the Fortune 500 – and they all started as emerging companies that preferred the NASDAQ.  Now companies are going to ‘prefer’ other markets with requirements that are less stringent than the SOX laden U.S. markets.” -- Robert Greifeld, CEO of the NASDAQ market warning of the potential demise of U.S. markets due to Sarbanes Oxley:

Brooks' Law: Adding manpower to a late software project makes it later. (Frederick P Brooks)

 

 

To unsubscribe please reply with “unsubscribe” in the subject line.

 

Chenault Systems, Inc.

972-306-3839

www.chenaultsystems.com