6
I
"elecommunication statistical data base: i a case study on microcomputers
by V. 8. DANG -\
.
and M. MINGES
packages made them an attractive option tion 61 powehiu comphter' natuware, for supporting telecommunication ana- easy-to-use analysis and presentation lvsts' requirements. Simple data base software tools as well as sophisticated systems such as base and spreadsheets data base management systemsavailable HE design and subsequent implesuch as Lotus 1-2-3and Excel were used on microcomputers. mentation of telecommunication to store data. Not much thought was statistical data bases (TSDB~on microgiven to data management because computers raise several issues. In this requirements were simple. As the article, we share our experience in develamount of data grew and user require- Case study oping a fairly large and comprehensive ments changed, the limitations of these system. systems became apparent. Problems Continuous improvements in micro- included inflexible file structures, Introduction computer hardware and software have extensive programming, data redunmade the realization of TSDB particu- dancy, limited data management tools, The project ofice of the regional African lady attractive in the telecommunica- awkward interfaces to external applica- satellite communication system for the tion eowiwdment ituowriig to 'amaef- &umarrd&p/eNeSb~otpb17i~m. development of Africa (RASCOM), kmbly reduce development time of the data The end result was that telecommunica- headquartered in the International m e [base and create efficient links between tion analysts found themselves devoting communication Union (ITU), is conk -datamanagement and data analysis. more and more time to overcoming the ducting a feasibility study on an African t limitations of these systems, leaving satellite system. The project office has collected large amounts of statistical them less time to analyse the data. 1 data from countries participating in the !Pad experience study. Storing the data in a computerFaced with the tasks of designing and based system would increase the produclow cost of microcomputers coupled implementing a sizeable telecommuni- tivity and efficiency of the project office the availability of relatively inex- cation statistical data base, the intention in processing the quantity of informaive, user-oriented application was to take advantage of the new genera- tion collected.
Overview
T
T E L E C O W N I C A TION JOURNA L - VOL. 56 - XI11989
7 13
Requirements, data stmctnre and size of A data base system was required that would support both the topical organizathe data base tion of the RASCOM data and relationThe project office wanted the data stored ships between topics. For example, in on a microcomputer-based system order to determine telephone density because of its relatively low cost and pos- (the ratio of telephones to population sible use by the participating countries multiplied by loo), the system needed and regional organizations as well as the to retrieve the number of telephones number of applications available on from the telephone service topic for the microcomputers. The variety and year and country specified, divide it by amount of data coupled with the user the population from the demography requirements, particularly operation on topic for the same year and country, and a microcomputer, posed a challenge. then multiply the resulting figure by 100. There was a wide range of topics to be considered including socio-economic, This example illustrates an important telephone and telex, satellite, sound and requisite of the system, namely the abiltelevision broadcasting and maritime ity to perform dynamic calculations. The radio. The structure of the data ranged telephone density is a derived data calcufrom time series (for example, number lated from two basic items stored in the of telephones) and matrices (for system, that is the number of telephones example, yearly telephone traffic be- and the population. Although it could be tween countries) to disaggregated nati- stored in the data base, this would create onal statistics (that is, the make and data duplication and compromise data capacity of local telephone-exchanges) integrity. Data is duplicated because and reference information (such as there is no need to store telephone denthe manufacturers of telephone equip- sity; it can be derived. Data integrity is ment). Data covered by these topics had compromised because if the number of to be stored for each of the approxima- telephones or the population is revised, tely 50 countries participating in the the stored figure for telephone density study. The minimum size of the data will then be incorrect. base is estimated at 50 000 records. Design Work on the design of the regional African telecommunication data base (RATDA), name given to the RASCOM data base, began in March 1988.
The RASCOM data-contained in country reports consisting of statistical tables designed by the project oficewas analysed in order to design an effio ient system that would not affect the way the project office was accustomedto deal with data. The aim was to create a flexible relatively simple data base to incorpoxate any new needs as the grobct grogressed. During this design phase, data was-or&&ed by toac and relations between topics identifed, redundant information weeded out, and spedal features designed to support &ta types, data integrity and multilingudity.
Implementation Relational data base system
A relational data base system enables topics with different file structures to be linked while hiding the complexity of how it is done from the user. It uses a standard data definition and query language known as structured query language which reduces development time and makes the data base compatible with a number of different packages.
tee comprises expert representatives of the AEtican Ministers responsible for telecommunications. Completion of the data base was scheduled for the summer of 1989 although entry of the large amount of data will continue beyond that date. System management
One of the benefits of relational data base systems are the extensive &ta management features they provide. The main benefit is the ease by which the data base can be modified to incorporate addition, deletion or modification of topics or data elements. Other benefits include security options, data base backup and recoverg and data integrityvalues have to be within a certainrangeas well as maintenance of all topics and data elements in a data dictionary. An up-to-date data dictionary can be consulted by users at any time to assist them in retrieving data and understand its meaning. Presentation
Presentation tools include generation of tables and linking of RATDA into word processing and desktop publishing applications. Currently, close to 100 tables designed by the RASCOM project office can be printed from RATDA onto a laser printer using different fonts, typefaces, and orientation. These pre-designed tables or the results of user queries can be output to disk fdes in various file exchange formats and imported to word processing packages for incorporation into reports or desktop publishing packages for additional formatting. Statistical anaIysis
Aprototype, implementedina relational data base system on a microcomputer, was initiated in July 1988. It was demonstrated at the RASCOM Interim Executive Committee meeting in October 1988 in Addis Ababa, Ethiopia. This Commit-
Besides basic mathematical operations such as addition,subtraction,multiplication and division, there are a number of statistical and mathematical functions available within RATDA including summation, average, maximum, minimum, standard deviation, variance, exponen-
tial, logarithms, mnatunrl Io&aithms, powex, square root and absolute value. Data can easily be extraded from RATPA ia standard Fie ex~btw#efixmats for further d y s i s by s p ~ d s $ e e t iurd forp&ages. Por e m p l e * RATDA data has been expofled to Excel, a spreadskeet pwlmge widely aaed within the ITU. BQSUSSextemive presentation wit3 graphics capabilities, Exoel &o prwidm a mmnber of fha-
Data types
for time series L ~omp1etelyOen%le; a yea^ can easily IreadWorc%&Watrtny time and does not mpke dDcation to the system. F m m t e : , "layers" of overlappin@pa's Me &o supported. For example#it is w&"ble tO h a v ~forecasted gross na&x%dp d u c t (GNP) F5wes from CSiEemnt fwmxstiaiJ techniques or data c0llect.d Erosl Werent sources such as popuWan figures from the country as well s the United Nations.
A special topic, again based on S O reaommendations, was created to identify the variety of data found in telwmd ~ t i o s4atistics. n Computer system gemally distinguish and allow operations on only W t e d types of data such as t s e p r * dedmd, chaaoter, money, date and time. %lecoa3~Wcationdata Madeamu& wider q e of tgpss such as &eqwmy,geogmphies5 co-ordinates, ciai, lxa&%mw l w i * rrtatisticat s o w aenypewtule, pawer, length and area. The aMty to sped@ additional and ~ ~ ~ ~ o M Rimdom MBBM~ data types A& ody makes the system Wh have also W e d RATDA to the easier to wrfeW but also makes it GAS 10software [7for time series mats- rnwe Bxible by m&ollina; valid mensis, remssioa, tntffic gni & tm af & tions (for example, a frequencydata type erlang cal&tions. Tfrese software rou- WOUfd be d e to recognize its dmensitines require data to be in a set f o m t on& uni8 (Hz, kHz, MEzIldKz, GHz) and A majar challenge wifh telecom~~unimwhich was predeslgned go that there is a suopmtiom such as o o n v e ~tion ~ W s t i mis detennbi~lgthe level of tramparent link between them and 6 ~ o n H z t u B ~ z ) ; aggregation. This has imptirtant repetIUTDA. A special RATDA da&t type is evwmet- cussions on oompate~mgourcm and the RATDA canbe used *th most statistical ated. This ibntifim Cta whid clun only wmpfexity and coasisteflcy of the data &psis padages. For exmble, it has have alimited set &values, Fbrexmi'ple, k. been intefitwd wit-$ SPSSTPC +, a a television system ctln ~ e n t f y be popabr commercial statistical anaIysis B L , SECAMor XlSC. This f a t w e sup- %1ecommWei8tioai staWm imlmbs both macro and micro data. Mam W e application. ports data integri€yand stand-on t x m m e t i o n data is agge$ated at the since the value can only be c h m n fPtMl country level aid is useW f o r~e g i d a standard set of values reducing ba5a anil international contlparisons. An A lisa of all of IWIDAs features is eatry erron. emnp1e of macro teleeonrrmnnieation beyond the s p p e ofthis W e . The fol&ta is the tow number of telephone lowing are p a s t h h l y relevant to teleeahnges for diffirent wantries. cammdcation statis&%. W e c ~ w ~ ~ t tdata i o is n di-gatOne of the user requirements was &at ed and useful fox in-depth national amthe system must support both English lysis. One example is a list of each teleCountry and French. This was done by designing phme exdxmge in a country she*its The InternationalOrgmhtion for Stan- the country, data me md other refer- location, the year it was eomdssioned, ddkation (ISO) 34li@t alphabetic ence topies with both Eng&h and the manufmturer snd Its atpacity. &s am med to distinp;nish country Frewh C E e ~ pWhen ~ . t$e syirbm is &&L This allows a sholr mgmonic code run,wers are asked which -1 they One of the advantages of dimaggregated tobe used for storing and retrieving data. want to work with. Depending on the national data is t h t it a n be EtSied to These-codes are mWed to a country response, the appropriatedescriptorsare derive macro level data. Us& ttEe example abcwe, the nxmber' am?to@ topic c&nkiniag thrt English and French displayed. capacity of a telephone 08zu1tfy m e used for presentation of exchanges is SEbtdned by Wcbg a m t the data Aaot4.k~Q a n a of the IS0 e Time series of the indiyidud exskangm and ad* &e is data ~&TY:bwew it ab wpt&y (table 1). Howver, T m o sedta data are important to tele- up t pWia e~cbmgeof data arrith . . that Berve doptd it. For txmlnoai~~~t-jan s&tisticdll da* bages p~sibiHtyof hta b a e a r a a e r n g n y d w p v l ~: and aezie~ ofhirrt&d d a r hte pmdwx f o ~ -smxw extra e a ~ tg from clther UniM N a t i sped&ed ~ M o f the topi- in RATDA && storage resoouoes, U also re-s &es to complemmt RATnA.mcio- GW~S. M@ut data entry resowam. Finally, eon* yearly time smies data. 8 u m t s~b&mM emnomic series is under study.
~~
~~
T E t E C O ~ N I C A 7 7 0 NJOURMAL - VOL. 56 - X1/1981) 7 15
if
i
These problems are inherent to relational systems; the next generation of data base management systems-objectoriented-is expected to handle them.
There are a number of refinements to be considered for future telecommunication data bases. A data base server utilizes the power gf microcomputers, tlle load area network and data base d b m e tedmalogies for providing e@eient mMXtide ttccessto different data bases. Thiswili enable access to data which is physically dispersed in the ITU micro&pnter ne-k-each containing dUTeren2 data-at the same time.
WiaBows
',
These intuitive, graphics-based displays
am e-etsy to l e m and language-indepen-
Exchange Services, provides access faciiities including solutions for external A c b Q w l e d g a d users to access data bases located in the ITU. The views and conclusions sostained in this document are thm of the apt5Mrs Objee4*rieated s~~shms and should not be interpreted as mpraD ~ e ~ o p ~ine an ; n ~ of computer senting officialpolicies, either e ~ m s e d so-e hob&ct-&e&& sys- Or implied From the nutems are Fitwing do& fmm a k c We wish to thank Messrs L. Goeloer far to cmmrcial applie8tionsnsTW will his valuable support and suggestions* allow more flexibility in the design of and A. 0.Taylor for providing RASCOM telecommunication data W, gr&m materiaI anel reviewing RATDA user adaptability to chaaigin$ user require- requirements. ments and a systematic approach to the integration of data management, antlly(Original language: English) sis and presentation.
a-o11
1'1 CCPrr : Banning Data and Formasting
Methoe, GA!3 10 Handbook (VO~S. 1
If careful considerationis given to design aspects and choice of data base software, a &crocomputer of sufficient capaciG can be used for the management of a large telecommunicaâ‚Źion statistical data base. In addition to the featuresa v M e within taEe data base; data can easily be extra~ted~fm lure with document pramssing and statistical analysis tools for presenting information and mgkine deusbm.
dent which increase productivity. Also, windows-based interfses W e it posh sible to deal with a number of applications operating at the same time. For example, under a windows-based interface, an analyst can emcurrently use dBferent data bases, prw~tationand analysis packages, all with aconsistent inter- A flexible data base system frees â‚Źhe face. tetecommunication expert from data W e m e n t complexities allowing him Pe&maie d d p to con~entmte on analysis. This increases productivity and quality by Smdards are emerging for document with more time to refine providing him fbmats that will make it possible to easmodels and produce more accurateplans ily integrate statistical information into sophisticated document processing and forecasts. packages such as phstocomgosition sys- The new generation of microcomputer tems. This wlIl dramatically reduce the opergtbg systems, data base managetime for extm&hg f@res from data meat systems as well as analysis and prebases for Stpithtioal pwblieaths leading sentation software tools will allow more to the &somination of more up-to-date sophisticated appliwti~mto be develinformation. oped for telecommunication sWst!cal data bas=, This wiil c e M y imp~ove the u w inhdwe thus providing eonsisA pilot project in the ITU, h o w as temeg aad trmsparency between applicathe B1ewh.lmuniation W m x d o n k n s .
2, and 3), ITU (Geneva, 1987)
Vea Ba D~IIWJ gmduated from the EcolepolyteckniquefZdttrsZe de Lmrsanne witli a doctorate degree in e&eerin& spsiW&g in the subject of stochastic proems in nuelear engineering. He began his non-academic career by computerizing the cost of living index, population &&istics and tax sWstics for the Geneva State (S&tzerEan&)wbere he developed an origin& technique to forecast State tax income, ffe &so worked as a computer consultant to design and iqiernent an accounting p a w e for a brokerage h Since October 1981, he has been with the Computer Deplldtment of the PI"U where he b position of Deputy Chief. In now hoMs t March 1988, hc was &ssignedatask of designing a data base for the RASCOM project.
MdBrret Mhgm graduated from the George Wkshhgton University withan M3A iniaformtiinsystems technology. Ift! spent 11yem with the fn&~rnational Monetary Fund d d oping and suppmthg large ststistical data Lwe$ t a t storage and retrieval .systems and networked workstation appEimtioos. S&ce June EBB, he bas been with the Cmputer m m e n t of the ITU 6ve1oping an information system for the WCOhrl project.
l E L B B M W N I a 21DN I W M A L - VOL. 56 - X1/1989 7 I7