Spreadsheets vs Database
Why do so many of us use spreadsheets for databases? A spreadsheet has
serious drawbacks when used for data storage, is cumbersome to retrieve data
from any but the most simplistic queries, offers little or no data validation
and little or no protection against data corruption from well-meaning but poorly
trained users. Why do we stick with Excel? In a word, familiarity.
Excel is better at a lot of things—displaying charts, showing PivotTables,
displaying different types of data on the same worksheet. It's pretty simple in
the current versions of Office to pull the data you need from any database into
a spreadsheet, though. Store data in a database, work on it in a worksheet. You
may hold out in Excel for a while, but several things may
be conspiring against you, plotting your exile from Spreadsheetland, forcing you
to create your first database.
Multi-User Access (no pun intended)
The number one reason to create a database is that multiple people need
access to a file. Of course, it doesn’t have to be simultaneous
access. Five people just need access to the file some time during the week
before the Thursday 2:00 pm meeting. Surely they won’t all try to make their
changes between 1:45 and 1:59, right? Surely one of them won’t go off to the
meeting with Excel still open on their desktop, right? Yeah, right.
You’ve no doubt experienced the “Someone else is using this file, go away”
error message above. The typical response is to open the file read only and then
create an independent copy using 'Save As' another file name—invalidating the
reason for ‘sharing’ the file in the first place. Databases allow multiple user
access without issues.
If you have Excel 2003 or 2007, you might be able to get around this issue by
sharing your workbooks. A shared workbook allows multiple users to have
simultaneous access to a workbook. There are a variety of issues with sharing,
but maybe it will work for your needs.
Keep Your Data Safe!
Other reasons to create a database are data integrity and data validation.
The worst nightmares ever seen in Excel have been caused by the Sort function.
Someone sorted on last name, but not any of the other columns. Someone sorted on
account code, but not on amount.
It’s a Rubik’s cube of data and there is no way to correct the spreadsheet.
Just delete it and look for the latest backup tape.
False dates like April 31st or February 30 can also cause havoc in a
spreadsheet but will be validated and disallowed as a matter of course in a
database. The product number 0042 which gets changed by Excel to 42 or the part number
3/14 which gets changed to March 14 are other examples of data corruption which
would not take place in a database.
(continued...)
Many public companies are being forced to move key reporting data out of
spreadsheets by the Sarbanes-Oxley Act, Section 404. Since the control over
spreadsheets is so limited, they simply cannot keep the data in Excel and
comply. Access, in this case, will not be an option either. I Get It!
Development can also design a database for SQL Server which does meet security
and control regulations and port the spreadsheet data. A qualified auditor can
inform you of what level of security and control is needed for what types of
data.
Read our success story
detailing how we helped a world-leading application infrastructure company
move hundreds of Excel spreadsheets into a database which still allowed
Excel spreadsheet output for management and accounting review.
Eschew Obfuscation
Data duplication is another reason for moving data out of a spreadsheet. If
the rows of your Excel spreadsheet contain redundant data like a customer name
and address on each row of an order, you’re wasting a lot of space.
That’s not the major issue since disk space is now so cheap—the real problem is
that redundant information eventually gets out of sync. The customer’s address
is different on 3 of 100 lines… Is that the new address or is it the old one?
There’s a saying about a man with two watches—he never really knows what time it
is. A relational database stores data in one place and minimizes redundancy.
Data can be updated in one place and only takes up the space it needs.
Do You Need A Database?
Take a look at the spreadsheets you use for storing data and ask
yourself if the data wouldn’t be better stored in a database.
- Is the data for long-term storage (as opposed to a work-in-progress)?
- Do multiple people need access to this data?
- Do I need to safeguard against erroneous entries?
- Does the data need to be protected against inadvertent corruption?
- Is a large part of the data redundant?
If the answer to any of these questions is yes, contact us directly
and we’ll be glad to help you migrate your spreadsheet data into a relational database. You’ll be glad you did.
|
|