METRIX – A Free Database Solution for Nonprofits

Fundraising & Grants Innovations Internet Resources Interns & Volunteers Government Special Populations Community Resources Training Troubleshooting Planning & Management

Tech News is proudly supported by IBM

METRIX – A Free Database Solution for Nonprofits
written by Arthur Vincie
Tech News Staff Writer

When it comes to databases, many organizations spend countless hours struggling to answer the questions that lead to a final business analysis for database deployment. In past Tech News issues we have discussed at length the 'build or buy' decision and the factors an agency weighs to determine if an off the shelf application or custom database is required.

The Fund for the City of New York (FCNY) has been working long hours for the past three years to show the sector a third way…download and install a database for free, then customize the product to meet your specific needs. This is more of a combination of buy vs. build. You get the off-the-shelf product that does 70% of what you need, then you only need to invest in the other 30%, saving time and resources.

Named Metrix, the database product has moved through several beta-tested development lifecycles with significant project success such as a city-wide deployment to benefit community-based organizations providing youth development services.

Developers and users have been given an open path to communicate with FCNY on everything from suggested enhancements to product questions.

Now with Metrix, FCNY aims to "leverage the common needs small agencies face by developing a scalable solution for groups lacking resources"
Sean DeWitt
Project Manager, Metrix, FCNY

The product is built with proprietary Microsoft database tools but is distributed with a General Public License (GPL), allowing users the ability to freely change the software and remove the burden of any per user or seat cost.

Catalyst for a free database solution
Sean DeWitt, the Project Manager for the effort, recently described the catalyst that drove FCNY to jump into the application development space. "Many similar database project requests have come from small nonprofit clients over the years leading us to this, " he says.

When Sean sat down with Tech News, he described an industry where two significant trends have emerged in recent years:

All funding streams – – be they government, corporate, foundation or individuals – – seek primarily to support programs.

Although limited dollars support administrative infrastructure, donors are looking for measurable outcomes.

DeWitt explained that the consultant team at FCNY made a tremendous effort working with small groups with limited resources for custom application development over the years in both FileMakerPro and Access. Now with Metrix, FCNY aims to "leverage the common needs small agencies face by developing a scalable solution for groups lacking resources, " he said.

Staff Training
FCNY, which trains over 4,000 nonprofit staff every year in technology and computer applications, has redesigned its database training program component to integrate with the Metrix application.

Upgrade Path
The system has a built-in upgrade path and FCNY continues to routinely roll out new modules of the application for database users, free of charge.

FCNY provides over 500 pages of documentation and envisions a website with a large library of downloadable customizations that nonprofits can contribute to and share freely.

In Summary
The Metrix product is still in development and a work in progress with early users in New York City working to eliminate bugs. FCNY believes that Metrix is currently most suitable for organizations that have a staff proficient in Microsoft Access.

The effort and vision that have gone into the product to date are significant. If successful, Metrix will deliver a tremendous benefit to community-based organizations struggling with ongoing database development efforts. Local organizations with the internal IT knowledge to review, use, and provide feedback to the Metrix team are critical to the long term effort at FCNY.

TECHNICAL REVIEW NOTES

CODE AND DATABASE SEPARATION
Metrix is built as a multi-user application. The server portion of the application consists of an MSDE (Microsoft Data Engine) database, which holds all of the data. The client application consists of an Access project file (metrix.adp), which holds all of the forms and reports, and which accesses the data through linked tables.

MSDE is a free, scaled-down version of the SQL Database engine. Access was built with single or small work-group databases in mind, and doesn't handle multi-user situations as well (performance tends to bog down). MSDE, on the other hand, handles multi-user situations quite well. MSDE also includes stored procedures functionality (a feature missing in Access). Stored procedures are a powerful method of extracting and manipulating data from within the database, rather than on the front-end. They tend to execute faster than front-end code, and can be used to aggregate, retrieve, add, append, or delete records (or even single values).

One advantage of this separation of code from data is that the client application can be installed on as many machines as need be, whereas the server application only needs to be installed once. Another advantage is that the client application can often be modified without affecting the database.

Metrix is clearly a "deep" application, which can grow and adapt to an organization's needs. For this reason, it's best customized by developers before being released to the general user base within an organization, or by staff with equivalent knowledge in Access. As with many open-source projects, the long-term success of Metrix will depend on the loyalty and friendliness of its user base. Ideally, I'd like to see an active Metrix user group that shares tips, factoids, and custom tables with each other.

Arthur Vincie
Tech News Staff Writer

BACK END INFORMATION
Metrix is a fairly "table-heavy" database. Functional modules (such as contacts, calendar tasks, etc.) are broken down into a highly normalized set of tables on the back end. For instance, lookups for drop-down boxes generally each have their own tables.

Stored procedures and queries tie the pieces back together. Metrix relies on stored procedures and server-side views for data retrieval and manipulation. This does appear to speed up performance of the application overall.

Naming conventions are very well observed ("sp" prefixes for stored procedures, "vw" for views, "tbl" for tables, etc.). Generally, names are easy to understand and relationships between tables are fairly obvious.

The developers have taken extra measures to ensure the "legibility" of the database. For example, the "tblUtil" prefix is used to name tables that are used to hold data relevant to the application (as opposed to user data). "Tlkp" refers to tables used as lookups (for drop-down boxes). After only a few minutes of looking through the database, I felt that I could figure out any part of it with just a little effort.

OVERALL SENSIBILITY
Metrix was designed to be open-ended and highly customizable. Thus, its menus, functionality and much of its appearance are actually data-driven. Menus and submenus are stored in the database and are manipulated through code in the client app. It's possible then to add (or subtract) menus. You can also add (or subtract) key functionality, and build custom tables to hold organization-specific data.

Metrix ships with contact management capabilities, shared calendar functionality, and basic integration with Excel and Word. It also ships with a powerful query-by-form wizard, which allows the user to create (and save) highly customized queries and reports. It also comes with a set of administrative tools which allows the user to edit drop-down list values, add database users, and import data from other sources.

Developers are encouraged to extend the application in a number of ways. Metrix ships with three other Access project files, designed to be used by developers to extend the application:

1. Interactsql.adp links directly to the back-end database and provides developers with a view of the structure. It also includes several forms that can help developers add "modules" (functionally related groups of customized tables) to the application.

2. Reports.adp links directly to the back-end database and holds the shared reports for the database.

3. Devmodule.mdb contains code and forms that allow developers to easily define and add custom tables to the database.

In addition, the admin tools mentioned above include a "Developer" Console, which contains various helpful guides and items for developers.

Finally, the application ships with a set of customized tables, and includes instructions on how to add more.

LOOK AND FEEL
The Metrix application is very smart-looking and well-designed. The developer avoided garish colors and tried to keep screens as uncluttered as possible.

My only real complaint is that clicking on a top-level menu item opens up a new form without closing the old. This will confuse some users, as it means that the Windows taskbar will quickly fill up with non-closed forms.

The custom query wizard is wonderful. It very easily handles what is, in reality, a very complex chore, and hides much of the complexity from the user. This is a significantly valuable feature.

The help files are genuinely helpful. I only wish they had compiled them into a PDF version as well. The FCNY website includes installation notes and a bug reporting form.

The installation instructions were mostly helpful, but it still took me several installation attempts on my Windows XP machine before I was able to use the app. I would recommend that the developers focus on a single installation program for all the installation chores.

FCNY is promising a new release of Metrix in Spring 2005, so I'm hopeful that many of these small issues may be resolved.

Metrix is clearly a "deep" application, which can grow and adapt to an organization's needs. For this reason, it's best customized by developers before being released to the general user base within an organization, or by staff with equivalent knowledge in Access. As with many open-source projects, the long-term success of Metrix will depend on the loyalty and friendliness of its user base. Ideally, I'd like to see an active Metrix user group that shares tips, factoids, and custom tables with each other.

Search Home About Tech News Events Link Library Feedback Site Map Subscription