System and method for enabling extract transform and load processes in a business intelligence server

A method for supporting extract, transform and load (ETL) processes, the method comprising:

• providing a business intelligence (BI) server executing on one or more microprocessors, wherein the BI server connects source tables on a source system to target tables on a target system;
• providing a plurality of data transformation metadata (DTM) objects on the BI server, wherein each of said plurality of DTM objects comprises a data transformation effecting a data shape;
• providing a user interface to said BI server;
• receiving user input via the user interface to select a plurality of selected DTM objects of the plurality of DTM objects;
• receiving user input via the user interface for manipulating the plurality of selected DTM objects in combination, to progressively build a resultant data shape;
• reading, with a code generator, the selected DTM objects manipulated by the user in combinations to progressively build the resultant data shape, and an ETL mapping association (EMA) object, and in response thereto generating, by the code generator, ETL scripts;
• executing the ETL scripts to move data from the source system to the target system by:
• extracting the data from the source tables of the source system;
• transforming the extracted data;
• and
• loading the transformed data into the target tables of the target system.

A system for supporting extract, transform and load (ETL) processes, the method comprising:

• a computer having a one or more microprocessors, and a business intelligence (BI) server executing thereon, wherein the BI server connects source tables on a source system to target tables on a target system;
• a plurality of transparent view (DTM) objects on the BI server, wherein said plurality of DTM objects each comprise a data shape using a data transformation;
• a user interface to the business server that is configured to:
• receive user input via the user interface to select a plurality of selected DTM objects of the plurality of DTM objects;
• and
• receive user input via the user interface for manipulating the plurality of selected DTM objects in combinations to progressively build a resultant data shape;
• a code generator that reads the selected DTM objects manipulated by the user in combination to progressively build the resultant data shape and an ETL mapping association (EMA) object, and in response thereto generates ETL scripts;
• wherein the system executes the ETL scripts to move data from the source system to the target system by:
• extracting the data from the source tables of the source system;
• transforming the extracted data;
• and
• loading the transformed data into the target tables of the target system.

A non-transitory computer-readable storage medium having instructions stored thereon for supporting extract, transform and load (ETL) processes, which instructions, when executed, cause a system executing on one or more microprocessors to perform steps comprising:

• providing a business intelligence (BI) server, wherein the BI server connects source tables on a source system to target tables on a target system;
• providing a plurality of data transformation metadata (DTM) objects on the BI server, wherein each of said plurality of DTM objects comprises a data transformation effecting a data shape;
• providing a user interface to said BI server;
• receiving user input via the user interface to select a plurality of selected DTM objects of the plurality of DTM objects;
• receiving user input via the user interface for manipulating the plurality of selected DTM objects in combination, to progressively build a resultant data shape;
• reading, with a code generator, the selected DTM objects manipulated by the user in combinations to progressively build the resultant data shape, and an ETL mapping association (EMA) object, and in response thereto generating, by the code generator, ETL scripts;
• executing the ETL scripts to move data from the source system to the target system by:
• extracting the data from the source tables of the source system;
• transforming the extracted data;
• and
• loading the transformed data into the target tables of the target system.