WWW-based Accelerator Data Warehouse
A. Chan, G. Crane, I. MacGregor, S. Meyer and R. Sass
Stanford Linear Accelerator Center*, Stanford, CA 94309 U.S.A.

Abstract
In addition to real time data needed for on-line control ofan accelerator, there is a need for prompt access to otherdata required to support and maintain an operating
accelerator complex. This includes data on troublereporting, spare parts, fabrication, calibration, drawings, etc.
This paper focuses on the latter infrastructure data, which isnecessary to maintain an operational facility at peakefficiency. We discuss using the World Wide Web (WWW)interface and data repositories to link these data, which are
generally segregated by departments and resides ondifferent computer platforms and software. A cornerstoneof this is the collaborative effort across departments toconform to a site-wide Formal Device Name, derived fromthe real-time control system. The disparate databases arelinked through this Formal Device Name field.
1 Is there a need for a data warehouse
In an accelerator laboratory we are inundated with data
related to the fabrication and maintenance of the machine,such as hardware, personnel, finance [1]--yet it is more
often than not quite difficult to obtain such related
information in a useful format. To resolve a maintenanceproblem, one may need to log onto diverse computer
systems and run different database software, frequently
devoting much time to trying out obscure search strings.
Such compartmentalization of databases of the various
accelerator departments evolved because different software
was used to suit particular problems and situations.
Although there is growing recognition to achieve database
implementation under the same database software, we will
still be faced to some degree with these differences.

can be mitigated by recognizing the need to make linkages
to the overall accelerator during the design of the database.
However, the implementation of a data warehouse isrequired for a lab-wide view of decision support
information for the accelerator.
2 Data warehouse in an accelerator environment
A data warehouse can be simply defined as [2] ″a single,
complete, and consistent store of data obtained from avariety of sources and made available to end users in a way
they can understand and use in a business context.″
There are 3 main architectures for a data warehouse (seeFig. 1), although in reality what is implemented is most
likely a mixture of these different types. The data layersthat Fig. 1 refers to are conceptual, rather than physical.
For the single-layer architecture, its strength is that datais only stored once, which avoids the need to synchronize
multiple copies of data. Its weakness is that contention canoccur between the online transaction processing systems
and the decision support systems.
For the two-layer architecture, its strengths lie in solving
the contention between the online transaction processing
systems and the decision support systems. It addresses thefact that end-user needs for information are different fromwhat is easily available from real-time data. However,there is a high level of data duplication in the two-layer
approach (with a tendency to become ′spaghetti code′).
For the three-layer architecture, its strengths lie in thereconciled layer which is based on enterprise data modeling(i.e., a normalized database). This reconciled layer cansupport new, unanticipated end-user needs. The derivedlayer can be used to fill most end user needs, being theequivalent of predefined queries. The enterprise data
modeling is a much more committed effort, and needs to bedone incrementally.
Legend:Lookuplinks/references
Update links

Parameters

Component
Travelers

CATER
datasource

Oracle--UNIX

Rdb--VAXFormalDevice Name

FormalDeviceName

Oracle--VAXSLAC Control--VAX

Component
Types

Component
Metrics

PEP-II
Component list
(Names)Oracle--UNIX

PEP-II
PeopleProblem Tracking
for Accelerator(CATER)

Component
Instances

Cable Plant(CAPTAR)Oracle, UNIX

Equipment
Tracking forControls (DEPOT)

PEP-II
Drawings

SPIRES, UNIX

Figure 1 -- Types of implementation of data warehouse
Another reason for this compartmentalization is thatdatabases developed by the departments, of necessity, focus
on their area of business functions. This short-term focus

WBS

Purch.Req.SPIRES,& PO etc.--VM

SLAC
PeopleSPIRES--VM

SLAC SPIRES
Drawings --VM

Figure 2 -- Linked SLAC accelerator databases

Table 1. Linkage of data elements.
Formal Device Name Primary Micro Unit
QUAD PR02 6072
Problem Tracking for Area
Micro Primary Unit
Accelerator (CATER) HER
PR02 (notrequired)
LER
Cable Plant
System Function(CAPTAR)QUAD:PR02,6072
Micro Crate Model
LI09
CR02 233-002-00
Equipment Tracking
Location
Modelfor Controls (DEPOT) LI09/CR02
233-002

4 Linking the lab databases
Figure 3 -- WWW screens of Formal Device Names, andmenu choices for each formal device identified by ‘Primary,Micro, Unit’
At SLAC, we have mostly implemented the single layer
and two-layer approaches. We recognize the benefits of thethree-layer data warehouse which will take some futurecommitment in effort.
The World Wide Web (WWW) technology has made it
possible to access and link disparate databases, allowing
the data warehouse to be a reality for us, and at a fractionof the programming effort in today′s resource-scarceenvironment.
3 Site-wide device names
The key to our ability to provide a lab-wide view of thedata is a consistent set of site-wide device names (FormalDevice Names). We took the Formal Device Names fromthe accelerator on-line control system, with its 37,000
records, as the master list. We used the Formal Device
Names as the link to the other disparate off-line accelerator
databases.
The real-time database used by the accelerator control
system is rigidly structured and designed for high-speed
access by the many different control system computers. Assuch, it is not in a format suitable for access outside of thecontrol system. To bridge the gap, an Oracle Device
Database is updated whenever there are any structuralchanges to the real-time database. This Device Database isthen available for remote access like any other Oracle
database.
Similar to other sites, we encountered two problems in
our ability to join disparate accelerator databases•where the Formal Device Name fields existed in otherdatabases, the data may not have been validated•
other databases may define their device names
differently, leading to imprecise mapping
We find that the effort for implementing data integrity inthese disparate databases is an iterative one. When the user
community can utilize some of the information provided bysuch a linked system, we are able to obtain more support
from management for the extra effort in requiring data
validation in the different departments.

Figure 2 is an overview of the lab′s major acceleratorrelated databases that we have linked for the accelerator
data warehouse. These systems are:•the The Formal Device Names. This is integrated into
the PEP-II project-wide database [3] which is inOracle, containing component fabrication history andcalibrations.
•the problem tracking database for the accelerator(CATER), which is in VAX Rdb and replicated into
Oracle tables.
•
the cable plant database (CAPTAR) in Oracle.
•
the equipment tracking database for Controls (DEPOT)
in SPIRES.
Via WWW Common Gateway Interface (CGI) scripts,
these databases are linked together using common dataelements (i.e., variations of formal device names shown inTable 1). The WWW interface gives excellent ′drill-down′
capabilities. Since linkages of the different databases aredone by the CGI scripts, huge savings in programmingtime are realized as we do not have to create physical tables
to join the databases.
Users can query the Formal Device Names List on
WWW, and the search results will contain hypertext
references that request information from the other databasesystems. In Fig. 3, among the search results returned forcomponents in Region 1 Cell 12 is the Primary/Micro/Unit
QUAD PR02 6072 (the three fields comprising the formal
device name), which has a hypertext link that produces amenu with additional hypertext links to:⇒ the CATER database summary and detail screens for
Area HER or LER, Micro PR02 (Fig. 4).
⇒ the CAPTAR database of cables linked to QUAD
PR02 6072. From the Micro field, one can obtaincrate profiles which are database reports in WWW
tables (Fig. 5)⇒ the DEPOT database with summary screen of thehistory of modules in this crate location, and detailscreens of the maintenance records of each module islinked from the CAPTAR crate profile. In Fig. 5, thislink is shown for Micro LI09 Crate CR02 Model 233002.
⇒ the scanned paper fabrication and alignment travelers,
online calibration measurements, drawings, photos,

polynomials files, etc. of the PEP-II Project Database.
5 Lessons and issues
This accelerator-wide view of the information delivered
through the WWW interface has been popular with users,
enabling more efficient work.
There are a few key issues that have enabled us to get tothis stage for an accelerator data warehouse•We ′web-ified′ both new or existing main disparate
databases at the lab. This provided immediate
benefits to the departments, and they were very
willing to work with us.
•
We approached each system with a lab-wide view of
the information-not a departmental view.
•We focused on linking the existing departmental
databases-not on rebuilding systems. Re-engineering
is a major effort that our scarce programming
resources cannot easily undertake, so we have left thateffort to the departments-though, hopefully, there iscoordination amongst related database work.
•We built incrementally, for each increment giving
quick turnaround and visible benefits to the users andthe departments.
•We relied on experience database programmer(s) fordata modeling.
With the ability now to link databases via a few common
data fields, the integrity of the data stored in these fields
need to be enhanced by cleansing the data and/or applying
better database constraints. But this effort becomes easier
when the benefits are now apparent to the users (even if it
may not benefit their own department immediately).
For PEP-II, EPICS has been used for several majorsubsystems. Unfortunately, these devices are contained
within a separate database and are presently not reflected inthe Oracle Device Database. Future work on the Device
Database should include integrating the EPICS database as
well as other devices not specifically accessed by thecontrol system.
In the future, we would like to rely more on commercial
WWW-database tools (such as Oracle Designer2000,Remedy, etc.), and less on CGI scripts which are time
consuming and less secure.
The URL for the PEP-II Project Database is:
http://www.slac.stanford.edu/accel/pepii/db.htm

Figure 4 -- Summary and detail WWW screens ofaccelerator problem report database (CATER) linked from‘Area HER, Micro PR02’ in figure 3

Figure 5 -- WWW screen of crate LI09/CR02 -- crate
profile from cable plant database (CAPTAR), and linked
history of modules in the Equipement Tracking forControls database (DEPOT) which leads to maintenancehistory screens

Acknowledgments
We thank the people in PEP-II and the various Technical
Division departments who manage the different databases,and who helped us analyze the linkages of these different
systems. We have drawn many lessons from discussions
with members of the International Accelerator Database
Group (IADBG).
References[1] J. Poole, ″Databases for Accelerator Control An

Operations Viewpoint″, Proceedings of 1995 Particle
Accelerator Conference, Dallas, May 1-5, 1995,
p.2157.
[2] Barry Devlin, ″Data Warehouse: from Architecture toImplementation″, Addison-Wesley, 1997.
[3] A. Chan, G. Crane, I. MacGregor, S. Meyer, ″The
PEP-II/BABAR Project-Wide Database Using World
Wide Web and Oracle*CASE″, Proceedings of 1995
International Accelerator Database Group Workshop,Argonne National Lab, November 6-8, 1995.