GASCO, a large oil & gas exploration company, manages a multi-billion dollar real estate portfolio that spans the globe. We are using the pseudonym "GASCO" (more) to protect their confidentiality from search engines. About 200 people are employed to manage these properties. To help coordinate and measure their efforts, an Asset Management System, AMS 2.0, was developed. AMS was designed to help property managers maximize the return on GASCO's real estate assets.

AMS 2.0 includes the following features:

	Instantly accessible from any Web browser on GASCO's Intranet
	9 standard reports and an ad-hoc HTML report designer on-line, each with its own selection criteria
	19 tables maintained on-line
	Data import from external accounting system
	Access authorization by table and transaction type
	Built-in data dictionary and input form designer
	On-line help for every page
	On-line Programmers Guide with hyperlinks to ASP source code
	Software updates can be deployed without interrupting service to on-line users

AMS Main Menu Screen (click to zoom in)

In July, 1997, GASCO was prepared to admit that AMS 1.0, developed in early '97, was not meeting their needs. AMS 1.0 did exactly what was required in January, 1997. But, the business did not stand still. As new requirements surfaced, AMS 1.0's heavy-client architecture presented an insurmountable barrier to change. To deploy the slightest enhancement, a 9MB setup program had to be tested, distributed and executed on about 200 PCs world-wide. In addition to user inconvenience, the support and training burden for deploying a new version of the software was significant. Most PCs could support AMS 1.0 without difficultly. But, there were a handful that seemed to resist every attempt at a smooth installation. Other PCs had insufficient disk or real memory to provide acceptable performance.

Software distribution and installation wasn't the only problem. Property Managers were having difficultly getting reports that focused on the problem at hand. The criteria used to select records for reports was insufficient and too much information was usually reported. Only a handful of GASCO property managers were prepared to use Microsoft Access to get just the information they wanted when they wanted it.

GASCO needed someone to lead the effort to replace AMS 1.0. That someone needed both the technical background to manage the AMS 2.0 software selection and/or development effort and the business background to understand his property manager's requirements. That someone needed to have good working relationships with key managers throughout GASCO's Asset Management Group. That someone had to have enough ambition to risk failing at GASCO's third attempt to deploy a successful Asset Management System. That someone was Darryl Lycett.

Darryl began to research the assignment in late August, '97. First, he had to get to the root of AMS 1.0 problems and then he had to decide whether or not they could be fixed. There were many diverging opinions as to what the ideal system would be, but within 30 days, Darryl was able to forge a consensus on AMS 2 requirements. He then had to make the "build vs. buy" decision. Darryl investigate several AMS-like packages, but they all had the same fundamental heavy-client flaw as AMS 1.0. AMS 2 would have to be built. But, what thin-client architecture should he choose?

Darryl approached The Logical Product, the consulting firm that acted as general contractor AMS 1.0. He reasoned that, since they were familiar with the application, that they might offer the best terms for a rewrite. As with AMS 1.0, The Logical Product subcontracted the job to Carl Kelley of Page Positioning. Carl's first task was to demonstrate a thin-client alternative.

Mr. Kelley accepted Darryl's challenge and, delivered a prototype in late September '97 based on Microsoft Visual Basic 5.0 ActiveX Documents in early November. Using ActiveX Documents, a VB5 application runs inside Internet Explorer 3.0x such that client software components are installed and updated as needed. The AMS 2.0 ActiveX Document prototype demonstrated that AMS 1.0 forms could appear inside of Microsoft's Internet Explorer 3.02 or better. The prototype software could also be installed on the server and client PCs were automatically updated via the ActiveX Internet Component Download facility.

We discovered several problem's with the ActiveX Document approach:

Richard Kimball, GASCO's Network Administrator, suggested that we use Active Server Pages (ASPs) instead of ActiveX Documents. In just two weeks, Mr. Kelley delivered a credible prototype that put an HTML face on the AMS 1.0 Oracle database. The prototype allowed the user to select records from one of several tables, display a hit list and select a record for update. It did more than its AMS 1.0 counterpart. But instead of being deployed inside a 9MB setup program, only a 50K zip file was required. Installation is immediate, there is no setup program. Most of these benefits are realized because ASPs are interpretted and the 9MB interpretter is already installed on the Windows NT server.

Mr. Lycett, who resides in Los Angeles, and Mr. Kelley, who resides in Berkeley,CA, met face-to-face exactly once to hone the requirements at the beginning of the project. Light-weight ASP deloyment made it possible for Mr. Kelley to respond to Mr. Lycett quickly and effectively.

Here is a list of the main reasons GASCO chose Active Server Pages as the basis for AMS 2.0:

	ASPs can use any Web browser for a client
	ASPs do not require any software to be installed at the client other than a Web browser
	ASPs do not require a set-up program to install.
	ASPs can be added, replaced and deleted without interrupting service to on-line users
	ASPs are small, which facilitates off-site development
	ASP subroutines are more reusable than VB because they can dynamically generate their own HTML user interface.

It took another month to freeze the requirements and execute a fixed-cost development Agreement. AMS 2.0 was specified to work like AMS 1.0 with the addition of an ad-hoc report designer and migrating the database from Oracle 7.2 to SQL Server 6.5. By late November '97, the development effort began and project milestones were reached as follows:

The remainder of this document goes into more detail about the nature of AMS 2.0 and what it took to successfully deploy the system.

GASCO has adopted SQL Server as their standard DBMS, so the first development hurdle we faced was moving the AMS 1.0 data from Oracle to the new schema in SQL Server. The lessons learned during this database conversion effort include:

	There is a laundry list of SQL syntax differences between Oracle and SQL Server. All of the table and view creation scripts including constraints had to be reinvented.
	You can't just copy the data from the old database schema to the new. You have to apply all of the new schema's validation criteria not only to insure database integrity but to test the new schema.
	Once the data is moved, you need to be assured that the transfer was complete. Its important to have accurate record counts.
	The system-maintained data dictionary tables are so different that equivalent data dictionary views could not be created in SQL Server. Instead, application-maintained data dictionary tables were substituted.
	More significant differences between Oracle and SQL Server are found in the stored procedure syntax. If portability is a requirement, we recommend that stored procedures be avoided.

A system can only deliver its benefits if people use it. Here are some of the many conveniences built-in to the AMS that made it easy to learn and easy to use:

AMS Property Update Screen (click to zoom in)

Without good data, AMS reports cannot support good decisions. AMS relies on data that only GASCO's professional property managers can supply. These people are not data entry clerks. The AMS 2.0 database is both accurate and complete because its users were willing to maintain it. AMS 2.0 was designed so that data entry effort is minimized. In the Ease of Maintenance section below, you will see how the input form layout tool makes it easy to start with a small number of data entry fields and gradually increase that number over time. The input form layout tool enables a non-programmer to hide, expose and rearrange the columns of any AMS 2.0 table. Discrete value constraints can be added incrementally as well.

Here are some of AMS 2.0 conveniences that make it easier to enter data:

Automatic Budget Record Generation	Whenever a master Property record is added, all of its related budget records are automatically created.
Automatic Budget Rollup	Whenever a budget record is updated, values are automatically propagated to future current-year forecasts.
Pull-Down Menus	Fields with discrete value constraints as well as fields that reference rows of other tables,a.k.a. foreign keys, are entered by pulling down a menu.
Consistent Error Handling	Error messages always appear juxtaposed to the offending value.
Data Entry Hints	A data entry hint can be defined in the AMS 2.0 table column dictionary for any table column that can help users avoid errors.
TAB Navigation within forms	As with any HTML form, the TAB button allows operators to quickly jump from field to field from left-to-right, top-to-bottom.

AMS Import Data Screen (click to zoom in)

The accounting system must feed AMS 2.0 the actual revenue and expenses associated with each property so that AMS can track budgets. Data integrity must be managed when transfering data from one application to another and the AMS 2.0 import utility provides extensive error checking.



The AMS database administrator may select any one of the available import files for processing. Importing records into AMS 2.0 is a two-pass process. If any errors are detected on the first pass, no records will be posted.

The data import facility offers the following conveniences:

Comprehensive Input Validation	All imported records are validated as though they were being entered manually.
Import Record Layout Specifications are Explicit	If an formatting error is detected in the input text stream, the problem can be quickly isolated.
Optional Detailed Error Messages	Brief and verbose error message modes are supported.
Restart at N-th Record	The import process may be started at either the beginning of the import file or the n-th record. This speeds up the process of recovering from errors.
Records are Inserted or Replaced	If a record is already on file, it will be replaced to facilitate accounting adjustments.

Today, an AMS 2.0 user typically performs the following actions to display a report:

AMS Record Selection Form

AMS Selection Hits Form (click to zoom in)

In addition to the standard reports listed above, users can maintain their own ad-hoc report definitions. The ad-hoc report builder allows them to:

	Select any combination of AMS tables in addition to the property table
	Select any combination of columns from selected tables in addition to unique key columns
	Select sorting precedence
	Select consolidation levels for currency sub-totals
	Update or delete existing ad-hoc report definitions

AMS 2.0 is designed to allow GASCO to perform many database administration tasks without the assistance of a programmer. Their business is, thus, able to adapt quickly to new requirements without expensive software upgrades or down time.

AMS 2.0 database maintains a lot of information about itself including:

	Table captions
	Column labels
	Column data entry hints
	Discrete values for encoded columns
	Encode column value descriptions

Field validation criteria is driven by ADO properties. As a result, the many database maintenance operations that can be performed without the assistance of a programmer including adding a new column to an existing table.

We could expect AMS users to master different dialogs for browsing and maintaining each of its 19 tables. Nor did we want to maintain 19 different sets of Active Server Pages. Thanks to ADO and our data dictionary, we were able to provide one simple dialog for all tables supported by one set of ASPs.

AMS Property Navigation Screen (click to zoom in)

AMS login screen (click to zoom in)

Access to AMS 2.0 is authorized by user IDs and passwords maintained in the AMS Employee table. After a successful login, the functions and data that a user can access is determined by their job function, also recorded in the Employee table. The Employee table is maintained by the AMS Database Administrator.

AMS 2.0 functions are dynamically enabled/disabled depending on the User’s relationship to the specific property that they are trying to access. By only presenting options that are authorized, AMS 2.0 spares its users the feeling of being denied.

For accountability, AMS records the last login date of each user session and displays the full name of the user in the status bar that appears at the bottom of each screen.