Software Through Picture Helps Environment Canada Reap the Benefits of Distributed Computing
By undertaking a massive database reengineering as part of its downsizing effort, Environment Canada is going well beyond traditional cost savings to dramatically improving its ability to provide crucial information. Environment Canada relied on Software through Pictures Information Manager to manage and speed the development of the new databases, tracking their complex interrelationships and enabling them to work within a tight time schedule. When completed, the new database architecture will not only provide more accurate information, but it will also enable government agencies to react much more quickly to emergencies.
As part of its ongoing mission, Environment Canada, the Canadian government agency charged with monitoring and collecting data on all aspects of air, water and weather, manages an extensive network of 3500 active weather stations and over 100,000 air and water pollution stations across Canada. It also maintains a climate archive comprising 150 years of historical data on weather-related items measured across Canada. The archive is instrumental in allowing weather forecasters to develop mathematical models for predicting weather, and provides architects and structural engineers with the information they need, such as highest sustained winds, to design bridges, buildings and other facilities.
Environment Canada manages its network of active and historical stations with its Station Information System. This system keeps a continuous history of all the measuring, reporting and archiving program the stations follow, among others; every instrument used, all the calibrations and maintenance performed; active personnel and awards received; the station's location in latitude, longitude and elevation; and any other relevant information.
"It's a multidimensional database," explains Dean Dohring, one of the project leaders revamping Environment Canada's system. "Not only does it span time, but it has spatial coordinates and all the components that make up the station."
Environment Canada had been using an IBM-port-compatible mainframe running the Model 204 database with an extensive disk farm capable of storing upwards of 80 gigabytes of information on-line to provide front-end management of climate data. The sheer operating cost of the mainframe system became to high, however, and management decided to replace it. After identifying the key components of the transition and assigning project leaders to them, management asked for the transition in 15 months.
"After talking with a number of the regional operations, I found that the only ones using the current mainframe-based Station Information System were the people at headquarters," said Dohring. "The regional inspectors were using their own systems based on spreadsheets, word processors or little PC database packages."
After establishing the need for an on-line, distributed database system to replace the current Station Information System, Dohring wondered whether he could extend the concept to include other information such as air and water quality and quantity. By creating a generic architecture for storing all the data, Dohring felt, he could satisfy the desire for a more global sharing of information.
Dohring convinced management that with an additional effort in a reasonable amount of time, they could make a system architecture that was generic enough to integrate not only their own stations, but those from many of the other services within Environment Canada, and beyond. Soon, what had started off as just a way of trimming costs would grow into an information revolution.
Environment Canada leveraged their existing Oracle RDBMS as the back-end database engine and added SmartStar Vision for creating the client applications. They used Software through Pictures to architect the data store.
"I had already started using Software through Pictures after a one-year Department of National Defense study determined that it was a very robust, flexible, multiuser product," said Dohring.
After merging the climate archive definition into the station management system, Dohring approached sister services within Environment Canada to determine their information needs for describing their stations and devices, and merged that into the data model.
"We very quickly realized we were not talking about a simple 10-table database," said Dohring. "In fact, our database has 123 tables. It takes 22 StP diagrams just to be able to read the diagrams, and 100 pages to print the DDL for Oracle."
Software through Pictures performed all the consistency checking to avoid duplicates and ensure everything had a data type. StP also enabled the other Environment Canada services to review this information to over the wide-area network.
"Without StP we would have had to manually design all the relationships, all the uniqueness constraints, the foreign keys, how things relate and what's inherited where," said Dohring.
Software through Pictures IM also allowed the conceptual development to be separated from the underlying database. In this way, Dohring could easily switch databases during development if it became apparent later that Oracle would not meet his needs.
"StP/IM is based on a generalized query-reporting language, and the SQL DBL generation is written with that," explained Dohring. "In a pinch, it wouldn't be all that difficult to take an Oracle DBL script and cross-customize it for another database."
StP's attribute tables enabled Dohring to hide unnecessary information and thus focus in on the actual modeling of the entities and how they relate to each other.
"Some of the patterns literally jumped out of the screen at us and screamed 'Look at this, I'm the same as that guy and the other one, why don't you make us one?'," said Dohring. "StP/IM also gives us great control over our diagrams without having to waste a lot of time worrying about which Unix commands to use, letting us focus on the job at hand -- data modeling."
Dohring also found StP useful to help him store his business rules with the data, a critical need when creating client-sever applications.
"By storing my business rules with my data, I can protect the data while providing access to the greatest number of users," explained Dohring. "StP enabled me to work graphically with the cardinality and the existence in the relationships themselves to impose certain constraints."
What started off as simply a replacement for the existing station management system has grown into a generic station information system handling not only climate data but air pollutants, water quality and quantity information and the huge archive for all the data. Initially, the mainframe is being replaced by an HP 755 workstation connected to 150 gigabytes of spinning disk and 200 gigabytes of optical storage in a jukebox. And the software development will be completed on time.
# # #