By Chris Bowman
Utility companies nationwide are attempting to gain every possible advantage as they prime themselves to operate in a competitive, deregulated environment. Among the tools utility companies are using to help them gain this advantage is the data warehouse, which is a separate database that consists of data extracted over time from other operational databases. The data warehouse is designed to provide business users with analytical and ad hoc access to business information in a timely, efficient manner. In less than two years of use, the data warehouse at Memphis Light, Gas and Water Division (MLGW) has provided the organization a new level of access to its corporate data and, consequently, a new level of power.
MLGW’s data warehouse not only stores historical data but also makes it readily available to users.
MLGW, located in Memphis, Tenn., is the nation’s largest three-service public utility. MLGW buys electric power from the Tennessee Valley Authority (TVA) and distributes it to more than 400,000 residential, commercial and industrial customers in Shelby County. MLGW’s data warehouse implementation came as the result of the utility recognizing that it needed to place itself in the best possible position going into the uncharted territory of deregulation. The data warehouse’s power is evident in several areas: access to historical data, ad hoc reporting, analytical capabilities, flexibility, speed and independence from the IS department. To date, the project, which saw its first iteration go to production in October 1999, has addressed personnel, equipment, work management and general ledger areas. Many other areas will be included in the data warehouse as the project moves forward.
The data warehouse is intuitively historical, and this access to historical data has provided immediate benefits to MLGW. Even though some traditional operational systems include history, most do not. They have been designed primarily with the company’s day-to-day operations-and not the retention of historical data-as their focus. However, organizations are coming to the realization that their corporate historical data holds tremendous value. The data warehouse provides the mechanism to both build up this history and make it readily available. The historical data in MLGW’s data warehouse has been captured by two methods. The first is through the initial conversion for a particular application in the data warehouse. This is done where the history already exists. The second is through incremental loads, in which data is added on a daily basis and accumulates over time. In many cases, the data warehouse provides access to information that was not available previously. As historical information becomes more readily available through the data warehouse, so do the opportunities for gaining additional power. Ready access to historical data is providing MLGW with the capability to make better business decisions for the future by looking at data from the past.
MLGW’s data warehouse offers a familiar user interface.
Ad hoc reporting capabilities are another beneficial feature of MLGW’s data warehouse. The most common use of the data warehouse involves the resolution of various reporting needs as they arise. This feature provides users with almost instant access to their data. This is in contrast to the previous procedure, where formal report request forms were completed, submitted to the IS department, scheduled for processing, and then, eventually, the programs were written. Even the shortest turnaround time could be several days, and in some cases weeks or months. Now, many reporting needs can be completed by the users themselves in a matter of minutes.
In addition to acting as a data repository, MLGW’s data warehouse also allows users to analyze the data stored within it. This helps users study the business, identify problem areas and take advantage of unique opportunities that otherwise might go unnoticed. By evaluating data within the data warehouse, users ultimately can change the way MLGW does business. This is where the real value of the data warehouse lies. For example, data warehouse users may want to analyze per-mile vehicle operational costs and then group this data by vehicle manufacturer to identify which manufacturers to purchase new vehicles from in the future. Another example would be to identify any patterns that may exist in personnel absences so that new incentive programs can be implemented to decrease those absences. The warehouse provides the mechanism to find these types of patterns within data. This functionality can provide the users at MLGW with tremendous power to make the company as efficient as possible.
Reports created from a typical operational system tend to be static. Once one of these reports has been designed and placed into the production environment, the report’s layout and format are set. In contrast, reports created using the data warehouse are flexible and easy to change. Users can change reports whenever and however they need to, including changing the report’s data columns, how the data is sorted, control breaks, various calculations, and conditions that are used to restrict the report data. This flexibility can provide tremendous power to users as they are able to resolve the various report changes that occur daily in a typical work environment.
The data warehouse allows its users to create and maintain their own reports. This is another aspect of the data warehouse that is in direct contrast to the normal operating environment, in which only programmers can create and maintain reports using corporate data. Historically, fulfilling these types of requests has been a slow, cumbersome process. But, with the data warehouse, users can create and change their own reports and, therefore, establish their own report libraries. This independence has several positive impacts within the company. First, users are able to address their reporting needs when they arise and are not dependent on other departments. Second, any required changes are quickly and easily accomplished. Finally, the programming department is free to work on more complex projects. Providing data warehouse users with this capability certainly provides an all-around boost to the company.
One of the greatest benefits of MLGW’s data warehouse is its speed. In addition to the time savings users attain by generating their own reports, the actual time it takes for reports to run can be incredibly fast. Granted, this depends on the design of the database, but as users communicate their anticipated use of the data warehouse, the project team is able to design the warehouse to be as efficient as possible. This results in most queries being completed in a matter of seconds. Before the data warehouse was implemented, MLGW users were accustomed to requesting reports from the mainframe system and waiting for them to print on green bar paper somewhere. Sometimes those jobs were scheduled to run only at certain times of day. With the warehouse, reports can be run whenever the user needs them. In some cases, the data warehouse provides the user with the ability to run a report that was not available on the operational system. This may be because the data warehouse includes historical data or because the data simply was inaccessible on the operational system.
The data warehouse provides previously unavailable power to MLGW. As the utility has educated its users and marketed the data warehouse internally, both the number of users and level of activity have increased. The users have acknowledged that there is power in having readily accessible data at their fingertips. Another advantage for users is being able to query this information themselves, free from dependency on the programming department. As MLGW continues to educate and inform its user community of the data warehouse’s power and potential, the utility expects to see a continuous pattern of success. The fact that the utility only began implementing its data warehouse less than two years ago offers promise to the technology’s potential.
MLGW has just scratched the surface, but the data warehouse project team and the user community already have seen the advantages and power gained through using this new technology. MLGW expects the data warehouse to continue to be a powerful tool as the company positions itself to be the utility of choice in an uncertain future.
Chris Bowman is a computer software specialist and the data warehouse project team lead at Memphis Light, Gas and Water. Bowman has been with MLGW for 11 and a half years. He holds a degree in computer science from Union University in Jackson, Tenn.