NIH fields data warehouse
Connecting state and local government leaders
The National Institutes of Health spent three years building a 27G data warehouse to answer frequent calls for management data from its own staff, Congress and the public. After assembling the NIH Data Warehouse, the agency's Information Systems Branch is looking for a sharp drop in demands on its time as managers get more familiar with the warehouse's self-service data marts.
The National Institutes of Health spent three years building a 27G data warehouse to
answer frequent calls for management data from its own staff, Congress and the public.
After assembling the NIH Data Warehouse, the agencys Information Systems Branch
is looking for a sharp drop in demands on its time as managers get more familiar with the
warehouses self-service data marts.
The investment ultimately will benefit all research activities at NIH, said John Price,
the branch chief and warehouse manager.
It seems the more successful you are, the more work you get, Price said.
NIH plans to build a new mart every four months.
One large IBM MVS database serves the six existing data marts. NIH warehouses many
kinds of data from the current year and five previous years to provide timely as well as
historical information about research activities, funding, travel, suppliers, inventories
and facilities, he said.
The warehouses budget and finance data gets updated every evening to keep track
of commitments, obligations, accruals, disbursements and funds status.
When program managers look up outstanding obligation amounts for specific work
requests, they can choose between predefined and ad hoc queries. For example, they can
look up all travel charges by project.
It took Prices warehouse team about three months to define the warehouse
architecture and to evaluate and buy needed tools. The hardest part of building the
warehouse was surveying users to find out the kinds of management information they needed
most, Price said.
The team has since begun building online analytical processing multidimensional cubes
that can display data from two or more marts over an NIH intranet. For drill-down
analyses, they use PowerPlay Web tools from Cognos Corp. of Burlington, Mass.
We have a very open shop at NIH, and the users are not limited to these
tools, Price said. For that reason, he maintains access controls within the IBM DB2
database management system on an IBM 3090 mainframe.
The old way of getting management data was too error-prone and resource-intensive, and
it wasted staff time, Price said. Managers from the 24 separate institutes routinely
requested data from various information systems groups, which sent back hard copy reports.
NIH warehouse managers then re-entered the data into spreadsheets or databases to make
consolidated reportsa process that took days and sometimes weeks, Price said.
Nowadays when administrators want a consolidated management report, they select a
topic, run a predefined query and view the results on-screen or send them to a printer
within minutes, he said.
About 1,300 registered users query the warehouse via browsers and Microsoft Windows NT
4.0 Terminal Server Edition. The system automates user registration.
NIH chose the Graphical Query Language from Hummingbird Communications Inc. of Mountain
View, Calif., to run queries and reports that display on PC, Macintosh and Unix desktops.
An Open Database Connectivity gateway from New Era of Networks Inc. of Englewood, Colo.,
routes users to the DB2 data.
The warehouse data, which comes from Oracle7 and DB2 tables and IMS and VSAM files,
must be extracted, cleansed and transformed by Prism-generated code from Prism Solutions
Inc. of Sunnyvale, Calif., before it can be moved into the DB2 warehouse.
Because data warehouses throw a new light on data, even the source systems become more
accurate over time, Price said.
Fifteen people, including two full-time training experts, stay busy supporting the data
warehouse. To bring administrators up to speed, NIH gives full-day classes on topics
ranging from property management to travel and personnel costs.
The NIH Data Warehouse is the first integrated source of payroll information that NIH
administrators have ever had, said Judy Mahaffey, chief of the Database Information
Section. In a survey of users, 47 percent of respondents said the warehouse
significantly enhanced their decision-making, she said.