Utilities Embrace the E-business Data Warehouse

The demands of an increasingly competitive environment are forcing companies in the utility industry to become more customer-focused than ever before. To market, sell, serve and support today’s demanding consumers, utilities need real-time access to information about their customers. However, utilities’ IT systems were traditionally designed with high transaction volumes in mind, not customer analysis. A solution is needed that takes the transactional data stored in legacy operational applications, translates it into a usable format for query and analysis, and moves it to a platform designed to be easily accessible and always available. This solution is called the e-business data warehouse, and utility companies across the country, such as Southwest Gas, Reliant Energy, Entergy and Commonwealth Edison, are quickly realizing its benefits.

Turning Data into Information

The utility industry is currently experiencing an unprecedented rate of change. In many states, consumers now have a choice of electricity and natural gas providers. To attract and retain customers, utilities need to become more customer-focused than ever before.

But how do utilities find these customers, and what are their wants and needs? The answer may be surprising-utilities already own this information.

Locked inside legacy, operational applications, such as customer billing, usage, and service, is a wealth of customer information waiting to be mined. However, the challenge has long been getting to this data, and turning it into something meaningful. Most production applications within the utility industry were originally designed to handle the high number of transactions and throughput required in providing service to a large number of customers. Sophisticated query and analysis on the data was not a consideration. As a result, capturing and making sense of this transactional data has been a difficult and expensive chore, left to scarce, internal programmers within a company’s IT department to solve. To ease this burden, companies have turned to data warehousing software tools to get to the data they need and then load it into a decision support system. The shortcoming of these tools, however, has been their inability to capture data changes as they occur within the operational applications. They require the user to use a batch window, usually weekly or daily, to capture the data in bulk and then move it into the data warehouse. The result has been that the data warehouse never represented the actual, current state of the business, which has always been unsatisfying for users who need information for real time decision support.

Evolution of the E-business Data Warehouse

Bill Inmon, the recognized father of the data warehouse concept, defines a data warehouse as, “A subject-oriented, integrated, time-variant, non-volatile collection of data used to support the strategic decision-making process for the enterprise.” In essence, data warehousing is an attempt to take transactional data, such as customer billing history, and turn it into information that is easy to analyze and decipher. Decision-makers across the enterprise use the information stored in the data warehouse to help analyze and predict consumer behavior. Service personnel can analyze usage patterns over time to predict where best to allocate resources in the case of an outage. The marketing department can utilize customer usage and billing information to recommend the most profitable mix of products and services for commercial accounts.

Traditionally, data warehouses were updated monthly or weekly in a batch process. This was sufficient when the users of the warehouse were few, and the amount of information stored was manageable. However, the data warehouse concept has gained acceptance within many organizations, and as a result more and more people across the enterprise are requesting access to it. More importantly, the adoption of e-business is forcing organizations to become available around the clock via the Internet. This is resulting in an explosion of data as customers are now being granted access to the enterprise in ways unimaginable only a few short years ago.

Suddenly, the batch process is no longer sufficient. There are more users with more requirements for more information than ever before. And they all want that information now.

To meet the requirements of the around-the-clock availability and demanding users, many organizations, including leading edge utility companies, have implemented an operational data store, which is considered the next phase in the evolution of data warehousing-the e-business data warehouse. The operational data store (ODS) is a high-performance hybrid between mainframe-based, production applications and the traditional data warehouse.

The ODS mirrors the content of the operational databases it supports. However in the ODS, legacy data is transformed into relational data types such as database rows and tables which are much more efficient for the types of query, analysis and reporting that most users expect from the data warehouse. The unique advantage of the ODS, over the traditional warehouse, however, is the fact that the ODS is kept synchronized within minutes, or even seconds, with the actual operational databases from which it is pulling data. As a result, users of this new e-business data warehouse can get to the information they want, when they want it, from wherever they are.

Building the E-business Data Warehouse

E-business requires up-to-the-second data currency. In order to meet this requirement, implementers of an e-business data warehouse need a high-performance data movement solution to capture updates as they occur within production applications, such as a customer’s change of address, and propagate these changes immediately to the operational data store. This type of data movement is referred to as change data propagation, and it is the only practical approach for keeping an ODS synchronized within minutes or seconds of the operational databases.

Change data propagation must support the key, legacy databases used by operational applications. In addition, it must be transparent and non-intrusive to existing operational applications so that no additional programming is required and transaction response times are not impacted. Most importantly, the change propagator must be able to handle the legacy data structures found in operational databases and be able to transform these in real time to a relational format that users can easily query and understand. High throughput is the goal, so that the ODS, or e-business data warehouse, reflects the current status of operational databases. That currency will allow users to make decisions based on up to date, accurate data.

Once a company decides to implement an ODS, the rewards will be realized throughout the enterprise. Primary among these is the benefit of having real-time decision support. Service personnel will be able to access the current status of a customer’s account in the case of an outage. Account managers will be able to see updates to a customer’s account as they occur and be able to make adjustments accordingly. Secondly, the e-business data warehouse is designed to be available and accessible 24 hours a day, seven days a week. Users will no longer have to wait days, or even weeks, for the report they need as was the case within a traditional data warehouse environment. Thirdly, the ODS is an architecture that can be leveraged across the enterprise. Not only can it be used for real-time query and analysis which e-business users require, but it can also serve as the source for updating the traditional data warehouse within a company’s environment. Since the legacy data now in the ODS is already in relational format, instead of expensive mainframe, processing can be used to simplify the transformations that may still need to be done to meet users’ requirements.

Last, but obviously not least, are the bottom line benefits that utility companies will reap by adopting the e-business data warehouse. The change propagator automatically handles sourcing of difficult-to-access mainframe data, freeing up expensive and scarce mainframe programmers to focus their talents elsewhere. Since only changed or updated records will be captured, transformed and moved into the ODS, utility companies will realize a tremendous reduction in mainframe processing, which translates into bottom line savings. There will be a similar reduction in the amount of data that moves across the enterprise’s network each day, freeing up that resource for more business critical activities. Although difficult to quantify, this type of architecture enables utility companies to more quickly leverage and integrate their existing legacy systems with their new e-business applications.

E-business places an even greater demand on utility companies to make current, accurate, up-to-date information continuously available. Without an e-business data warehouse, in the form of an ODS, this type of information only exists in the mainframe-based, operational applications. And, it’s already known that it is costly to develop applications for these databases, and these databases are difficult to access and not well suited for users’ increasing demands for real-time data query and analysis.

Bill Talbot is a product marketing manager for BMC Software’s data propagation products. BMC Software will conduct a series of briefings this summer for the utility industry to present customer implementations of the e-business data warehouse. More information about the series or BMC software can be found at www.bmc.com/products/edp, or by calling 1-800-841-2031.

Southwest Gas Reaps Rewards from the E-business Data Warehouse

Southwest Gas Corp. purchases, transports and distributes natural gas to more than 1.2 million residential, commercial, and industrial customers in Arizona, Nevada and California. Southwest Gas added more than 65,000 customers during 1999, making it the fastest growing natural gas distribution company in the United States.

Six years ago, the company developed an in-house system for migrating customer billing and service information from its mainframe-based customer information system to an Oracle database, so the information could be used by customer service representatives and account managers. However, the system was very slow and difficult to maintain because custom coding was required to get the information out of the legacy system. “Data moving into the Oracle database was already 48 hours old due to system constraints,” said Lynn Del Carlo, a Southwest Gas senior systems analyst. Plans were also being made to allow customers access to their account information via the company’s Web site. “Customers want to know information like what they paid, when they paid, and what their usage is,” Del Carlo said. “Rather than have to call somebody up and wait on the phone, they want immediate access to the information.”

To handle current and future demands, Southwest Gas needed a way to move current customer data in real time to its e-business data warehouse with a solution that would provide cost savings and be easier to maintain.

After an extensive product review, Southwest Gas chose BMC Software’s Enterprise Data Propagation solution to do the job. The solution provides the company a high-speed method for extracting and loading the data into the e-business data warehouse without compromising either the data or the production applications where the data reside. In addition, the solution also allows Southwest Gas to capture just the changes made in the production environment and update the customer information system with these changes in real time. “That simply was not possible before,” Del Carlo said. “Our users are very excited to be able to immediately view changes made to a customer’s account, instead of having to wait two to three days as was the case with the old system.”

Besides increased availability, Southwest Gas has also found that these products cut down on processing time and maintenance. “Because we don’t have to do all of the programming and maintenance in-house as before, we save an enormous amount of time on activities such as mapping, extracting and moving the data,” said Del Carlo.

Southwest Gas is expanding the scope of its e-business data warehouse to allow customers access to their accounts via the Web.


Click here to enlarge image


1. Route e-business insert update and delete transactions to existing operational applications to update the operational databases.

2. Use high performance change data movement to maintain a current, accurate and highly available e-business data warehouse (i.e. operational data store).

3. Use the e-business data warehouse as the primary interface of data access, inquiry and analysis.

Previous articlePOWERGRID_INTERNATIONAL Volume 5 Issue 5
Next articleELP Volume 78 Issue 7

No posts to display