Large Commercial Database

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

A large sales analysis system was described. The aim was to access and summarize sales data to produce comparisons of budget against current year and current year against last year. The size of the system was stressed: estimated at 12Mb new data each week and prints up to 40,000 pages!

Scope¶

The system is designed to answer queries by outlet, product or depot. ‘Trees’ of categories of outlet are used to indicate the summaries required. For example, outlets are grouped into company regions and areas but also into customers and regions used by customers.

The advantage of this tree structure is that an outlet could be switched groups at the lowest level and all the rest of the tree would be right. Keeping the tree up-to-date is seen as a substantial problem. An ‘undefined’ category was kept in the tree at all levels, so that every outlet would appear in reports, and outlets that needed information updated would be obvious.

Development¶

Before APL was chosen two other options were tried and rejected: (i) The original intention was to write the programs in COBOL, and to store all the subtotals that would be needed in reports. (rejected as 40 hours CPU time needed for weekly update) (ii) Bureau solution. No package was found that could cope with complexity. (She did admit that if they were starting again they might choose a different solution.)

I wonder what happened to all the complex Sales Analysis Problems which were solved by these approaches, and didn’t end up as talks at BAA meetings . . . Anyway, APL was chosen for the following reasons: (i) cheap – APL was already available and only 4 people were needed to implement the system (ii) quick – live data from the start allowed an immediate return (first summary reports after 3 months and fully implemented after 6 months) (iii) low risk – it would work, probably (iv) experimental – they didn’t have to finalise design at the start and it could be used as a prototype.

Reports¶

Reports are produced in batch runs of APL under TSO as there was so much data. The data was sorted into the appropriate order for a report outside APL. Each day’s reports were therefore reordered and submitted in the sequence that needed least sorting. The final result was a JCL program requesting the following steps: (i) invoke APL and read in data (7Mb workspace) (ii) sort data (iii) return to APL and generate report (iv) repeat from steps i,ii, or iii as required.

Conclusion¶

No system would have been possible without APL. They knew it was a success as the user had requested extra features!