System and method for universal format driven data transformation and key flex fields in a analytic applications environment

A method for providing a universal format driven data transformation in an analytics application environment, comprising:

• providing, at a computer including a processor;
• a target database instance comprising a target data model;
• an extract, transform, load (ETL) server, the ETL serving performing a ETL process on a source database comprising a source dataset, the source dataset being associated with a set of metadata, the ETL process being performed based at least upon the set of metadata and the data model, wherein the ETL server accesses a transformation format file, wherein the transformation format file defines a data driven transformation format that indicates source data to extract from the source dataset, a structure of a data warehouse that the source data of the source dataset will be transformed into, and one or more universal format driven transform instructions, wherein the transformation format file comprises a mapping, wherein the mapping is constructed based at least upon the target data model;
• configuring the transformation format file further with one or more editable variables, wherein the variables are editable in a readable format;
• and
• defining a plurality of keywords within the transformation format file, the plurality of keywords defining a target table type, a dataset list, and dataset operations to be performed on the dataset list.

A non-transitory computer readable storage medium having instructions thereon for providing a universal format driven data transformation in an analytics application environment, which when read and executed cause a computer to perform steps comprising:

• providing, at a computer including a processor;
• a target database instance comprising a target data model;
• an extract, transform, load (ETL) server, the ETL serving performing a ETL process on a source database comprising a source dataset, the source dataset being associated with a set of metadata, the ETL process being performed based at least upon the set of metadata and the data model, wherein the ETL server accesses a transformation format file, wherein the transformation format file defines a data driven transformation format that indicates source data to extract from the source dataset, a structure of a data warehouse that the source data of the source dataset will be transformed into, and one or more universal format driven transform instructions, wherein the transformation format file comprises a mapping, wherein the mapping is constructed based at least upon the target data model;
• configuring the transformation format file further with one or more editable variables, wherein the variables are editable in a readable format;
• and
• defining a plurality of keywords within the transformation format file, the plurality of keywords defining a target table type, a dataset list, and dataset operations to be performed on the dataset list.

A method for supporting the use of key flex field in analytics application environment, comprising:

• providing, at a computer including a processor,
• an extract, transform, load (ETL) server;
• a source dataset associated with a tenant, the source dataset comprising key flex fields (KFF) comprising a plurality of segments, wherein the ETL server performs an ETL process on the source dataset using a transformation format file that defines a data driven transformation format that indicates source data to extract from the source dataset, a structure of a data warehouse that the source data of the source dataset will be transformed into, and one or more universal format driven transform instructions, and
• the data warehouse comprising a first storage schema and a second storage schema, the second storage schema being associated with the tenant;
• extracting from the key flex fields segment label names, corresponding segment numbers, value sets and data values;
• joining the segment label names, corresponding segment numbers, value sets, and data values to form a joined table, the joined table comprising a mapping data and tenant data;
• loading the joined table at the storage second schema;
• loading the mapping data at the first storage schema;
• and
• dynamically creating reports for multiple tenants based on the mapping data stored at the first storage schema.