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,"
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'
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
Efficient dissemination of information, and a reduction in the expense and time needed to complete evryday activities.
In addition, clients have benefitted greatly from the increased access to information.
Tools used to design and implement the data warehouse: Star Schema, Smalltalk, Sybase Database Management System,
Tools used to populate the data warehouse: Virtual DB, GemStone OODB, Exceed, Unix Scripts, Sybase Stored Procedures
Tools used to provide connectivity between the IBM mainframe and the data warehouse: Sybase Omni, InfoHub, Sybase
Tools used to implement the user interface: HTML editor, Visual Café, Exceed Software, Apache Web Server,
PhotoShop, Virtual DB Virtual Web Interface, Smalltalk language
IBM mainframe, VAX; Database: Sybase SQL Server
The Data Warehouse Project undertaken by Jefferies & Company successfully enabled timely, accurate dissemination of trade and commission information to its disbursed departments. Management
and users consider the project an overwhelming success and clients greatly benefit from increased information access.
The project utilized solid project management concepts emphasizing QA, contingency planning and configuration management,
and utilized prototyping with an iterative design/development approach. The system enabled real-time data access
to mainframe sources, bridging both IBM and VAX technology with a Unix-Sybase data warehouse.
The developers implemented a three-tier architecture solution with a Java, browser-based end-user interface, and
addressed configuration management and version upgrade challenges with flexible/creative solutions. The project
delivered incremental business benefits on time and within budget, while factoring Y2K into project plans.
Team Members: Keith Custer, Joe Zucchero, Tammy Slocum and Richard MondorOPMENT