Whitbread: Large Scale Database

Transcribed from the printed issue; not yet reviewed. Read it in the PDF of the issue, page 81

Case Study¶

edited by Adrian Smith

Acknowledgements¶

Many thanks are due to Christine Brewster of the Group Management Services Department of Whitbread & Company. I had previously heard of ‘The Whitbread System’ by repute, but to see it in action was a wholly different thing. From the perspective of my hitherto sheltered existence the prospect of using APL to gulp massive amounts of data in megabyte workspaces, indeed to beat COBOL comprehensively at its own game, was highly attractive, and impossibly remote.

Well, I know better now; it really can be done, and done well. In fact I would have to say that the way APL is being used at Whitbreads was one of the most impressive syntheses of old and new that I have ever seen. I hope the flavour of this comes out clearly in Christine’s notes; I’m sure she will forgive the occasional editorial interjection from time to time!

Incidentally, readers may be interested to know that the excellent pub chosen for lunch did not in fact offer for sale her company’s product. Well, I have (on rare occasions) been seen eating Mars Bars, so perhaps she may be forgiven for this momentary lapse!!

APL as a System Development Language¶

by Christine Brewster (Whitbread & Co)

Introduction¶

APL had been in use at Whitbread for around 5 years, covering all the normally accepted areas of personal computing. However in the system about to be described it was used much more as a ‘conventional’ systems development tool. As it happened APL turned out to be very much more effective than conventional data-processing methods; it was quicker to implement, gave a better end-product, and was cheaper to run.

Background¶

The brewing and wholesale interests of Whitbread are today complemented by two other high-profile areas: retailing and Wines & Spirits. It was from the National Sales division (responsible for ‘take-home’ sales) that the pressure for a responsive sales analysis system first came. Typically, take-home sales involve cans and bottles (of both beer and wine) through shops, off-licences, wholesalers and cash-and-carry outlets.

The system was deemed URGENT because it was necessary to have both recent and accurate information on the business, in order to know the effect of sales promotions, advertising campaigns and the status of new products in a changing and very competitive market.

It was known to be fairly COMPLEX because it needed to be able to report on whichever areas of the market were of interest at any time. It was difficult to predict which these areas would be.

It was going to be LARGE in terms of data quantities – 5 to 12 megabytes of sales history data being input each week, building up over two years to a database of about a gigabyte of data. The major data keys are outlet, product and depot and there are some 720 million possible key combinations.

It was also going to be LARGE in terms of print volumes – the month end reports were expected to produce between 10,000 and 40,000 pages of print – between 5 and 20 boxes of paper.

It was also going to pose an unusual processing problem in that the users felt they would need up to 300 reports a month, each report covering between 10 and 300 megabytes of data.

There was a huge area of UNCERTAINTY:

How fast would the database grow? This growth is determined by how many different products each customer orders. The average would increase at Christmas and other peak sales periods, but by how much?
The effect of promotions – products sold on promotion take on new characteristics and need to be reported on separately. A product sold on promotion effectively becomes a new product and adds to the size of the database. It was uncertain how many transactions were going to be under promotion and what their overall effect would be.
Reports – the number and content of the reports requested each day was unpredictable and depended on the marketing interest at the time.
Interpretation of data – it was suspected that there were items of the source data that were ambiguous. There was also additional data such as budgets and targets whose source, quantity and interpretation were unknown.
Organisation of reports – 11 different types of reports had been defined. It was uncertain how many different subtotalling systems were needed, or how complex these were. This subtotalling might reflect the structure of the customer’s company, as well as of NSD’s different sales forces and management reporting structures.

In summary there were a lot of problems!

Where to Start?¶

It was of course natural to use the conventional and proven ‘Whitbread Approach’ to the problem, especially because of the size of the system and its importance to the business. In due course a proposal was developed to meet the requirement; it involved holding a VSAM database of all outlet/product/depot occurrences with all subtotals necessary for reports. This made reports and on-line enquiries cheap, but the weekly update would be expensive.

The subtotalling systems were to be represented by structured codes, the management of these to be left to the user. Any changes to the subtotalling system would necessitate a recalculation of the database.

The method was found to be unacceptable because:

The run times of the jobs which updated the database were too long. It was estimated that the weekly run would be in excess of 40 hours, due to the huge number of disk I/Os involved.
The database would be too big. This created problems of cost, reruns and security.
There was too much uncertainty about the subtotalling structures. Changes in customer companies might mean amendments to COBOL programs.
As there were so many uncertainties it was obviously very risky to commit programming resources to producing a system which was not guaranteed to meet the users’ requirements.
The timescales were too long. The users would not start getting results from the system for at least three months. (?! Ed)

Essentially the problem was a classic chicken-and-egg situation. We needed information to design the system, but that information could only be provided by the system we did not yet have.

The Search for a Stop-gap¶

Because our conventional solution was unacceptable, we looked for a stop-gap measure based on the Business Systems Design and requirements reports already produced.

The minimum criteria were:

Acceptable run times
Flexible reporting
Quick, phased implementation
Low profile

We looked for a stop-gap measure in order to:

provide the users with as much information as possible with which to run their business as soon as possible.
generate information which would provide a clearer understanding of what was needed so that a ‘proper’ system could be specified and the necessary processing capability could be afforded.

Firstly, an external bureau was approached. They proposed that we use a relational database to set up and update the data, a query language to extract the subsets, and a general reporting language to produce the final reports (with infinite variations).

This approach was not feasible because:

The size of the problem was too great;
The complexity of the system could not be handled elegantly by general languages;
The CPU and I/O time estimates were again enormous.

APL was then adopted as a stop-gap measure. The APL solution involved storing all the data at the lowest level only, processing it offline using SORT (with SORT exits to reduce the number of I/Os) and doing all the calculations at report time.

Using the APL approach we could tackle the problem by making only a small number of basic assumptions, and build up towards a complete solution. Since all the reports require data for one month, with year to date, compared to data for the same month last year, we decided to use a database that was segmented by month.

The APL solution was accepted because:

It was CHEAP. The implementation was estimated to require one man-year of effort – a team of four for 3 months. We did not need to buy extra software.
It was QUICK to implement. Any problems with the system would appear soon, before too much money had been spent. As all development was done using live data, the development process would produce information useful to the business.
It was LOW PROFILE. The small team would not use programming or other resources required by other systems, and would not be highly visible. The team was made up of three APL contractors provided by a specialist APL bureau, in addition to our own APL specialist.
It was almost CERTAIN to work. The team had already produced results for the users while in the initial feasibility stages.
The system was seen as an EXPERIMENTAL stop-gap measure and was not intended as the final solution. If the user was not satisfied he knew that he would have another chance later.

Experiences of Implementation¶

Our first venture into this type of development was more successful than we could have imagined. This was because:

We used PROTOTYPING – we effectively wrote the system specification in APL. It could convey infinitely more information than a specification in words; the user could log on to the system and try it out. It also avoided all the ambiguities of a written specification.
Users quickly became involved in the development process, and could provide valuable FEEDBACK.
Changes to the system could be made while sitting at the terminal with the user. Problems could be sorted out IN FLIGHT. Writing a system in APL is like driving a car with a mechanic sitting in the back seat. If the car breaks down the mechanic fixes it and you continue your journey, without having to have your program towed back to the garage.
The system was designed using an EVOLUTIONARY process. It was used to generate the clearer understanding of the problem that was needed to refine the system design. The initial system was the egg which hatched into information which laid the next version, in turn hatching into more information and so on.
The system design was GENERALISED. A small number of basic assumptions were made to start the process. These were acknowledged as guesses and refined as necessary with hindsight. There were as few limits hard-coded into the system as possible.
The system designed was OPEN-ENDED. No time was wasted on areas of uncertainty – these were omitted until we had a clearer understanding. We split the large area of uncertainty into small areas that could be tackled separately.
The system was developed by a SMALL TEAM, comprising four APLers and a user representative. This meant that there was GOOD COMMUNICATION: within the team; with the users; with other technical experts.
Each team member could see (and understand) the whole system. This eliminated problems with consistency and ensured that components interfaced. It also eliminated re-inventing the wheel, and enabled problems to be identified SOON.
Our team had LOW OVERHEADS. Because our APLers had a high concentration of skills, each team member combining abilities in: analysis (business); systems design; APL programming; technical (JCL etc.) – there was no need for the systems analyst to explain the user’s requirements to the programmer because the analyst was the same person as the programmer.

The Solution¶

The APL solution introduced the idea of trees. These are hierarchies which are subject to strict rules:

Each tree can be based on either outlets, products or depots.
All products must appear in each product tree.
Each product can appear only once in a tree.
The number of levels in a tree is defined by the user when the tree is set up.
The products are all at the bottom level of the tree.
Each level in a tree has a name.
Each node in a tree has a code and a description. The nodes are sorted by code.

The trees are used throughout the Sales Analysis system to specify the level of subtotalling, data selection and ordering in reports. Trees can even be stacked on top of one another! They are maintained by the user with a full-screen editing system, where . . . .

The user gets a screen of all trees and can select the one he wants to edit.
The next screen is a list of all nodes at the top level of the tree, and he can select one by moving the cursor and pressing a PF key.
He is then shown a screen of nodes at the next level of the branch he has chosen and can select one for further investigation.
The process continues down to the bottom of the tree where the screen shown has the products belonging to that branch.

The user can insert or delete nodes, change their codes or descriptions, or GET branches from the same or another tree. He can also move products from one branch of the tree to another. Alternatively, he can edit a special tree called PRODUCTS and see where one particular product fits into all the product trees.

The trees are also a fundamental part of report requests. These are again generated from a full-screen system, based on menus, which allows the user to select from data known to the system:

Which report from the list of standard types.
Which trees to use to structure the report. Where the report requires a product tree a menu of all available product trees is shown.
How far down the tree to do subtotalling. The user can select from a menu of level names in the tree.
At which level to do data selection. The user can select from the levels down to the subtotalling level.
Which branches of the tree to select data. The menu shows all nodes at the level chosen.
Which level of the tree to do page-throws. The user can choose from the levels covered by the report.
Which month’s data. A menu shows all the months that make up the database.

Once a report has been defined, it is ‘posted’ away for offline processing in overnight batch. This is where things really start to happen.

The Offline Reports System¶

Each report in the stream of requests generates a sequence of job steps, such that:

Firstly, an APL step reads data from tape, tags on the necessary sort keys (tree information), and writes the result to a temporary dataset.
A standard JCL SORT is then used to sort the data.
A final APL step reads the sorted data and produces the report.

JCL is written from APL which examines all the pending requests and decides on order of reports, which tapes to use, which data fields to extract from the database, how many and which sort keys to use, if a sort is necessary, and the logical record length of data to be sent to the intermediate dataset. The JCL generator also writes a schedule sheet for use by operations staff, and a profile sheet listing the expected CPU time and the tapes to be ready for each job step.

The appendix shows part of a typical job stream. The DTL step puts sort keys on to the data. Each SRT step sorts the data for reports. Each REP step produces a report. The number of steps in each job is determined by the number of reports that have been requested. The users often request up to 35 reports at a time.

(Ed. This all sounds very simple; to make it work in practice, with very large data volumes, is altogether another matter. Some of the ‘tricks of the trade’ used in the Whitbread system are detailed below.)

The structure of the database is clearly all-important:

The sales history data is held throughout the Sales Analysis system in APL format.

APL sees the data as matrices of integers. The number of columns (the logical record length) is determined by the system and depends on the number of data fields and the number of sort keys that are in use at the time. The number of rows is calculated so that each matrix will fit into a half track of a 3380 disk (23476 bytes).

SORT sees the data as single record blocks which are decoded by an E15 exit. The E15 uses the APL header and trailer information to determine the actual length of the logical records and to pass these separately to the sort process. The E35 exit reverses the process on the sorted records. This method reduces the number of I/Os performed by the system to a minimum, while preserving the APL format of the data.
Sales history data is held on tape – each tape holds a month’s figures plus year to date. The database for a year is thus made up of 12 tapes. The tape files grow as the year progresses because of the accumulation of year to date figures.

Each report requires the tape for the relevant month this year and possibly the tape for the same month last year.

Some of the software enhancements needed to optimise the running times were:

SORT uses E15 and E35 exits in Assembler to unblock and reblock data which is in internal APL format.
APLPRINT is used to output reports a page at a time.
Table lookups were speeded up using a fast dyadic iota routine (written in Assembler) which takes advantage of the fact that the table and the data are both in ascending numerical order. This is interfaced using ‘CALL/AP’ from Interprocess Systems.

(Ed. So much for all the Whizzbangs . . . how well does all this really perform? The answer is interesting; in APL terms it still manages to crunch massive amounts of CPU, but of course this is mostly off-line and overnight, and therefore ‘free’. In terms of the same system written in conventional batch terms, the run-times look much less frightening. Strange how one’s perspective changes; I used to think very little of FORTRAN jobs which ran for 1,200 sec of CPU.)

System Performance¶

The system produces approximately 80 standard and 150 ad hoc reports per month. It prints on average 15,000 pages on paper per month (mostly multi-part paper) used by over 100 people. Some 20,000 pages are also sent monthly to microfiche (for archiving).

The average CPU per report is about 6 mins on a 3083J; an estimate because the system has never run for a whole year between optimisations. Typically, 110 megabytes of data are processed for each report; to manage this efficiently, large amounts of memory are essential:

A 6.2 megabyte address space is needed for batch steps. This enables APL to load large quantities of data into memory to do calculations.
A 7.5 megabyte workspace size for on-line sessions. This is necessary to hold the APL variables which make up the trees.

How much APL code is involved? Measured simply in lines of code:

40,000 lines (in 10 workspaces);
Approximately 2 megabytes of code (compared to 7,500 lines in 0.4 megabytes for ADRS; Sales Analysis 5 to 6 times more complex than ADRS).

Estimates for re-writing current system in COBOL – no volunteers.

What do the Users Think of it All?¶

We know that the system is successful because the users continue to identify new areas that the system could cover. These new requirements emerge as a result of information that the system provides, as well as from the users’ increasing experience. These requests are easy to implement because our team has expertise in the whole system.

Data extract – Sales Analysis can produce data extracts from the database in machine readable form. These can be used in Personal Computing (usually APLDI) for the users to do their own analysis and manipulation.
Drop size analysis – a parallel database with data keyed by delivery date and delivery number to give an analysis of the size of deliveries. The reports produced by this development were difficult to verify because there was no existing system for reconciliation of the results.
Report generator – this allows users to change the format of reports by specifying column widths, headings, which data in which columns, ad hoc calculations, and so on. These changes previously had to be made by the development team.
Distribution analysis – a type of report not previously planned for, measuring market penetration of products.
Budgets and targets – the users can use ADRS to set up budget and target figures and to report on them. Sales Analysis now has a facility to access this data.
Tree prints – since the idea of trees has become so popular with the users, they asked for a facility to print them out in graphic form. Every manager who is anyone in NSD has a tree print on his office wall.

Conclusion¶

It is our opinion that the evolutionary development process was the most sensible approach because we were faced with so many uncertainties over a system that was undoubtedly going to handle large quantities of data. APL provided us with the best answer for prototyping. Also APL was suitable because of the power of the language to handle large quantities of data in a way that was completely user-driven.

The methodology used in APL enabled a system to be designed to suit the user’s requirements even though he couldn’t specify those requirements at the time.

Among other things, the undoubted success of the system is demonstrated by:

Its continuing use. In spite of the implementation of data extract, which allows users to do their own reports, usage is higher than expected. The users anticipated a maximum of five outlet trees and five product trees. They now have 14 outlet trees, 12 product trees and three depot trees. There is a thriving user group.
The ease with which it is being applied to new areas, and the requirement to do so. Development has been continous since the beginning of the APL project.

The final accolade is surely this; the scrapping of the original plan to produce a permanent solution to replace the stop-gap APL experiment.

Appendix. Typical job stream¶

//*************************************************
//* GO INTO APL AND DO STEP: DTL5
//*************************************************
//*
//DTL5     EXEC PGM=IJKEFT01,REGION=6200K,DYNAMNBR=5
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
PROF PREFIX(NSSA)
CALL 'SYS1.TFAX.LOADLIB(TFAXPGM5)' 'SYSTSGG'
VSAPLNEW SHR(128K) FREE(512K) CODE(1) SMAPL(OFF) AP(CALL802)
/*
//CONTINUE DD DUMMY
//CLIB     DD DSN=PNSSA.LOAD.LIB,DISP=SHR
//APLPRINT DD SYSOUT=*,CHARS=APL,DCB=LRECL=133
//APLIN    DD *
)LOAD NEWREP
DETAILS
/*
//THISYR   DD DSN=NSSA.PERIOD.D1985070,DISP=OLD
//LASTYR   DD DSN=NSSA.PERIOD.D1984070,DISP=OLD,UNIT=AFF=THISYR
//FULLYR   DD DSN=NSSA.DUMMY.EMPTY,DISP=SHR
//TEMP     DD DSN=NSSA.REPTEMP,DSP=OLD
//FIRST    DD DSN=NSSA.CONTROL.FILE,DISP=OLD
//BUD      DD DSN=NSSA.BUDGET.INTERIMU,DISP=SHR
//LAY      DD DSN=NSSA.BUDGET.LAYOUT,DISP=SHR
//REQS     DD DSN=NSSA.REPORT.REQ(N5),DISP=SHR
//*
//*************************************************
//*         SORT FOR REPORT: SRT5
//*************************************************
//*
//SRT5     EXEC PGM=SORT,REGION=6200K,PARM='SIZE=6000K,DIAG'
//SYSOUT   DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTLIB  DD DISP=SHR,DSN=SYS1.SORTLIB
//USERLIB  DD DISP=SHR,DSN=PNSSA.LOAD.LIB
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(75,20))
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(75,20))
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(75,20))
//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(75,20))
//CONTROL  DD DSN=NSSA.CONTROL.FILE,DISP=SHR
//SORTIN   DD DSN=NSSA.REPTEMP,DISP=OLD
//SORTOUT  DD DSN=NSSA.REPTEMP,DISP=OLD
//SYSIN    DD *
  SORT FIELDS=(17,4,BI,A,21,4,BI,A,25,8,BI,A,5,4,BI,A,1,4,BI,A)
  RECORD TYPE=F,LENGTH=(23476,48,23476)
  MODS E15(NSDE15,5000,USERLIB,N),E35=(NSDE35,40000,USERLIB,N)
/*
//*
//*************************************************
//* GO INTO APL AND DO STEP: REP5
//*************************************************
//*
//REP5     EXEC PGM=IJKEFT01,REGION=6200K,DYNAMNBR=5
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
PROF PREFIX(NSSA)
CALL 'SYS1.TFAX.LOADLIB(TFAXPGM5)' 'SYSTSGG'
VSAPLNEW SHR(128K) FREE(512K) CODE(1) SMAPL(OFF) AP(CALL802)
/*
//CONTINUE DD DUMMY
//CLIB     DD DSN=PNSSA.LOAD.LIB,DISP=SHR
//APLPRINT DD SYSOUT=*,CHARS=APL,DCB=LRECL=133
//APLIN    DD *
)LOAD NEWREP
REPORT
/*
//REP      DD DSN=NSSA.REPTEMP,DISP=OLD
//HSAPLOUT DD SYSOUT=(A,,LUIN),FREE=CLOSE,
//            DCB=(RECFM=FBA,LRECL=133,BLKSIZE=18886)
//CNCL     DD DSN=NSSA.REPORT.CNCL,DISP=OLD
//REQS     DD DSN=NSSA.REPORT.REQ(N5),DISP=SHR
//PDIR     DD DSN=NSSA.EXTRACT.DIR,DISP=SHR
//*