Research And Application Of ETL Code Generation Approach Based On Model Transformation

The core content of Model-Driven Architecture(MDA)is the model-to-model transformation.Also,it provides the basis for model-to-code generation and code automation.Extract-Transform-Load(ETL)is the most important part of the data warehouse,providing the data warehouse with the necessary integration data.Common Warehouse Metamodel(CWM)contains Relational Metamodels and Multidimensional metamodels which provide data source representations for ETL transformations.In order to solve the problem that the ETL code generated by the relational data source to the multidimensional data warehouse meets the requirements of different database management platforms,an ETL code generation method based on model transformation is proposed.This method for achieving design reuse and improving ETL development efficiency has significance.First of all,this thesis studies the model,the model representation method,the model transformation principle and the transformation rules in the MDA development process.Using the model of MDA representing model,relational models are used to represent relational schemas,relational schemas are used to describe structured data sources,multidimensional metamodels are used to represent multidimensional schemas,and multidimensional schemas are used to describe target data warehouses.Secondly,the method of formalizing the data model is given.The relationship between the meta-meta element and the meta-metal element of the relational metamodel and the multidimensional metamodel is expressed as a concept and a role by using the Terminological Box(TBox)of the description logic language SHIQ.The Assertion Box(ABox)is used to represent the relational pattern and multidimensional data schema,forming a knowledge base.Then,relational algebra is used to represent the semantics of the ETL conversion function,and the object of the relational algebra is formally represented,so that the ETL conversion function has clear semantics.The Business Process Modeling Notation(BPMN)is used as the background to build the platform-independent model of the ETL process.The constructed ETL process model generally includes the business control chart and the data conversion diagram,and the data transformation diagram generates the relational data type.Through the MDA model to code conversion method,define the ETL conversion function to generate the code rules of the specified platform.The relational expression is invoked by the control flow provided by BPMN to implement the generation of the relational expression to the ETL code.Finally,the method of ETL code generation proposed in this paper is applied to the sales scenario of the product,and Structured Query Language(SQL)code is described by the template generation language(GTL),and the developed GTL template is placed in the PowerDesigner experimental platform to implements the SQL code in the ETL process automatically generated.Finally,the generated code is executed in the Oracle database,which verifies the validity of the method.