Therma Corp. is a large mechanical contracting company which specializes in design, installation, and service of environmental systems and manufacturing processes.
Therma has a single HP3000-based ERP job-cost system which is used to run all of their accounting and job-cost activities.
Although the HP3000 mainframe system was meeting Therma's accounting and job costing needs, its technology and security requirements restricted its project managers' ability to view timely job costing information for planning, forecasting, and corrective action. In addition, the user-interface on the mainframe system is not ideally suited to large volumes of data viewed interactively, and, since many of Therma's jobs are quite large, the mainframe was not suitable for this purpose.
Therma decided to create a data warehouse to summarize and manage the job costing information, and, allowing for security considerations, make this data available to the project managers. The key requirements for the data warehouse included:
- Data in the system must be up to date, with no more than one day lag from the mainframe system
- Data should load into the data warehouse for only those jobs selected on the HP3000
- The Extract-Transform-Load process must be automated
- The data load should consist of job costing information only, and not include highly secure mainframe data
- The user-interface must be easy to use and provide immediate access to large volumes of data
- The system must provide project management and forecasting ability, with calculations for efficiency and projected totals
- The target software and hardware must be secure, fast, inexpensive, and easily accessible
- The data warehouse must include security functions to control data at the user, form and project team levels
Since Therma already had a large network of windows-based servers and knowledgeable staff supporting them, we opted for a Microsoft Windows server running Microsoft SQL Server for the hardware/database software configuration, with a PC-based client for the user-interface to the database.
Several technical considerations affected the design of the extract-transform-load process to be written. The data warehouse ETL process contains the following features:
- Handles both incremental loads (changed data only) and full re-loads for resynchronization.
- While doing incremental loads, the ETL process extracts all data for all jobs not previously or recently extracted, and changed-only data for the rest of the jobs.
- Data is retained on the data warehouse indefinitely, even if purged on the source system
- The ETL process for incremental loads requires about 1 hour on the HP3000 and about 2 hours using the SQL bulk-load facility on the Windows server, allowing it to easily fit within a nightly schedule. A full load, done on the weekends, requires about 4 hours on the HP3000 and about 8 hours on the Windows server.
The Data Warehouse client is composed of many forms allowing views of master job information, summarized costs to-date, costs margins and sell margins grouped and totaled by category, billing and payment information, and detailed labor and material cost records.
The project managers use this information to detect project overruns, estimate project completion timeframe and dollars, and review actual cost information against estimates. The information is presented using form tabs and scrollable tables. Easy drop-down selections limit the data viewed for quick drill-down. Columns may be selected for ad-hoc sorting and totaling.
There are a variety of tight security features that allow user and project team security to be easily administered. The data warehouse security scheme is linked to the HP3000 employee records so that users view only jobs and data for which they are authorized. And, there is a logging and monitoring feature to see who is using the system, and a screen timeout for inactive sessions.
Comp Three implemented the Data Warehouse system in just over six months. It mirrors the 5-gigabyte transactional database with over 5 million detailed costing data rows, and about 75 thousand jobs. Senior management, financial managers and planners, and over 30 project managers, both on site and off site, currently use the Data Warehouse. Therma project managers say that they have achieved substantial savings in projected schedule and cost overruns using this system.
“Data warehouse has been an invaluable tool for Therma since its completion. It has allowed our project managers to review almost real-time information regarding job charges. In this fast-paced business, knowing job costs is vital to making informed decisions and forecasting job completion.”
Therma Business Operations Manager of Projects