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.

Thursday, November 25, 2010

Top 50 Database Procurement Tips for Charities and Not-for-Profit Organisations

I have been tweeting my Database Procurement Tips for charities and not-for-profits over the last few months, for all sorts of databases from  fundraising and membership databases, CRM systems, or a bespoke development. So here is a collation of my first 50. Note they are not in any order of importance; the numbers simply (hopefully!) make the list more readable.

I will be tweeting more tips in the week and months to come so if you want to see them then follow me on Twitter.

I hope they are useful!

1. You must do an internal needs analysis before looking at software
2. Make sure you allow a budget for hardware and IT infrastructure costs
3. Create a business case before looking at software
4. Ask the suppliers to bring a support/implementation staff member to the demo, not just a sales person
5. If you create an ITT, don’t just ask yes/no questions, but get the suppliers to give fuller answers to some questions
6. When comparing costs, use a Total Cost of Ownership cost model: i.e. not just the software but all costs
6a. When comparing TCO, do it over 5 years, not just the initial procurement costs
7. At the demo, ask the supplier what weaknesses their software has…
8. Get your users involved in the needs analysis stage and keep up the communication
9. At the demo, try phoning the supplier’s help desk live and see what sort of response they give!
10. Ask for costs in a structured way on your ITT/RFP so that you can compare them more easily

11. Remember that the software is just an enabler – find out how aware the supplier is about understanding your business
12. At the demo, ask the supplier what their software can’t do. Could get some interesting answers…
13. Don’t cram too many supplier demos in one day – it’ll kill you and you simply won’t get the same benefit from them
14. Ask the supplier what they think the key risks are for your project
15. At the demo, ask all suppliers to show you the same things, so you can compare more easily
16. At the demo, make sure you see how you could create reports, do queries, create mailings – get data out of the system
17. If you create an ITT, incorporate it into your contract with the supplier
18. Ask the supplier what experience they have of fundraising, or what they believe the latest fundraising trends are
19. Never under-estimate the costs and complexity of synchronising data across multiple databases
20. Ask to see the suppliers’ roadmap for future developments. If they haven’t got one…

21. Be careful when suppliers talk about Workflow – it can mean different things to different men
22. At the demo, ask the supplier for a reference who used to be unhappy with them but who is now a happy client
23. Ask whether you can segment the database on any and all fields you have in the database, including user-defined fields
24. Consider how to integrate the database with your web site. This can be one of the most costly and time-consuming elements
25. Find out how flexible the system is for importing data from different sources
26. Ask for system requirements and performance figures for at least twice your expected record numbers
27. Don’t under-estimate the time and effort needed for data migration
28. Find out from the supplier exactly how they will help with data migration & what their experience is
29. Purchase time is the best time to get discounts, so if you expect to increase your licenses later, bargain now!
30. Check whether future upgrades are included in your annual maintenance costs

31. Don’t skimp on training costs
32. Ask the supplier for their project management approach, but don’t rely solely on the supplier for this
33. Ask the suppliers how they provide bug fixes
34. Ask the suppliers how they train their own staff
35. Ask the suppliers how they manage Quality Assurance within their organisation
36. Consider the cost of decommissioning your existing database
37. Remember to include the cost of third party apps in your budget, e.g. PAF software, banking sw
38. Don’t expect your data to magically be cleaner or more accurate just by buying a new database
39. Ask the suppliers for their experience, input and recommendations of change management and your new db
40. Consider the option of asking 2-3 short-listed suppliers to prototype a specific element of your requirements

41. Never base a procurement decision on just software, just costs, just the salesperson…
42. Consider visiting a supplier’s offices before you make a final decision
43. Ideally, talk to a number of the supplier’s references, not just one or two
44. Ask the supplier if you can attend one of their user groups before you actually buy the software
45. Ask the suppliers how they recommend you create and structure your implementation project team
46. Ask the suppliers directly what costs are not included in their quote
47. Ask the suppliers why they have lost customers in the past
48. Ask the suppliers how many new clients they have added each year for the past 5 years
49. Don’t expect the supplier to provide a fixed price on data migration until they have seen your data
50. Ensure you have your fundraising/membership strategy in place before buying a new database

Thanks for reading. If you found these tips useful, then you can get more in-depth advice on this area in my eBook. Click on the image below to see more information on that.

My eBook: How to Buy Fundraising Software