Tuesday, November 30, 2010

Getting Data Out of Your Database

I still come across too many database implementations where the users simply cannot get the data out of their system unless they have a degree in rocket science (well, a qualification in normalisation and ER diagrams at least), and even then, it doesn’t always give them what they want. And it is such a fundamental aspect of any database that it negates half the purpose of implementing the system in the first place: so many of the benefits of a database come from reporting, analysis, querying, mailings, data extractions, segmentation and so on, so if you can’t get the data out of your database then what is the point of having it?

Remember this incorporates two key elements: obviously, the creation of the actual reports, mailing fields etc is one, but the ability to select and segment on the required records/data is another. Even if you can get the necessary data fields out of the system, you still have to be able to extract/report on just those records/supporters which you are interested in on a specific occasion. That is an area which some systems still fall down on unless you do have technical skills.

So what you can do about this to prevent this from happening?
The ideal scenario for most small to medium (and many larger) charities and NFPs is that trained users – there will always be an element of training – can get the data out of the system for many of the above purposes, at a fundamental level at least, without recourse to an IT/database expert. That usually means having a GUI approach (Graphical User Interface) of some sort (i.e. rather than having to write SQL) and/or having flexible enough reports and screens that are parameter-driven so that users do not have to understand how the data is stored or structured. They clearly still need to know where the data is stored, in which fields, and how the data is used but not so they have to know any programming skills.

Such interfaces might be native to the product (perfect if they are flexible enough) and if a product has a good built-in report writer and query tool then that is a great option. That said, the key word there (rather obviously) is “good”, because if it still can’t extract the data the users want then it may as well not be there.

An alternative, and often still a good option for flexible data reporting/extraction etc is through a third-party reporting/analysis application which can access the database; e.g. Microsoft SQL Reporting Services, Crystal Reports, QlikView. Such tools do vary in their level of ‘technical knowledge’ which a user needs, so you need to choose carefully to ensure your users are comfortable with whichever product is selected.

Ideally, the database supplier would bundle such software with their system so that it is integrated ‘tightly’ enough in order that there is minimal additional work needed by the users to get the data integration aspects set-up and then use the data. Or at least provide documentation on how to integrate with their table structure. Even Microsoft Access query tool is a start – it leaves plenty to be desired, and there are better ways of querying a database - but at least you don’t have to know SQL..

Extracting all data
But even if the above is not possible (and actually, in most cases, even if it is) then the system must at least allow you to be able to do a straight forward data export of the data into another system where you can query it and use it further; e.g. Excel, Crystal Reports, SPSS and so on. And again, ideally by selecting the data and records through a native interface within the database. The problem with this is that very often extracting data from what is probably (hopefully!) a normalised database into a “flat file format” means data manipulation in another system can be complex and time-consuming because of “duplicates” created from “one to many relationships”.

But if your database can’t do that, or if it only offers limited tools or limited access to particular data entities, then you may need to resort to using SQL to enable the reporting, analysis, segmentation et al. But that means having serious IT skills and also learning and understanding the underlying database structure and that is not always easy if you didn’t design it yourself. Certainly it is not for the faint of heart and I sympathise with anyone who is not an IT person and has to do this.

The other critical factor…
However, even after saying all that, and even if you can get to the data in one way or another as per above, then there is another critical factor which must be considered: and that is that you must be able to access, query, extract, report on any item of data which you are storing - and unfortunately, some database packages do not always allow this, which is incredible, frustrating and very disappointing. If you are using SQL then that shouldn’t (?!) be a problem, but if your database has its own native analysis/extraction tool, then it isn’t always the case. 

So if you are looking at procuring a new database or you are about to take over the management of an existing system, or even if you are a fundraiser who would like to ensure that they can access their data in a useful and meaningful way, then do ensure that you can get the data out of the system as well as getting it in.

No comments: