Microsoft .NET Expert Inside
Business Intelligence OLAP Business Intelligence OLAP
Product Configurator Product Configurator
Hospital Medical Record Order Entry Hospital Medical Record Order Entry
High-Traffic ASP.NET Web Farm High-Traffic ASP.NET Web Farm
Web Dashboard Web Dashboard
Large-scale ETL from Excel files Large-scale ETL from Excel files
Staff Scheduling Staff Scheduling
ID Scanning ID Scanning
Pharmacy Performance System Pharmacy Performance System
Real Estate Asset Management Real Estate Asset Management
Who We Are Who We Are
Contact Us Contact Us

WinMetrics Corporation Microsoft ASP / SQL Server Case Study: RealEstateAssetManagement

Microsoft ASP SQL Server used for 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.

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

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 for full-scale image)
AMS Main Menu Screen
(click to zoom in)
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.

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

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.

Active Server Pages 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.

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:

Time Frame
Events and Deliverables
Week 1 The fixed-cost development agreement was executed and the AMS 2.0 database schema was frozen.
Week 2 Defined our Oracle-to-SQL Server migration plan
Week 3 Data entry routines with access authorization was delivered for all tables.
Week 4 An Oracle-to-SQL Server conversion facility was delivered complete with validations and friendly error messages. Numerous SQL Server database schema optimizations were implemented. Conversion uncovered referential integrity problems.
Week 5 More conversion. Christmas vacation.
Week 6 More converion.
Week 7 Ability to logon to either the Oracle or the SQL Server AMS databases delivered. Application log file maintained so that all program exceptions are recorded.
Week 8 Budget table normalized. Accounting data import specifications frozen.
Week 9 JAVA applet delivered that presents a hierarchical view of all database records associated with a given property.
Week 10 Input form layout tool is delivered that enables non-programmers to add, rename or delete columns from any AMS table. Encoded column dictionary update facility is delivered that enables non-programmers to add, change or delete discrete value constraints on encoded columns. Password maintenance delivered. Production server is configured.
Week 11 Site Business Plan report delivered. Proxy User access enabled. AD-hoc report design specifications frozen.

AMS 2.0 is deployed for production use!
Week 13 Bug fixes delivered to keep the users happy.
Week 14 Ad-hoc report design and execution delivered. Universal selection criteria delivered so that records can be selected by specifying the value of any of their fields.
Week 15 Financial tracking detail and summary reports delivered.
Weeks 16 Project Management report delivered. Data import utility delivered. Programmers guide delivered.
Weeks 17 Budget performance report is delivered and enhanced to offer user-defined columns and sort orders. All outstanding features are deployed for real users including the ad-hoc report designer.
Weeks 18+ Incremental improvements.

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:

Simple, Consistent Look and Feel All AMS 2.0 pages share the same header and footer information including a link to the main menu, logout and help. We limited the GUI to three colors and avoided Web page gimicks that tend to annoy users over time.
On-line Help Each page has a corresponding help Web page.
Convenient Support Access The footer of each help file presents the phone number and an email link to the AMS support desk. The AMS software version and screen ID appears in the footer at the bottom of each page to facilitate support. Program exceptions are automatically recorded in the application log file so that users don't have to transcribe error messages.
Dynamic Hit List Page Resizing Users can dynamically adjust the number of hits that appear in a page of selected records to best fit their screen.
Intelligent GoBack links Users are automatically returned to the appropriate hit list after an update procedure. The "Go to Last Query" link also takes you to the appropriate page where the hit list spans multiple pages.
Hierachical Database Navigation A JAVA applet was developed so that users could point and click to select any record and report for a given property in their Web browser just as they would use Windows Explorer to locate files on their hard disk.
Multi-Window Support Users can take advantage of the Web Browser's "New Window…" function to spawn new AMS windows so that related data can be juxtaposed or unrelated operations can proceed in parallel.
Persistant Option Selections In most cases, AMS remembers the users last option selection so that menus don't have to be pulled down more often than necessary.
Report records are hyperlinked to table records Hyperlinks are provided that make it easy to jump between report records and related table records to reveal more details or to facilitate updates.
Selected Record Set Navigation Users can click forward or backward through a set of selected records before locking one to be added or changed. A "Page 2 of 12" type display keeps the user informed as to where they are in the list of selected records.
Friendly Field Formatting Currency fields are automatically formatted with dollar signs and commas while browsing.
Automatic Active Record Maintenance Whenever a budget or monthly actual record is updated with a non-zero currency amount for the current year, the effected property's status is automatically changed to "ACTIVE". A property manager can retrieve all of there active records at the touch of a button.
5.1 Data Entry Conveniences

AMS Property Update Screen (click for full-scale image)
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.

5.2 Accounting System Data is Imported

AMS Import Data Screen (click for full-scale image)
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.

" 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.
AMS Record Selection Form (click for full-scale image)
AMS Record Selection Form

AMS Selection Hits Form (click for full-scale image)
AMS Selection Hits Form
(click to zoom in)
6.1 Standard Reports Track Financial Performance
Site Business Plan Everything a property manager needs to know about a given property and its financial goals for the current year.
Site Information Report The geographic and organizational location of a given property.
Project Management Report An organization chart of the Asset Management Group.
Financial Tracking Detail Report Budget and actual revenue and expenses year-to-date by property.
Financial Tracking Summary Report The sum of all budget and actual revenue and expenses year-to-date for one of more properties.
Budget Performance Report This is actually four different reports that share the same name. Optionally, the budgets of two or three consecutive years can be compared or the variance of budget vs. actual revenue and expenses are reported by property over any range of months is reported. The report may be sorted either by property over revenue/expense category or by revenue/expense category over property. The revenue and expense categories to be included in the report are user-defined.

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
AMS Ad-hoc Report Builder

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

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.

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.

AMS Property Navigation Screen (click for full-scale image)
AMS Property Navigation Screen
(click to zoom in)

7.3 How Security is Administered

AMS login screen (click for full-scale image)
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.

Server IIS Microsoft Internet Information Server (IIS) 3.0) with Active Server Pages and Scripting Engine 3.1 supported by Windows NT 4.0 Server SP3 running on a Compaq Proliant computer with dual 300 MHz Pentium CPUs and 512 MB RAM.

(This production server was over-configured to allow for future expansion. The test server had a single 200MHz Pentium CPU with 128MB RAM.)
Database Management Systems SQL Server Microsoft Access 97 was used for schema prototyping. Microsoft SQL Server 6.5 SP4 was used after the data was converted from Oracle 7.2
ODBC Drivers Microsoft MDAC 1.5 ODBC drivers for SQL Server and Oracle. ActiveX Data Object (ADO) properties that were essential to the AMS 2.0 hit list page were not supported by Oracle's then current ODBC driver.
Client Netscape 3.0 or Microsoft Internet Explorer 3.02 or better supported by Microsoft Windows 95 for all client Intel-based PCs having 32MB RAM and Pentium 133 or better.
Application Languages VBScript for most application programming, HTML for the GUI, JAVA for the tree control, JavaScript for some client-side input validation, ANSI-standard SQL for database queries and view definitions, Microsoft Transact-SQL for SQL Server trigger procedures.
The Measure of Success
Any Comments? 1997-2009 WinMetrics Corporation. All rights reserved. Terms of Use.