What Is Xcel Energy?
Industry: Energy
Customer Site: Minnesota and Michigan, United States
Xcel Energy (NYSE: XEL) is a major U.S. electricity and natural gas company that provides a comprehensive portfolio of energy-related products and services to 3.4 million electricity customers and 1.9 million natural gas customers through its regulated operating companies in eight Western and Midwestern states.
Success Story Highlights
- End-to-end automation of Xcel's data warehousing and reporting processes
- Advantages of using ActiveBatch to schedule Informatica PowerCenter processes
- A single point of control for automating Oracle Database functions
- Increased productivity and freed overnight IT staff for other projects
Reliable, Enterprise-Wide Job Scheduling
For Xcel Energy, finding a reliable, enterprise job scheduling solution for the automation of its overnight batch processes and data warehousing workflows was the difference between working during the day or working overnight.
Xcel Energy’s nuclear division, which operates two nuclear power plants in Minnesota that produce 30 percent of the electricity provided to customers throughout the Upper Midwest, was originally using a job scheduling solution from SoftTree Technologies. After years in production, increasing workload volumes quickly started to overload it. “It got to the point where we couldn’t trust it anymore, and as a result, we had to physically monitor the overnight processes by running a second IT shift overnight,” says George Bowen, Solution Consultant at Xcel Energy.
As a result, the decision was made to implement a replacement. The new job scheduling solution would have to be robust enough to handle a hybrid Windows/UNIX environment and have direct integration with Informatica PowerCenter, Xcel Energy’s primary data warehousing solution. Having neither would result in more scripting, something Bowen was looking to avoid. CA Autosys, which is leveraged by another division within Xcel Energy for mainframe scheduling, was considered but ruled out because Autosys is a UNIX-based scheduler that would have required extensive scripting to work within a Windows environment. “The Autosys version that’s installed in-house does not provide direct support of Windows,” Bowen says. “And upgrading versions or adding a second instance of Autosys within Xcel Nuclear would have been cost prohibitive. For its price and out-of-the-box functionality, ActiveBatch was the value-added selection that was much easier to implement.”
ActiveBatch, by Advanced Systems Concepts, Inc., was selected based largely on its cross-platform functionality, which includes direct support for Linux and UNIX platforms, and on its integration with Informatica PowerCenter. The ActiveBatch Extension for PowerCenter adds a series of production-ready PowerCenter Job Steps to ActiveBatch’s Integrated Jobs Library, allowing IT organizations to build and automate workflows that automate PowerCenter ETL/data warehousing processes end-to-end without the need to rely on scripting.
Xcel Energy found ActiveBatch via the Informatica Marketplace, which is a hub for Informatica customers to find and research over 1000 data integration, data quality, automation, and other solutions provided by Informatica partners. “It provides a one-stop-shop for researching all of the partner solutions that integrate with Informatica. We might not have discovered ActiveBatch otherwise.”
Beyond PowerCenter Scheduling
ActiveBatch is now used to handle all the interfaces between Xcel Energy’s primary business applications in addition to automating PowerCenter data warehousing processes, which are dependent on data from these systems. One such application is Passport, Xcel Energy’s work order management solution that is used to schedule and manage maintenance tasks at the company’s two nuclear power plants. ActiveBatch is used to pull data from Passport via an FTP operation and execute a PowerCenter workflow to upload that information into a data repository for the business to report on via reporting services. ActiveBatch is also used to automate the cross-platform processes whereby Informatica ETL workloads are triggered based on the successful completion of batch processes that pass data and manage dependencies between platforms including Linux, UNIX, and Windows.
To build these workflows without the need to develop scripts, Bowen uses ActiveBatch’s Integrated Jobs Library and its production-ready Job Steps for PowerCenter, such as the StartWorkflow Job Step. Bowen uses the Jobs Library’s workflow designer to drag-and-drop these Job Steps into end-to-end workflows, designate key PowerCenter job criteria such as Folder and Workflow Name from auto-populating drop-down menus, and then establish job variables/data to be passed downstream to proceeding jobs. “Having to hard code that with command-line scripts via PowerCenter’s PMCMD to integrate and automate those data warehousing processes takes a lot of time and a lot of testing,” Bowen says. “That’s why finding a scheduling solution with direct integration of PowerCenter was so important.”
For Bowen, being able to dynamically trigger these workflows and manage key dependencies between systems and PowerCenter ETL workloads was the other reason he elected to leverage an enterprise automation solution rather than PowerCenter’s native scheduling capabilities. “ActiveBatch provides the flexibility and level of control that we require to automate PowerCenter and dependent processes, all from a single point of control... no scripting required.”
For example, Bowen required the ability to trigger PowerCenter processes dynamically based on more granular calendar capabilities, event management, and system dependencies. ActiveBatch’s advanced date/time scheduling, including its support of Fiscal, Business and Gregorian calendars is used to schedule PowerCenter workloads tied to business processing requirements. Alternatively, ActiveBatch’s event automation framework is used to trigger other workloads dynamically based on an IT event. ActiveBatch file triggers are used to execute the Passport/PowerCenter workflows based on a file being uploaded to a directory while other ETL processes are executed based on SQL Server database queries. Finally, Bowen leverages ActiveBatch Job Variables to automatically populate the PowerCenter workflow parameters at runtime, thereby ensuring accurate workflow execution and downstream data quality.
To manage and direct conditions and dependencies within these multi-step workflows, Bowen leverages ActiveBatch’s Flow Control Job Steps. For example, he uses the If-Branch Job Step to manage the execution of downstream jobs based on the successful completion of a preceding PowerCenter workflow. Along the same lines, users can leverage ActiveBatch to execute PowerCenter workflows based on the completion of another PowerCenter workflow stored within a separate folder, thereby allowing cross-workflow triggering within a single ActiveBatch Job without the need to write a script or use PMCMD.
Because many of these processes are scheduled overnight between datacenter maintenance windows, workflow monitoring, alerting, and error handling are critical to ensure all processes are completed within the allotted timeframe. To address SLAs tied to certain classes of jobs, runtime monitoring allows Bowen to proactively monitor a job’s progress and take action if the job is running longer than expected or based against historical average runtimes for that job. This includes using ActiveBatch’s alerting and error handling framework for certain classes of jobs that will receive an automatic restart if they fail due to a datacenter network/connectivity issue. If the job fails after three restart attempts, then an ActiveBatch SMS alert will “wake somebody up in the middle of the night,” Bowen says.
End-To-End Datacenter Automation
The ActiveBatch implementation has been expanded to also include automation of datacenter processes... what Bowen calls “housekeeping and administrative-type tasks” that would otherwise consume a large percentage of time. This includes automating file system and database table cleanups in addition to imports and exports across a series of Oracle Databases. To automate these tasks, Bowen makes use of the Oracle Database Job Steps within the Integrated Jobs Library, including Export, Import, and Start Job. These production-ready steps allow Bowen to drag-and-drop them into a workflow, use auto-populating drop down menus to designate job criteria such as data source, database credentials, directory and file names and synchronize the execution of these database tasks across a range of Oracle Databases from a single interface.
A series of database Flow Control Job Steps within the Integrated Jobs Library allows Bowen to manage the data and dependencies within the Oracle Databases dynamically. For example, for the ActiveBatch workflows that Bowen has built to clean database tables, he uses the ForEachRow Job Step to designate which rows of data from within the database table should be loaded, retrieved or passed downstream within the workflow to another database. The automation of these Oracle Database processes has reduced the time spent managing these tasks manually by over 80%, according to Bowen.
ActiveBatch’s ability to execute PowerShell scripts has also allowed Bowen to move the automation of several housekeeping tasks within their Windows environment into ActiveBatch. These PowerShell scripts tackle nightly chores including archiving, file management, directory cleanup, and more that had previously been executed via Task Scheduler.
The Benefits
In addition to the time saved, Xcel’s IT organization has been able to completely eliminate the overnight shift that managed the Informatica workflows manually, thus freeing those IT professionals to work other shifts and focus on other projects, "making for happier employees,” Bowen says. “ActiveBatch has been an invaluable addition to our IT inventory and is the tool that allows us to sleep
well at night.”