Customs Service puts stock in its data warehouse

What elements
support Customs’ OMR warehouse?


Data element owners
Data integrity ratings
Data dictionary


When Customs Service commissioner Raymond W. Kelly took up his post last August, one of
his first actions was to consult the data warehouse that metes out metrics on every aspect
of the bureau’s operations.

The Office of Management Reporting (OMR) data warehouse also is the first place any new
Customs port director goes to sniff around “and get a feel of what’s gone on at
the port for the past several years,” said Michael Raithel, a computer specialist at
the headquarters Office of Field Operations.

The warehouse holds metrics on everything from trade compliance, passenger processing
and narcotics interdiction to human resources and financial management, Raithel said. When
Congress asks Customs for information, more often than not the warehouse supplies the

Many data warehouse projects fail because they start too big, Raithel said. The Customs
warehouse team decided to start small and continue that way. For one thing, it still
distributes the data only on disk. And the project, now in its fifth year, still has a
staff of only two programmers, three business analysts, a team leader and “no
humongous budget,” he said.

Customs built the warehouse before such facilities became mainstream. “Over the
years, we’ve figured out what are better measures and what are not-so-good
measures,” Raithel said.

The project began as a series of red-flag reports that alerted managers when a process
seemed to be going awry. Now it has stretched to cover 320 data elements.

Cobol and SAS language programs extract most of the data elements from Computer
Associates International Inc. CA-Datacom databases created for flat-file management. The
Datacom databases reside on an IBM 9672/RY5 mainframe under OS/390 3.0.

Raithel, one of the two programmers on Customs’ Measurement Team, said he prefers
to write his extraction programs in the SAS fourth-generation language from SAS Institute
Inc. of Cary, N.C. A SAS program also crunches and summarizes the mainframe data, getting
it whipped into shape for import into a Microsoft Access database, Raithel said.

From the Access database, the OMR data and application software goes out on CD-ROMs,
which employees can search from local jukebox servers or CD-ROM drives.

On the 14th day of each month, a member of the headquarters Mission Support Staff
Measurement Team downloads the extracted mainframe data over a Novell NetWare 4.1 LAN to
Raithel’s Dell Computer Corp. OptiPlex 486 PC running Microsoft Windows 3.1.

A member of the Measurement Team imports the extracted data into a Microsoft Access 2.0
database management system. From there, the team indexes and stores the data, most of it
in a single table, Raithel said. The entire process takes about three hours.

After running several data validation programs, Raithel said, he copies the warehouse
data and Microsoft Visual Basic application software onto 10 floppy disks. He also cuts a
CD-recordable disk on a Hewlett-Packard Co. SureStore CDWriter Plus 7200 for mastering 425
CDs and distributing them to field offices where most of Customs’ 19,000 employees

About 97 additional floppy disks go out to sites that have no CD readers, Raithel said.
Each disk and CD holds the current fiscal year’s data and summaries of the previous
two fiscal years.

Over time, Customs programmers have shortened the data production cycle from a week or
two down to a few hours. They also have found ways to improve the integrity of the
warehoused data, Raithel said.

“We revamped the process so there is less human intervention and more
automation,” he said. Mainframe programs now produce the monthly and yearly data
summaries, but processes do misfire when a glitch somewhere interrupts one of the data

Stay Connected

Sign up for our newsletter.

I agree to this site's Privacy Policy.