Sybase Data Warehouse settled in Statistics Canada

Statistics Canada (Statistics Canada) is a federal government agency in Canada, which is responsible for censuses and calculations, which, every five years, carries out a census of 27 million people in Canada and provides results to government departments, research institutes and business organizations. The accuracy and timeliness of the published census information affects Canadians ' lives in many ways. The federal government has established and adjusted the delineation of constituencies based on census information, and the allocation of federal funds is based on this. Private enterprises and Government departments use these statistics to determine the direction of future operations.

During the census year, about 40,000 investigators spread to 38 million square miles of land to collect data and then focus the data into the headquarters of Ottawa and enter the computer. The census operation Group oversees the entire process of editing and publishing data. The resulting report brought the Government not only information, but also millions of dollars in revenue as the government sold the information to a wide range of clients nationwide.

Work based on existing systems

In the early 70, Statistics Canada developed an industry-leading DBMS for the IBM MVS Mainframe, which was used to collect and import raw data. Today, although the system is still used for the front end of census operations, it has become more and more powerless in the face of growing demand. As Ray Lackey, the system and database project manager and relational database expert at the Census operations Group, said: "Although the old software is good and strong, it is too inflexible and too time-consuming to manage." It locks a batch-oriented, Single-user system, and of course there is no GUI and other tools. ”

Create an appropriate solution

Statistics Canada invited Sybase to help it achieve such a goal. "Our RFI needs a database system that runs in a UNIX environment, it must be able to handle large databases and meet the needs of multiple users," Lackey said. We tried many different systems (including all the major competitors) and Sybase won the final victory. ”

The census operation Group uses Smalltalk to write their own desktop interface in Windows environment. It runs on the SGI Challenge 150s hardware as a large, cross-platform generator that uses two Sybase SQL Server 10 databases. The front-end is a metadata server that describes the enterprise's data and, with it, users can construct their query requests directly on the screen. It generates a C code base for database queries, has specific code for each integrated package, and sends them as batches to the third SQL Server running on the SGI Challenge XL multiprocessor, which can access the original census data. The ability to aggregate computing is stored in the clustered engine, and the resulting reports can be sent in different forms, such as CD-ROMs, tapes, and disks, depending on the customer's needs.

The core of the system is electronic Warehouse, which stores tables and reports that were previously created, allowing users to browse the results of previous queries instead of recreating the query every time. The tool is now very popular, and if users can find a table that already exists to meet the needs of their customers, then the development time can be greatly shortened. Lackey estimates that with the creation and storage of new tables, the size of the data warehouse could quickly become three times times larger than it is today.

Lackey was particularly excited by a new development project being tested at the census center. "Sybase in Ottawa has been instrumental in providing Statistics Canada as part of the Sybase IQ test process," he said. They arrange training, provide us with software, and provide support throughout the process. "Sybase IQ promises to improve query performance by 10 times times or more without adding any hardware devices." Lackey explains: "SQL Server is ideal for relational queries, but not for special analysis." "In the 1996 census, Sybase IQ will play a part in the complex query work in parallel." Finally, Lackey predicted that the introduction of Sybase IQ at the level of the original data would greatly improve the comprehensiveness and comprehensiveness of the analysis and achieve higher access speeds.

Establish an Intelligent background Office system

In order to enable the competition organizers to work more effectively, developed a called score! Backstage Office system, which allows the CFO to manage all aspects of the tournament. In addition to full-time staff, there are 12,000 volunteers, personnel management alone has a lot of work.

score! The system and its user interface are developed using Sybase SQL Server and PowerBuilder, which enables the CFO to develop a game schedule by the number of people needed and the needs of the staff and volunteers, and in the course of the initial interview to the final job assignment, Track the performance of staff and volunteers. score! Will monitor shifts, personnel availability and working languages. By determining the number of people present at a given date, score! can provide information to identify sensitive food distribution centers to enable them to determine the availability of food. The application can also be queried for the different requirements of dress for staff and volunteers.

The field management system has many functions, including security and the daily assignment of volunteers during the 30-day schedule. The booking system will provide tickets for the game to 2.5 million people.