Indicator Database
Web Input form system
Log on



The Indicator Database was born in 1989 as a terminal based Oracle/VMS application and has been through PL/1, SAS and is now again a pure Oracle/web application. In all the years Oracle has been used as a database, but the user interface has been living an exiting life.

Forms 3.0 and PRO*PL/1 userexit

The Indicator Database was developed in sql*forms 2.3 as one of the first Oracle/VMS systems in The Ministry of Education around 1989/90 and was soon re-programmed to forms 3.0. As well I tried to use forms also for presentation of indicators, but soon the screen view and the triggers were too large and impossible to maintain. Therefore select and presentation of indicators were in stead implemented as a PRO*PL/1 userexit. This was before C was the ministry's official programming language in connection with Oracle systems. The system worked excellent, but was difficult to learn because of the many function buttons in Oracle forms and the great number of special buttons in the presentation program. The demarcation typically took about 2 minutes and was a relatively complicated process. The presentation on the other hand was fast and flexible, but of course complicated to maintain because of userexit. As an often used function transfer of indicators were implemented to 20/20 spreadsheet.

The Indicator Database Server process

Due to problems with numbers of open cursors in Oracle and as an attempt to improve the performance at select of indicators, a PRO*C server process was implemented. Process communication was used to select indicators. This moved the very select of indicators out of the userexit to presentation of indicators. Furthermore a store function was implemented in the server process, so that already referenced indicators were saved in the intern memory in the server process. This meant partly a better performance at repeated references of the same indicators and partly a reduction of the total number of open cursors, as it was now the The Indicator Database Server process which selected indicators in stead of the individual client-processes.

Transfer to lotus 123

As a first little step in connection to the transition to personal computers the transference to worksheet was re-programmed to mail of a 20/20 worksheet to lotus notes. In the memo the worksheet could be disconnected/opened as a lotus 123 worksheet. This worked fine except from the problems with mail containing worksheets from Allin1 to lotus notes. The time of transition could vary from 30 minutes up to several days …

SAS/Frame, SCL and object oriented programming

In the middle of the 90'th the user interface was altered to a SAS/Frame application with a SAS client/server connection to VMS, where the very select of indicators took place.

The first SAS version was an application, which was a lot faster and more user-friendly than the original forms 3.0 application, but also an application where most parts of the code were gathered in the main display screen, for instance choice of database, division, drill/swap, choice of values in the different dimensions as well as select and presentation of indicators. The main display unit had grown too large to be translated in debug mode - and impossible to maintain. The application was therefore re-programmed by using object oriented programming techniques. Still with the same Oracle database as in the forms 3.0 version. Most parts of the application code were now moved to the component objects, and the application was much easier to maintain.

And what lotus 123 worksheet concerns I used DDE communication, and now the time for transferring to 123 was about 15 seconds.

From SAS client/server towards ODBCC

The above application made use of SAS client/server. During low charging conditions references and presentation of indicators took about 15 seconds. At references during charging conditions or at references for larger amounts of data the time for reply could reach up to 30-60 seconds. A nice improvement compared to the forms 3.0 application, but still not entirely satisfying.

The next step was to get direct connection to Oracle and thereby avoid the long way through SAS client/server and PL/1-server process to Oracle. I relocated the very reference of indicators to ODBC and the time for reference and presentation was now reduced to about 4-5 seconds.

SAS/Frame, OO-programming and ODBC

In spite of the application of OO-programming in SAS/NDB the application was still difficult to maintain, and especially the administration part of The Indicator Database was heavy. This experience together with the wish of using ODBC in stead of SAS client/server for improvement of performances, and a wish from Steffen Jensen from The Economical Department about being able to demonstrate The Indicator Database from a portable pc in Moscow, started the next reprogramming project.

The database was now totally redesigned. At the same time there was now a chance of making The Indicator Database into a decentralized and general system, where this was not hard-coded, how and for what use the local Indicator Database was supposed to be used in the departments. The database is now open for installation of own indicators, code systems, pre-columns and divisions. User context was saved in Oracle tables. Numbers of sql operations were minimized, as it was now ODBC to make the bottle-neck. Numbers and sizes of selects were minimized, and the collected data were stored in internal SCL-lists (pointer chains). The result was an answering time for about 2-3 seconds at reference/presentation of smaller references and about 0-1 second at drill/swap transactions.

The Indicator Database on WWWW

At the IT-office in the ministry I had already made a few Oracle web applications. PL/SQL web toolkit was used. I.e. pl/sql is used for print html code dynamic. Partly a case generated system (Oracle designer) was used and partly a metadata based web form system (The Web Input Form System). The metadata based system appeared to be the most flexible easiest system to use and became the present web input form system of The Ministry of Education.

In this way it was evident to try to use PL/SQL web toolkit as a the user interface to The Indicator Database, for instance because already all data already were present in Oracle, and the database was metadata based.

The result is the database you see today. Here the user context is hidden in the database, and each click and choice in the web interface becomes a call of a stored pl/sql package, which generates the next display screen etc. etc. Today the database has 150.000 unique users a year, and the application has been tuned for fast reply times and many contemporary users. Context is parted across user sessions, and in that way writing to the database is reduced to a minimum. Almost all writing happens, when the first user opens a saved presentation. Mostly the response time is just about 1 second.

-    Jan-Roslind.dk    -