Data Warehouses: Where Workload Automation Really Shines

Wed, Jun 13, 2018 @ 10:06 AM

Data Warehouses: Where Workload Automation Really Shines

Data centers are exceedingly busy places. In many centers it’s not unusual for hundreds of data warehousing processes, involving millions of rows of data, to be executed every day. Add in the difficulties presented by the shortage of qualified IT personnel—a situation caused by the rate of growth in IT computing resources, budgets not keeping pace with the rate of growth, and skilled IT resources being harder to find and retain—and you have the formula for a stressful environment.

The answer to gaining data warehouse control and visibility is automation—and here’s why. Today’s intelligent automation platforms have the power and flexibility to handle virtually any data warehouse operation, regardless of complexity. The tested logic and drag-and-drop convenience of Workload Automation solutions allow staff to design, build, implement, and monitor workflows faster, more reliably, and with less custom scripting.

Let’s look at the positive impact of Workload Automation on data warehouse operations as experienced by actual users:

Simplicity of a single job scheduler.
According to Gartner, most IT organizations have, on average, three to eight different scheduling and automation tools to learn and maintain. This siloed approach adds complexity. One cross-platform Workload Automation solution, by contrast, will support the heterogenous mix of operating systems, applications, processes, and combinations of physical, virtual, and cloud-based resources present in the IT environment.

Minnesota-based Xcel Energy employs such a solution. It operates a hybrid Windows/UNIX environment and, among many other tools, uses Informatica PowerCenter to manage its ETL tasks. With its comprehensive Workload Automation solution, Xcel Energy is able to pull data from its in-house work order management application via an FTP operation, then execute an Informatica PowerCenter workflow to upload that information into a data repository for reporting purposes, managing it all from a single platform.

Reduce—or even eliminate—custom scripting. The prebuilt job steps in Workload Automation solutions can significantly reduce coding time—by as much as half, in many cases. “[Our batch processes] are dynamic, constantly changing,” noted the senior director for IT for The Retail Equation, a “big data” retail analytics provider. “Using a script-driven solution added a lot of man hours to building and managing these workflows.” By replacing its script-driven job scheduler with an automation platform, The Retail Equation’s IT department now spends less than 5% of its time building and managing batch workloads. In one case, it was able to reduce the number of job steps required to execute its nightly SFTP/FTPS file system processes from 131 individual steps to just 4 or 5.

Speed up workflow design. Lamar Advertising, one of the largest outdoor advertising companies in North America, uses Workload Automation to ease workloads for busy in-house developers. Using the automation platform’s workflow designer, Lamar can use templated job steps for virtually every scheduled job, assembling workflows with drag-and-drop simplicity. “Any time we can take an IT assignment out of the development area, that’s a plus,” notes the company’s MIS operations manager. Lamar currently runs 10 to 12 thousand data warehousing, ETL, and accounts receivable jobs each day, with a 99% success rate.

Dynamic event-driven triggers. Standard date/time job triggers have serious limitations, as Children’s Hospital & Medical Center of Omaha can attest. “A job may take one hour one day and 20 minutes on another—yet we would have to schedule a ‘worst-case scenario’ each time,” said the center’s ETL architect. By moving to the event-based triggers in his automation solution, the center was able to eliminate downtime between jobs altogether. It allows the hospital to execute and manage multi-job workflows based on IT events such as a file constraint or file being received, ensuring that jobs aren’t run until feeder systems are fully updated.

Third-party integration. Workload Automation can provide a host of benefits—especially for those using third-party tools and products like Informatica PowerCenter and Informatica Cloud.

In his role as a senior IT architect for one of the world’s largest biotech companies, Peter MacDonald utilizes Informatica PowerCenter to manage data warehouse operations. At one time, MacDonald’s financial warehouse processes were not automated—and it took up to 12 hours each day to extract MDM data, execute PowerCenter workflows, administer database processes from Informatica mappings, and build Hyperion cubes.

“Clearly, we needed to replace manual steps with automation,” MacDonald stated. After moving to a workload automation solution that provided prebuilt job steps for Informatica Cloud and PowerCenter, MacDonald implemented a four-phase program that now runs workflows of substantially increased complexity, involving three to four times the volume of data (as much as 90 million rows per day), in just 45 minutes.

“Done well, IT Automation drives progress and brings value across the business,” notes MacDonald. “It increases visibility and scales multidimensionally to increase speed, efficiency, and accuracy. It even reduces IT labor, since the intuitive user interface allows business analysts to do scheduling, instead of IT.”

To see Peter MacDonald discuss how his organization has reduced data processing workflow times by more than 93% while improving reliability and adapting faster to changing business and IT conditions, watch this video:

What Every Informatica User Needs to Know About Data Management Automation

 

Katti Wolfe

Written by Katti Wolfe

Interested in Writing a Blog?

Lists by Topic

see all

Featured Resource

Shift Left! A Look At Automation As Prologue, Not Postscript