Microsoft ASP / SQL Server Case Study: Real Estate Asset Management

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.

The View from the Top

"AMS 2.0 not only helps us make good decisions, it's actually fun to use." -- Darryl Lycett, AMS Project Manager

• 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

In just 17 calendar weeks, AMS 2.0 was deployed on time and on budget to 200 users nationwide. AMS 2.0 required over 23,000 lines of VBScript, HTML, JavaScript, Java, SQL and Tranact-SQL. Programmer productivity averaged 225 lines of debugged source code per day.

Today, Darryl can go to any Web Browser on his Intranet and get a real-time report of his asset balance sheet and just about anything else he would like to know about the properties that he manages. But, as Darryl will tell you, it wasn't always that easy.

"Before AMS 2.0, our software platform was inflexible and could not keep pace with our rapidly changing business environment. AMS 2.0 not only kept pace, but it empowered management to make informed business decisions." -- Darryl Lycett, AMS Project Manager

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.

1. The Players

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.

"One executive told me that if I pulled this (AMS 2.0) off, I'd be a hero." -- Darryl Lycett, AMS Project Manager

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.

"Vendors are always making claims. My position is, "Show me." Carl Kelley was not only able demonstrate what he could do to meet our requirements, but he could also offer fresh alternative solutions." -- Darryl Lycett, AMS Project Manager

2. Active Server Page Prototype

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:

• After the ActiveX Document prototype was delivered, we discovered that GASCO's Network Administrators would not support ANY application that required ActiveX controls, including ActiveX documents. This policy was enforced for security reasons. They didn't want to enable any dynamically downloaded programs that could with read-write access to to client PC hard disks.
• About half of GASCO's Intranet users had Netscape 3.0 installed on their PCs. Netscape can only support ActiveX controls with a 3MB plug-in from Ncompass Labs called ScriptActive. ScriptActive 2.7 supported ActiveX controls, but not VB5 ActiveX Documents.
• At the time, we couldn't get the ActiveX Document prototype to install properly on GASCO's server. The error message that resulted had a dozen probable causes. After several failed attempts to correct the problem, we concluded that there was something lacking in the VB5 master dependency list, which was later fixed by Microsoft in a Visual Studio service pack. The setup required too many dependencies and the programming environment was just too difficult. After two weeks of trial and error, we decided not bet our careers and reputations on ActiveX documents.

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.

"In all my 25 years in the software industry, I have never experienced a more productive environment for developing on-line transaction processing applications than Active Server Pages." -- Carl Kelley, AMS 2.0 software developer

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.

3. System Development Milestones

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.

4. Oracle to SQL Server Database Migration

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.

5. Ease of Adoption Features

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:

5.1 Data Entry Conveniences

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:

5.2 Accounting System Data is Imported

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:

6. Flexible Reporting Features

"After listening to suggestions from AMS 1.0 users, it became obvious that everyone's reporting requirements were different. We needed a dynamic reporting tool that could bridge the gap between static reports and the changing needs of our user community." -- Darryl Lycett, AMS Project Manager

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

• Select the report type
• Select records to be included in the report. Any column of related tables can be used as selection criteria.
• If the report it property-specific, then select the property from the selection hit list.
• Browse the report, which begins with a summary of the selection criteria used.

6.1 Standard Reports Track Financial Performance

6.2 Ad-hoc Report Builder Empowers Users

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

7. Ease of System Maintenance Features

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.

7.1 Data Dictionary Enables the Business to Evolve Rapidly

• 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.

7.2 The Same ASPs Browse and Maintain All Tables

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.

7.3 How Security is Administered

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.

8. Platform and Development Tools