Kalle Pedastsaar
Toomas Murulo
Estonian Animal Recording Centre, Tartu, Estonia

Introduction
This article gives an historical overview of data processing of milk recording in Estonia. Above all, we would like share our experience of the rightsizing project of 1994-1998.

Getting started with computers

The introduction of computers into data processing of Estonian milk recording falls into four stages:
The first attempts at the computerised data processing in Estonian milk recording were made in 1964. At this time it was only the herd yearly averages that were calculated by computer. The evaluation of bulls on the basis of the productions of daughters was also started.
During the second period, the elaboration and implementation of the preliminary computerized data processing of milk recording got under way. In 1969 the program was launched in one county, but by 1973 the system included as much as 105000 cows which made up 43% of the total number of the cows in collective farms. The work was shattered between several computing centres. The use of computers of different type called for the adaption of programmes.
The beginning of centralized data processing in 1984.
The application of the RDBMS Oracle was started in 1998.

Table 1. Abstract of process of development
1964 Herd yearly averages. Sire evaluation
1969 Start of computerised data processing of milk recording.
Up to 1982 Data processing in different units on computers of 2 generation
1978 Start of designing new data processing system for the IBM/370 compatible computer
1982 Estonian Research Institute of Animal Husbandry got a computer EC-1035 (1MB RAM, 3x29MB disk units, SVS)
1982-1984 Transition period to the new system. Since 1984 there is centralised data processing in Tartu.
1986 Replacement of the computer and the operating system: EC-1055M with 4MB, 8x100MB disk units, CBM 3.0 (VM/SP) Trial of ADABAS. It was clear that the capacity of the computer is not sufficient for using the database system.
1986-1992 Quiet development.
1993 Animal Recording Centre was formed. We made a decision to replace the mainframe background by the PC background for the future. We chose Oracle 7 under NetWare 3.12 for the development.
1994 Start of taking local network and Oracle into use. Milk Analysing Laboratory got two lines of Combifoss 4300 and the so-called Test-day program was taken to use on PC-s. It was an essential improvement of the milk recording service. Data Processing Unit replaced EC-1055M with HDS-8053.
1995 Enjoying of the capacity of the mainframe and a continued development of service on the mainframe. The rough draft of data model and technology.
1996-1997 Stopping the development for the mainframe. Programming in Oracle. BLUP Animal Model 1,4. Synchronisation data between the mainframe and the Oracle database. Base server and operating system replacement.
January, 1998 Start of production in Oracle. The so-called Test-day program is running in Oracle. That means that the data input of test milking and four different reports (Milk recording reports, pre-print of barn lists, udder health report and invoice) is executed in Oracle.
May, 1998 Printing modified pre-print of barn lists for small size herds.
June, 1998 Full cycle of data processing for small herds in Oracle. Milk recording reports, error-list and as option action-list added. The batch technology of data processing was taken into use.


A survey of the rightsizing project. Our experience

Animal Recording Centre (ARC), a full service provider of milk recording, occupies a central place in Estonian Animal Breeding. Hereafter we would like share our experience of the rightsizing project - the transmission from the mainframe data processing system to the use of RDBMS on Intel/Win NT platform - of 1994-1998.

Something has to be changed. Years 1992-1993

The re-establishment of Estonian independence in 1991 brought about major changes in society as well as in agriculture. The creation of independent farms gained enormous momentum. In 1992 there were 429 herds using the services of the ARC, whereas by the end of 1993 their number had gone up to 3767. The tendency was accompanied by a rapid decrease in the average herd size caused by the land reform in Estonia. The data processing system of milk recording, designed at the beginning of the 1980-s for a small number of large farms and often with a view to meeting the requirements set by the Soviet government in Moscow, was well out of date. The lack of computer capacity determined the primitive nature of the data processing technology. The service was no longer acceptable to the client.

Passing the decision. Year 1993.

We had decided to change the mainframe background as its development and exploitation costs were too high. By this time we had seen the first PC-s and had some working experience with the database system FoxPro. In no time we decided for the introduction of a server of RDBMS architecture. We chose between RDBMS Progress, Gupta and Oracle. We argued a lot over the choice of the operating system. In retrospect it does not make much sense.
We opted for Oracle 7 for NetWare 3.12 and Oracle Tools for DOS as the development tools. As for the database server as well as the local network server, we picked Olivetti Suprema M6-880 with 60 MHz Pentium processor, 64MB, 3GB. Thus, in the beginning we had to make do with modest means.

The first acquaintance with Oracle and its developer tools. Year 1994.

It was in March 1994, when the local network and Oracle were installed, and most of the programmers got their first working experience with PC-s. We were amazed by the obsoleteness of the Oracle Tools for DOS. We were not able to put it to actual use, but at the same time the service was in need of improvement.

The improvement of the service quality. Years 1994-1995.

In October 1994 we laid a foundation for the further improvement of the service by fitting the Milk Analysing Laboratory with two Combifoss 4300. These devices allowed of the real electronic recording of the milk analyses results. In co-operation with Verden computer centre in Germany and, in particular, with Dr. Jürgen Claus we had completed a FoxPro application for the merge of the milk analyses and barn lists results, and for printing the test_day reports for the clients.
At the same time the small capacity mainframe EC-1055 of the DPU was replaced by a real mainframe HDS-8053 which allowed a large-scale implementation of the database system Adabas and granted a good contact between the PC-s and the mainframe.



The elaboration of the data model and the conception of data processing for Oracle. Year 1995.

We launched the Oracle development environment Oracle Developer/2000 and found it possible to program in Oracle. The visit of Dr. Eildert Groeneveld (from Germany) to Tartu in the November of 1995 was of great relevance and its results were stated in the article “Konzept zur Organisation der Datenbank und Datenverarbeitung am Jõudluskontrolli Keskus in Tartu”2. The implemented ideas about the automatic batch processing are derived from the very same article. As for the method of production calculation, we opted for the interpolation method 3, suggested by the ICAR and as the processing period we chose the interval between one test milking (day excluded) and the next one (day included). Intraherd and interherd comparison is based on calendar month.

Programming. The data synchronisation between the mainframe and the PC-s.
Years 1996-1997.

After the establishment of the data model structure the rightsizing project advanced at full speed. There were four DPU programmers, some more, some less, engaged in the programming. The completion of the project took approximately 1000 man-days.
At the same time the saving of the current and historical data of the mainframe was started in Oracle. The synchronisation of the data between the mainframe and the Oracle database was now our every-day work. The loading of the data model and the synchronisation of the data form one of the most complicated parts of the project. It was mainly due to the deficient quality of the data saved in the mainframe.

Status quo. July, 1998

Hardware and software of rightsizing project

Database server, DELL POWEREDGE 6100, installed in July 1997 with:
2 PentiumPro 200MHZ, 512KB Cache processors,
256 MB ECC EDO RAM,
6 x 4 GB SCSI-2 Ultra/Wide 7200 rpm HDD,
DLT 15/30 GB backup unit,
Windows NT operating system,
Oracle Server Enterprise Edition 8.03 for Windows NT.
Local network server, DIGITAL Prioris MX 6266, installed in January 1998 with:
Pentium II 266MHZ processor,
128 MB RAM,
3 x 4GB HDD,
Novell NetWare 4.11 operating system for 50 user licenses and
Connect 2.0 of NetWare with 8 licenses.
Network printer, 24-ppm HP LaserJet 5SI/MX with duplex unit, multi-bin mailbox and 2000 -sheet input tray.
Developers tools
Oracle Developer/2000 Release 1.5.1,
Microsoft FrontPage98,
Micrografx FlowCharter7.

Data processing services

There are altogether 3033 herds with 123000 cows (which makes up 73% of the total number of Estonian cows) in milk recording. Since January 1998 the entry of data and the processing of test milking is executed wholly in Oracle, i.e. all our clients get the following Oracle reports:
Milk recording reports
Pre-prints of barn lists
Udder health reports ( extra service)
Invoices
Printed Estimated Breeding Value reports and cow reports are provided as extra services.
In addition we offer the On-line service. If the client is in possession of a PC and a modem, (s) he has an access to her/his data in the central database. (S) He can make inquiries and unload the data for local use.
The current calculation of production and monthly report printing is at present executed in two environments.
Small size herds (up to 30 cows), i.e. 2500 herds with 21000 cows i.e. 82% of the total number of herds and 17% of the total number of cows are processed wholly in Oracle since June 1st. Besides the above-mentioned reports these clients get the main report of the milk recording production period and in case of errors an error report. The procedures are performed on the test milking day or the next day and the reports are posted.
In the case of large size herds, the milk recording data processing and the main print of production period (530 herds) is executed by old technology on the mainframe and the clients get the reports twice per the production period (month).

Table 2. Oracle main tables and data capacity.
Table name Content Number of entries
OWNER Our clients 40622
ANIMAL Animal pedigree 1.43 million
OWNERS_ANIMAL Location of animals 606000
MILKING Milk recordings 3.56 million
LACT Lactation’s 3.2 million
INSEMINATION 740000


Summary

The transmission from the mainframe data processing system to the use of Relational Database Management System (RDBMS) is a complicated task.
It is a long-term process during while the data has to be synchronised;
The quality of the data used in mainframe system was deficient for the implementation of the RDBMS. For the data transmission we had to improve the quality of the data saved in the old system;
The draftsmen, programmers and users of the system need some time to adapt to the RDBMS.

Soft- and hardware experience:
We must use the widespread version of the RDBMS. It gives us the tools for the development and support for the future;
For the development of the system we do not have to use powerful hardware and expensive operation system. For example in the case of Oracle the changing of the database server and the operational system is not a problem.
In the case of relatively small population of animals the system can successfully be exploited at the cheap INTEL/Windows NT platform.

Development experience:
The advises of the foreign experts were important to us;
People need extensive training;
The data model has to be planned in the early stage of the development of the system;
For the unique identification of animals (objects) in the database it is very useful to apply the identifications generated by the system (in the case of Oracle by Sequence). The identifications of animals used by people - register numbers, inventory numbers, names, etc. - are not suitable;
As few as possible calculated values should be saved into the database. For instance the calculation of the current lactation production and the control-year production “in-the-fly” is justified.
It is practical to save the pedigree of animals recursively into database - the data of the animal’s sire and dam is not saved in the animal’s own record. Only the pointers to sire’s and dam’s records from the same table are saved in the animal’s record.

References

1 - Claus J. Seminar on Animal Model Breeding Values: How to understand and how to
make use of them. 19-20 November 1996, Pirgu, Estonia.
2 - Groeneveld E., 1995. Konzept zur Organisation der Datenbank und Datenwerarbeitung am
Jõudluskontrolli Keskus, Tartu.
3 - International Committee for Animal Recording (ICAR). Constitution. Rules for the
Granting and for the use of the Special Stamp. International Agreement of recording
Practices. 02-06-1994.
4 - Reents R., Uba M., Pedastsaar K., Vares T. Implementation of animal model for
production traits of dairy cattle in Estonia. INTERBULL meeting, June 23-24, 1996.
Veldhoven, The Netherlands.