Ameren Corporation

Utility Company Subsidiaries Improve Trading Using Business Intelligence Solution

The energy trading subsidiaries of St. Louis–based Ameren Corporation operate in the fast-paced environment of wholesale electricity trading. Each day, Ameren traders analyze large amounts of data on power availability and prices so they can help meet the energy needs of customers in the U.S. Midwest. In the past, the traders’ efficiency was hampered by a lack of integration between data sources and outdated tools for analyzing information. Working with Microsoft® partner Avanade, Ameren deployed a new business intelligence solution based on Microsoft Business Intelligence technologies, including Microsoft SQL Server™ 2005. With the new solution, information from disparate data sources is compiled in one location, and a new client application helps Ameren traders quickly view and analyze information to make fast business decisions that help the company’s profitability.

Situation

Our traders can view information and determine quickly whether it makes sense to increase production at a plant, or whether it’s going to be possible. It helps them be faster and more focused.  Randy Allen Technical Architect Ameren Corporation

Working through their subsidiaries, most utility companies today are as likely to be in the business of trading electricity in the wholesale marketplace as they are in selling electricity directly to customers. Ameren Corporation, which provides power to approximately 2.4 million electric customers and almost 1 million natural gas customers in the central United States, is the parent company to the AmerenEnergy and AmerenEnergy Marketing trading and marketing subsidiaries.

In early 2005, Ameren became a member of Midwest Independent Transmission System Operator (Midwest ISO), an independent nonprofit organization that supports the constant availability of electricity in 15 U.S. states and the Canadian province of Manitoba, including a 94,000-square-mile area of the Midwest. Midwest ISO administers a wholesale power marketplace where members such as AmerenEnergy and AmerenEnergy Marketing buy and sell electricity on a day-ahead or real-time spot market basis.

This type of business is extremely fast-paced. Trades happen as often as every few seconds, with bids based on demand, forecast, and where power—which is traded in “mega-watt hours”—is being added to the grid. When Ameren traders offer to sell or buy power, their bidding has to be very precise because it affects the company’s profits and its ability to fulfill power contracts. They also need to sort through a variety of data points to under-stand when it is more profitable to produce energy at one of the Ameren generation plants or purchase from the market.

To perform their complex jobs, Ameren traders need access to information coming from many different sources and markets. Every few minutes they receive data on the output of each of Ameren’s 100 generation units and how power is dispatched. Additionally, once an hour they get information from every location on the Midwest ISO network in order to forecast demand. Ameren also gets information directly from Midwest ISO on what bids have been won. This information is used by AmerenEnergy and AmerenEnergy Marketing to determine which plants will be used to supply power to the grid.

Ameren traders traditionally consolidated data from disparate sources by pulling information into Microsoft® Excel® spreadsheet software, a process that could take up to 30 minutes. The data relationships were then manually maintained.

“Our entry into Midwest ISO brought us into a very data-intensive situation,” says Joe Mehallow, Applications Development Specialist for Ameren Corporation. “Transactions were flowing back and forth rapidly, but we didn’t have a common place to pull all this information from. Our traders could not get data out of the source systems effectively, and they had a hard time relating all the different kinds of data to get a bigger picture of what it all meant.”

By the end of 2005, the traders, managers, and IT department at Ameren all realized that the company required a business intelligence solution. “We needed to give traders a centralized source of information that could tell them, ‘Here are all your market conditions at this point in time,’” says Mehallow. “Then they could use that information to make better business decisions.”

Solution

We needed to give traders a centralized source of information that could tell them, ‘Here are all your market conditions at this point in time.’  Joe Mehallow Applications Development Specialist Ameren Corporation

Ameren evaluated several business intelligence and data warehouse solutions, and determined that a solution using Microsoft Business Intelligence technologies could deliver information faster and more efficiently than other options could. Working with Avanade, a Microsoft Gold Certified Partner, Ameren built and deployed a business intelligence solution, including a data warehouse based on Microsoft SQL Server™ 2005 Enterprise Edition database software. The solution runs on the Windows Server® 2003 Enterprise x64 Edition operating system, providing high levels of performance achieved through the ability of 64-bit technology to run operations in very large amounts of memory.

To help consolidate data, Avanade created custom extraction, transformation, and loading (ETL) templates based on Microsoft SQL Server 2005 Integration Services. The templates are used as a platform for creating high-performance data integration capabilities for Ameren. The ETL templates track data from the source to the destination, while displaying all loads and activities.

Ameren has Oracle databases in use, and some of the Ameren IT staff members were initially unfamiliar with the SQL Server database tools. Avanade consultants took staffers through the process of moving data from the Oracle systems into SQL Server.

“We go into four or five data systems, profile the data, and pull it all together,” says Randy Allen, Technical Architect at Ameren Corporation. “Avanade brought in its experience from previous business intelligence projects and showed us how to do it.” He adds that it took only one afternoon of training for AmerenEnergy IT staff to begin moving data through the ETL process.

Another aspect of the project was teaching the Ameren IT department how to use the new, unified data at its disposal. Avanade helped create standard reports for monitoring ETL processes using SQL Server 2005 Reporting Services.

Avanade also helped Ameren develop an intuitive interface for traders to use. Originally Ameren wanted a portal site. However, portals sometimes experience significant time delays, and traders need their information in seconds. So Avanade assisted with the development of a Windows®-based client application called Position Report that accesses and presents data in real time.

The application was designed using Microsoft .NET Framework version 2.0, the Microsoft Visual Studio® 2005 development system, and the Microsoft Enterprise Library of application blocks designed to assist developers with common enterprise development challenges.

Position Report provides access to nearly 100 gigabytes of data that Ameren traders can view in a standard format. The information in the data warehouse is pulled from several different sources, and then transformed and stored in SQL Server. Web services are used to present updated information in Position Report to traders.

The business intelligence solution went into full production in mid-2006, after about seven months of development and testing by Ameren and Avanade. The Ameren IT department is currently refining a Web-based “report viewer” in Windows SharePoint® Services that provides traders with an additional, alternative source to access information when they are off-site or otherwise need access through the Web. The report viewer provides standardized reports with continually updated information.

Benefits

The new business intelligence solution gives Ameren traders real-time trading status by providing a unified location to store information from disparate sources, and an integrated view of information through the Position Report client application. The solution provides real-time information updates and comprehensive reporting functions, as well as a foundation for building more features in the future that can benefit Ameren traders.

The biggest benefit of the data warehouse is how it gives us the ability to pull in data from many different sources and have that data in one central location.  Joe Mehallow Applications Development Specialist Ameren Corporation

Unifies Data from Many Sources

Ameren traders now have access to a unified source of data to make decisions that affect the company’s profitability. “The biggest benefit of the data warehouse is how it gives us the ability to pull in data from many different sources and have that data in one central location,” says Mehallow, noting that the manual process that once took a trader up to 30 minutes is now completely automated. “This capability is necessary if Ameren traders are expected to make informed decisions that will affect our business.”

Allen adds that Position Report complements the data warehouse and has turned into an invaluable tool for Ameren traders. “Calling it the ‘Position Report’ is an understatement of what it does,” says Allen. “It really provides richness and depth of information on trades on an hourly basis. With this technology, our traders can view information and determine quickly whether it makes sense to increase production at a plant, or whether it’s going to be possible. It helps them be faster and more focused.”

Provides Real-Time Updates and Comprehensive Reporting

Mehallow says the combination of the SQL Server technologies and the Avanade ETL solution built on top of them delivers updated information in way that was difficult to achieve in the past. “The ETL solution from Avanade and Position Report do the work of supplying continually updated information to our traders,” Mehallow says.

With the implementation of the real-time updates, Ameren power traders can work more efficiently. That, in turn, gives them more time to sort through information such as the data in Position Report. This helps create the kind of business intelligence and expedited decision making that was not available to the company’s traders in the past.

The business intelligence solution includes comprehensive reporting functionality that helps Ameren employees understand the data that affects power trades each day. “Along with the standard reports that we provide on-site, we are also taking advantage of Windows SharePoint Services to provide data so that employees can view reports from any Web browser,” says Allen. “We’ve also found that the SQL Server reporting capabilities are easy enough for users to manage themselves. That helps reduce the amount of support required from the Information Technology department.”

Creates Foundation for Additional Uses in the Future

The initial success of the solution means expanded use in the future. “The word is getting out that we’re putting all data in one place, and other departments—like risk management, corporate planning, and generation plant managers—want to start using it,” says Mehallow.

“The Midwest ISO market is a new one for us,” he adds, “but now Ameren is improving our performance with a system that provides information in real time. Eventually we plan to expand the data coming into our ETL system and increase the use of the data with more analytical reports, which will increase our competitiveness in the market.”

For More Information

For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to:
www.microsoft.com

For more information about Avanade products and services, call (206) 239-5600 or visit the Web site at:
www.avanade.com

For more information about Ameren products and services, visit the Web site at:
www.ameren.com

Microsoft Server Product Portfolio

For more information about the Microsoft server product portfolio, go to:
www.microsoft.com/servers/default.mspx

Microsoft SQL Server 2005

Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. By providing high availability, security enhancements, and embedded reporting and data analysis tools, SQL Server 2005 helps companies gain greater insight from their business infor-mation and achieve faster results for a competitive advantage. And, because it’s part of the Microsoft server product portfolio, SQL Server 2005 is designed to integrate seam-lessly with your other server infrastructure investments.

For more information about SQL Server 2005, go to:
www.microsoft.com/sqlserver

This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Document published June 2007