An Article by Denim Group’s CTO, Dan Cornell
How do you protect your data?
From the smallest mom-and-pop to the largest multinational, every business has the need to compile, organize, store and analyze data. Implementing an information system for your business can sound daunting, conjuring up images of rooms full of rack-mounted computers and teams of technicians and programmers. Occasionally, however, you need to make a quick business decision – and in all likelihood, you already have powerful tools at your disposal to help you do just that.
Microsoft Excel and Access, contained in the Office suite, are excellent for small-scale data compilation and analysis, but you should use caution when looking at the long term. With these types of quick-and-dirty data structures designed to meet an immediate need, it can be too easy to lose control – and this means a potential loss of valuable, and perhaps irreplaceable, data.
Excel: The Financial Analysis Swiss Army Knife
It’s multi-functional, familiar and inexpensive – but it has its limitations. Excel, Microsoft’s flagship spreadsheet application, is an excellent tool for prototyping financial projections, computing statistics and running calculations to feed into other information systems such as an organization’s General Ledger. It has a staggering variety of computational abilities, and it is surprisingly user-friendly, approachable and flexible. The danger lies in expecting too much of the application.
Although Excel is capable of creating spreadsheets with very complicated calculations, automating extremely complex tasks can (and often does) create unmaintainable and unverifiable systems. Using a true database system such as Microsoft Access or Microsoft SQL Server for this automation is far preferable in these situations.
Excel also does not have the capability to control who can change functionality and formulas, and it cannot provide version control. One way to sidestep this problem is to store Excel spreadsheets in Microsoft SharePoint site systems. This insures the integrity of your data by allowing you to maintain a history of changes for auditing purposes.
Moving up the scale, Access allows fairly non-technical users to create sophisticated data-backed applications. While it is markedly sturdier and more robust than Excel, Access has limitations of its own.
First and foremost, Access is not scalable. Once your datasets begin to reach even a medium size (50MB or so), it can be risky to depend on Access to maintain and secure them.
Access also does not support multiple concurrent users. This inability makes it less than useful when attempting to integrate it with applications that require simultaneous access, such as most web applications.
These limitations don’t mean you need to toss (or worse, redo) that unwieldy Access database when you’re thinking of putting it on the Web. Migrating the data storage functions to Microsoft SQL Server will allow applications to support arbitrarily large datasets and many concurrent users. Microsoft also provides upsizing tools that help to automate the migration process.
Don’t Wait for it to Break Before You Fix It
Every business accumulates data as it grows. One key to successful data management is using the right tool for the right job.
Whenever possible, attempt to plan for the long-term applications of what might appear to be a short-term solution. Many in-place short-term solutions may need to be re-evaluated to ensure they have not inadvertently grown beyond their underlying data storage technologies. There are a number of Microsoft applications available to help support the growth of system structures that are found to be at risk.
While nothing substitutes for accurately assessing and evaluating a business need, the same tools used for a quick fix may also provide the ability to implement a more permanent one.