Mining data transformation flows in spreadsheets

A computer system comprising:

• a memory;
• and
• a processor in communication with the memory, wherein the computer system is configured to perform a method comprising:
• obtaining a spreadsheet comprising operations defined in the spreadsheet and source data, in the spreadsheet, on which the operations operate, the operations defining one or more data transformations implemented through definition of formulas of the spreadsheet and execution of the formulas in a spreadsheet application environment to transform the source data thereof into results in the spreadsheet application environment;
• automatically creating, based on (i) identifying the operations and (ii) the source data in the spreadsheet, an extract, transform, load (ETL) data transformation flow in an ETL environment, the ETL environment being a different environment than the spreadsheet execution environment, the creating comprising:
• selecting, in an ETL system, one or more source data endpoints for data extraction, the one or more source data endpoints including at least some data other than the source data;
• selecting one or more target data endpoints for data loading;
• mapping at least one of the identified operations defined in the spreadsheet, the at least one of the identified operations comprising at least one spreadsheet function of the spreadsheet application environment to one or more ETL operations of the ETL system for data transformation, the one or more ETL operations of the ETL system being one or more functions of the ETL environment;
• and
• building the ETL data transformation flow, the ETL data transformation flow defining extraction from the selected one or more source data endpoints, transformation based on the one or more ETL operations, and loading to the selected one or more target data endpoints;
• and
• executing the created ETL data transformation flow, the executing performing the extraction from the selected one or more source data endpoints, including extraction of the at least some data other than the source data, transformation based on the one or more ETL operations, including transformation of the at least some data other than the source data, and loading to the selected one or more target data endpoints.

A computer program product comprising:

• a computer readable storage medium readable by a processing circuit and storing instructions for execution by the processing circuit for performing a method comprising:
• obtaining a spreadsheet comprising operations defined in the spreadsheet and source data, in the spreadsheet, on which the operations operate, the operations defining one or more data transformations implemented through definition of formulas of the spreadsheet and execution of the formulas in a spreadsheet application environment to transform the source data thereof into results in the spreadsheet application environment;
• automatically creating, based on (i) identifying the operations and (ii) the source data in the spreadsheet, an extract, transform, load (ETL) data transformation flow in an ETL environment, the ETL environment being a different environment than the spreadsheet execution environment, the creating comprising:
• selecting, in an ETL system, one or more source data endpoints for data extraction, the one or more source data endpoints including at least some data other than the source data;
• selecting one or more target data endpoints for data loading;
• mapping at least one of the identified operations defined in the spreadsheet, the at least one of the identified operations comprising at least one spreadsheet function of the spreadsheet application environment to one or more ETL operations of the ETL system for data transformation, the one or more ETL operations of the ETL system being one or more functions of the ETL environment;
• and
• building the ETL data transformation flow, the ETL data transformation flow defining extraction from the selected one or more source data endpoints, transformation based on the one or more ETL operations, and loading to the selected one or more target data endpoints;
• and
• executing the created ETL data transformation flow, the executing performing the extraction from the selected one or more source data endpoints, including extraction of the at least some data other than the source data, transformation based on the one or more ETL operations, including transformation of the at least some data other than the source data, and loading to the selected one or more target data endpoints.