The Data Management Conundrum: Spreadsheet or Database? Part 3

Written by Administrator.

In our previous articles, we compared spreadsheets to databases.  We recommended upsizing to a customised database solution when your spreadsheet reaches its limit of effectiveness.  Since databases require a degree of skill and knowledge, we further recommended making use of a consultant programmer.

In this article, we look at how you can make this decision and how to plan for such a leap.  It sometimes may seem to be a daunting prospect, especially if it is the first time you are going down this road.  These guidelines will help you put everything into a more structured approach.

Define the minimum outcomes required

Take a look at your spreadsheet and think about what, in essence, the outcomes are and write them down.  Try to categorise in broad strokes and then define the detail for category. 

For example, you may have a spreadsheet that tracks sales for each salesperson.  You may require that the outcomes are a monthly breakdown of sales for each salesperson, total sales for the month, sales for each customer and commissions calculations.

Your categories might be customers and salespeople.  Under each category, you would then list the requirements for data capture, calculations and reports in as much detail as possible.

This provides the consultant with the initial framework for the design of the database structure.

Define any additional “nice to haves”

It makes sense that, while you are embarking on this project, to consider the added features or functionality that would be great to have included in the database.

In our example above, you may wish to include an invoicing feature.

Schedule the project

Since you are currently frustrated with the spreadsheet you are using, it would be great to be able to implement your database right away.  However, reality dictates that there will be database development involved, which is proportional to the size and complexity of the project.

Work on two scenarios:  the best scenario, which would be right away, and the absolute worst scenario, which would be a number of months away.  With the latter, determine a date which is non-negotiable for the system to be working and operational.  The ideal scenario is somewhere in between these two, which must be discussed with the consultant.

Remember, the consultant is in all likelihood servicing other clients and needs to schedule his/her work accordingly.

Decide on a budget

Most consultants cost their jobs on a time/value basis.  This will be the means by which they will cost your job, and it is usually based largely on thumb suck estimates, with their experience in the industry. 

Remember, the consultant is an expert in database consulting and development.  They will not necessarily know much about your particular industry.  So, your involvement is key to the success of the project.

When deciding on a budget, it should, obviously, be affordable for your organisation. 

Engage with the consultant

Your initial meeting with the consultant should start off with a discussion about his/her experience, background and capabilities.  References are essential and a portfolio of their work should be available, usually on their laptop, for you to look at.

Discuss your current situation and the problems you are facing.  This should be a fairly lengthy conversation and you should be prepared to show them the spreadsheets and paper-based system you want integrated into the database. 

Ask for their assessment and how they think the problem should be resolved.  Also, ask them what they think the project would cost.  If you fall off your chair at this point, inform them of the budget you have available and find out what they think they can offer within these constraints.

Ask for a written proposal and indicate that you would require a non-disclosure agreement to be signed before proceeding. 

Do not give them any copies of spreadsheets at this stage.  You should, however, be prepared to give them printouts with notes for them to work with, in preparing their proposal.

Arrange the next meeting, in which they will present their proposal and ideas.

This will be the start of something good, in that you will be entering into a partnership with the consultant.  There will, over time, be many modifications to your database, as your business changes.

The Data Management Conundrum: Spreadsheet or Database? Part 2

Written by Ian MacOlive.

In our previous article, we looked at the spreadsheet in some detail and considered the limitations that they can present when a project gets too large.  The upsizing of a spreadsheet to a database is the next logical step. 

Since a database management system, or DBMS, contains the data in a series of related tables, the record-keeping and adding is dynamic and fluid.  Records are simply added, much the same way as in a spreadsheet.  The main difference is that the added records in a DBMS do not interfere with formulae and named ranges.

Databases do, however, have some disadvantages:

Difficulty in construction

Databases require a degree of knowledge and skill, which involves either training or self-study.  Since the concepts are somewhat different to that of the spreadsheet, the knowledge required is often time-consuming and difficult to grasp, especially for the layman.

Knowledge of Structured Query Language (SQL)

SQL has become the standard for database querying and creation of record sets.  It is a time-consuming exercise to learn and implement, through trial and error, effective queries.

While these disadvantages may seem somewhat daunting, don’t despair.  Consider the advantages, bearing in mind the limitations of the spreadsheet:

Size

There is no real upper limit on the size of a database for a vast majority of business applications.  Different tools have different limits, but there are many ways around this, should those limits be reached.

Protection of calculations

Since most calculations, or formulae (known as expressions) are contained in locked objects, queries or code, they cannot be meddled with or accidently deleted.

Multi-user

Databases offer a true multi-user environment, with secure record-locking.  Many users can capture data simultaneously.

Data validation

Data types and other tools, such as input masks and drop-downs, force the correct type of entry.  In the case of drop-downs, or combo boxes, the user can be forced to select an item in the list.  This avoids those erroneous spaces and punctuation marks from creeping in and messing up the data.

Data relationships

With data tables being joined, forming a relationship, rules can be applied to ensure integrity.  An example would be that a sales order cannot exist without a customer.

Customised capture screens and reports

Some people say that a computer program is only as good as what goes in and what comes out.  What goes in is represented by the capture screens, or forms.  These can be customised to suit your requirements, with ease of use in mind.  What comes out is in the form of the reports the system can generate.  Using powerful queries, the reporting ability of database systems is extremely powerful.

Data movement

Data can be easily imported or exported in a variety of formats, from queries or reports as the data source.  Many people like to manipulate records in a report by sorting or grouping easily.  Reports are effortlessly exported to spreadsheets, where this can take place.

User-level security

Users can be allocated to groups to allow access to only certain areas of the database, thereby maintaining information confidentiality, when required.

The most effective way to overcome the disadvantages listed above is to employ the services of a consultant database programmer.  Such a person would be able to analyse your existing systems and offer a viable and affordable solution.

Currently, this industry is highly competitive and this places you, the client, in the ideal position to explore your options.

 

The Data Management Conundrum: Spreadsheet or Database? Part 1

Written by Ian MacOlive.

The spreadsheet is a powerful tool for performing complex calculations and data storage for small projects. 

When VisiCalc hit the scene back in the eighties (in the last century), the relatively small group of Apple II users went crazy.  It was like manna from heaven for the poor accountants, scientists and engineers who were used to filling in enormous analysis sheets with a pencil.  Now, they could use VisiCalc to do the same task, AND calculate the result automatically.

Over time, the spreadsheet grew into what we see today, in the form of Microsoft Excel and a number of similar products on the market.  The power of the spreadsheet has grown almost exponentially, allowing the user to create customised formulae, charts, pivots and so on.  Worksheets can be linked and updated automatically.

For pure data analysis, the spreadsheet is, indeed, king.  To a point…

Despite all of these extremely powerful features, the spreadsheet has limitations, which tend to manifest when you least expect it.  Often, Murphy’s Law applies and the spreadsheet falls over when you are right in the middle of a major undertaking, like month-end or meeting a tight deadline.

Spreadsheets have significant limitations.  Here are a few:

Size limitation

When a spreadsheet is first constructed and tested, the size tends to be small.  However, as the spreadsheet grows with more and more data, it start slowing down, until it reaches that point where your feet go to sleep while you waiting for the re-calculation to finish.

Protection of formulae

How often have you forgotten to protect the cell contains the formula that took you hours to test and perfect.  It takes one instance of honest human error, and that formula is gone.

One user at a time

In the instance where there are a number of people required to work on a spreadsheet, they can only access it when no-one else is working on it.

Inconsistent data

This becomes most apparent when trying to find a particular record of cell, based on the contents.  If a space is incorrectly added in the wrong place, the search or filter fails. 

Not dynamic

Data ranges become redundant when new records are added, resulting in established formulae needing to be altered.  Overlooking these errors can be embarrassing and downright expensive, especially if important decisions are being made based on the data.

 

When your spreadsheet start to hit its limits, what options are open to you?

Perhaps the best option is to consider the conversion of your spreadsheet to a database solution.  Relational databases became viable not too long after the spreadsheet was well-entrenched as the affordable and quick means of gathering data and performing calculations.

Early databases available for the PC market were simple two dimensional solutions and were essentially designed as a record-keeping system.  However, when computing power increased, the advent of the relational database became an affordable option.  Applying the rules proposed by Edgar Codd, the Database Management System (DBMS) became a reality, with products such as dBase being widely implemented in the desktop market.

Today, there are a large number of options to choose from.

The downside to upsizing your spreadsheet to a database system is that databases require fairly in-depth knowledge, especially with regard to extracting data in queries, form construction and reporting.

Rather than having to go through the pain and suffering of learning a new technology and having to experience trial and error development, the best route would be to acquire the services of a developer, who can identify your needs and create a customised solution.

Obviously, the cost of such a project would depend on the size and complexity of the spreadsheet and your requirements.  However, you might be surprised at how cost-effective this project would be.

In Part 2, we will look at the features of a database management system and the power it can deliver.