Data warehouse automation: Unlock speed, efficiency and visibility

Data centers are exceedingly busy, and managing data warehouse operations poses significant challenges. With the complexity of data sources, the importance of maintaining data quality, the need for efficient data modeling and the demand to consistently execute SQL queries, IT teams often struggle with time-consuming tasks. 

Add the pressure to keep up with trends in real-time data, enterprise-wide data integration and streamlined decision-making processes, and the strain on traditional data warehousing teams is bigger than ever.

Intentional workload automation offers a solution to minimize reliance on manual coding and enhance adaptability.

Benefits of data warehouse automation (DWA)

Today’s intelligent automation solutions have the power and flexibility to juggle the needs of virtually any traditional data warehouse operation, regardless of complexity. Tested logic and drag-and-drop convenience streamline data processes, allowing staff to design, build, implement and monitor workflows faster, more reliably and with less custom scripting, increasing time to value.

Let’s look at some use cases that demonstrate the positive impact of workload automation on enterprise data warehouse operations.

Optimizing ETL processes with a single job scheduler

According to Gartner, on average, most IT organizations have three to eight different scheduling and automation tools to learn and maintain. This siloed approach often leads to inefficiencies and operational challenges. 

Adopting a unified cross-platform workload automation solution remedies this issue by seamlessly accommodating the diverse array of data sources, applications and environments, including on-premises, cloud-based and virtual setups and enabling streamlined management of IT operations while enhancing adaptability and efficiency across the organization’s data infrastructure.

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 can pull data from its in-house work order management app 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.

Decreasing dependence on custom scripts

The pre-built job steps and templates in some workload automation solutions can significantly reduce coding time — by as much as half in many cases. These convenient features make automation development accessible for business users and to target specific business needs.

“[Our batch processes] are dynamic, constantly changing,” noted Senior Director of 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 to just four or five.

Speeding up workflow design and reducing repetitive tasks

Lamar Advertising, one of the largest outdoor advertising companies in North America, uses workload automation to ease workloads for busy in-house developers. Using a built-in 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.

Building uninterrupted workflows with 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 implementing event-based triggers, the center eliminated downtime between jobs. It allows the hospital to execute and manage multi-job workflows based on IT events such as a file constraint or a file being received, ensuring that jobs don’t run until feeder systems are fully updated.

Enhancing data platforms with third-party integrations and connectors

Workload automation with data warehouse automation tools provides 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 sets, 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 pre-built job steps for Informatica Cloud and PowerCenter, MacDonald implemented a four-phase program that now runs substantially more complex workflows that involve three to four times the volume of data (as many 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 multi-dimensionally 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.”

See Peter MacDonald discuss how his organization reduced data processing workflow times by more than 93% while improving reliability and adapting faster to changing business and IT conditions:

What every Informatica user needs to know about data management automation

Ready to simplify your data warehousing with workload automation?

Schedule a demo to watch our experts run jobs that match your business requirements in ActiveBatch. Get your questions answered and learn how easy it is to build and maintain your jobs.

Data automation FAQs