| Downloads |
|---|
| 7639.zip |
Designing and implementing a database for a not-for-profit organization is a challenge. Among the limitations and restrictions facing the consultant who undertakes such a job are a tight budget, a one- or two-person information systems group, and a hard-working staff with only basic computer skills. Working in such an environment gives a database consultant the opportunity to exercise imagination and develop cost-effective techniques that get the job done. On a recent project, I worked closely with the Windows NT systems administrator and the office manager of a nonprofit organization to develop a technique to deliver data to users efficiently and inexpensively, using assets already in place.
These lessons apply to more than nonprofit organizations. If everyone in a company who needs information can get to the corporate data in a timely manner and can understand what the data means, everyone can be more responsive to customer and industry challenges.
A Six-Step Solution
If your organization has its data in a database, you can easily give users read access to the data. You can create a series of database views that turn simple data into useful information.
The solution I've crafted is simple; it exploits already-installed software packages and leverages the existing skill sets of the end users, thus minimizing the amount of custom programming and specialized training needed. It provides a cost-effective method of getting the data into every user's hands.
The technique I developed for the nonprofit organization is a six-step process that I believe you can use in most environments. Before you start on Step One, understand the implied Step Zero: know your system. Know what kind of server hardware and software environment you're dealing with. Know what hardware and software is present on the end user's desktop. You probably have a good understanding of the hardware and software installations from performing a systems analysis.
For this article, I use example code and data from the Pubs database. This month, I cover the first three steps in the process, preparing the data for distribution. Next month, I'll cover the last three steps, distributing the data.
Step One: Determine User Strengths
First, determine the strengths of the user audience. In this case, we chose the direct approach—we asked each staff member to complete a self-evaluation profile that contained questions on the current software installed on the organization's computers. We structured the questions to reveal competency levels in the various software packages.
In this situation, everyone had Microsoft Office or Office Professional on their desktop computers, so the software packages we represented on the questionnaire were Windows 9x, Word, Excel, and Access. A review of the questionnaire responses revealed that almost everyone had used or was using spreadsheets in their daily tasks. We wanted to keep the database interface as familiar as possible for the greatest number of users, so we elected to use Excel as the standard interface to the database. We used Microsoft Query and ODBC to retrieve data from the database.
Step Two: Discover Reporting Requirements
Next, discover what information the user audience wants to see. The corporate database stores data, but the users want to see information—data that has been recombined, reorganized, summarized, sorted, filtered, and manipulated so that it becomes useful, in a business sense. Usually, you determine the information and reporting requirements during the requirements analysis phase at the beginning of a project. Compare your notes to the database tables and create a set of views, one for each report. (SQL by Design, "Views and Stored Procedures," September 1999, contains a review of views from a how-to perspective.) These reports won't be highly formatted; they'll be two-dimensional sets of records. Remember to give the appropriate groups SELECT permission on each view.
Listing 1 is an example of view code for the Pubs database, which I'll use to demonstrate this technique. Because of the database's level of normalization, the views are either three- or four-table joins. Notice that the view names begin with an underscore. The technique I'm using to get the data to the end users lists database objects in dictionary order, in which an underscore sorts ahead of letters of the alphabet, so the views appear at the top of the pick list. I've given the public role read permission on this view because everyone in the organization needs to see this information and is part of the public group.
Step Three: Create a Data Source
The key to making Excel work as a database interface is ensuring that all the ODBC components are in place on the users' computers. You'll need to create an ODBC data source on each user's computer. The standard (default) installation of Microsoft Office 97 leaves out the functionality that lets you register an ODBC data source. You can add this functionality by going back to the Office setup program and installing the data-access components. Office 2000's install-on-demand feature makes this process simpler because it automatically prompts you for any missing components it requires.
To create the data source that makes this technique work, start Excel and open a new worksheet. (I'm describing the process for Office 97, but Office 2000 works similarly.) From the top menu, select Data, Get External Data, Create New Query. When the Choose Data Source dialog box opens, select New Data Source and click OK. The Create New Data Source dialog box will open and prompt you to enter a name for your data source, as in Screen 1. Choose a meaningful name that end users will recognize, because in the future these users might be creating queries.
In Screen 1, the data source name is PubsDatabase, and the ODBC driver type is SQL Server. Clicking Connect opens the SQL Server Login dialog box, which Screen 2 shows. Enter the name of the database server and the type of user connection—trusted (passed through from NT) or direct SQL Server login. Click the Options command button to open the bottom half of the dialog box, then select the appropriate database for the data source (Pubs, in this example). Click OK to close this dialog box and return to the previous screen (Screen 1). For this example, you don't need to designate a default table, but do check Save my UserID and password in the data source definition. After you do, you'll see a dialog box announcing that Microsoft doesn't encrypt passwords in the data source definition files. Your options are to save the password and make sure your network is locked securely against intruders, or to not save the password and force your users to log in to SQL Server with every database access. For this example, choose the first option (save the password) and click OK. Your new data source is ready to use.
Then the Query Wizard's Choose Columns dialog box opens with a pick list of views and tables on the left, as Screen 3 shows. The views show up first in the list of available tables and columns. Highlight the view _PublisherStaff, and click the right-pointing arrow to move all columns into the window on the right, Columns in your query. Click Next.
In a large data set, you might want to filter the rows, which the next dialog box (in Screen 4) lets you do. Although this data set is small, let's filter it anyway. Highlight PublishingCompany in the Column to filter box. Use the combo box on the left under Only include rows where: and choose equals. The combo box on the right shows a list of publishers from the current data set. For this example, choose Binnet & Hardley and click Next. The next dialog box, which Screen 5 shows, is the Sort Order chooser. For this example, sort first by job title, then by staff name, both in ascending order. Click Next.
The Finish dialog box lets you save the query, return the data to Excel, or use Micro-soft Query to view or edit the query. For this exercise, choose Return Data to Microsoft Excel and click Finish. The next dialog box prompts you for placement—where do you want to place the data in the open spreadsheet? The default is =$A$1, which is the absolute value of the spreadsheet's upper left cell. Accept the default, then click OK. The result is a spreadsheet full of data copied from the database, as Screen 6 shows. This view of the data is static, a snapshot that you can use, print, manipulate, or include in reports. The view isn't dynamically linked to the database, so the database won't reflect changes you make to data in the spreadsheet.
Using installed software packages and existing user skill sets is a cost-effective way to develop solutions for database access. The scheme I present here, which involves SQL Server views and Microsoft Office front-end software, is one of many possible solutions. With a little creative thinking, you can craft any number of cost-effective solutions. Next month, I'll look at some extensions to this technique.
