Software management solutions are about automating business and IT operational processes within a single framework, so customers can lower their cost of operations while increasing IT agility. These companies help simplify the complexity of large projects/tasks and facilitate team collaboration. Features include resource and staff management, coordinating schedules, company budgets, task assignments, and time and risk analysis.
Subway is the world's largest submarine sandwich chain with over 37,000 locations worldwide. Subway, but it was struggling to manage multiple dependencies across different systems and find a way to unify and automate its batch processes.
The team needed a system to manage the relationships among the tasks, which determine the order that activities are performed between Sequel Server Integration Services (SSIS) and Data Transformation Services (DTS) jobs. The reason: They couldn't run the data warehouse load until the OpenVMS and .NET platform was populated and synchronized. The database team was having problems with this time-consuming process of manual handoffs, which made it difficult to manage dependencies between technologies.
"We were relying on a disparate collection of Structured Query Language (SQL) servers to schedule SSIS and DTS packages to integrate a legacy OpenVMS system, a newer .NET/SQL platform and a data warehouse solution. The ability to dynamically manage the dependencies between SSIS and DTS jobs was critical," says Leslie Kosturko, senior data manager at Subway.
According to Kosturko, automating SQL Server jobs across different machines was difficult because the SQL Server Agent provided no way to manage dependencies between different servers or start/stop a job within a workflow. As a result, the database team used a lot of manual flag settings to report status and control job flow. "We determined that a change was necessary, so we began looking for an automated solution that would provide a centralized view of these critical workflows and dependencies, as well as minimize manual flag settings and intervention," adds Kosturko.
Subway chooses a tech solution: ActiveBatch
Subway wanted an enterprise scheduling tool that provided easier methods to orchestrate its data loads and develop the necessary dependencies and alerts. Primary considerations included easy to use, crossplatform support, and competitive pricing. The company also considered Microsoft Orchestrator but, in the end, the team chose Advanced Systems Concepts (ASCI) ActiveBatch because of the flexible pricing/licensing model and ActiveBatch's Integrated Jobs Library, which provided the team with a graphical interface of workflows and dependencies between jobs.
"Before implementing ActiveBatch, Subway was using a group of SQL servers to schedule SSIS and DTS packages to integrate business processes with a legacy data warehouse solution--an OpenVMS System--and a newer .NET/SQL platform," says Jim Manias, vice president of ASCI.
Manias explains that Subway purchased ActiveBatch to overcome the challenges of its data warehousing process with a solution that would enable better end-to-end automation.
ActiveBatch gave the team cross-platform support, a centralized control pane, and high value in comparison to other magic quadrant automation solutions. Specifically, the Integrated Jobs Library intrigued Subway because it provided a way for the team to more easily develop workflows without having to rely on time-consuming, custom scripting.
"For cross-platform automation, ActiveBatch has dozens of key integrations for a broad range of technologies, applications, and databases including Informatica PowerCenter, Microsoft Dynamics AX, SQL Server, Teradata, and more. ActiveBatch's innovative Integrated Jobs Library provides users with over 160 production-ready jobs steps including workflow logic, which facilitates the building of end-to-end workflows across disparate systems, while also reducing the need for custom scripting," says Manias.
How ActiveBatch was installed and deployed
According to Kosturko, ActiveBatch was installed on a standalone, virtual server that orchestrates jobs across many disparate servers, located across multiple data centers that consist of virtual memory systems (VMS), SQL Server (multiple versions), and Teradata. Currently, Subway's data architects and Extract, Transform, Load (ETL) developers establish the ActiveBatch plans and jobs; however, alerts and notifications are sent to network administrators and database administrators when jobs fail or encounter problems. Restarting a job from a particular point is quite useful. The initial task of how to set up the jobs requires planning and should not be considered trivial in a complex environment. These steps include decisions regarding the level of granularity plus how other tools such as SSIS should be incorporated.
"The primary users are Subway's database team," says Manias, "Which is comprised of roughly 15 people. The team dynamically manages its workflows from a central console based on event triggers such as a database update or a File Transfer Protocol (FTP) that's sent or received. The team reported that, so far, ActiveBatch has required 75 percent less time than the SQL Server Agent, in terms of building workflows, updating existing ones, time saved from manually managing dependencies, and more reliable execution of processes."
How the ActiveBatch deployment benefits Subway
Since the deployment, Subway can handle the complexity of its data loads much more efficiently. The team can dynamically manage workflows and dependencies from a central console. And, because they divided these complex workflows into 'manageable chunks,' they can now manage based on event triggers--something they completely lacked when scheduling via SQL Server.
"We can also reexecute pieces of the workflow, if necessary, as opposed to rerunning the entire workflow from the start, which greatly reduces the amount of time to troubleshoot and address problems as they arise. The option to troubleshoot an issue from a central console instead of weeding through several SQL Servers to find the offending job saves valuable time," adds Kosturko.
Tips and advice to other companies
Kosturko advises other companies to be prepared and plan everything up front. For example, establishing internal standards for development such as what granularity the jobs will have, what other tools will be utilized, how alerting will be established, and how the environment will be organized are key to a successful deployment. "We initially separated our development and production environments by utilizing variables and organizing the environment accordingly. This was inadequate when we needed to test out system upgrades or when making necessary system wide changes such as updated drivers."
Manias notes that organizations facing similar challenges, especially when automating data warehousing processes or other projects across different platforms and technologies, can benefit from employing an architectural strategy to IT automation. He suggests looking for a solution that centralizes disparate technologies, databases, and applications, and then dynamically manages the workflows and dependencies between those disparate pieces. Organizations like Subway who use Teradata or other database appliances would also benefit from finding a solution that integrates with Teradata and provides templates with proven logic to enable better end-to-end automation of workflows.
"All organizations have scripts," says Manias, "So protect your investment, use what you already have, while simultaneously enhancing it with tools such as lifecycle management and script vaulting. Additionally, use reference templates so teams can create a master object for reuse without copying and pasting; thus, only requiring changes once at the template level, which are automatically passed down to each reference. This creates a better, more manageable environment with fewer objects to maintain; thereby, making it easier to develop workflows that reduces Total Cost of Ownership (TCO) and increases agility."
Join the CIO Australia group on LinkedIn. The group is open to CIOs, IT Directors, COOs, CTOs and senior IT managers.