Spreadsheets (Excel) compared to Database
29-Oct-1111 Leave a comment
Triangle of Solution
Based on number of times used:
- 1-5 use a piece of paper
- 5-20 use a spreadsheet
- 20+ use a database
Spreadsheets Disadvantages
- Data should be stored once and only once. Using spreadsheets often creates multiple out of date files
- VLookup is far inferior to SQL links
- Some problems are very difficult to solve in comparison with similar solution in database transact sql
- Limited or no programmatic power dependent on developer
- Out of systems – suggests refactor not found
- Out of security control
- Unable to unit test
- Unstructured data
- A little knowledge is a dangerous thing
- Spreadsheets exported from a database – just adds another step – NOT combine, reduce, eliminate
Spreadsheet Advantages
- More people can prepare them
- Users can demonstrate requirements by attempting to build what they want . Form of tracer bullet
- Possibly more print control
- offset by users knowledge of how to use Context Menu in grids
- why are they printing anyway – why not two screens – are they avoiding IT – find it easier to work with paper
Spreadsheets Interesting
- For users showing advanced spreadsheet skills. Could promote move to SQL Express of other. Or MS Access using ODBC. Or Reporting.
- Could direct them to request views for plugging in
Real-life Examples
- Model in Database exported to Spreadsheet
- Created a model.
- User downloaded to spreadsheet and then edits some data.
- Model is blind to changes.
- Also Model then changed in database and spreadsheet is out of date.
- Also spreadsheet is not used for a few days so data in it becomes a little out of date.
- User wants to export to excel – to print out a stock driven demand. This is a step that was removed before in order to save time.