Supporting ETL processing in BPEL-based processes

A system for executing a process flow including one or more control activities and one or more extract, transform and load activities for a data warehouse, the system comprising:

• a process control engine operable to execute activities included in the process flow, wherein the process flow is described in a process definition file, wherein the process definition file includes:
• references to local and remote extract, transform and load activities, wherein each activity includes domain specific attribute values,
• automatically generated process-scoped variables for each extract, transform and load activity, wherein the process-scoped variables hold and enable sharing of the state of each extract, transform and load activity,
• references to specific dataflow execution metadata for dataflow activities included in the process flow,
• a partner definition for each remote extract, transform and load service host, and
• global process-scoped variables for holding pre-defined message definitions for the remote extract, transform and load services;
• a data transformation subsystem operable to store domain specific definitions of extract, transform and load activities of data residing in one or more databases in a data warehouse, the data transformation subsystem further being operable to be invoked as one of:
• a local service and a remote service;
• and
• a control data repository operable to store domain specific activity information related to the process flow.

A computer-implemented method for integrating control activities and extract, transform and load activities in a process flow for a data warehouse, the method comprising:

• deploying a process definition file into a system comprising a process control engine for executing a process flow described in the process definition file, and one or more data transformation systems for performing the extract, transform and load activities, wherein the process definition file includes:
• references to local and remote extract, transform and load activities, wherein each activity includes domain specific attribute values,
• automatically generated process-scoped variables for each extract, transform and load activity, wherein the process-scoped variables hold and enable sharing of the state of each extract, transform and load activity,
• references to specific dataflow execution metadata for dataflow activities included in the process flow,
• a partner definition for each remote extract, transform and load service host, and
• global process-scoped variables for holding pre-defined message definitions for the remote extract, transform and load services;
• processing the process definition file, by the process control engine, to execute an activity described in the process definition file;
• in executing the activity, determining, by the process control engine, whether the extract, transform and load activity is a local activity;
• and
• in response to determining that the extract, transform and load activity is a local activity, invoking, by the process control engine, the extract, transform and load activity on data residing in a data warehouse through local invocation, by performing a method call to a service for local invocation in a data transformation processing subsystem.