INTELLIGENT SYSTEMS

 

Volume VIII, Number 4     December, 2001

 

A quarterly newsletter for clients and friends of Chenault Systems

 

Chenault Systems

2001 and Beyond

This has not been an easy year for any of us.  The terrorist attack on September 11 was the last thing our already troubled economy needed.  As with many other organizations across the country, we did not get much accomplished that terrible week.  Yet we are thankful for our wonderful clients and all the interesting projects.  The relationships derived from these experiences are the most important of all.  Companies such as Marfield Corporate Stationary, Hanley-Wood, VNU, Frymire Engineering, and MainStreet Technologies, along with rekindled relationships from past, such as ExxonMobil, have kept us strong with repeat business.

In its 6th year, Chenault Systems, Inc. grew by 15% in spite of several postponed projects due to the slower economy.  Profitability improved and so did the balance sheet with much improved lines of credit.  Networking with Dallas area businesses improved partially due to the privilege of serving on the board of directors of the Dallas Business Association, committees with various chambers of commerce, and college alumni groups.  We have found networking to be the best form of marketing for our company to date.

We have many new prospects lined up for 2002.  Furthermore, we are eager to prove ourselves – as we have in the past – with new projects for new and present clients.

Our goal is to continue streamlining and automating business processes for our clients, helping them decide whether systems should be custom built or acquired (which may still require modifications) from software vendors, maintaining the objectivity of consultants and the efficiency of software developers.  We wish to continue teaching our clients how to be as self sufficient as possible, which is the creed of the objective consultant.

 

Spreadsheets and Databases

By Tom Chenault

 

This article has been brought back 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®.  This was a very popular article.

 

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 languages, such as BBL, Profit II, IFPS, Model, Express, etc. that provided financial modeling type spreadsheets.  “Decision support system” was the big pitch 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."  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 management 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.

Nearly every month we see spreadsheet products, such as Excel or Lotus, being stretched beyond their limits when perhaps the application would be better placed in a database system.  This is hardly a surprise when one considers the computer professionals, who in the past had determined what data best fit a spreadsheet and what 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 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 or R:Base, store formulas in update screens and reports, but not in the tables where the data is stored.  The primary job of the spreadsheet is the analysis of small amounts of data.  The primary job of a database product 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 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 always used for custom-built accounting systems.  For example, an accounts payable system could have one table to accumulate vendor invoices with another “related” vendor table.  The vendor table will contain fields, such as vendor number, vendor name, address, etc. and would be considered 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 number is already entered into the vendor table.  This is one of the main concepts behind relational databases.  Also, redundant vendor names will not be repeated over and over again in the invoice tables.  This insures optimum system speed and storage, not to mention data accuracy and validation.

The primary job of the spreadsheet is the analysis of small amounts of data.  The primary job of a database product 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

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 be a combination of vendor number, invoice date, and invoice number to insure the uniqueness of each invoice transaction.  Database products are backed up with extensive programming languages to allow for complex business situations.  For example, with Access, you can use Visual Basic or SQL procedures with SQL Server to handle any complicated reporting or validation procedures.  Typically Access is the user-friendly front end for forms, queries and reports and SQL Server is the “back office” for industrial strength data maintentance in the form of tables.  FoxPro, along with other database systems, have the Structured Query Language (SQL) for those queries or reports to select records based on certain screening and sorting criteria.  Spreadsheets use “macros” to handle procedures that are repetitious, such as the consolidation or the adding together of multiple worksheets after one data item has been changed (what if analysis).

In summary, a great deal of applications start out as a spreadsheet prototyping the resultant database application.  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 of amount of data

·         Simple validation

·         Matrix oriented

·         Future projections

·         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

·         Infinite number of rows (records)

 

Quotes Worth Noting

“The first and last thing required of genius is the love of truth.” – Johann Wolfgang von Goethe

 

“They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety.” – Benjamin Franklin

 

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.