When data analysis requirements really get tough, the tough get going- and start to seriously use databases.

Let’s face it, if you’re considering Microsoft Access chances are what you need to get done is beyond what Excel does well, so you’re looking for options. Its also likely that your IT department is unable or un-willing to help you out- this being even more likely as the recession reduces reporting budgets left, right and center.

Two of the key things that lead someone to search for a database solution are:

• Data Volume– More than a million rows and Excel becomes very difficult, even before that the performance suffers.
• Flexibility to Join Tables – Vlookup and VBA code only go so far- Access gives an easy way to make joins between tables, one of the powerful features of relational databases.

Now, the data volume is what it is- if you have millions and millions of rows, you need something to cut it down to size before you move it into your Excel spreadsheet.

On the other point, however, I can hear the Excel fans saying “now wait a minute, Excel can do that, I don’t really need a database” and they are right. But they are almost always right- Excel can do almost anything. It does not mean, however that its the best tool for the job. Using Vlookup and VBA scripts to join up multiple tables is not my idea of a fun time. And even in Excel 2007 I find the pivot tables annoying and prone to break if I’m adding categories, moving data sets or heaven forbid changing number and order of columns.

Microsoft Access has a very nice interface for creating joins between tables, just a simple drag and drop between fields. The cross tab query capability is useful and good, and being a relational database it’s more tolerant of changes to table structure because it’s not messing with cell references.

“But”, many who have used MS Access will say, “its pretty complex to learn, and even if I do start to get the query stuff down, it doesn’t handle bad data well.”

Bad data? Who has bad data? Isn’t all data pristine, as intended, correctly formatted and accurate?

One of the huge differences between Excel and MS Access is that Excel is extremely flexible. (Probably more flexible than your auditor would like, but thats a different story). One source of Excels flexibility is its ability to accept different data types in the same column, and to allow editing of cells quickly. In Microsoft Access, for example, when it sees some variation it either discards the data or defaults to the data type “Text”- meaning now you can’t perform the calculations you need to do on your data.

This illustrates one of the challenges people face in trying to use a database – databases are very strict on data types. Once you declare a data type for a column, if you import data into the table, the database will discard the values that do not conform to that data type. In Excel, you get cell errors if you try calculations but the original data is still there.

One of the powerful features of the Datamartist tool is the fact that it has an underlying database structure that provides flexibility on data types. Unlike MS Access and other databases, Datamartist can store dates, numbers, strings and booleans natively in a single column. (It does not convert to strings- it stores the full object). Take a look at this example:

In each individual row, Datamartist completes the calculation if possible. Datamartist is a database that gives you the freedom of a Spreadsheet. Of course, just like excel, if you ask for a calculation on a value that is meaningless you will get an error- but at the individual value- not a full row discard. This means that with messy data you can still work with it, bring it in, and fix it. In Access or another database, you can’t even get it through the front door (or it defaults to text, making many calculations impossible).

This won’t be the last time I compare these three tools- and the types of data structures and tasks each of them are most effective with.

In the mean time- Download Datamartist– see what I’m talking about with your own data.