Research And Implementation Of OLAP Optimum SQL Statements Generation System Based On BI Tool

Business intelligence (BI) tool,which helps the enterprise make smart business decisions by converting the existing data to knowledge, has been widely used in the enterprises at home and abroad.BI tools SQL statement generation system (SQL-Engine) is an important component in the indispensable BI tools on-line analytical processing (OLAP) part. Due to the business characteristics in the field of business intelligence, the research on SQL-Engine is relatively less, and the relative projects and papers are very scarce.In this paper, This paper analyze the mainstream BI tools and study SQL-Engine by using the reverse engineering method.Specifically,this paper studied SQL Engine of the mainstream BI tools on the market (including MSTR,BO and COGNOS). Through the specific structure input, this paper can speculate its inner possible SQL generated internal rules by analyzing the output. On this basis, combining with database optimization theory, this paper summarize the generation rules which can optimize SQL. Firstly, this paper define a set of data model corresponding to the data warehouse concept as the input system combining with the mainstream BI tool. Secondly, this paper model systematically for the report.Thirdly, this paper design a set of SQL statements to generate the overall architecture of the system and the corresponding process,and this paper elaborate the process that SQL-Engine automatically generates SQL statements and its internal architecture.Finally, this paper implement a system based on BI tools which can generate SQL statements and meet OLAP optimization by coding, whats’s more, it is verified by testing the correctness of the system to generate SQL statements.It is verified in some scenarios by test case to prove generation of SQL statements is efficient.To sum up,the system based on BI tools which is designed and implemented by this paper can generate SQL statements and meet OLAP optimization, and it’s prove to be stable. According to the report definition input by users, it can generate the corresponding correct and efficient SQL statements.