BLASTMAN PROPOSAL:
Dennis Box, Norman Gelfand, David Harding
20 October 2003

Magnet properties and alignment data are used for modeling and tuning Tevatron performance.  This data is collected by technicians and physicists using methods convenient for them, but collecting it into a central location is urgently needed.

Proposed Solution:

For accelerator physicists who need to model and understand beam-magnet interactions, we propose to develop the Beams Lattice Alignment Survey Tracker for Modeling Accelerators Numerically (BLASTMAN) database.  BLASTMAN will be a database of magnet locations, field strengths, calibrations and other magnet data that facilitate repeatable generation of lattices for modeling purposes.  Unlike the current system where data from various sources are laboriously combined and the resulting lattices distributed via ad hoc methods, the BLASTMAN system will have documented input and output procedures with data flowing through a single source, featuring data consistency checks, and an extensible schema which will allow magnet location lattices of different geometries to be more easily and reliably constructed than is now possible.

The project will be divided into multiple phases, each of which will be an improvement over the previous state.  This provides the advantage of being able to gracefully wind down and restart the project at logical, useful points should priorities or resources change .

An important project goal is to develop BLASTMAN into a maintainable and extensible database, and put it into a condition suitable for capture of the relevant data and its efficient use.  It is proposed that significant CD development effort be devoted to bringing the database to that point, represented by the end of Phase II.

Phase III requirements have not been discussed to the point where reliable estimates can be made.  CD personnel are willing to pursue defining them, and thereafter make reliable time estimates and work proposals.

Further improvements, if needed, would be identified and implemented by BD and/or TD personnel, with only minor consulting assistance from the CD developers required.

PHASE 0:

Meet the hardware/software prerequisites to implementing the rest of the project.  Prototype, development and production environments are all different.  The following are needed to move out of the prototype arena:

a) Establish software requirements.  Sybase running on Linux has been suggested, if this is accepted then licenses for Sybase server and Red Hat Advanced Server must be secured.

b) Establish hardware requirements. Purchase or identify the hardware and configure it as needed.  (Does BD, TD do this or CD-CSS-DSG?)

c) Assignment of support task to DBA who accepts this role.  Developers users, and DBA have to form working relationship.

d) Consensus of all stake holders with respect to the proposal including scope of (initial) database content

e) Install Operating system and DB server on hardware server, and verify its operation. Make available to developers and users.

PHASE 1:

The first step will be a direct port of Norm Gelfand's' VAX RIM database to a modern database server.  The ported database will be accompanied by documented procedures for updating with upcoming Alignment & Metrology survey data, and consistency validation checks to make sure the data is entered correctly.

Checklist for success of phase 1:

a) Phase 0 completed, modern production server installed and maintained by identified, trained DBA who has accepted this role.  Jerzy Nogiec's group in TD has agreed to provide a development database for this first step.  This requirement is enabled but not fulfilled by this step, BD or TD must supply a PRODUCTION database that is regularly backed up and has an agreed on support policy.

b) current VAX RIM database schema ported to SQL, with obvious data constraints enabled.

c) current schema documented by data dictionary, explaining what each table/column is, its measurement units, other notes as appropriate.

d) Schema populated from VAX RIM data dumps and contents verified to be equivalent to VAX RIM.  Rowcounts and checksums match or discrepancies explained to stakeholders satisfaction.

e) checksums or equivalent methodology demonstrates that the ASCII dumps from the VAX RIM database and Phase 1 BLASTMAN database are equivalent.

f) current maintainer of VAX RIM data (Norm) accepts procedures for updating schema as correct and usable.  Maintainer must be able to:
    - swap out magnets at a given location.  Historical locations of moved magnets must be recoverable from database
    - enter new survey data (x,y,z rho, theta, phi) for a given location
    - enter updated data as needed from MTF database reports. This will be done manually on phase I as the updates are believed to happen infrequently and their exact format is unknown at this time.
    - enter and edit magnet evaluations, specifically the sgrade codes.
    - Need to be able to update status of spare magnets
    - Different lattice configurations (magnet assignments, operating currents, etc.) must be supported to allow studies and comparisons.

Phase II:

The VAX RIM schema was originally implemented on a CDC Cyber computer which limited alphanumeric strings to 6 characters.  Many relations were denormalized for performance reasons, which made data maintenance labor intensive and inflexible`. As an example, table mtfcof contains 28 columns, 14 a and 14 b harmonic moment coefficients.  This was an adequate choice for Tevatron magnets but it may make sense to store more or fewer coefficients for new magnets. This table will be normalized into 2 tables, magnet_description and magnet_moments, with the moments table able to store 0 to infinity harmonic coefficients for each magnet, with the actual number dictated by the particular application.

Checklist for success of phase II:

a) working example of phase I database available for validation of phase II work.

b) database schema normalized to at least 2NF, with foreign key constraints .  All tables and columns given more descriptive names.

c) data dictionary updated to reflect attribute name changes

d) checksums or equivalent methodology demonstrates that the ASCII dumps from the Phase 1 and Phase II BLASTMAN databases are equivalent.

e) views defined where appropriate to ease the transition from the Phase I schema.

f) enhanced functionality/maintainability:

- elimination of duplicate data, storage more compact
- variable number of harmonic coefficients per magnet
- variable number of dcx,current quench measurements per magnet
- obsolete tables identified and eliminated.
- well documented 'mapping' between MTF database and these tables to facilitate updates which will come from TD on occasion.

PHASE III:

Possible enhancements for phase III which have been mentioned, and need to be better defined to make a reasonable work estimate include:

- Lattice ordering information will be introduced with new tables to allow other beam line geometries to be built into lattices.  Norm, Dave, and Dennis are discussing the feasibility of this.

- Other data sets will be imported into BLASTMAN after agreement is reached on their usefulness.  For instance, subsets of the MTFA database may be of interest, developing extraction procedures that do not disturb a running production database is imperative.

- Defining the structural changes and additions will require discussions with representatives from all the BD systems departments (those responsible for the various rings and beam lines) along with MTF.

- The changes and additions may include additional formats for representing the field shape and other magnet behavior.

- The user needs to ability to specify generic magnet parameters for a given magnet or class rather than using measurements that may not exist or may be less reliable than average values.  This implies that the generic data must be stored.

- Perhaps, enhanced lattice configuration definition capability to allow storage of multiple operating configurations (currents).

- BPM calibration data may belong here.

Personnel:
-Dennis Box, database developer (CD)
-Norman Gelfand, domain expert and user (BD)
-Dave Harding, developer, domain expert and user (TD)
-Kelley Trombley-Freytag, development DBA (TD)
-Needed: DBA for production system (BD)

Time Schedule/Constraints:

Phase I is under prototype development and can proceed to 'real' development when Phase 0 is implemented by BD.  Prototype requires a few hours per week of effort from Norman, Dave, and Kelley, and Dennis. Phase I schedule assumes 70% effort by Dennis (assuming his CDF and Minos obligations can be deferred) and approximately 10 hours/week effort by Dave & Norman.

In the following schedule, all times are given in weeks, t0 is 'start time' when Phase 0 development environment requirement has been satisfied.

Phase I

t1. Design the schema (t0+1w)
t2. Build an instance of the database and essential procedures (t1+1w)
t3. Populate the database (t2+1w)
t4. design and apply validation procedures (t3+1w)
t5. implement and validate data entry procedures (t4+1w)

Phase I total estimate: 5 weeks from t0.

Phase II Optimize

1. Optimize schema (1w)
2. Implement (1w)
3. Validate  (1w)

Phase II time estimate: assume that this has to be done twice,
6 weeks total time.

Phase III  put additional information into database.

Making time estimates for these tasks is pointless before the tasks themselves are defined. Phases I and II are worthwhile activities that aid BD physicists, phase III can be properly defined to add further value.  Broadly speaking the activities to be defined are:

1. Identify information to add (how much? from where?)
2. Populate and validate

Phase III estimate: ? total