BACK TO INNOVATION AWARDS PAGE

APPLICATION: Jefferies Data Warehousing Project -- In 1997, Jefferies & Company, a Los Angeles-based full-service investment-banking firm, was struggling to tie together data from many different sources. Although not mission-critical, the company needed to distribute accurate trade and commission information from its mainframe to various user systems/departments in a timely and effective manner.

The Technology Division, according to Vice President Janelle Briggs, had very little flexibility. "People were keying mainframe reports into [Microsoft] Excel, and then making them look pretty and distributing them," she said.

In order to reduce the number of reports generated, Jefferies & Company wanted to report commission revenue, expense and reference data information via its intranet. The firm also wanted to use a Web browser to simplify access and updates to reference data, and to provide information to users regardless of their location.

Thus, the Jefferies Data Warehousing Project was born. The project's software environment consists of three areas: test, quality assurance (QA) and production. Programmers develop software in the test environment, and then perform unit and integration testing of their application programs. More rigorous testing of the application as a whole takes place in the QA environment. While business functionality and performance are the focus of testing here, user acceptance testing is also conducted. Any detected defects are fixed in the test environment, while application programs are remigrated back to the QA environment for retesting. Once the entire application is deemed fit, it is installed in the production environment.

The development team consisted of a DBA system admin-
istrator, a mainframe programmer/an-alyst/virtual DB de-veloper, a virtual DB/ Web developer, a system architect/virtual DB/Web developer and a project manager. Team member's experience ranged from five years to more than 15 years. All team members attended a Smalltalk programming class and an Intro to Virtual DB class. Some members also attended a Java class.

The team of developers encountered a number of challenges in implementing the project. For example, Virtual DB posed a high learning curve to developers, requiring that they achieve a unique skill set. To overcome this obstacle, Virtual DB consultants from Enterworks teamed with and mentored in-house staff and developed the initial appli-cations. Subsequent applications were co-developed by in-house staff and Enterworks consultants.

Virtual DB version upgrades also required application rework. "We had to redo a lot of development work as Enterworks came out with enhancements to its tools," said Briggs. To solve this problem, developers treated these upgrades as if they were independent efforts, scheduling their tasks and resources accordingly.

The team also needed to figure out how to clean up dirty data in order to present it in a logical manner to senior managers. This was accomplished by building the design of the data warehouse on the 'Star' Schema. Transaction data was cleansed using Virtual DB's virtual data types and post-query processing features.

Because there was no available configuration management tool for Virtual DB objects, Enterworks made extensions to its software. This allows Virtual DB objects to migrate from a development to a QA environment and, eventually, to a production environment.

Developing applications in Java proved to be a challenge due to incompatibility issues between the Java classes/libraries, and browsers that support the Java virtual machine and Java IDE. The development team decided to evaluate each JDK, IDE and browser in order to determine which ones would work as a set. They used Java JDK 1.0, Visual Works and Netscape Browser Version 4.0x. This required installing the browser on approximately 300 PCs.

Jefferies & Company's data warehouse system infrastructure consists of a three-tier architecture. Transaction data is extracted from IBM mainframe CA-Datacom databases. To allow relational database access to these CA-Datacom databases, InfoHub and Open Server Transaction were installed on the mainframe in addition to the CICS software that already existed. In order for Virtual DB to access the CA-Datacom databases, the Sybase Omni server and Sybase NetGateway server were installed. These two pieces allowed real-time access to the mainframe databases and
supported Virtual DB. Virtual DB -- which requires GemStone runtime software -- was installed as an application running under the GemStone environment.

Virtual DB's Virtual Web Interface comprises several physical processes that run in the Unix machine. These processes communicate with the HTTP server, in this case the Apache Web Server. Apache was the server of choice because Jefferies had already standardized on it. The Netscape browser with compatible Java support was then installed on all PCs that would access the data warehouse.

Existing legacy systems, which track trade data and revenue information, reside on the IBM mainframe. Expense data resides on a VAX system. Trade, commission, expense and reference data is extracted from the mainframe into the data warehouse on a nightly basis. Reports are then created through Virtual DB, and displayed via Jefferies' intranet.

Jefferies considers the project an overwhelming success. If the project had to be done again, however, the firm would focus on doing the Web front-end development first since that is what managers see first. Getting reports to managers faster and in a better-quality format would have saved the company even more time and money, said Briggs. -- Lana Gates